Data and Information Management

Data and Information Management
Normalization Assignment
To document dependencies you can use the following notation:
StudentID –> StLname, StFname, StDOB
ClassID –> ClassRoom, ClassCapacity, CourseID, InstructorID
InstructorID –> InstructorLname, InstructorFname
CourseID –> CourseName, CourseHrs, CourseDescription
ClassID, StudentID –> Grade
Instructions:
Create the original and 3NF dependency diagrams as well as an ERD in Visio that
captures
the information requirements described below.
Combine your diagrams and ERD into one file and save in PDF format, and submit
your file
Scenario:
A friend of yours works for a company that runs charter flights and has asked you to
help her evaluate
a database that contains the table structure shown below in Table 1. She created this
table so she can
keep track of the charter flights flown by the company’s planes. AC_NUMBER is the
aircraft number.
CHAR_PAX indicates the number of passengers carried. The CHAR_MILES entry is
based on roundtrip
miles, including pickup points. (Hint: Look at the data values to determine the nature
of the
relationships. For example, note that employee Melton has flown two charter trips as
pilot and one trip
as copilot.)
Assignment
1. You recognize that the existing table structure is very poor and want to help your
friend.
Given what you see in the table above, draw the dependency diagram for the existing
table
structure. Label all transitive and/or partial dependencies. (20 points)
2. You notice several dependencies in the original diagram and now need to break up
the
dependency diagram you drew to produce dependency diagrams that are in 3NF. As
you
work through the diagram you might have to create a few new attributes based on the
scenario description. You also should make sure that the new dependency diagrams
contain
attributes that meet proper design criteria; that is, make sure that there are no
multivalued
attributes, that the naming conventions are met, and so on.) (25 points)
3. Now that you have a better understanding of the data that your friend is trying to
track, list
the business rules that you have identified (for example, every charter trip must have a
at
least one crew member, i.e. pilot, but could have many). (10 points)
4. You know that normalization is a very useful tool to help eliminate data
redundancies,
however, it is incapable of serving as the sole source of good database design. Every
good
database design must start with a data model. Thus, to help your friend you decide to
create
an ER diagram to properly document and design the database that she needs. Based
on the
tables that you identified through normalization, create a Crow’s Foot ERD. Label all
entities, attributes, keys (primary and foreign) and relationships. Make sure all
attributes
are atomic and properly named. Resolve all many-to-many relationships by creating
associative entities and identify their primary and foreign keys. (20 points)