Data modelling – how to analyse and normalize data

Logical Model

Talk to your client and create a logical model. Here is a very brief example. “Our students enroll in a program, they then complete as many units as required and get a grade for each unit.” At this point we can write a brief list of entities and attributes to get started.
studentId, studentName, studentAddress, programId, programDesc, unitId, unitName, grade, unitsCompleted

1NF

Label and select the primary key for the main thing you are trying to store

STUDENT(studentId#, studentName, studentAddress, programId, programDesc, unitId, unitName, grade, unitsCompleted)

Remove all derived attributes

STUDENT(studentId#, studentName, studentAddress, programId, programDesc, unitId, unitName, grade)

Identify repeating groups (a given student can do many units)

STUDENT(studentId#, studentName, studentAddress, programId, programDesc, ((unitId, unitName, grade)) )

Identify the primary key for the repeating group (a student can do many units) and create a new entity with a composite key (putting the parent primary key with the newly identified primary key)

STUDENT(studentId#, studentName, studentAddress, programId, programDesc)

STUDENT_UNIT(studentId#, unitId#, unitName, grade)

All other attributes must be functionally dependent on the primary key and there must be no repeating groups.

2NF (Identifying many to many relationships)

Remove partial dependency (each attribute has to be functionally dependent on the entire key not part of the key)

STUDENT_UNIT(studentId#, unitId#, unitName, grade)

unitName is not functionally dependent on studentId AND unitId so we create a new entity called Unit.

STUDENT(studentId#, studentName, studentAddress, programId, programDesc)

STUDENT_UNIT(studentId#, unitId#, grade)

UNIT( unitId#, unitName)

3NF (Identifying one to many relationships)

Remove non key attributes that are functionally dependent on other non key attributes. (Remove the transitive dependencies)

STUDENT(studentId#, studentName, studentAddress, programId, programDesc)

Notice how programDesc is functionally dependent on programId (which is not the primary key). It is known as being transitively dependent on the primary key which is s No No.

We therefore create a new entity

PROGRAM(programId, programDesc)

Assign a primary key

PROGRAM(programId#, programDesc)

and leave behind the value which was identified as being functionally depended on (programId) as a foreign key in its original entity.

STUDENT(studentId#, studentName, studentAddress, programId#)

PROGRAM(programId#, programDesc)

Final result

STUDENT(studentId#, studentName, studentAddress, programId#)

UNIT( unitId#, unitName)

STUDENT_UNIT(studentId#unitId#, grade)

PROGRAM(programId#, programDesc)

Here is another quick example where there are repeating fields within repeating fields

placeid, placename, buildingid, buildingname, roomid, roomname, mayorid, mayorname

1NF
Remove derived attributes, identify primary key of main item we are storing,

PLACE(placeid#, placename, (( buildingid, buildingname, ((roomid, roomname)) )) , mayorid, mayorname )

Identify repeating groups

PLACE(placeid#, placename, mayorid, mayorname)
PLACE_BUILDING(placeid#, buildingid#, buildingname)
PLACE_BUILDING_ROOM(placeid#, buildingid#, roomid#, roomname)

2NF
Remove partial dependencies

BUILDING(buildingid#, buildingname)
ROOM(roomid#, roomname)

3NF
Remove transient dependencies
PLACE(placeid#, placename, mayorid#)
MAYOR(mayorid#, mayorname)

FINAL RESULT
PLACE(placeid#, placename, mayorid#)
PLACE(placeid#, placename, mayorid, mayorname)
PLACE_BUILDING(placeid#, buildingid#, buildingname)
PLACE_BUILDING_ROOM(placeid#, buildingid#, roomid#, roomname)
BUILDING(buildingid#, buildingname)
ROOM(roomid#, roomname)
MAYOR(mayorid#, mayorname)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s