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 speci?c product in a certain quantity for a given price.

Question

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.

Answer 1

a. ```
CREATE TABLE Supplier (
Id Suppliers,
Name CHAR(20),
Address CHAR(50),
PRIMARY KEY (Id) )
```
```
CREATE TABLE Customer (
Id Customers,
Name CHAR(20),
Address CHAR(50),
PRIMARY KEY (Id) )
```
```
CREATE TABLE Product (
PartNumber Products,
Name CHAR(50),
PRIMARY KEY (PartNumber) )
```
```
CREATE TABLE Contract (
Customer Customers,
Supplier Suppliers,
Product Products,
Quantity INTEGER,
Price INTEGER,
PRIMARY KEY (Customer, Supplier, Product),
FOREIGN KEY (Customer) REFERENCES Customer(Id)
ON DELETE NO ACTION
ON UPDATE CASCADE,
FOREIGN KEY (Supplier) REFERENCES Supplier(Id)
ON DELETE NO ACTION
ON UPDATE CASCADE ),
FOREIGN KEY (Product) REFERENCES Product(PartNumber)
ON DELETE NO ACTION
ON UPDATE CASCADE )
```
```
CREATE DOMAIN Suppliers INTEGER
CHECK ( VALUE =< 999999999 AND VALUE >0)
```
The domain Customers is de?ned identically. The domain Products is similar, except that 999999 is used instead of 999999999.

b.
```
CREATE ASSERTION ContractsShaltExceedSuppliers
CHECK ((SELECT COUNT(*) FROM Supplier)
< (SELECT COUNT(*) FROM Contract)))
```