Re: E-R model and normalisation

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

"vimalraj" <member38025_at_dbforums.com> wrote in message news:3328474.1062655659_at_dbforums.com...
>
> 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
Received on Fri Sep 05 2003 - 18:39:39 CEST