Objectives:
INFO20003 Tutorial – Week 3
This tutorial will cover:
I. Entity-Relationship (ER) modelling review
II. Case study – Use the previous week’s case study to design a conceptual model
III. Convert the conceptual model to a logical model
IV. Introduce the notion of physical model
Exercises:
1. ER Review – fundamental concepts
• Entity, weak entity
• Attribute
• Business rules to relationships – key constraints and participation constraints
2. Consider the following case study:
A cinema chain operates a number of cinemas. Each cinema has several screens, numbered starting from 1. The chain keeps track of the size (in feet) and seating capacity of every screen, as well as whether the screen offers the Gold Class experience.
The cinema chain owns hundreds of movie projectors – both film projectors (16 mm and 35 mm) and digital projectors (2D and 3D). The chain stores key information about each projector, namely its serial number, model number, resolution and hours of use. Each movie screen has space for a single projector; technicians must be able to identify which screen each projector is currently projecting onto.
A wide range of movies are shown at these cinemas. The system should keep track of the last time a movie was shown on a particular screen. The marketing department needs to know the movie’s title and year of release, along with the movie’s rating (G, PG, M, MA15+ or R18+).
Each cinema has a numeric ID, name and address. For cinemas that are not owned outright, the business also keeps track of yearly rent. The system needs to be able to generate weekly activity reports for the chain’s chief operating officer.
Follow the steps to create a conceptual model in Chen’s notation:
a. Revise last week’s identified entities.
b. Form relationships between entities.
c. Apply constraints (key constraints and participation constraints) to the relationships.
d. Add attributes which describe the entities and relationships.
e. Finalise your conceptual model by marking weak entities, identifying relationships and
key attributes.
3. Logical and physical modelling
a. What needs to be changed to convert a conceptual design to a logical design? Develop a logical design for the above case study.
b. What will you change in the logical model to generate a physical model?
INFO20003 Tutorial – Week 3 1