Description

R. Brooks
1 Cinema
Draw an E/R diagram that captures this information:
• Cinemaxx has multiple cinemas, though at most one in a city
• A cinema has multiple cinema halls (max 6), each identified by a letter: A, B, C, D, E, or F.
• A cinema hall has many seats. A seat has a seat number, and is located on a row, given by a letter.
• The cinema shows a specific movie at a specific time and day, in a specific cinema hall. Multiple movies can be shown simultaneously in different halls.
• A customer can book one or more seats for showing. The customer uses their phone number as identification.
Hint: for some entities, we can only define partial primary keys.
2 Company
Draw an E/R diagram for a company with the following requirements (oversimplified for illustrative purposes):
• Each department controls a number of projects, each of which has a unique name, a unique number, and a single location.
3 DVD Rental Company
Read the following case study, which describes the data requirements for a DVD rental company.
The DVD rental company has several branches throughout the United States. The data held on each branch is the branch address made up of street, city, state, and zip code, and the telephone number.
Each branch is given a branch number, which is unique throughout the company. Each branch is
1
a Identify the main entity types of the DVD rental company.
b Identify the main relationship types between the entity types described in part (a) and representeach relationship as an ER diagram.
c Determine the multiplicity constraints for each relationships described in part (b). Representthe multiplicity for each relationship in the ER diagrams created in part (b).
d Identify attributes and associate them with entity or relationship types. Represent each attributein the ER diagrams created in (c). e Determine candidate and primary key attributes for each (strong) entity type.
f Using your answers to parts (a) to (e), attempt to represent the data requirements of the DVD rental company as a single ER diagram. State any assumptions necessary to support your design.
4 Library
A new small library wants to keep track of the books they lend out. They are not absolutely sure about the information that is important to hold in the database but the following ideas should be included. They have thought about holding information about the loaners. Besides the usual stuff about names, email, etc. they want to be able to grant a VIP status to the loaners who have borrowed 10 books without returning any of them too late. They also want to know certain things about the books: They want to know about the author, genre, publisher, year of publishing, ISBN. About genre they mean that the books are categorized, so you can find books about fish for example. It is also very important that they know all about who have borrowed/returned the book and when. If a loaner has a book more than a month, the system must automatically block him as a loaner until the fine is paid. They might have forgotten some important information. If they do, please supply it to the system.
Design the database using UML
Note: Some of the information given above is not relevant for designing the database but the users/customers can never distinguish between design and use of databases.
2