1 / 7

DAT702 Normal Forms Normalization Rules

DAT702 Normal Forms Normalization Rules. Normal Forms. Normal Forms, also called Normalization rules, are basically processes or steps taken to allow for the efficient creation of Relational databases. Following these rules lead an administrator towards a set of tables that:

Download Presentation

DAT702 Normal Forms Normalization Rules

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. DAT702Normal FormsNormalization Rules

  2. Normal Forms Normal Forms, also called Normalization rules, are basically processes or steps taken to allow for the efficient creation of Relational databases. Following these rules lead an administrator towards a set of tables that: • allow data to be organized in an efficient manner • reduce disk space requirements for data storage • minimize or eliminate redundant data • greatly reduce the chance of data entry errors • provide programmers with a stable data platform from which to create efficient applications

  3. The Rules We will concentrate on the first three rules as they should be enough to create proper data tables for most circumstances. Rule 1: If a table contains repeating groups (fields) of data then these groups (fields) should be removed and placed into another table (child/foreign) as a single field. Rule 2: If a table contains records that have repeating data (redundant) then create a new table. Rule 3: All fields within a table must relate directly to and describe the Primary Key.

  4. Form 0(Flat File) • Table: Students • Student# Name Course1 CourseDesc1 Course2 CourseDesc2 • Fred DAT702 Database Admin CDD301 DataComm • Jane OPS400 AS/400 OPS440 Unix Admin Notice the repeating groups of data (Course1, Course2). Adding course data limited to number of current fields Violates all 3 Normal Form rules

  5. Correcting with Rule #1 • Table: Students • StudentNo Name Course1 CourseDesc1 Course2 CourseDesc2 • Fred DAT702 Database Admin DCN386 DataComm • Jane OPS400 AS/400 OPS435 Unix Admin Converted to 1st Normal Form Table: Students StudentNo Name CourseCode CourseDesc 12345 Fred DAT702 Database Admin 12345 Fred DCN386 DataComm 12456 Jane OPS400 AS/400 12456 Jane OPS435 Unix Admin Better: But still violates Rules 2 & 3

  6. Correcting with Rule #2 StudentNo Name CourseCode CourseDesc 12345 Fred DAT702 Database Admin 12345 Fred DCN386 DataComm 12456 Jane OPS400 AS/400 12456 Jane OPS435 Unix Admin Converted to 2rd Normal Form Table: Students Table: Courses StudentNo(P) Name 12345 Fred 12456 Jane CourseCode(P) StudentNo(F) CourseDesc DAT702 12345 Database Admin DCN386 12345 DataComm OPS400 12456 AS/400 OPS435 12456 Unix Admin Rule 3 violation Primary – Foreign Key Relationship

  7. Correcting with Rule #3 Table: Students Table: Courses CourseCode(P) StudentNo(F) CourseDesc DAT702 12345 Database Admin DCN386 12345 DataComm OPS400 12456 AS/400 OPS435 12456 Unix Admin StudentNo(P) Name 12345 Fred 12456 Jane Converted to 3rd Normal Form Table: Students Table: Stud_Courses Table: Courses StudentNo(P) Name 12345 Fred 12456 Jane CourseCode(F) StudentNo(F) DAT702 12345 DCN386 12345 OPS400 12456 OPS435 12456 CourseCode(P) Description DAT702 Database Admin DCN386 DataComm OPS400 AS/400 Primary – Foreign Key Relationship

More Related