Chapter 3 Lab Exercise 1: Drawing E-R Diagrams

Drawing tool software such as SmartDraw, Dia, Microsoft Visio or similar products should be used for this laboratory exercise.  If these are not available, the drawing tool in Microsoft Word or the Windows Paint program may be used.

Using a drawing tool, draw an E-R diagram for the Employee-Project-Department example described here:

1. Consider the entity set Employee with attributes empId, socSecNo, empName, jobtitle, and salary

a. Show how the entity set and its attributes would be represented on an E-R diagram.

c. Identify all candidate keys for the entity set.

d. Identify a primary key for the entity set and underline it on the E-R diagram.

2. a. Assume in the same enterprise as in question 1, that there is an entity set called Project with attributes projName, startDate, endDate, and budget. Show how this entity set and its relationship to Employee would be represented on the E-R diagram. Assume you want to represent the number of hours an employee is assigned to work on a project, and show that in the diagram.

b. Stating any necessary assumptions, make a decision about the cardinality and participation constraints of the relationship, and add appropriate symbols to the E-R diagram.

c. Assume there is another entity called Department to be added. Each employee works for only one department. Projects are not directly sponsored by a department. Making up attributes as needed, add this entity and appropriate relationship(s) to the diagram.

Chapter 3 Lab Exercise 3: Drawing E-R Diagrams

Drawing tool software such as SmartDraw, Dia, Microsoft Visio or similar products should be used for this laboratory exercise.  If these are not available, the drawing tool in Microsoft Word or the Windows Paint program may be used.

Using a drawing tool, draw an E-R diagram for the Horse Racing schema shown in Figure 3.14 of the textbook. Explore the creation of entities, weak entities, attributes, and relationships, as well as the specification of cardinality constraints, keys, and partial vs. total relationships.

Chapter 3 Lab Exercise 4: Drawing EER Diagrams

Drawing tool software such as SmartDraw, Dia, Microsoft Visio or similar products should be used for this laboratory exercise.  If these are not available, the drawing tool in Microsoft Word or the Windows Paint program may be used.

Using a drawing tool, draw an EER diagram for the Horse Racing schema shown in Figure 3.23 of the textbook. Besides the creation of entities, attributes, and relationships, pay particular attention to the creation of total vs. partial subclasses, disjoint and overlapping specifications on subclasses, multiple inheritance, and the category specification.

Chapter 3 Lab Exercise 2: Drawing an EE-R Diagram

Using drawing tool software such as Visio, SmartDraw, or the drawing tool in Word, draw an EER diagram for the example described below

(a) Starting with the E-R diagram you created in Lab Exercise 1, assume the entity set Employees is specialized into Clerical, Sales, and Management. Add symbols to the current E-R diagram, showing how the specialization is represented on an EE-R diagram, making up attributes as needed, and stating any assumptions you need to make.

b. For the specialization in Part  (a) add relationships that show:

(i) All employees can have a pension plan

(ii) Sales employees have clients

(iii) Management employees have projects

c. Using (min,max) notation, add constraints for the relationships, stating any additional assumptions you need to make.