INFO20003 Tutorial – Week 2 Solutions Objectives:
This tutorial will cover:
I. Review of the Database Development Lifecycle focusing on the design stage (Q1) – 20 mins
II. Case study – identify entities, business rules and attributes (Q2) – 30 mins
Exercises:
1. The diagram above shows the stages of the Database Development Lifecycle.
a. What is the purpose of each stage and what do we, as database designers, need to do in each stage?
This question reviews the concepts taught in the class following the same diagram presented in the lecture. You should find the stages (apart from conceptual, logical and physical) to be fairly self-explanatory as most of these are from the lecture material. You may note that designers may not necessarily be involved in all stages, particularly in large organisations. The red text is out of scope for this subject.
• Database planning: Planning how to do the project and how the stages be completed efficiently and effectively. This stage involves understanding how the enterprise works for better data modelling.
INFO20003 Tutorial – Week 2 Solutions 1
• System definition: Specifying scope and boundaries, how the system will operate from different perspective, how it interfere in other organisational systems. It involves studying different user views or job roles and how the database application will be linked with the other information systems of the organization.
• Requirements Definition and Analysis: Collection and analysis of requirements of the part of enterprise to be served by the new database. At the end of this phase the designers would potentially have the description of the data used or generated, the details of how the data is to be used or generated and any additional requirements. You will be given a case study containing all the requirements during this course whenever a database design is required. There should be some kind of data dictionary at the end of this stage which can be extended in the design phase.
• Design:
o Conceptual design: Construction of a model of the data used in the database –
independent of all physical considerations mostly working on data models such as ER and EER diagrams and data dictionaries. The model is based on the requirement specification of the system.
o Logical Design: In the logical database design phase, the model of the data to be used is based on a specific data model, but independent of a particular database management system is constructed. This is based on the target data model for the database e.g. relational data model.
o Physical Design: In the physical database design phase, the description of the implementation of the database on secondary storage is created. The base relations, indexes, integrity constraints, security, etc. are defined using the SQL language.
• Application Design: In the application design phase, the design of the user interface and the application programs that use and process the database are defined and designed. (done in conjunction with design)
• Implementation: During the implementation phase, the physical realization of the database and application designs is to be done. This is the programming phase of the systems development.
• Data conversion and loading: This phase is needed when a new database is replacing an old system. During this phase the existing data will be transferred into the new database.
• Testing: Before the new system is going to live, it should be thoroughly tested. The goal of testing is to find error and analyze performance, robustness and adaptability etc.! The goal is not to prove the software is working well.
• Operational Maintenance: Monitoring means that the performance of the system is observed. If the performance of the system falls below an acceptable level, tuning or reorganization of the database may be required. Maintaining and upgrading the database system means that, when new requirements arise, the new development lifecycle will be done.
INFO20003 Tutorial – Week 2 Solutions 2
b. Describe the tasks that are performed in the conceptual design stage to generate a conceptual model.
During conceptual design, the requirement analysis is evaluated to identify entities and relationships in the enterprise. An entity is a real-world object or concept distinguishable from other objects or concepts. Each entity is described using a set of attributes. During this stage, the designers identify and document business rules. Business rules allow the identification of relationships, participation rules and constraints in creating a correct data model. They also allow designers to understand business processes, and the nature, role and scope of the data. The designers identify what information about these entities and relationships should be stored in the database. From this, a database schema is developed and displayed as an ER model (diagram), using the basic constructs of entities, relationships and attributes.
For simplicity, we will discuss the logical and physical model tasks next week.
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.
a. Identify the entities. • Cinema
• Screen
• Projector • Movie
“Cinema chain” is not an entity in this scenario. You do not normally include the actual business or company whose business processes you are modelling. This is because there is only one instance of this company, and there is no data to store about it in any case.
b. Identify the business rules (statements that define or constrain aspects of the business)
• Each cinema has several screens, numbered starting from 1
• Each movie screen has space for a single projector
• Technicians must be able to identify which screen each projector is currently projecting
onto.
• The system should keep track of the last time a movie was shown on a particular screen
INFO20003 Tutorial – Week 2 Solutions 3
c. For any three identified entities, list the attributes.
• “Each cinema has a numeric ID, name and address. For cinemas that are not owned
outright, the business also keeps track of yearly rent.” → Cinema (ID, name, address, yearly rent)
• Screens are “… 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.”
→ Screen (number, size, seating capacity, has Gold Class?)
• There are “… 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.”
→ Projector (format [16 mm film/35 mm film/2D digital/3D digital], serial number, model number, resolution, hours of use)
• “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+).”
→ Movie (title, year of release, rating)
INFO20003 Tutorial – Week 2 Solutions 4