IMS9001
Tutorial 6 – Introduction to ER Modelling
Tutorial Objectives:
-to develop basic understanding of ER modelling
-to practise these modelling skills
Tutorial Task:
1.Introduction to data and ER modelling:
Discuss the following problems in small groups.
2. All-Hours Convenience Stores
A convenience store, franchised to the All-Hours chain, is to be established on the corner of your street.
•list and describe the business functions (e.g. payroll) that need to be performed in order to run this business
•identify those functions in which a computer(s) could be used to assist the management and staff of the business
•identify which sub-systems are interrelated and in what ways.
3.Heather’s Headhunting Service
Heather Jones ended her full-time working days recently. She decided that since she worked in the computer industry for many years, she could set up a small business at home finding trained computer personnel for people requiring assistance in setting up and maintaining PC-based systems. She felt that she had sufficient experience to know what is required by users of such systems and enough contacts to ensure that she would be able to find the necessary people to carry out the required work. She also had her own PC system at home and a connection to the internet which she intended using to help her with her new venture.
•discuss and identify Heather’s data requirements for running her new venture.
4. Lend-all Library System
A computerised library system is being developed for the new Lend-all Library. The system designer has suggested storing the following list of data items. The librarian who is the owner of the system wants to be able to produce and send overdue notices to borrowers and to advise potential borrowers of the expected return of particular books that they have requested. What additional data is required to do this? What unnecessary items have been included in the list below?
Suggested data items:
BookBorrowerLoan
ISBNnameborrower no
Titleaddressbook no
Authoragedate of loan
Publisherdate of birth
Date of publicationborrower classification
Number of pagesoccupation
Colour of jacketincome bracket
Size of book
Note:
There may be multiple copies of a book
There are 5 standard loan durations
Different classifications of borrowers allow different durations of loans
Some borrowers are not permitted to borrow certain types of books
Fines for overdue books are computed at a standard rate per day
The cataloguing function is a separate subsystem.
5. For each of the situations below identify the appropriate entities and draw an ER model to represent the situation.
a)A company is made up of several departments with each department employing many people. The sales staff who work for the Sales Department write many sales orders which are each placed by one customer.
b)Jet Away Travel sells tours to customers. Tours may either be package tours or specially arranged tours which are put together to suit the specific needs of a customer. Package tours are offered by tour operators while arranged tours are constructed by Jet Away Travel staff who make the necessary airline, hotel etc. bookings. Customers must confirm their tour bookings with a deposit. They are then invoiced for their trips, and full payment must be made before their departure date. Partial payments after the initial deposit has been made are accepted.
c)Draw an ER model for the Video Library Loans System described in question 1 d) in the Tutorial 4 exercises.
6.Draw an entity relationship diagram to describe each of the following situations. You may list any assumptions you have made about the "business rules" which apply.
a)The ACME Credit Card Company maintains accounts for either individual clients or companies. Each account is owned by either a person or a company. For each account, many credit cards may be issued.
Each credit card is allocated an authorised holder. This means that any individual may have an account with one card for themselves and a second card for their spouse. In addition, that individual may also have a credit card owned by their company and held for them for their expenses.
Each credit is embossed with a valid-from date, an expiry date, the account number and the name of the authorised holder. There are three different types of credit card, each with different limits and payment terms.
b)The Monash Soccer League consists of a number of clubs which compete against each other in the winter season. There are currently ten clubs each of which has several teams. Each team has eleven players and during the season players in a club may play in several of the teams. Each team has one of the players as its captain.
There are two types of team, senior and junior. Each team has one coach and some of the coaches coach both senior and junior teams.
During the season, each team plays several matches and, of course, each match is between exactly two teams. Many of the clubs have several soccer pitches and each match is located at one of the pitches of the opposing teams.
c) Cutprice Airlines offers flights throughout Australia. In order to travel on Cutprice Airlines, passengers need to purchase a ticket from a travel agent. The ticket has the date of issue and the full price printed on it and contains many coupons. Each coupon is for a particular flight and contains the departure date and time of the flight. For example, a ticket from Melbourne to Cairns contains two coupons. One coupon is for the flight from Melbourne to Sydney and the other coupon is for the flight from Sydney to Cairns.
Each flight is scheduled from the predefined routes which are offered by Cutprice airlines. For example, the route from Melbourne to Sydney is a popular route and has many flights scheduled each day. Each route is allocated a unique route number and has a prescribed departure airport and destination airport. Each airport is identified by a three character code and is described by its name and location.
Each flight has a particular aircraft allocated to it well before the departure date. Prior to boarding the aircraft, passengers are allocated a seat on the aircraft. Each seat on an aircraft is identified by a seat number and seat type (first class, business class, and economy). Each coupon therefore relates to exactly one seat.
You may also wish to complete some of the extra ER modeling exercises available at the unit lecture page – week 5.