Chapter Three ( Relational Data Model)

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