The Databases Course

Exercise No. 1

Entity-Relationship Model

Due on December 22th by midnight

 

Important Course Information:

• See the course home page on www.cs.huji.ac.il/course/db
• Subscribe to the course's newsgroup local.course.db
• Register to the course immediately to get an oracle account, by running ~db/bin/register

 

1. You are required to design a database for a car rental company. The company has many branches all over the world, and customers may pick a car in one branch and return it in another. Every car belongs to one branch, and until now every branch kept separate car lists. Branches are responsible for regular maintenance of their cars, and records of accidents, periodic treatments and replaced parts should be kept. Customers pay either by one of several major credit cards or by cash. For every rental, the driver's personal information, type and condition of the car, insurance information and so on is kept. For private customers, the company offers a 'frequent renter' program to encourage repeating business. Customers that are companies, on the other hand, may enjoy bigger and more constant discounts, but do not take part in the 'frequent renter' program. Companies can also use several insurance companies for the cars currently at their disposal, while private customers can only use one.
   
   Define the entity sets, relationship types, and their relevant properties (not all of which have been stated above). Specify keys for each entity set, and the functionality (many-to-one, for example) for each relationship type. Denote weak entity sets and their partial keys. Answer in the form of an E-R diagram, and state explicitly any assumptions which you have made.

 

2. Attribute domains, functional relationships and keys are all forms of constraints on the modeled system. Another common form is that of participation - stating that each entity of an entity set must take part in a relationship that set is attached to. This constraint is marked by a double line between that entity set and the relationship type; for example, the following states that each child has exactly one mother, and women may have between zero to any number of children:

Create an E-R model with entity sets for workers, departments and projects, and add relationship types and constraints to model the following rules. Every worker works at exactly one department, and every department has some workers. Every project is assigned to at least one department, but some projects belong to several ones. A department may have many projects or no projects at all. Every project also has a set of workers assigned to it; every worker has at most one project, but not always has one at all. Every worker has a supervisor (another worker), except the big boss who nobody supervises. Not all workers are also supervisors, although some workers supervise many others.

 

3. E-R diagrams are useful for modeling, but not for implementation - other models can be described by efficient data structures and can compute queries with known time and space complexity.

1. Transform the E-R diagram from question 2 into a relational database schema. See last year's exercise 1 (on the web) for an example.
2. Which forms of constraints are kept by the relational schema, and which are lost?