ER MODELLING EXERCISES
1.Draw an entity relationship diagram to describe the following situations.
You may list any assumptions you have made about the "business rules" which apply.
- The Monash Main Bookshop is required to supply textbooks to students for classes at the university’s five campuses. The Main Bookshop has a branch store located on each campus. Several weeks before the start of each semester the academic departments submit information to the Monash Main Bookshop about the subjects to be offered at each campus during the following semester. For each subject the Main Bookshop needs details of projected student enrolments at each campus, required textbooks, and all subject lecturers for each subject. The Main Bookshop staff then prepare purchase orders which are sent to the various publishers who supply the textbooks. Academic departments are notified of any textbooks which are unavailable so that alternative texts may be ordered. Book orders arrive at the appropriate campus branch bookstore accompanied by packing slips. These packing slips are then verified by the bookstore staff. If delivered orders are correct, the relevant academic departments are billed by the bookshop for the textbooks they have ordered.
b.The Hummel Music School offers music lessons to pupils aged from five to fifteen years. A variety of instruments is taught, including violin, cello, piano, flute and all woodwind instruments. Teachers are hired by the school and paid on an hourly basis. Many teachers give lessons in more than one instrument.
Bills for lessons are prepared at the start of each term and are sent to the pupil's parent who is paying for the lessons. Frequently more than one child in a family is taking lessons, and some children take lessons in more than one instrument. Each bill includes all children in the family who are taking lessons. The cost of a term's lessons for each pupil is determined by the instrument being studied, by the length of the lessons (30, 45 or 60 minutes) and by whether the lessons are taken on an individual or group basis.
Parents who encounter difficulties in meeting payments may be offered special payment facilities including payment by weekly instalments. Some parents prefer to pay for a whole year's lessons in advance in which case they are allowed a discount of 20%.
c.Stay-a-While Motor Inns is a national motel chain that franchises a number of local motels. Each motel has about 30 rooms and operates its own restaurant. A centralised reservations system is operated by the motel chain. Guests may make bookings for any of the motels via this system. Each morning the centralised system sends reservation information to the front office of every motel. This includes names and addresses of all guests included in each reservation as well as check in and check out dates.
When the guests check in, they fill out a registration form and a file is created for that reservatiion. Details of all services used during their stay, including restaurant meals, laundry services and telephone calls, are entered onto the reservation file.
When guests check out, a bill with the cost of all services and accommodation charges is prepared and given to the guest who is responsible for paying the account. Guests may pay either by cash or credit card. A receipt for payment is produced and handed to the guest who has paid the account
d.Prism Gallery is an up-and-coming art gallery located in a suburb of Melbourne. The gallery mounts approximately twenty exhibitions per year of artworks of various categories, eg. oils, watercolours, ceramics, sculpture. Most exhibitions present the work of one or more artists working within one particular category, although different categories are sometimes combined, especially when artists have not been able to produce enough artworks for an entire exhibition.
The gallery maintains a register of past and potential exhibitors. They are listed alphabetically within each category in which they work. Information about new artists is obtained via the gallery's numerous contacts in the "art" world and via art magazines.
Exhibitions are scheduled up to 12 months in advance. Negotiations with the artists take place until finally the gallery prepares contracts which must be signed by the artists concerned. A file is created for each exhibition and details of the exhibition are entered as they are finalised. The artists' progress towards completion of all required artworks is monitored and also recorded in the file. Eventually a detailed list of all items to be exhibited is typed up and added to the file. A copy of this is sent to the gallery's insurance company for insurance cover for the exhibition and another copy is sent to a printer for exhibition catalogues to be produced. Once exhibition details are finalised an official opening is scheduled with invitations sent to the press and other interested parties. Delivery of artworks to the gallery is arranged by the gallery using a number of reliable carriers.
Once the exhibition is underway, any sales of artworks are recorded. A deposit is required from the buyer, and at the end of the exhibition an invoice is prepared for the balance outstanding. Artworks are only handed over when full payment is received. Receipts are issued for deposits and for final payments. The gallery's commission is calculated and deducted from the amount which is paid to the artists for their works which have been sold.
e.A theatre company gains much of its income from subscriptions to its plays. The company offers several series of plays (eg. a Shakespeare series, a comedy series, an Australian series) and each series is offered on several days of the week (eg. the comedy series may be offered on both Fridays and Saturdays so that subscribers can nominate their preferred day as well as series). Several series may be offered in each subscription season. Subscribers who book tickets for more than one series obtain a discount on their subscriptions as do pensioners and students.
f. Ted's Tuck Inn Family Restaurants is a company which operates a chain of franchised restaurants across metropolitan Melbourne. At present the company's central distribution centre acts as supplier to these restaurants. Each franchisee is required to submit to the distribution centre a day-by-day projection of sales for each of the menu products offered by their restaurant (i.e. the items listed on the menu) for the coming month. The range of menu products offered to customers can vary from restaurant to restaurant. All menu products require ingredients.
Based on projected sales for each restaurant, a day-by-day ingredients requirements summary is produced by the distribution centre for each restaurant. The summary is then used to determine weekly purchase requirements for each store. Once these have been determined, delivery orders for each week of the coming month are produced for each restaurant. Deliveries of ingredients to each store are made daily. The distribution centre invoices each restaurant at the end of each month for all deliveries made during the month.
1