Objectives:
INFO20003 Tutorial – Week 4
This tutorial will cover:
I. Additional concepts in ER modelling, plus a simple case study – 20 mins
II. Bus company case study – conceptual and logical modelling – 30 mins
Exercises:
1. ER modelling – additional concepts
• Multivalued and composite attributes
• Unary relationships
2. Practising these concepts:
Australia’s corporate regulator, ASIC, stores a range of information about companies, including the name, the nine-digit ACN (Australian Company Number), the date of registration and deregistration, and the names of the company’s directors. Every company has a registered address, made up of the street address, suburb, state and postcode. A company may be owned by another company; in this situation ASIC keeps track of the company’s parent company.
Use this information to model a “company” entity using Chen’s notation.
3. Consider the following case study:
A bus company owns a number of buses. Each bus is allocated to a particular route, although some routes may have several buses. Each route passes through a number of towns. One or more drivers are allocated to each stage of a route, which corresponds to a journey through some or all of the towns on a route. Some of the towns have a depot where buses are kept – each bus always returns to its allocated depot at the end of the day.
Each of the buses is identified by its registration number and can carry different numbers of passengers, since the vehicles vary in size and can be single or double-decked. Each route is identified by a route number and information is available on the average number of passengers carried per day for each route. Drivers have an employee number, name, address, and sometimes a telephone number, and the names of the training courses they have completed need to be stored.
a. Identify the entities.
b. Identify the relationships (use business rules to identify relationships). State all the key
constraints and participation constraints.
c. Draw a conceptual model and populate entities with appropriate attributes (use Chen’s
notation).
d. Discuss the logical modelling of the Driver entity.
INFO20003 Tutorial – Week 4 1