193 Memphis blues again
Figure 62: Album Table First Normal Form
The Artist information depends on the Track. (Think about an album with tracks by multiple artists.) To conform to the Second Normal Form, the two functional dependencies—big themes—must be broken into separate Entities.
Album
Figure 63: Album Entity Second Normal Form
To relate the Album entity to the Track Entity, it is necessary to create a primary key for the Album entity that can be used to create a key—foreign key relationship with the Track entity. It is also a good idea to give the Track entity a Primary key. Here is what the tables look like now:
Figure 64: Album Table Second Normal Form
AlbumKey AlbumTitle
ABRD Abby Road
BLBL Blond On Blond
Figure 65: Track Table Second Normal Form
TrackKey TrackTitle AlbumKey Artist ArtistCountry
HCTS Here Comes the Sun ABRD Beatles UK
SMTH Something ABRD Beatles UK
OPGD Octopus’s Garden ABRD Beatles UK
RDWM Rainy Day Woman BLBL Bob Dylan Us
SELL Sad Eyed Lady of the Lowlands BLBL Bob Dylan US SMMB Stuck in Memphis with the
Mobile Blues
BLBL Bob Dylan US
194 Example 2:
In the Contact spreadsheet example, there are really a two distinct types of contacts: Employees and Departments. They have separate attributes within the entity. Employee has LastName and FirstName attributes, for instance, which are always blank for Department. Separate themes should be given their own entities.
Below is the sample data reflecting the new entities. Creating the Employee entity required some additional changes. The ContactTitle entity is changed to EmployeeTitle and ContactKey is changed to EmployeeKey. The numbers have been changed to reflect the new relationship. Additionally information not in the original table has been added to fill in the Department Entity
EmployeeKey LastName FirstName Phone
Building
Figure 66: Employee Table
Building
Address Office DeptKey Type Status Email
1700 Broadway 314 1 Instruction FT [email protected] 1650 Broadway 212 2 Instruction PT [email protected] 1650 Broadway 113 2 Instruction PT [email protected]
1700 Broadway 114 3 Staff FT [email protected]
1650 Broadway 201 2 Exempt [email protected]
Figure 67: Employee Table Cont
DeptKey DeptAbrv DeptName DeptPhone
Building code
1 Hum Humanities 206.555.1300 BE
2 IT Information Technology 206.555.1200 SA
195
Edison 1700 Broadway 301
South Annex 1650 Broadway 200 Broadway
Edison 1700 Broadway 245
Broadway
Edison 1700 Broadway 124
Figure 68: Department table
TitleKey TitleName
Figure 70: Employee Title Table
There is still one major functional dependency in the entities remaining. Both the Employee and the Department entities contain a group related to building. “Building Name,” and “Building Address” both depend on “Building Code” and repeat whenever the attribute “Building Code” is present. Building is another separate theme and should have its own entity.
The new building entity looks like this:
BuildingKey BuildingCode BuildingName BuildingAddress 1 BE
Broadway
Edison 1700 Broadway
1 SA South Annex 1650 Broadway
196
Figure 71: Building Table
The “Building Code,” “Building,” and “Building Address” attributes in Employee and Department are replaced by the “BuildingKey” attribute. So they now look like this:
EmployeeKey LastName FirstName Phone
Building code
1 Able Susan 206.555.2356 1
2 Anderson Elliot 206.555.1029 2
3 Anderson Jolene 206.555.9001 2
4 Bradley Lisa 206.555.2323 1
5 Brown Martin 206.555.1200 2
Figure 72: Employee Table Second Normal Form
Office DeptKey Type Status Email
314 1 Instruction FT [email protected]
212 2 Instruction PT [email protected]
113 2 Instruction PT [email protected]
114 3 Staff FT [email protected]
201 2 Exempt [email protected]
Figure 73: Employee Cont.
DeptKey DeptAbrv DeptName DeptPhone
Building
code Office
1 Hum Humanities 206.555.1300 1 301
2 IT Information Technology 206.555.1200 2 200
3 MAT Math 206.555.1400 1 245
4 ADM Admissions 206.555.1000 1 124
Figure 74: Department Table
197 The ERD of the data in Second Normal Form looks like this:
Employee
Figure 75: Contact ERD Second Normal Form
Things to Watch out for
An un-normalized or poorly normalized database can lead to numerous problems including difficulties inserting or updating data, difficulties deleting data, problems with data integrity and the inability to retrieve the data you need.
Professor Collins looked through each of the entities for functional dependencies. He stops again at the Request entity.
198 Request
PK RequestKey FK1 CourseKey
RequestDate RequestStatus
RequestStudentName RequestStudentID RequestStudentEmail RequestNotes
Figure 76: Request Entity
“We already talked about the RequestNotes, but look at the entity again,“ he says. “Can you see two different things going on?”
Sharon looks at it for some time before she finally sees it. “Request is one theme, and student is another.”
“Yes, there is a functional dependency there. RequestStudentName, RequestStudentEmail, depend on RequestStudentID, rather than on the RequestKey.”
“I see that, but I was thinking was that a student shouldn’t have to register as a tutoring student to make a request for additional tutoring. In particular they shouldn’t have to enter all the demographic
information. In fact if they do register just to make a request, it may make it more difficult for Terry to develop her demographic reports.”
“I understand, but if you leave the entity the way it is, it could cause problems. The student information would be repeated with every request the student makes. That could lead to update and other
anomalies. “
Bill thinks about it for awhile. “I can think of a couple of solutions. The best solution would be to have them register in the student table. As I understand it, any student that wants tutoring must register.
199
Being registered, in and of itself, does not mean they are actually signing up for tutoring sessions. To do the demographics Terry would have to compare the student information to the student Keys in the Session table anyway. The other option is to create a Requester table that contains the student
information for those requesting tutoring. I don’t think this option is as strong because it creates a lot of potential redundancy.”
“Which one do you think I should go with?”
“I think you should talk it over with Terry. Use the Student Entity if possible.”
“Thanks.”
“OK, Let’s see how it looks for Third Normal Form.”