Unit III – ER Model and Database Design

Entity-Relationship (ER) Model

ER Modeling and Diagram Design

1. Explain the different types of attributes that occur in the ER model. Also, discuss the various reasons that lead to the occurrence of NULL values.

2. When is the concept of weak entity type used in data modeling? Define the terms owner entity type and partial key.

3. Construct an ER diagram for a company database by assuming suitable entities, attributes, and relationships. Also, identify and specify the structural constraints.

4. Consider a company database that keeps track of:

   • Employees, Departments, and Projects

   • Projects controlled by Departments

   • An employee assigned to one Department but can work on multiple Projects

   • Each Department managed by one Employee

   • Each Employee can have Dependents

   • An Employee supervised by another Employee

   • The number of hours an Employee works on a Project is tracked

   Draw an ER diagram for the above scenario. Specify key attributes of each entity and the structural constraints on each relationship.

5. A university database contains information about professors (identified by SSN) and courses (identified by Course ID). Draw an ER diagram for each of the following cases:

   i) Professors can teach the same course over several semesters; each offering must be recorded
   ii) Each professor teaches exactly one course
   iii) Each professor teaches at least one course; some may teach multiple courses
   iv) Each professor teaches at least one course; some must teach all courses

6. Construct an ER Diagram for the following requirements of a restaurant.

   • The restaurant employs a number of chefs. A record is kept on each chef’s name, address, phone number and salary.
   • Each chef can prepare a number of meals. The name of the meal and the price of the meal are recorded.
   • Each meal consists of a number of ingredients. The name of the ingredient and the quantity required for that particular meal is recorded.
   • These meals are ordered by customers. A record is kept on the customer’s name, address and phone number. A record is kept of time and date the meal is ordered.
   • Further, more details about the menu and orders placed by the customers can be recorded to keep track of the meals that are frequently ordered by the customers.

   State any assumptions made in storing the menu details in the design of E R Diagram.

7. Construct an ER diagram for a restaurant management system with the following:

   • Chefs: name, address, phone number, salary

   • Meals: name, price (prepared by chefs)

   • Ingredients: name, quantity per meal

   • Customers: name, address, phone number

   • Orders: meal ordered, time and date

State any assumptions you make in storing menu details.

Database Design

1. Define the process of Normalization. Explain 1NF, 2NF, and 3NF with examples.

2. Define Normalization. Explain the different Normal Forms with respect to the Primary Key.

3. Explain the tests to check if a table is in 3NF. If not, provide the steps to convert it to 3NF.

4. What is a Functional Dependency? How can we determine if two functional dependencies are equivalent?

5. Define the various phases of the Database Design process. Illustrate with a diagram.

6. Explain Boyce-Codd Normal Form (BCNF). State the steps to normalize a table into BCNF.

---

Informal Guidelines for Schema Design

7. State and discuss four informal guidelines that may be used to determine the quality of a relational schema design.

8. Bring the following relation into First Normal Form (1NF):

Dname	Dnumber	Dmgr_ssn	Dlocation
Research	5	345599100	{Bellaire, Sugarland, Houston}
Admin	4	(NULL)	Stafford
Headoffice	1	985674900	{Houston, Texas}

Hint: Multi-valued attributes need to be flattened for 1NF.

---

9. State if the following table is in 1NF. If not, correct it.

Table: DEPARTMENT

Dname	Dnumber	Dmgr_ssn	Dlocations
Research	5	333445555	(Bellaire, Sugarland, Houston)
Administration	4	987654321	(Stafford)
Headquarters	1	888665555	(Houston)