Tutorial 2: Entity Relationship Modelling

Faculty of Computing,

Engineering and Technology

Tutorial 2: Entity Relationship Modelling

Module Code : CE00873-2: Information Retrieval and Use

Academic Year 2010/11

Relationships

In the following

Remember to name all relationships and to show their multiplicity and optionality.

1. A university student is enrolled onto several modules. There are lectures each week for each module and he finds that the attendance varies. Draw an entity relationship model which would allow you to find out the following

The number of students enrolled on each module

The number of students attending each lecture

The number of modules that a student has enrolled on

The number of lectures for each module that a student has attended.

1. Scenario

A hospital employs a staff of doctors, nurses and orderlies. The doctors are all specialists and are attached to certain wards. Some nurses also have specialisms and are attached to specific wards. Other nurses and the orderlies have general skills and work across all the wards. Draw an entity relationship model to represent this scenario.

You should use a superclass and take care to avoid connection traps.

1. Scenario

A lady orders several books from an online bookstore. She is surprised to receive a separate invoice for each book and is appalled at the waste of paper. On querying this she is told that this is because a separate order has to be raised for each book and a separate invoice is generated for each order. This is time consuming (not to mention costly in postage) and a request has been made for the database to be redesigned so that a single customer order can contain several books.

Draw two entity relationship diagrams to model the customer ordering system

The first should depict the current (one item per order) scenario

The second should allow more than one item per order,

Extension

Extend the model to include invoicing such that an invoice can contain several items (possibly from different orders).

1. Scenario

A hotel has many rooms, some of which are function rooms and have facilities allocated to them. Some function rooms have dining facilities allocated to them while others have a gym.

Design an entity relationship model which ensures that customers can book a room with the facilities they require allocated to it.