Application for 5nf application the normalization

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: on Process The Unnormalized Data User View Remove repeating groups Data in First Normal Form (1NF) Remove partial dependencies (exist only when primary key is concatenated) Data in Second Normal Form (2NF) Remove transitive dependencies Data in Third Normal Form (3NF) 1. 2. Base Table All nonkey attributes depend on the primary key All nonkey attributes are independent of other nonkey attributes Normalization Example Normalization Registrar’s view of university student enrollment (unnormalized database): Registrar’s Stu Num Stu Name 6432 Adams Major ACCT Course ACCT2331 ACCT2332 MATH1421 7689 8653 Shinn Miller MANA ACCT MANA2366 HIST1010 ACCT4397 MATH1421 MANA2366 Crse Name Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study Calculus Intro Mgt Professor Stinson Francia Smith Phillips Archer Stinson Smith Phillips Office 390 370 230 343 285 390 230 343 Phone Grade 34898 34825 31892 32788 33561 34898 31892 32788 A B B C B B B C Are there any repeating groups that should be eliminated? If so, how? Remove Repeating Groups Remove Repeating Group Stu Num Stu Name PK Stu Num Stu Name Major Course Crse Name Professor PK Office Phone Grade Major ACCT MANA ACCT 6432 7689 8653 Adams Shinn Miller Student (3NF) Stu Num 6432 6432 6432 7689 7689 8653 Course ACCT2331 ACCT2332 MATH1421 MANA2366 HIST1010 ACCT4397 MATH1421 MANA2366 Crse Name Professor Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study Calculus Intro Mgt Stinson Francia Smith Phillips Archer Stinson Smith Phillips Office Phone Grade 390 370 230 343 285 390 230 343 34898 34825 31892 32788 33561 34898 31892 32788 A B B C B B B C Theme: student 8653 8653 What anomalies exist? Course-Grade (1NF) Remove Partial Dependencies Remove PK Stu Num Course Crse Name Professor Office Phone Grade PK Stu Num 6432 6432 6432 7689 7689 8653 8653 8653 Course ACCT2331 ACCT2332 MATH1421 MANA2366 HIST1010 ACCT4397 MATH1421 MANA2366 Grade A B B C B B B C Course-Grade (1NF) PK Course ACCT2331 ACCT2332 MATH1421 MANA2366 HIST1010 ACCT4397 • • Crse Name Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study • • Transitive Dependencies (depend on Professor, not Course) Office 390 370 230 343 285 390 • • Phone 34898 34825 31892 32788 33561 34898 • • Professor Stinson Francia Smith Phillips Archer Stinson • • FK Student-Grade (3NF) Course-Professor (2NF) What anomalies exist? Theme: grades Remove Transitive Dependencies Remove Transitive Dependencies (depend on Professor, not Course) Course Crse Name Professor Office Phone Course-Professor (2NF) PK Course ACCT2331 ACCT2332 MATH1421 MANA2366 HIST1010 ACCT4397 • • • Crse Name Fin Acct Mgt Acct Calculus Intro Mgt US Hist Ind Study • • • FK Professor Stinson Francia Smith Phillips Archer Stinson • • • PK Professor Stinson Francia Smith Phillips Archer Office 390 370 230 343 285 Phone 34898 34825 31892 32788 33561 Professor (3NF) Course (3NF) Theme: course (assumes each prof teaches only one section of each course) Theme: professor (assumes phone depends on prof, not office) Four Normalized Tables Four Linked by Common Attributes PK PK Student (3NF) Stu Num Stu Name Major Stu Num Course Grade Student-Grade (3NF) FK Course (3NF) Course Crse Name Professor Professor Office Phone Instructor (3NF) PK FK PK Accounting and Normalized Data Accounting Update anomalies can cause conflicting and obsolete database Update values values Insertion anomalies can result in unrecorded transactions and Insertion incomplete audit trails incomplete Deletion anomalies can result in loss of accounting records Deletion and destruction of audit trails and Summary Summary Splits unnormalized tables into smaller tables such that • All nonkey attributes in the table are dependent on the All primary key primary • All nonkey attributes are independent of the other nonkey All attributes attributes Look for “themes” for each table END OF CHAPTER 4 END...
View Full Document

Ask a homework question - tutors are online