This is the entire question. do part a and b Consider a database schema with fou

Question

This is the entire question.

do part a and b

Consider a database schema with four relations: SUPPLIER, PRODUCT, CUSTOMER, and CONTRACTS. Both the SUPPLIER and the CUSTOMER relations have the attributes Id, Name, and Address. An Id is a nine-digit number. PRODUCT has PartNumber (an integer between 1 and 999999) and Name. Each tuple in the CONTRACTS relation corresponds to a contract between a supplier and a customer for a specific product in a certain quantity for a given price. a. Use SQL DDL to specify the schema of these relations, including the appropriate integrity constraints (primary, candidate, and foreign key) and SQL domains. b. Specify the following constraint as an SQL assertion: there must be more contracts than suppliers.

Explanation / Answer

a)

CREATE TABLE Customer (

ID int NOT NULL,

Name varchar(255) NOT NULL,

Address varchar(255),

PRIMARY KEY (ID)

);

CREATE TABLE Supplier (

ID int NOT NULL,

Name varchar(255) NOT NULL,

Address varchar(255),

PRIMARY KEY (ID)

);

CREATE TABLE Product (

PartNumber int NOT NULL,

Name varchar(255) NOT NULL,

PRIMARY KEY (PartNumber)

);

CREATE TABLE Contract (

SupplierID int,

CustomerID int,

PartNumberID int,

Quantity int,

Price float,

FOREIGN KEY (SupplierID) REFERENCES Supplier(ID),

FOREIGN KEY (CustomerID) REFERENCES Customer(ID),

FOREIGN KEY (PartNumberID) REFERENCES Product(PartNumber)

);

b)

create assertion greater-constraint check

(not exists ( select count(*) from Contracts > select count(*) from Supplier))

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

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