E-R model and normalisation

“Cars may be uniquely identified by their registration number. Other

data of interest concerning our cars includes: year of manufacture, VIN

(unique vehicle identification number) manufacturer and model,

kilometres travelled, date last serviced (so we can tell when a car is due

to be serviced) and comments on the condition of the car. Additionally,

each car is classified into one particular type. The types of car we

have are listed below:

small manual

small automatic

medium manual

medium automatic

large manual

large automatic

We work with different service stations. Each service station has the

capacity to service any of our cars. Service stations are identified by a

unique number. We also require details about the service station such

as: name, address, telephone and contact person. We personally

choose a particular service station at a given time based on a number of

factors which are not required to be stored in the system. When a

service station is chosen, a booking is made with that station. The

information we require about these bookings includes: car registration

number, service station number, date in, date out, service type (e.g.

20,000km service or 50,000km service etc.), service details, amount due

and amount paid. So that I check if a car is available to be serviced, I

need to be able to view the following data about client rental bookings

for cars – car registration number, client number, date out and date

due.”

User 2: Car Rental Booking Department.

“Each of our clients is assigned a unique client number. Other information we

store about clients includes their name, address, telephone number and discount

category. There are three discount categories which we use in conjunction with

the standard rate for the car to calculate the final price for a rental booking.

When a client makes a booking for a car, we store the following information for

the booking: client number, car registration number, date required, date

delivered, date due, date returned, kilometres when delivered, kilometres when

returned, rental rate (depends on the type of the car), drivers license number of

the driver (since our clients may be companies), and amount due. We also need

to record the amount due for car insurance. This amount varies depending on

the age of the driver and the type and age of the vehicle rented. The final price

for the rental booking is the sum of the rental rate and the insurance due.

When I look up information on available cars, I need to view the following

information: car registration number, year of manufacture, manufacturer and

model, kilometres travelled and comments on the condition of the car. We keep

all the booking data for the cars in the system, not just bookings which are

currently active ( i.e. bookings for cars which have not yet been returned).”

Draw initial local Entity-Relationship diagrams for each user view. To what level of the ANSI/SPARC architecture do these diagrams correspond? Document all the entities, relationships (including cardinality), attributes, domains, candidate keys and primary keys. State explicitly any

assumptions you make at this point.

 Merge the local Entity-Relationship diagrams into a single global data  model (assuming that these two local views define the entire scope of  the system under consideration). To what level of the ANSI/SPARC  architecture does this diagram correspond? Again, state any assumptions  you make at this point and document all the working involved in  generating this diagram. Show that each local user view is completely  derivable from this global data model.

 Derive relations from the global Entity-Relationship diagram you have  just generated.Normalise the relations to BCNF. Show all working  involved in the normalization process.Document the final BCNF relations  in DBDL (Database Design Language). Define appropriatereferential  integrity update rules (state any assumptions you make here concerning  the business processes which may imply these rules).

--
Posted via http://dbforums.com