1 / 33

Introduction to Relational Database Models: Key Concepts and Normalization Guidelines

Learn about the benefits of relational data models, keys, good database design, and normalization in this comprehensive guide. Understand the structure and advantages of the relational data model as well as the process of normalization to minimize data redundancies and anomalies.

jchu
Download Presentation

Introduction to Relational Database Models: Key Concepts and Normalization Guidelines

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. Chapter Three( Relational Data Model) Objectives Introduction to Relational Data Models. Advantages of Relational Data Models. Restriction of Relational Data Model. Keys. What are a good BD Design?

  2. Relational Data Model: • Developed by Codd in 1970. • Conceptually simple. • Based on mathematical set theory.

  3. Relational Data Model: • Most important advantage of the RDBMS is its ability to hide the complexities of the relational model from the user.

  4. Relational Database Models: Data are represented as a set of tables.

  5. Structure • A set of tables ( Relations). • Each relation has a unique name. • Each relation has a set of attributes. • Each relation has a set of tuples.

  6. Restriction on Relational DBMS • No two tuples are the same. • No two attributes are the same. • Order of tuples are immaterial. • Order of attributes are immaterial. • Value of attributes must be atomic.

  7. Relational Database Example: Students Courses Faculty

  8. Attributes of a Relation: Students (Name, ID, Major, Minor,….) Courses (C_num, Dept, Cr, Description) Faculty (Name, ID, SSN, …..)

  9. Tuples: Students (Name, ID, Major, Minor,….) _________________________________ John Smith 1111, ITEC, VART Mary Smith 2222, COSC, ITEC Lorry Joys 4444, ACCT, COSC Courses (C_num, Dept, Cr, Description) _______________________________________ 345 ITEC 3 Databases I 445 ITEC 3 Databases II

  10. Keys: (Constraints) A set of attributes whose values uniquely identify each entity. Examples: Students (Name, ID, Major, Minor,….) Courses (C_num, Dept, Cr, Description) Faculty (Name, ID, SSN, …..)

  11. Keys: (Constraints) Primary key: 1- Uniqueness: At any given time, no two tuples can have the same value for a given primary key. 2-Minimally: None of the attributes in primary key can be discarded without distorting the uniqueness property 3-Selection: A key selected by the Database Administrator.

  12. Primary key: • Key’s role is based on determination • If you know the value of attribute A, you can look up (determine) the value of attribute B. • ID  GPA

  13. Keys • Composite key • Composed of more than one attribute. • Key attribute • Any attribute that is part of a key. • Superkey • Any combination of attributes that uniquely identifies each row. • Candidate key

  14. Keys: (Constraints) Foreign key: An attribute(s) in an entity set (relation) which is the primary key of other entity set (relation) • Example: Department(Name, Dept_Id, ….) Faculty(Name, Id, Dept_Id,…)

  15. Normal Forms (Guidelines for RD design) • How do we know this is a good design? • If it is not a good design, What should we do? • Modify our design ??.

  16. Normalization: • Process for evaluating and correcting table structures to minimize data redundancies • Reduces data anomalies • Works through a series of stages called normal forms: • First normal form (1NF) • Second normal form (2NF) • Third normal form (3NF)

  17. Why Normalize Tables: • Structure of data does not handle data very well • The table structure appears to work; • Report generated with ease • Unfortunately, report may yield different results depending on what data anomaly has occurred

  18. Each table represents a single subject • No data item will be unnecessarily stored in more than one table • All attributes in a table are dependent on the primary key

  19. Normal Forms (Guidelines for RD design) • A relation is in 1NF if the values of domain is atomic for each attribute.

  20. First Normal Form: 1NF Example: • Person (Name Age Children ) Smith 42 John, Lori, Mark • Person (Name Age Children ) Smith 42 John Smith 42 Lori Smith 42 Mark

  21. First Normal Form: 1NF Example: • Student ( Name Birthday ) S1 Feb 2,91 S2 March 8,88 • Student (Name, D_Birth, M_Birth, Y_Birth) • Note: 2NF and 3NF Deal with the relationship between non-key and key

  22. First Normal Form: • Repeating group • Derives its name from the fact that a group of multiple entries of same type can exist for any single key attribute occurrence • Relational table must not contain repeating groups • Normalizing table structure will reduce data redundancies

  23. Second Normal Form: 2NF • A relation is in 2NF if it is in 1NF and every attribute is Fully dependent on the entire key in this relation.

  24. Second Normal Form: 2NF • A, B ---> C, D • A ---> D • Example: R(A,B,C,D) • D partially depends on A,B • C fully depends on A,B • A&B are prime (part of key) • If A is primary key. Is this in 2NF? • If A&B is primary key. Is this in 2NF?

  25. Second Normal Form: 2NF • What should we do with a relation which is not in 2NF? • Example: R(A,B,C,D) • A, B ---> C, D • A ---> D • R1 (A,B,C) • R2(A,D)

  26. Second Normal Form: 2NF • Example: What is the primary key? Part, Warehouse ---> Quantity Warehouse ---> Address

  27. Second Normal Form: 2NF • Problems: • Repetition of information: Changing the address W! • Unable to present information: Warehouse with no part • So … R1 (Warehouse, Address) R2 (Part, Warehouse, Quantity)

  28. Second Normal Form: 2NF • Example: Professor ---> Course Student ---> Degree Professor ---> Student Key? Not in 2NF R1(Student, Degree) R2(Professor, Course, Student)

  29. Third Normal Form (3NF): • A relation is 3NF if it is in 2NF and every non-key attribute non transitively depends on the Primary Key. • Example: R(A,B,C,D) • A, B --->D • D ---> C • Fact: 3NF is violated when a non-key is a fact about another non-key • R1(A,B,D) • R2(D,C)

  30. Third Normal Form (3NF): • Example: R(Employee, Dept, Location) • Employee ---> Dept Dept ---> Location R1(Employee, Dept) R2(Dept, Location) Problems?

  31. Third Normal Form (3NF): • Example: R(A,B,C,D) • A,B ---> C • A,C ---> D • So A,B is the Primary Key • Not in 3NF • R1(A,B,C) • R2(A,C,D)

  32. Null Values: • No data entry • Not permitted in primary key • It is represent as: • An unknown attribute value • A known, but missing, attribute value • A “not applicable” condition

  33. Controlled redundancy: • Makes the relational database work • Tables within the database share common attributes that enable the tables to be linked together • Multiple occurrences of values in a table are not redundant when they are required to make the relationship work • Redundancy exists only when there is unnecessary duplication of attribute values

More Related