Question

section B                                                                                                                            (10 Marks)

Answer all the questions in this section. Read what is required from each question carefully and answer all parts of the question. Begin each Answer on a fresh page, and label the question on the answer booklet clearly.

Consider the following business rules and information to maintain a database of Car Rental Co. (CRC):

• The database maintains a list of all the Cars, Subcontractors, Company revenues and customers
• Cars are to be described by such data as make, model, year of production, and engine size, and fuel type, number of passengers, registration number, purchase price, purchase date, rent price and insurance details.
• All major repairs and maintenance are done by Subcontractors (i.e., franchised garages), with whom CRC has long-term agreements. Therefore the data about garages to be kept in the database includes garage names, addresses, range of Services and the like. Some garages require payments immediately after a repair has been made, with others CRC has made arrangements for Credit facilities.
• Company expenditures are to be registered for all outgoings connected with purchases, repairs, maintenance, insurance etc.
• Similarly, the cash inflow coming from all Sources – Car hire, car sales, insurance claims – must be kept of file.
• CRC maintains reasonably stable client base. Special credit card facilities are provided for payment. These customers may also book in advance a particular car.
• All major credit cards care accepted. Personal details (such as name, address, telephone number, driving license number) about each customer are kept in the database.
• These reservations can be made for any period of time up to one month.

Draw as ER diagram for the above scenario.

Your ERD should:

1. Include all major entities, attributes and relationships (with names).             (4 marks)
2. Use UML or Crow’s feet notation with cardinality/multiplicity shown.             (2 marks)
3. For each entity show the primary. Represent foreign keys for entities if any.      

                                                                                                                              (2 marks)

4. Resolve many-to-many relationship if present.                                               (2 marks)

Remember to state any assumptions necessary to support your design

Answer 1

Hand drawn ERD for the given senario is given in page below:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------