Question 2 (15 points) The following Relational Schema is written in SQL/DDL to

Question

Question 2 (15 points) The following Relational Schema is written in SQL/DDL to create a database to represent data about Suppliers who supply Parts to Projects. A Supplier supplies parts for only one Project. A Part may be supplied by several Suppliers for several Projects. A Project may be supplied by several Parts from several Suppliers. A Supplier is identified by both Fname and Lname. A Part is identified by Pno. A Project is identified by Jno. The database also stores the Quantity (an integer value between 1 and 99) of each Part supplied by each Supplier to each Project. Complete the following schema by adding all the necessary Integrity constraints and writing the complete schema of table Supply.

Create Table Supplier

( Fname VARCHAR2(15),

Lname VARCHAR2(15),

……………………………………………………,

…………………………………………………………………………………………………………………………………

);

Create Table Part

( Pno CHAR(5) ……………………………………………………………………………………………………..,

…………………………………………………………

);

Create Table Project

( Jno Number(2) ……………………………………………………………………………………………………,

………………………………………….

);

Create Table Supply

(………………………………………………………………………………………..,

………………………………………………………………………………………..,

………………………………………………………………………………………..,

……………………………………………………………………………………….., …………………………………………………………………………………………………………………………………..

.................................................................................................................

………………………………………………………………………………………………………………………………….. …………………………………………………………………………………………………………………………………. …………………………………………………………………………………………………………………………………. ………………………………………………………………………………………………………………………………….

);

………………………………………………………………………………………………………………………………… ……………………………………………………………………………………………………………………………….. ………………………………………………………………………………………………………………………………..

Explanation / Answer

Create Table Supplier

(
SID primary key,
Fname VARCHAR2(15) NOT NULL,

Lname VARCHAR2(15) NOT NULL,
project_name varchar2(20),

city varchar(10) NOT NULL;

)

Create Table Part

( Pno CHAR(5) primary key,

Color int NOT NULL,
Weight real NOT NULL,
Quantity int NOT NULL,

);

Create Table Project

( Jno Number(2) primary key,

SID int NOT NULL;
pno char(5);
Quantity int NOT NULL;

);

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.