ISYS3412 Practical Database Concepts
2020 Semester 1
Week 4 Tute/lab – Entity-Relationship (ER) Model
The objectives of this tute/lab session are:
• Learn about the entity-relationship model;
• Learn how to build an entity-relationship model;
• Learn how to apply cardinality and participation correctly;
• Learn how to use LucidChart to draw ER models
Activity 2: ER Modeling Exercise.1
1. [Easy] For each of the following description, draw an ER diagram showing entities, attributes, and relationships and their multiplicity. Where necessary state any assumptions.
a. Customers identified by a name and have an address. They purchase items. Items are identified by a number and have a colour . The quantity bought of each item is recorded.
Answer:
Assumptions:
Customers can exist even if they didn’t buy any item. There can be items that’s never been bought.
As such, both ends of the ‘buys’ relationship is partial.
b. Students are allowed to register in only one program at any given time. They enrol in courses in each semester. Students can enrol in the same course multiple times across different semesters. The system keeps track of marks obtained in each enrolment.
Answer: This question has been discussed in detail within Week 5 Tute/Lab session (preamble section). Please refer that for the complete answer.
1 Some questions were adopted from following text books: (1) Database Management Systems by Raghu Ramakrishnan and Johannes Gehrke, McGraw Hill Publications; (2) Database Systems: A Practical Approach to Design, Implementation and Management by Thomas Connolly and Caroline Begg, Pearson Education.
c. A real estate agency keeps track of rental properties their agents manage. Each property has a unique address and other attributes, such as no. of bedrooms, no. of car spaces and type (unit |house| semi-detached). Each property is owned by one or more landlords. Landlords are uniquely identified by their email address. Each property is assigned to an agent. At any given point in time, there can be only one active rental contract but, it is required to retain all expired contracts as well. There could be one or more tenants party to a contract. A contract has a unique contract number, monthly rental, bond amount and start and end dates.
Answer:
Assumptions:
Landlords can have multiple properties.
One property can be owned by more than one landlord.
Landlords exist in the system, if and only if they have at least one property in the system.
Tenants exist in the system, after they sign a contract (i.e after. became a tenant). Prospective tenants’ details are not stored.
2. [Easy] The officials at Essendon Airport have decided that all information related to the airport should be organized using a DBMS, and you’ve been hired to design the database. Your first task is to organize the information about all the airplanes that are stationed and maintained at the airport. The relevant information is as follows:
a. Every airplane has a registration number, and each airplane is of a specific model.
b. The airport accommodates a number of airplane models, and each model is identified by a model number (e.g., DC-10) and has a capacity and a weight.
c. A number of technicians work at the airport. You need to store the name, SSN, address, phone number, and salary of each technician.
d. Each technician is an expert on one or more plane model(s), and his or her expertise may overlap with that of other technicians. This information about technicians must also be recorded.
e. The airport has a number of tests that are used periodically to ensure that airplanes are still airworthy. Each test has a Civil Aviation Safety
Authority (CASA) test number, a name, and a maximum possible
score.
f. The CASA requires the airport to keep track of each time that a given
airplane is tested by a given technician using a given test. For each testing event, the information needed is the date, the number of hours the technician spent doing the test, and the score that the airplane received on the test.
Answer:
Assumptions:
Each aircraft model has at least one technician who is an expert in that model.
Each technician is an expert in at least one model.
Some aircraft models may not have real planes (i.e. we keep model information, but currently no registered planes of that model.
After some discussion at tutorials, we were tossing between having the “testEvent” as a ternary relationship vs. making it a strong entity (by giving it a unique testEvent number). There is merits in doing so, especially considering the test event has its own attributes too.
In that case, the model will be like the following.
3. [Easy] Create an ER model for each of the following descriptions:
a. A large organization has several parking lots, which are used by staff. b. Each parking lot has a unique name, location, capacity, and number
of floors (where appropriate).
c. Each parking lot has parking spaces, which are uniquely identified
using a space number.
d. Members of staff can request the sole use of a single parking space.
Each member of staff has a unique number, name, telephone
extension number, and vehicle license number.
e. Represent all the ER models described in parts (a), (b), (c), and (d) as
a single ER model. Provide any assumptions necessary to support
your model.
Answer:
Assumptions:
Point C is vague – it is not clear whether the space number unique across all parking spaces, or it is only unique within a particular parking lot. Some discussions ensued during the tutorials and decided that it is natural to assume that it is only unique within a particular parking lot. As a result, ParkingSpace became a weak entity with SpaceNo being a partial key.
4. [Moderate] Create an ER model to represent the data use by the library. The library provides books to borrowers. Each book is described by title, edition and year of publication and is uniquely identified using the ISBN. Each borrower is described by his or her name and address and is uniquely identified using a borrower number. The library provides one or more copies of each book and each copy is uniquely identified using a copy number, status indicating if the book is available for loan and the allowable loan period for a given copy. A borrower may loan one or many books and the date each book is loaned out and is returned is recorded. Loan number uniquely identifies each book loan.
Answer:
Assumptions:
It is assumed that each book has at least one copy in the collection. There can be some book copies that was never loaned out.
5. [Hard] Read the following case study, which describes the data requirements for a DVD rental company. The DVD rental company has several branches throughout the USA. 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 allocated staff, which includes a Manager. The Manager is responsible for the day-to-day running of a given branch. The data held on a member of staff is his or her name, position, and salary. Each member of staff is given a staff number, which is unique throughout the company. Each branch has a stock of DVDs. The data held on a DVD is the catalog number, DVD number, title, category, daily rental, cost, status, and the names of the main actors, and the director. The catalog number uniquely identifies each DVD. However, in most cases, there are several copies of each DVD at a branch, and the individual copies are identified using the DVD number. A DVD is given a category such as Action, Adult, Children, Drama, Horror, or Sci-Fi. The status indicates whether a specific copy of a DVD is available for rent. Before hiring a DVD from the company, a customer must first register as a member of a local branch. The data held on a member is the first and last name, address, and the date that the member registered at a branch. Each member is given a member number, which is unique throughout all branches of the company. Once registered, a member is free to rent DVDs, up to maximum of ten at any one time. The data held on each DVD rented is the rental number, the full name and number of the member, the DVD number, title, and daily rental, and the dates the DVD is rented out and date returned. The rental number is unique throughout the company.
a. Identify the main entity types of the DVD rental company.
b. Identify the main relationship types between the entity types described in (a) and represent each relationship as an ER diagram.
c. Determine the multiplicity constraints for each relationship described in (b). Represent the multiplicity for each relationship in the ER diagrams created in (b).
d. Identify attributes and associate them with entity or relationship types. Represent each attribute in the ER diagrams created in (c).
e. Determine primary key attributes for each (strong) entity type.
f. Using your answers (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.
Answer: Left as an exercise.