“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

On Thu, 04 Sep 2003 02:07:39 -0400, vimalraj wrote:

What's up, Doc?


--
_ Leandro Guimarães Faria Corsetti Dutra +41 (21) 648 11 34
/ \ http://br.geocities.com./lgcdutra/ +41 (78) 778 11 34
\ / Answer to the list, not to me directly! +55 (11) 5686 2219
/ \ Rate this if helpful: http://svcs.affero.net/rm.php?r=leandro

Originally posted by vimalraj

That is your assignment.



What is your question?



This is a discussion forum, not a homework outsourcing service!


--
Posted via http://dbforums.com

Just send me you professor's email address, and I'll answer the questions
for you.


"vimalraj" <member38025 (AT) dbforums (DOT) com> wrote