Assignment1 db Essay - 322 Words

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

Show More