1 / 17

Relational Terminology

Relational Terminology. Normalization. A method where data items are grouped together to better accommodate business changes Provides a method for representing data and relationships precisely in tabular format that makes the database efficient (redundancy kept to a minimum).

jatin
Download Presentation

Relational Terminology

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. Relational Terminology

  2. Normalization • A method where data items are grouped together to better accommodate business changes • Provides a method for representing data and relationships precisely in tabular format that makes the database efficient (redundancy kept to a minimum)

  3. Normalization begins after analysis of system needs • Normalization requires identification of entities, attributes and relationships

  4. Advantages of Normalization • Direct translation from logical to physical design in RDMS • Reduced Data Redundancy • Protection against update and delete anomalies • Ability to add or delete entities, attributes and relations without wholesale restructuring of tables

  5. Advantages (cont’d) • Smaller tables • Tables with fewer columns(fields) and therefore shorter rows(records), allowing more rows of data being used per I/O (input/output) operation making the database very efficient

  6. Unnormalized Employee SSN Name Dept Code Dept Name Dept Location Skill Code Skill Skill Name Skill Level Normalized Employee(p) SSN Name Dept Code Dept Name SSN(p) Skill Code Skill Name Skill Level 1NFRepeating Groups Not Allowed

  7. 2NF • Separate relations are required for any attributes that depend on only part of a composite key

  8. Second Normal Form Violated SSN Skill Code Skill Skill Name Skill Level Second Normal Form Achieved SSN(p) Skill Code(p) Skill Level Skill Code(p) Skill Name 2NF

  9. 3NF • Non key attributes should not contain facts about another nonkey attribute in the relation

  10. Third Normal Form Violated SSN Employee Name Department Code Department Name Department Location Third Normal Form Achieved SSN(p) Employee Name Department Code Department Code(p) Department Name Department Location 3NF

  11. 3NF • Each Attribute is a fact about the key, the whole key and nothing but the key

  12. 3NF • Experienced Database Architects will move directly to 3rd Normal Form. Fourth and Fifth Normal Forms are rarely used. • Normalization results in one Table, or Relation for each entity with attributes shown as columns(fields) and each occurrence as a row(record).

  13. Summary • Produces database designs that offer efficient performance • Minimizes chances for data inconsistency • In some cases performance advantages may be gained by violating normalization - Denormalization must be done with great care and caution

  14. Referential Integrity • Every foreign key value must have a corresponding primary key value • Enforcement optional

  15. Relational ModelData Stored & Retrieved As Tables • SQL Statement • SELECT NAME, PAYGRADE • FROM EMPLOYEE • WHERE DEPT = FIN001 • AND SALARY >50000

  16. Relational ModelData Stored & Retrieved As Tables • SQL Statement with two tables joined • SELECT NAME, GRADE, DEPT • FROM EMPLOYEE, PROJECT • WHERE AGE>40 • AND EMPLOYEE.LABOR_CODE = PROJECT.LABOR_CODE

  17. Guidelines For Development of Unique Identifiers(keys) • Must be unique • Must be mandatory • Values must never change • Values must be factless • Must be controllable • Must be usable

More Related