I wanted to check my answers for ISMG3500 (Enterprise Data and Content Management clas)

This module is on Normal Forms and Normalization

 

 

ISMG 3500 Homework 3, Due Wednesday 2/22 Before
class. Submit hard copies.
10 Points. Graded on a 50-Point scale then scaled down.
Problem 1 (8*1 Point = 8 Points) Answer Yes or No to the following Functional
Dependency questions. Base your answers on the data shown in Table X below. There
are no further rows in this table. Provide reasoning VERY briefly.
X:
A
a1
a1
a1
a3
a3
a5 B
b1
b1
b2
b2
b3
b3 C
c1
c2
c1
c3
c3
c2 D
d1
d1
d1 E
13
13
14
14
14
14 1). Does A→C?
2). Does C→A?
3) Does A→E?
4) Does B→E? Hint: consider null value as a regular
value. 5).Does B+C→D?
6). Does C→D?
7). Does C+E→A
8). Does A+C→D? 1 Problem 2 (7 Points) Normalize to 1NF:
Normalize the following table into first normal form. The table uses one row to record
information about each student. A student may take one or more electives. This table is
not in 1NF. Normalize this table so it is in 1NF. In your answer, list all columns of the new table,
identify the primary key, and
enter all data for the normalized table. SID
100345
200898
300987 LName
Ford
Gibbs
Jordan FName
George
Mary
Jeff Electives
Geometry, Dance
Orchestra
Orchestra, Creative Writing, Dance Problem 3 (4* 7 Points = 28 Points). Normalize each table to 2NF given the primary key
and additional functional dependencies.
For each table below, determine if the table satisfies 2NF. If the table is already in 2NF, say
so. If a table violates 2NF, say so and produce the 2NF tables. It is sufficient to just list
the results of 2NF normalization. Make sure all tables you write have the primary key
defined.
A. Book: (BookTitle, DateofPrint, #copiesPrinted, #copiesSold, Price,
MajorSponsorName, MajorSponsorPhone )
Additional FD: MajorSponsorName MajorSponsorPhone
B. ProjectMileStone: (ProjId, MilestoneId, MilestoneDesc, CompletionDate)
Additional FDs:
None
C. Tutoring: (TutorId, StudentID, TutorFName, TutorLName, Sfname, SLName,
#ofTimes, TotalFee)
Additional FD:
TutorId#TutorFName, TutorLName
StudentIdSFName, SLName
D. RoleAssignment: (ShowId, RoleId, ActorId, actorFName, actorLastName)
Additional FD:
ActorIdActorFName, ActorLastName 2 Problem 4 (7 Points): Refer to Problem 6 on Ex-3. Note that I’ve changed the data in the
table.
EmployeePrj
EmployeeId LastName FirstName ProjectId Assignment Assignment 1001
1001
1002
1002
1003
1004
1004
1004 Park
Park
Walters
Walters
Smith
Gupta
Gupta
Gupta Amy
Amy
Mary
Mary
Carol
Alok
Alok
Alok Proj1
Proj2
Proj2
Proj3
Proj3
Proj1
Proj2
Proj3 StartDate
6/4/2014
10/11/2014
10/11/2014
1/5/2015
1/7/2015
6/3/2014
10/1/2014
1/8/2015 EndDate
7/8/2014
11/9/2014
11/9/2014
7/8/2014
11/9/2014 Given the business rules as given in Ex3, Problem 6, the solution file has normalized this
table to 2NF and obtained the following final tables.
Employee: (EmployeeID, LastName, FirstName)
ProjectAssignment: (EmloyeeId, ProjectId, StartDate, EndDate)
Problem 4 Question: For this homework problem, present the final result of the 2NF
tables with data in them. 3

Attachments:

• 1493706004-Homework 3 ISMG3500 Spring 2017.docx