DATABASE I
Winter 2014
Assignment 1

Question I:

Considerations:

1. A CHECK CONSTRAINT has to be created in the rent table in order to ensure that the customer is only allowed to rent a scooter between 8 hours and 1 month. I wouldn't add this same principle to the 50 scooters, 50 stations, the 3 shops or the 50 stations, because I believe that this wouldn't be beneficial in any way, also in the future this numbers can easily change, and adding a constraint wouldn't be practical.
2. An ON DELETE CASCADE clause was added to the foreign key of address to ensure that if a customer is deleted from the system all addresses referencing this customer will also be deleted. This was also applied to all the tables that have a reference to the Company table.
3. Customer table have a relationship of one to 2 with the address table.

Question II:

Part I:

CREATE TABLE Customer( Customernumber INT, Lastname VARCHAR(30), Firstname VARCHAR(30) NOT NULL, Email VARCHAR(30), Phone VARCHAR(30) NOT NULL, PRIMARY KEY (Customernumber)
);

CREATE TABLE Pizza( PizzaType VARCHAR(30) PRIMARY KEY, Description VARCHAR(100), Price NUMERIC(8,2) NOT NULL
);

CREATE TABLE Customer_order( Customernumber INT DEFAULT 000000, PizzaType VARCHAR(30), OrderDate DATE, OrderType VARCHAR(30), Phone VARCHAR(30), PRIMARY KEY (Customernumber, PizzaType), FOREIGN KEY (Customernumber) REFERENCES Customer(Customernumber) ON DELETE SET DEFAULT, FOREIGN KEY (PizzaType) REFERENCES