Draw table for each part.
(B) Normalize the following table to 3NF and show the results of each normal form using the "listing dependencies" notation as shown in "Listing Dependencies.docx". State any assumptions you make about the table and data in a bulleted list (note that your assumption may be incorrect, so be cautious when making them) STU ID STU CLASS CLASS CLASS INSTR CLASS CLASSROOM CODE NAME NAME CREDHRS NAMEDAYS TIMES Assumptions: Write the tables in 1NF and the dependencies: Write the tables in 2NF and the dependencies: Write the tables in 3NF and the dependencies: Listing Dependencies EMPLOYEEEMP NUM, EMP NAME, JOB_CLASS, CHG HOUR) Page 1 of 2 FIGURE 6.3 FIRST NORMAL FORM (1NF) DEPENDENCY DIAGRAM Full: Transitive (JOB_CLASSCHG_HOUR) EMP NUMEMP NAME ASSIGNMENT PROJ NUM, EMP NUM, ASSIGN HOURS) PROIN UM PR NAME EMP NUM EMP NAME IOB CLASS CHG HOUR HOURS Another Example (based partially on Pages 224-225) Employees at Vandelay Industries may volunteer for many charitable organizations. These employees may also have several work project assignments at Vandelay Industries. In 3NF the table is: Partial dependency VOLUNTEER EMP NUM, ORG CODE, ASSIGN NUM Full Partial: Transitive: None Multivalued: EMP NUMORG CODE Sometimes it is difficult to draw dependency diagrams, especially if you do not have the tools available. Therefore, we are going to define a notation to list the dependencies based on the relational schema notation. Give the table a reasonable name if it does not already have one. The primary key should be bold and underlined. EMP NUMASSIGN NUM The other attributes may be non-bold unless it is clearly specified that they are mandatory. For example: PROJECT PROI NUM EMP NUM, PROT NAME, EMP_NAME, JOB_CLASS, CHG HOURS, HOURS) List the dependencies under the table, which include full, partial, transitive, and multivalued, dependencies. In 4NF (note that they added a few other attributes too): ORGANIZATION(ORGCODE. ORG_NAME) "If there is not a dependency type in the table, you can leave it out or put none. .You may exclude the functional dependency of the key to all the other attributes because that is implied, PROJECTPROJ CODE, PROJ NAME, PROJ BUDGET) other full dependencies need to be in the list. The determinant i on the left-hand side of the arrow and the dependents are on the nit. .There is no need to bold or underline the attributes in the dependency lists. For composite key, put a+ between each attribute in the composition and surround it by parentheses. Dependent attributes are in a comma separated list. Example: otes: Tables in 1NF may have all four dependency types. Tables in 2NF may have full, transitive, and multivalued dependencies, but no partial dependencies. Tables in 3NF may have full and multivalued dependencies, but not partial or transitive dependencies. Table sin 4NF will not have any dependencies listed unless there is multivalued dependencies that are not independent of each other. PROJECT PROI NUM. EMP NUM, PROT NAME, EMP_NAME, JOB CLASS, CHG HOURS, HOURS) Full: PROJ NUM EMP NUMHOURS PROJ NUM PROJ NAM Partial EMP NUM EMP NAME, JOB CLASS, CHO-HOUR You can create the arow with hyphen-hyphen-greater than sign and Microsoft products will typically convert it to an arrow automatically:--> becomes . In other editors, like the Moodle text box, you can just using--to represent the arrow Transitive: JOB CLASSCHG HOUR Multivalued: None If you have multiple tables, you need to st all dependencies for each table. Primary keys are always bold and underline, and foreign keys added during the normalization are always bold (ie. mandatory) and italics. Example: PROJECT PROJ NUM, PROJ NAME)
