Homework 3:

3.9 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.

Solution:
	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 defined identically. The domain Products is similar,
except that 999999 is used instead of 999999999.

NOTE: 

+ Instead of creating the domain for suppliers, one can use insert the 
constraint into the command for creating the table as follows:

	CREATE TABLE Supplier (Id INTEGER,			 
		Name CHAR(20),
		Address CHAR(50),
		PRIMARY KEY (Id),
		CHECK (Id =< 999999999 AND Id > 0))

The same can be done for other attributes. The difference is that one 
can not use 'Customer' as a domain in other DDL commands.

+ Oracle does not implement 'CREATE DOMAIN'. This means that if one wants
to enforce this type of constraints, one needs to use this alternative.


b. Specify the following constraint as an SQL assertion: there must be 
more contracts than suppliers.
Solution:
		CREATE ASSERTION ContractsShaltExceedSuppliers
			CHECK ( (SELECT COUNT(*) FROM Supplier)
				< (SELECT COUNT(*) FROM Contract) ))

NOTE: Again, Oracle does not implement 'CREATE ASSERTION'. Therefore,
one needs to insert the above CHECK statement as a constraint of the
table CONTRACT.

3.10 You have been hired by a video store to create a database for tracking 
DVDs and videocassettes, customers, and who rented what. The database 
includes these relations: RentalItem, Customer, and Rentals. Use SQL DDL 
to specify the schema for this database, including all the applicable 
constraints. You are free to choose reasonable attributes for the first two 
relations. The relation Rentals is intended to describe who rented what and 
should have these attributes: CustomerId, ItemId, RentedFrom, RentedUntil, 
and DateReturned.

Answer: This is fairly similar to 3.9. 

a. Customer relation: we need the information about customers so that we 
can track them down when they did not pay or hold the videos too long etc.
So, name and address are necessary; but we need to have a way to identify
them and name and address migh be the same; so, we assign for each customer
an id. So, for the Customer relation, the 
minimal information needed is (name, address, CustomerId).

We use 

	CREATE TABLE Customer (Id INTEGER,
		Name CHAR(20),
		Address CHAR(50),
		PRIMARY KEY (Id))

NOTE: Since customers need to pay for their rentals and might own the store
late fees, we could have add another attribute for this purpose; let say, 
we add 'Balance' as an attribute to this relation, the DDL statement to 
create this relation is

	CREATE TABLE Customer (Id INTEGER,
		Name CHAR(20),
		Address CHAR(50),
		Balance FLOAT, 
		PRIMARY KEY (Id))

b. RentalItem relation: a video store might have several items for rental 
(of course:-) For each item, we might need a description and a type (for 
example whether it is a CD, DVD, or a VHS). A store might have several 
copies of the same item, so it may be good to add an attribute for storing 
the item number. It is also good to mark the copy as 'available' or not.

	CREATE TABLE RentalItem (Id INTEGER,
		No INTEGER, 
		Description CHAR(50),
		Status      BOOLEAN, 
		PRIMARY KEY (ItemId, ItemNo))

c. Rental relation: this can be created as stated in the problem statement
with the following exception:

	"Because we introduce ItemNo to the RentalItem relation,
	 the Rental relation should also has this attribute"

	CREATE TABLE Rental (CustomerId INTEGER, 
		ItemId INTEGER, 
		ItemNo INTEGER,
		RentedFrom DATE, 
		RentedUntil DATE,
		PRIMARY KEY (CustomerId, ItemId, ItemNo), 
		FOREIGN KEY (CustomerId) REFERENCES Customer(Id)
			ON DELETE NO ACTION
			ON UPDATE CASCADE,
		FOREIGN KEY (ItemId, ItemNo) REFERENCES RentalItem(Id, No)
			ON DELETE NO ACTION
			ON UPDATE CASCADE
		)

Constraints: there are several constraint that can be added to the schema;
the minimal requirements should be:

+ A common practice in nowaday systems is to have Ids with the same length;
  So, one might want to specify a range for the Ids. For example,
  one can have 
  	CustomerId to have values between 100000000 and 999999999 
        RentalItemId to have values between 1000 to 9999
	RentalItemNo should have value >= 1

+ Balance should be a non-negative number 

+ RentedFrom should be less than RentedUntil

3.11 You are in a real estate business renting apartments to customers. 
Your job is to define an appropriate schema using SQL DDL. The relations 
are 
	Property(Id, Address, NumberOfUnits), 
	Unit(ApartmentNumber, PropertyId, RentalPrice, Size),
	Customer (choose appropriate attributes), 
	Rentals (choose attributes; this relation should describe who 
		rents what, since when, and until when), and 
	Payments (should describe who paid for which unit, how much, and when).

Assume that a customer can rent more than one unit (in the same or different 
properties) and that the same unit can be co-rented by several customers.

Solution: 

The SQL DDL are given below

	CREATE TABLE Property (Id INTEGER,
		Address      CHAR(50),
		NumberOfUnits INTEGER, 
		PRIMARY KEY (Id))

	CREATE TABLE Unit (ApartmentNumber INTEGER,
		PropertyId      CHAR(50),
		RentalPrice 	INTEGER, 
		Size		INTEGER,
		PRIMARY KEY (ApartmentNumber, PropertyId),
		FOREIGN KEY (PropertyId) REFERENCES Property(Id)
		)

NOTE: You should recognize that the key of Unit is 
	(ApartmentNumber, PropertyId) and not just ApartmentNumber. 
      This is because several properties might use the same number, say 1, 
      for an apartment. 

	CREATE TABLE Customer (Id INTEGER,
		Name CHAR(20),
		Balance INTEGER, 
		PRIMARY KEY (Id))

NOTE: A Customer rents an appartment, which has its own address, 
      so there is no need to store his/her address. In practice, we could 
      store his/her previous address; office address; phone number; 
      or credit card number; etc. 

	CREATE TABLE Rentals(CustomerId INTEGER,
		ApartmentNumber INTEGER,
		PropertyId      CHAR(50),
		RentedSince	DATE,
		RentedUntil	DATE, 
		PRIMARY KEY (CustomerId, ApartmentNumber, PropertyId),
		FOREIGN KEY (CustomerId) REFERENCES Customer(Id), 
		FOREIGN KEY (ApartmentNumber, PropertyId) 
			REFERENCES Unit (ApartmentNumber, PropertyId)
	) 

NOTE: one can add constraint about rental date such as 
		RentedSince <= RentedUntil

	CREATE TABLE Payments(CustomerId INTEGER,
		ApartmentNumber INTEGER,
		PropertyId      CHAR(50),
		Amount 		INTEGER, 
		PRIMARY KEY (CustomerId, ApartmentNumber, PropertyId),
		FOREIGN KEY (CustomerId) REFERENCES Customer(Id), 
		FOREIGN KEY (ApartmentNumber, PropertyId) 
			REFERENCES Unit (ApartmentNumber, PropertyId)
	) 

NOTE: You should recognize that both Rentals and Payments should include 
attributes that reference (ApartmentNumber, PropertyIdi). Furthermore, 
 neither ApartmentNumber, PropertyIdi nor CustomerID should be a key in 
these relations because of the statement "a customer can rent more than 
one unit (in the same or different properties) and that the same unit 
can be co-rented by several customers."

One can add the constraint saying that the total amount paid for a unit 
should be greater than or equal its RentalPrice; how to do it, I leave it 
for you.