CSE1205/IMS1002 Systems Analysis and Design
Week 3 – ER Modelling
Tutorial Objectives:
- to develop an understanding of ER modelling
- to practice ER modelling skills
Tutorial Resources:
- Tutorial 3 handout – ER Modelling
- CSE1205/IMS1002 Lecture 2/3 – Introduction to Data Modelling: ER Modelling
Tutorial Task:
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 company is made up of several departments with each department employing many people. The salespeople who work for the sales department write many sales orders, each of which is placed by one customer. Each order can be for many products and each product can appear on many orders.
- The ordering section maintains the standing order file by round on colour-coded cards, a different colour for each day of the week.
Alterations to a customer's standing order may be made permanently or temporarily and the request may be taken by phone or passed on by the driver. Requests from new customers for an order are referred to the salesmen who visit the customer and assist him to estimate the quantity required. This order is then entered on the appropriate round's standing order cards.
Order cancellations are relatively infrequent, but must also be recorded. Each day the order clerks compile the Production Order, a summary of orders for two days hence used for planning baking schedules and purchasing ingredients. A copy of the next day's standing order for each round is also made (called a Load Sheet) and sent to the Loading Bay to enable the bread stackers to prepare the van loads. A summary of changes top the next day's orders is made for van drivers to update their delivery books.
- The Monash Soccer League consists of a number of clubs that 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.
- 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.
- National Office Supplies (NOS) is a supplier of stationery and office equipment to business organizations throughout the country. They produce mail-order catalogues on a regular basis. There are various types of catalogues such as weekly, monthly and quarterly. These catalogues may be general but can sometimes specialize in certain types of goods, for example, office equipment, paper and stationery supplies, etc.
NOS wants to store the details of the products it supplies to the trade, in particular, the item code, description, colour, wholesale price and units per pack. The range of products in each catalogue varies from a few hundred in the weekly catalogues to several thousand in the quarterly catalogue.
Products can appear in many catalogues, but may be priced differently. Each catalogue has a start and end promotion date. Promotion periods for different catalogues may overlap.
NOS also wish to record the products ordered by their customers. When a customer orders a product from NOS that has appeared in a particular catalogue, the catalogue number is entered next to the item code in the order. This allows NOS to supply the customer with the product at the price listed in the particular catalogue from which they ordered.
- 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 that 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.
- Jetaway Tours sells tours to customers. Tours may either be package tours or specially arranged tours that are put together to suit the specific needs of a customer. Package tours are offered by tour operators, while arranged tours are constructed by Jetaway Travel staff who make the necessary airline, hotel, etc bookings. Customers must confirm their tour booking with a deposit. They are then invoiced for their trip, and full payment must be made before their departure date. Once the initial deposit has been received, partial payments of the balance are accepted.