Assigment 3

Assigment 3 (Due September 12, 11:49 pm)

Use the class time to work on the three problems 3.9, 3.10, and 3.11 (pg 67)

Homework 3 (60 points) - there are extra points.

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.

* Correct answer: 15 points (3, 3, 3, 6 for the relations); missing key -1; missing foreign key -1; missing constraints -1; because Oracle does not allow the creation of domains, the inclusion of the constraints will be sufficient for grading.

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.

* Correct answer: 5 points; again, two alternatives can be used.

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: (Note: This is fairly similar to 3.9.) (20 points)

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 may be the same; so, we assign for each customer an id. So, for the Customer relation, the
minimal information needed is (name, address, Id) with Id as its key.

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))
Correct answer: 4 points

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))

Correct answer: 4 points

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
)

Correct answer: 6 points

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

+ A common practice in nowadays 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

Each constraints (correctly specified in the SQL DDL) can get 1 point.

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))
4 points

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

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))
4 points

NOTE: A Customer rents an apartment, 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)
)
8 points

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)
)
8 points

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.