National University of Computer and Emerging Sciences, Lahore Campus
Course: Database Systems
Program: BS(Computer Science)
Instructor: Muhammad Ishaq Raza
Practice Problem: Relational Model (1)
Question 1:
Problem Description
A car rental company rents cars to customers. The company owns several cars. Each car has
a brand, model name, production year, mileage, colour, and so on. Cars are divided into
different categories: small, mid-size, large, limousines.
The company has many locations where you can rent a car. The rental locations are located in
different cities throughout the country. There can be more than one company location in a city.
Anyone over 21 who has a valid driver’s license can rent a car. Customers under 25 or over 75
years pay different (higher) charges then other customers.
Before renting a car, a customer usually makes a reservation for a car. A customer specifies
the dates when the car will be rented, the pick-up location, the drop-off location, and
the category of car he wants to rent. A customer may specify, that he wants some
extra equipment in the car, for example a GPS, a car seat for a child, etc.
When a customer rents a car, he declares the pick-up and drop-off location, and the drop-
off date. The customer can buy various types of insurance. He can also decide that he doesn’t
need insurance because the insurance is covered otherwise, for example by his credit card
company. The customer can choose additional options such as the possibility of an early drop-
off, various refueling options, etc.
The customer pays the charges when he returns the car.
Requirement:
For the scenario in the above problem, identify the relations and their attributes.
Page 1 of 5
Question 2:
For this question consider relations that you identified in question 1.
a) Identify domain, including data type, for each attribute.
b) Identify Candidate key(s) for each relation.
c) Identify Primary Key and Secondary Key(s) for each relation.
d) Identify Foreign Keys (if any) and Specify foreign keys relationship.
Question 3:
Consider 3 relations R, S, and T. In relation R, the attribute A and B are unique. In relation S,
column F is unique. Whereas in relation T, the composition of columns A & F is unique.
A B C D E
F G H I
A F H I
a. State all the super key(s) for the above relation.
b. Mention the super key(s) which are minimal (i.e. key).
Page 2 of 5
Question 4:
Employee Department
ID Name Salary Department ID
1 Tom 15000 1
2 Pam 20000 3 ID Department_ Name Location
3 John 50000 2 1 CS London
4 Sam 60000 3 2 EE NULL
5 Todd 70000 2 3 BBA New York
6 Neil 10000 NULL 4 Other Department Sydney
Discuss all integrity constraints violated by each operation, if any, and the different ways of
enforcing these constraints. If the operation is unsuccessful due to some integrity constraint, then
discuss the reason as well as the solution. If the operation is successful, then show the new state
of the both relations. Also assume that department ID can be null in Employee Table.
*Consider all questions are Independent.
1. For Delete
a. Assume that foreign key with cascading option is implemented.
i. Delete the department(s) whose location is NULL
ii. Delete the employee(s) whose department id is 3.
iii. Delete the department having id equal to 4.
b. Assume that foreign key with Set NULL option is implemented.
i. Delete the department whose location is New York.
ii. Delete the department whose department name is CS
iii. Delete the employee whose id is 4.
iv. Delete all departments.
Page 3 of 5
c. Assume that foreign key with no action is implemented.
i. Delete the department whose location is New York.
ii. Delete the department having id equal to 4.
iii. Delete the employee(s) having salary greater than 40000.
2. For Insert
a. Assume that foreign key with cascading option is implemented.
i. Insert <NULL, ‘Physics’, ‘Tokyo’> into Department.
ii. Insert <7, ‘George’, 54000, 6> into Employee.
iii. Insert <8, ‘John’, 60000 > into Employee.
b. Assume that foreign key with set NULL is implemented.
i. Insert <4, ‘Ali’, 100000, 2> into Employee.
ii. Insert <NULL, ‘Hameed’, 80000,1> into Employee.
iii. Insert <5, ‘Physics’, ‘Karachi’ > into Department.
iv. Insert < ’7’ , ‘Mathematics’, ‘Sydney’> into Department.
c. Assume that foreign key with No Action is implemented.
i. Insert <7, ‘Ali’, 40000> into Employee.
ii. Insert <10, ‘Umer’, 60000, 6> into Employee.
iii. Insert <3, ‘Physics’, ‘New York’, 1>.
3. For Update
a. Assume that foreign key with cascading option is implemented
i. For the department having id=4, change the id to 3.
ii. For the department having id=1, change the location to Karachi.
iii. For the employee having id=1, change the id to 8.
iv. For the department having id=3, change the id to 10.
Page 4 of 5
b. Assume that foreign key with set NULL option is implemented.
i. For the department having id=1, change the id to 8.
ii. For department having location as NULL, change the id to 4.
iii. For the department having name=’BBA’, change the name to ‘Mathematics’.
c. Assume that foreign key with no Action is implemented.
i. For department having department name as NULL, update the department
name to ‘Karachi’.
ii. For department having department id=4, update the department id to 3.
iii. For department having department id= 1, update the department id to 10.
iv. For department having department id=4, update the department id to 6
.
Page 5 of 5