Question 1 A library service wants to create a database to store details of its libraries, books and borrowers. Details include the following: A book has a unique ISBN number, a title and one or more authors. The library service may own several copies of a given book, each of which is located in one of the services libraries. A given library contains many books, and in order to distinguish different copies of the same book, a library assigns a different copy-number to each of its copies of a given book; the price that was paid for each copy is also recorded. Every library has a unique name and is either a main library or a branch library. A main library may have zero or more branch libraries and every branch library is a branch of exactly one main library. A borrower has a name and a unique ID code. A borrower can have many books on loan, but each copy of a book can only be on loan to one borrower. A borrower could borrow the same book on several occasions, but it is assumed that each such loan will take place on a different date. Draw an enhanced entity relationship diagram in Chen notation. Answer Question 2 Consider the following scenario of a bank. Commonwealth Bank has many customers who opened various types of accounts. The customers need to register as clients by providing their personal details such as names, addresses, ICs, phone numbers, types of account (either account or loans) and emergency contacts. In order to give satisfaction services to the customers, the Manager need to have a system that monitors and keep tracks all the transactions from different types of accounts (savings accounts, checking accounts and trust) that have details like type name, client name and identification card number, and also account number. Each account type will have its own details that are total deposit when the customer registers for saving account, cheque book number when they apply for a cheque book and duration of trust account. Besides that, loans are also one of the main services provided to the clients. They offer a few types of loan that are car loans, home loans, credit line and personal loan. The manager is also need to keep track of each account's transactions like deposits, withdrawals and checks and each loan's payments; both of these include the amount, date and time. Draw an enhanced entity relationship diagram in Crows Foot notation. ANSWER Question 3 A transport company needs to model their activities. There are two types of employees in the company: drivers and operators. For drivers it is interesting to know the date of issue and type of the driving license, and the date of issue of the taxi drivers certificate. For all employees it is interesting to know their personal number, address and the available phone numbers. The company owns a number of transport. For each transport, there is a need to know its type, year of manufacturing, and date of the last service. There are two types of transport commonly booked by the customers that are MPV and Saloon. There are also other types of transports that the company provides to the customers. The company wants to have a record of car trips. A transport may be picked on a street or ordered through an operator who assigns the order to a certain driver and a car. Departure and destination addresses together with times should also be recorded. Draw an enhanced entity relationship diagram in UML notation. Answer: Question 4 A museum director wants to develop a database system to keep details of its arts collections, events and rental services. Under rental services, the museum will keep information of the customer such as names, addresses, telephone numbers, type of art_collection rented and durations. The museum has a collection of art_objects. Each art_object has a unique id,an artist (if known), a yea r(when it was created, if known) and a title. Art_objects are categorized based on their types. There are two main types: that are paintings and sculptures. The museum allows clients to rent more than one art-objects from different types at one time. Painting has a paint type and style. Sculpture has a material from which it was created,height and weight. Once in a while, the musem will held exhibitions to show its collections to the public.Details of exhibitions like name,start date and end date are kept for future references. Exhibitons are related to all the art objects that were on display on display during the exhibition. Draw an enhanced entity relationship diagram in Crows Foot notation. Answer: Question 5 The Olympic facilities are divided into sports complexes. Sports complexes are divided into one-sport and multisport types. Multisport complexes have areas of the complex designated for each sport with a location indicator (e.g., centre, NE corner, etc.). A complex has a location, chief organizing individual, total occupied area, and so on. Each complex holds a series of events (e.g., the track stadium may hold many different races). Each event normally has several activities concurrently held at more than one areas. For each event there is a planned date, duration, number of participants, official involved, and so on. A roster of all officials will be maintained together with the list of events each official will be involved in. Different equipment is needed for the events (e.g., goal posts, poles, parallel bars) as well as for maintenance. Each equipment has names and id for record keeping. The two types of facilities (one-sport and multisport) will have different types of information. For each type, the number of facilities needed is kept, together with an approximate budget. Draw an enhanced entity relationship diagram in Chen notation. ANSWER