Deck 5: Entity-Relationship Modeling and Normalization

Question 1

Represent each of the following requirements with an ER diagram:
(a) A company called Perfect Pets runs a number of clinics. A clinic has many staff and a member of staff manages at most one clinic (not all staff manage clinics). Each clinic has a unique clinic number (clinicNo) and each member of staff has a unique staff number (staffNo).
(b) When a pet owner contacts a clinic, the owner's pet is registered with the clinic. An owner can own one or more pets, but a pet can only register with one clinic. Each owner has a unique owner number (ownerNo) and each pet has a unique pet number (petNo).
(c) When the pet comes along to the clinic, it undergoes an examination by a member of the consulting staff. The examination may result in the pet being prescribed with one or more treatments. Each examination has a unique examination number (examNo) and each type of treatment has a unique treatment number (treatNo).
(d) Represent the complete set of requirements in one ER diagram.

Answer 1

(a)
(b)
(c)
(d)

Question 2

Represent each of the following requirements with an ER diagram:
A regional council requires the design of a database system that can provide information on all schools in the region. The requirements collection and analysis phase of the database design process has provided the following data requirements for the schools database system.
(a) Every school has many pupils and many teachers. Each pupil is assigned to one school and each teacher work for one school only.
(b) Each teacher teaches more than one subject but a subject may be taught by more than one teacher. The database should store the number of hours a teacher spent teaching a subject. Data held on each teacher includes his/her national Insurance Number (NIN) name (first and last), sex, and qualifications. The data held on each subject includes subject title and type.
(c) Each pupil can study more than one subject and a subject may be studied by more than one pupil. Data held on each pupil includes the pupil's code, name (first and last), sex, and date of birth.
(d) Each school is managed by one of its teachers. The database should keep track of the date he/she started managing the school. Data stored on each school includes the school's code, name, address (town, street, and post code) and phone.

Answer 2

The complete diagram is shown below.

Question 3

Reliable Rentals Case Study
The requirements collection and analysis phase of the database design process has provided the following data requirements for a company called Reliable Rentals, which rents out vehicles (cars and vans). The Company has various outlets (garage/offices) throughout Glasgow. Each outlet has a number, address, phone number, fax number, and a manager who supervises the operation of the garage and offices at each site.
Each site is allocated a stock of vehicles for hire, however, individual vehicles may be moved between outlets, as required. Only the current location for each vehicle is stored. The registration number uniquely identifies each vehicle for hire and is used when hiring a vehicle to a client.
Clients may hire vehicles for various periods of time (minimum 1 day to maximum 1 year). Each individual hire agreement between a client and the Company is uniquely identified using a hire number. Information stored on the vehicles for hire include: the vehicle registration number, model, make, engine size, capacity, current mileage, date MOT due, daily hire rate, and the current location (outlet) of each vehicle.
The data stored on a hire agreement includes the hire number, the client's number, name, address and phone number, date the client started the hire period, date the client wishes to terminate the hire period, the vehicle registration number, model and make, the mileage before and after the hire period. After each hire a member of staff checks the vehicle and notes any fault(s). Fault report information on each vehicle is stored, which records the name of the member of staff responsible for the check, date checked, whether fault(s) where found (yes or no), the vehicle registration number, model, make and the current mileage.
The Company has two types of clients: personal and business. The data stored on personal clients includes the client number, name (first and last name), home address, phone number, date of birth and driving licence number. The data stored on business clients includes the client number, name of business, type of business, address, telephone and fax numbers. The client number uniquely identifies each client and the information stored relates to all clients who have hired in the past and those currently hiring a vehicle.
Information is stored on the staff based at various outlets including: staff number, name (first and last name), home address, home phone number, date of birth (DOB), sex, National Insurance Number (NIN), date joined the Company, job title and salary. Each staff member is associated with a single outlet but may be moved to an alternative outlet as required, although only the current location for each member of staff is stored.
-Create a conceptual schema for Reliable Rentals using the concepts of the Enhanced Entity-Relationship (EER) model. To simplify the diagram, only show entities, relationships and the primary key attributes. Specify the cardinality ratio and participation constraint of each relationship type. State any assumptions you make when creating the EER model (if necessary).

Answer 3

Question 4

Explain the purpose of data normalization and describe the main steps in the normalization process.

Question 5

The table shown below displays the details of the roles played by actors/actresses in films.

(a) Describe why the table shown below is not in first normal form (1NF).
(b) The table shown above is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.
(c) Identify the functional dependencies represented by the table shown above. State any assumptions you make about the data shown in this table (if necessary).
(d) Using the functional dependencies identified in part (c), describe and illustrate the process of normalization by converting the table shown in Figure 1 to Boyce-Codd Normal Form (BCNF). Identify the primary and foreign keys in your BCNF relations.
(e) Sketch an Entity-Relationship model for the data shown in table above.

Question 6

Briefly describe how the techniques of normalization and Entity-Relationship modeling can be used to produce a set of relations with desirable properties.

Question 7

Describe the purpose of normalizing data and identify the four most commonly used normal forms.Discuss how normal forms support a database designer.

Question 8

The table below lists customer/car hire data. Each customer may hire cars from various outlets throughout Glasgow. A car is registered at a particular outlet and can be hired out to a customer on a given date.

(a) The data in the table is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State any assumptions you make about the data.
(c) Using the functional dependencies identified in part (b), describe and illustrate the process of normalization by converting Table 1 to Third Normal Form (3NF) relations. Identify the primary and foreign keys in your 3NF relations.
(d) Sketch an Entity-Relationship model for the data shown in Table 1. Show all the entities, relationships, and attributes.

Question 9

Examine the table shown below. This table represents the hours worked per week for temporary staff at each branch of a company.

(a) The table shown above is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State any assumptions you make about the data (if necessary).
(c) Using the functional dependencies identified in part (b), describe and illustrate the process of normalization by converting Table 1 to Third Normal Form (3NF) relations. Identify the primary and foreign keys in your 3NF relations.
(d) Create an Entity-Relationship (ER) model using the Unified Modeling Language (UML) to represent the data shown in Figure 1. Your ER model should show all entities, relationships, and attributes.

Question 10

The table below lists customer/car hire data. Each customer may hire cars from various outlets throughout Glasgow. A car is registered at a particular outlet and can be hired out to a customer on a given date.

(a) The data in the table is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State any assumptions you make about the data.
(c) Using the functional dependencies identified in part (b), describe and illustrate the process of normalization by converting Table 1 to Third Normal Form (3NF) relations. Identify the primary and foreign keys in your 3NF relations.

Question 11

The table below lists customer/car hire data. Each customer may hire cars from various outlets throughout Glasgow. A car is registered at a particular outlet and can be hired out to a customer on a given date.

(a) The data in the table is susceptible to update anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table.
(b) Identify the functional dependencies represented by the data shown in the table. State any assumptions you make about the data.
(c) Using the functional dependencies identified in part (b), describe and illustrate the process of normalization by converting Table 1 to Third Normal Form (3NF) relations. Identify the primary and foreign keys in your 3NF relations.

Question 12

The table below provides osme sample data for an agency called Hotel Services supplies part-time/temporary staff to hotels within Strathclyde region. The relation in Figure 2 lists the number of hours worked by each staff at various hotels. The relation is first normal form (1NF). Assuming that a contract is for one hotel only but a staff may work in more than one hotel on different contracts, identify the functional dependencies represented by the data in this relation.

Question 13

Given the following relation schema and its functional dependencies:

(a) Specify candidate keys and state the primary key.
(b) Assuming that the relation is in first normal form (1NF), describe and illustrate the process of normalising the relation schema to second (2NF) and third (3NF) normal forms. Identify the primary and foreign keys in your schemas.

Question 14

The table below represents data about employees of a company and the projects they work on. An employee may work on one or more projects a certain number of hours

Assuming that the functional dependencies in the relation in Figure 2 will hold for any additional data, which of the following functional dependencies are true and which are false? Justify your answer.

Question 15

Given the following relational schema and its functional dependencies:

(a) Specify candidate keys and state the primary key.
(b) Assuming that the relation is in first normal form (1NF), describe and illustrate the process of normalising the relational schema to second (2NF) and third (3NF) normal forms. Identify the primary and foreign keys in your third normal forms.

Question 16

(a)Give a set of functional dependencies for the relational schema R(A, B, C, D) with primary key AB under which R is in 1NF but not in 2NF.
(b) Give a set of functional dependencies for the relational schema R(A, B, C, D) with primary key AB under which R is in 2NF but not in 3NF.
(c) Consider a relational schema R(A, B, C) with a functional dependency B $\rightarrow$ C. If A is a candidate key of R, could R be in BCNF and, if so, under what conditions?

Question 17

Consider a relational schema R(A, B, C, D, E) with the following functional dependencies: A $\rightarrow$ B, BC $\rightarrow$ E, and ED $\rightarrow$ A.
(a) List all keys of R.
(b) Is R in 3NF?
(c) Is R in BCNF?

Question 18

Consider the following relational schemas and functional dependencies. Assume that the relations have been produced from a relation ABCDEFGHI and that all known dependencies for this relation are listed. State the strongest normal form for each one and, if appropriate, decompose to BCNF.
(a) R(A, B, C, D, E); A $\rightarrow$ B, C $\rightarrow$ D
(b) S(A, B, F); AC $\rightarrow$ E, B $\rightarrow$ F
(c) T(A, D); D $\rightarrow$ G, G $\rightarrow$ H
(d) U(C, D, H, G); A $\rightarrow$ I, I $\rightarrow$ A
(e) V(A,C, E, I)

Question 19

Consider the relational schema R(A, B, C, D). For each of the following functional dependencies identify the candidate key(s) for R and state its strongest normal form. If appropriate, decompose to BCNF.
(a) B $\rightarrow$ C, C $\rightarrow$ A, C $\rightarrow$ D
(b) B $\rightarrow$ C, D $\rightarrow$ A
(c) ABC $\rightarrow$ D, D $\rightarrow$ A
(d) A $\rightarrow$ B, A $\rightarrow$ C, BC $\rightarrow$ D
(e) AB $\rightarrow$ C, AB $\rightarrow$ D, C $\rightarrow$ A, D $\rightarrow$ B