Consider a database schema with four relations: SUPPLIER, PRODUCT, CUSTOMER, and

Question

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

Answer a

CREATE TABLE SUPPLIER
(ID int(9) NOT NULL,
NAME varchar(30) NOT NULL,
ADDRESS varchar(50),
CONSTRAINT p_supplier PRIMARY KEY (ID)
);

CREATE TABLE CUSTOMER
(ID int(9) NOT NULL,
NAME varchar(30) NOT NULL,
ADDRESS varchar(50),
CONSTRAINT p_customer PRIMARY KEY (ID)
);

CREATE TABLE PRODUCT
(PART_NUMBER int(6) NOT NULL,
NAME varchar(30) NOT NULL,
CONSTRAINT p_product PRIMARY KEY (ID)
);

CREATE TABLE CONTRACTS
(ID int NOT NULL,
SUPPLIER_ID int(9) NOT NULL,
CUSTOMER_ID int(9) NOT NULL,
PRODUCT_ID int(6) NOT NULL
CONSTRAINT p_contracts PRIMARY KEY (ID),
CONSTRAINT f_supplier FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER(ID),
CONSTRAINT f_customer FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(ID),
CONSTRAINT f_contract FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PART_NUMBER)
);

Answer b

CREATE ASSERTION a_checking
CHECK ((SELECT COUNT(*) from CONTRACTS) > (SELECT COUNT(*) from SUPPLIER));

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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