Develop database tables using a relational database system

Student_ID Student_ Name Major Course_ID Course_Title Instructor_Name
Instructor_Location Grade

168300458 Williams IT ITD320 Database Imp Codd B104 A

168300458 Williams IT ITP310 Programming Parsons B317 B

543291073 Baker Acctg ITD320 Database Imp Codd B104 C

543291073 Baker Acctg Acct201 Fund Acctg Miller H310 B

543291073 Baker Acctg Mktg300 Intro Mktg Bennett B212 A

Update Anomaly Insertion Anomaly
l Occurs when a change of a single attribute in one l Occurs when there does not appear to be any
Introduction to record requires changes in multiple records Introduction to reasonable place to assign attribute values to
Normalization Normalization
of l Example: of records in the database. Probably have
Database Database
Tables
uA staff person changes their telephone number Tables overlooked a critical entity.
and every potential customer that person ever l Example:
worked with has to have the corrected number u Adding new attributes or entire records when they
inserted. are not needed. Where do you place information
l Solution: on new Evaluator's? Do you create a dummy
u Put the employees telephone number in one Lead.
location--as an attribute in the employee table. l Solution:
u Create a new table with a primary key that
contains the relevant or functional dependent
attributes.




Database Tables and Normalization Database Tables and Normalization
l Conversion to First Normal Form l Dependency Diagram
Introduction to
u A relational table must not contain repeating groups. Introduction to
u The arrows above the entity indicate that the entity's
Normalization
of u Repeating groups can be eliminated by adding the
Normalization
of
attributes are dependent on the combination of
Database
Tables appropriate entry in at least the primary key Database
Tables
PROJ_NUM and EMP_NUM.
column(s). (See Database Table 5.3) u The arrows below the dependency diagram indicate
less desirable dependencies based on only a part of
the primary key -- partial dependencies.




Database Table 5.2 The Evergreen Data
Figure 5.1 A Dependency Diagram: First Normal Form




Database Tables and Normalization Database Tables and Normalization
l 1NF Definition l Conversion to Second Normal Form
Introduction to
u The term first normal form (1NF) describes the Introduction to
u Starting with the 1NF format, the database can be
Normalization
of
tabular format in which: Normalization
of
converted into the 2NF format by
Database Database
Tables
l All the key attributes are defined. Tables
l Writing each key component on a separate line, and then
l There are no repeating groups in the table. writing the original key on the last line and
l All attributes are dependent on the primary key. l Writing the dependent attributes after each new key.

PROJECT (PROJ_NUM, PROJ_NAME)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS,
CHG_HOUR)
ASSIGN (PROJ_NUM, EMP_NUM, HOURS)