ISM-4212
Assignment-2
ER Diagram
Due Tuesday, February 19th, 11:59 p.m.
1. For the following ER diagram, provide the logical model. You may provide your model below the ER diagram. Please save this document ISM4212Assign2ByLastNameFirstName.docx
Worth 1 point
STUDENTID*
StudentName
Time Registered
CourseID*
REGISTERS FOR
STUDENT
COURSE
CourseTitle
Credit Hours
Phone
Date Registered
2. For the following ER diagram, provide the logical model. (worth 1 point)
StudentID*
StudentName
Time Registered
CourseID*
Time Offered
CourseID*
Title
SectionID*
Days Offered
REGISTERS FOR
STUDENT
SECTION
Phone
Date Registered
OFFERED AS
COURSE
Credit Hrs
3. For the following ER diagram, provide the logical model. (worth 1 point)
EMPLOYEEID*
EMPLOYEE
Phone
Name
Description
StartDate
ASSIGNED TO AT A TIME
PROJECT
ProjectID*
ExpectedCompletionDate
4. For the following ER diagram, provide the logical model. (worth 1 point)
ApplicantID*
APPLICANT
Name
Applies For
PositionID*
Title
DateCloses
POSITION
DateOpen
Phone
5. Suppose a relation in a logical model looks like this: (worth 2 points)
EntityTypeA (AttrA, AttrB, AttrC, AttrD) Suppose we know that AttrC is a multi-valued attribute.
a. Which normal form does this relation violate? ______________
b. Why?
c. Normalize the relation and provide a new logical model so that the problem identified in b is removed.
d. What normal form is each of your relations in c above is?
6. Suppose a relation in a logical model looks like this: (worth 2 points)
EntityTypeA (AttrA, AttrB, AttrC, AttrD, AttrE)
Suppose AttrB, AttrC, AttrD and AttrE are all single valued and atomic. Suppose we know that AttrD determines AttrE.
a. Which normal form is this relation in?
b. What is a problem in this relation?
c. What normal form does it violate?
d. Why?
e. Normalize the relation and provide a new logical model so that the problem identified in b is removed.
7. Suppose a relation in a logical model looks like this: (worth 2 points) EntityTypeA (AttrA, AttrB, AttrC, AttrD, AttrE)
Note that in this relation, we have a composite key. Suppose we know that AttrB alone determines AttrD. Suppose AttrA and AttrB together determine AttrC and AttrE.
a. Which normal form does this relation violate? ______________ b. Why?
c. Normalize the relation and provide a new logical model so that the problem identified in b is removed.
d. What normal form is each of your relations in c above is?