Chapter 4 Relational Model Characteristics

1. Chapter 4 Relational Model Characteristics Database Principles: Fundamentals of Design, Implementation, and ManagementTenth Edition

2. Objectives In this chapter, students will learn: • That the relational database model offers a logical view of data • About the relational model’s basic component: relations • That relations are logical constructs composed of rows (tuples) and columns (attributes) • That relations are implemented as tables in a relational DBMS

3. Objectives (cont’d.) • About relational database operators, the data dictionary, and the system catalog • How data redundancy is handled in the relational database model • Why indexing is important

4. age Student dob stdid name cgpa Converting ERD’s to Relational Schema Relational Schema: is a set of relations (tables) that represent data content of a problem (or ERD). Each relation must have a P.K. Conversion Rules: • Every entity in an ERD becomes a relation. Attributes + P.K. of the entity become attributes + P.K. of the relation. Table / Relational Schema: Student (stdid, name, cgpa, dob) Note: Calculated attributes do not become part of the relational schema.

5. A Logical View of Data • Relational model • View data logically rather than physically • Table • Structural and data independence • Resembles a file conceptually • Relational database model is easier to understand than hierarchical and network models

6. Tables and Their Characteristics • Logical view of relational database is based on relation • Relation thought of as a table • Table: two-dimensional structure composed of rows and columns • Persistent representation of logical relation • Contains group of related entities (entity set)

9. Keys • Each row in a table must be uniquely identifiable • Key: one or more attributes that determine other attributes • Key’s role is based on determination • If you know the value of attribute A, you can determine the value of attribute B • Functional dependence • Attribute B is functionally dependent on A if all rows in table that agree in value for A also agree in value for B

11. Types of Keys • Composite key • Composed of more than one attribute • Key attribute • Any attribute that is part of a key • Superkey • Any key that uniquely identifies each row • Candidate key • A superkey without unnecessary attributes

12. Types of Keys (cont’d.) • Entity integrity • Each row (entity instance) in the table has its own unique identity • Nulls • No data entry • Not permitted in primary key • Should be avoided in other attributes

13. Types of Keys (cont’d.) • Can represent: • An unknown attribute value • A known, but missing, attribute value • A “not applicable” condition • Can create problems when functions such as COUNT, AVERAGE, and SUM are used • Can create logical problems when relational tables are linked

14. Types of Keys (cont’d.) • Controlled redundancy • Makes the relational database work • Tables within the database share common attributes • Enables tables to be linked together • Multiple occurrences of values not redundant when required to make the relationship work • Redundancy exists only when there is unnecessary duplication of attribute values

16. Types of Keys (cont’d.) • Foreign key (FK) • An attribute whose values match primary key values in the related table • Referential integrity • FK contains a value that refers to an existing valid tuple (row) in another relation • Secondary key • Key used strictly for data retrieval purposes

18. Integrity Rules • Many RDBMs enforce integrity rules automatically • Safer to ensure that application design conforms to entity and referential integrity rules • Designers use flags to avoid nulls • Flags indicate absence of some value

21. The Data Dictionary and System Catalog • Data dictionary • Provides detailed accounting of all tables found within the user/designer-created database • Contains (at least) all the attribute names and characteristics for each table in the system • Contains metadata: data about data • System catalog • Contains metadata • Detailed system data dictionary that describes all objects within the database

23. The Data Dictionary and System Catalog (cont’d.) • Homonym • Indicates the use of the same name to label different attributes • Synonym • Opposite of a homonym • Indicates the use of different names to describe the same attribute

24. One–to–Many(1:M) Relationships Rule:To represent 1:M relationship, we add the P.K. of the relation created for the one-side entity to the relation created for the many-side entity as a Foreign Key. Briefly, Many-side takes the one-side’s P.K. as a F.K. Note that: The names of the attributes don’t have to be the same but must be of the same data type

25. The One to Many (1:M) Relationship Crow’s Foot example:

27. dname sname dno sid Enroll Student Department Example: (One – to – Many) Student (sid, name, dno ) dno: references Department (dno) Department (dno, dname)

28. dno id dname name direct Instructor Department One – to – One (1:1) Relationships Rule: Either one of the side (NOT BOTH) will take the P.K. of the other side as a F.K. Instructor ( id, name) Department (dno, dname, insId ) insId: References Instructor (id)

29. Crow’s Foot example

30. ccode Grade sid title sname take Course Student Many – to – Many (M:N) Relationships Rule: To represent many-to-many relationship we create a new relation for the relationship by taking the P.K.’s of both participating entities as F.K.s separately. The P.K. of the new relation is the combination of these two F.K.s. Student (sid, sname) Course (ccode, title) Take (stid, ccode, grade) Sid: references Student (sid) Ccode: references Course(ccode)

31. Crow’s Foot example

34. Recursive Relationships • A recursive relationship is a relationship that a relation has with itself. • Recursive relationships have the same rule as the binary relationships. • One-to-One and One-to-Many relationships are saved using Foreign Keys. • Many-to-Many relationships are saved by creating an intersecting relation.

35. Example: (Recursive Relationships) OR • Employee(empId, name, address, managerId) • managerId: References Employee (empId)

36. Example: (Recursive Relationships) Employee (empId, name, address) Manages( XManagerId, YManagerId)

37. Course Group Weak Relationships Rule: To represent a Weak Relationship we add the P.K. of the relation created for the Strong entity to the relation created for the Weak entity as Foreign Key and as part of the P.K. has ccode Course day title gno Course ( ccode, title) Course_Group (gno, day, ccode ) ccode: references Course (ccode)

38. pid name Person salary stid Student Instructor cgpa stid name cgpa pid Person salary Inheritance Relationships (Method I and Method II) Solution 1: Combine all subclasses and super classes together to form one entity. Convert the new entity to a relation. Person (pid, name, stid, cgpa, salary) P.K. of the super class is the P.K. of the new entity.

39. stid id name pid Student Instructor cgpa salary name Solution 2: Form a new entity for each subclass by combining with the super class. Super Class is eliminated. If the Subclass has a P.K., it is used as the P.K. If not, the P.K. of the super class is used as the P.K. of the new entity. Student (stid, pid, name, cgpa) Instructor (id, name, salary)

40. stid salary Solution 3: Leave all entities as they are representing the inheritance relationship as a set of one-much or weak relationship. The super class is always on the one (or strong) side. If a subclass has a P.K. its relationship is one-many otherwise, it is weak. typeof Student Student cgpa cgpa stid name name pid pid Person Person typeof typeof Instructor Instructor salary Person (pid, name) Student (stid, cgpa, pid ) pid: references Person (pid) Instructor (pid, salary) pid:references Person (pid)

41. Composite Attributes • When an entity has composite attribute, only the simple component attributes of the composite attribute are included in the relation. • Employee( empId, name, street, city, zip)

42. Multi-Valued Attributes • When an entity has multi-valued attribute, two new relations are created. • First relation contains all the attributes of the entity type except the multi-valued attribute. • Second relation contains two attributes that from the primary key of the second relation. • The first of these is the primary key for the first relation, which becomes a foreign key in the second relation • The second is the multi-valued attribute.

43. Multi-Valued Attributes Employee( empId, name, address) Employee_Skill(empId, skillId, description)

44. Data Redundancy Revisited • Data redundancy leads to data anomalies • Can destroy the effectiveness of the database • Foreign keys • Control data redundancies by using common attributes shared by tables • Crucial to exercising data redundancy control • Sometimes, data redundancy is necessary

46. Exercise 1

47. Exercise 2

48. Exercise 3

49. Exercise 4

50. Exercise 5