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)