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

create table product(partNumber number not NULL PRIMARY KEY CHECK(portNumber<=999999), name varchar2);

create table supplier(id number(9,0) not NULL PRIMARY KEY CHECK(id<=999999999 and id>=100000000), name varchar2,address varchar2);

create table customer(id number(9,0) not NULL PRIMARY KEY CHECK(id<=999999999 and id>=100000000), name varchar2,address varchar2);

create table contracts(id number not NULL PRIMARY KEY, custId number FOREIGN KEY REFERENCES customer(id), supplierId number FOREIGN KEY REFERENCES supplier(id), partNumber number FOREIGN KEY REFERENCES product(partNumber), qty number not NULL, price number(8,2) not NULL);

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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