Relational Model Chapter 3 PDF

CHAPTER 3 The Relational Model Learning Outcomes 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) an...

CHAPTER 3 The Relational Model Learning Outcomes 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 Learning Outcomes  About the data dictionary, and the system catalog  How data redundancy is handled in the relational database model  Why indexing is important A Logical View of Data  Relational model  Enables us to view data logically rather than physically  Table  Resembles a file conceptually  Has advantages of structural and data independence  Relational database model  Is easier to understand than hierarchical and network models Tables and Their Characteristics  Logical view of relational database is based on relation  Table: two-dimensional structure composed of rows and columns  Contains group of related entity occurrences → an entity set  Terms entity set and table are often used interchangeably  Table also called a relation (Codd) Table = Entity set = Relation Tables and Their Characteristics Tables and Their Characteristics Tables and Their Characteristics 1. A table is perceived as a two-dimensional structure composed of rows and columns 2. Each table row (tuple) represents a single entity occurrence within the entity set 3. Each table column represents an attribute, and each column has a distinct name 4. Each row/column intersection represents a single data value Tables and Their Characteristics 5. All values in a column must conform to the same data format. 6. Each column has a specific range of values known as the attribute domain 7. The order of the rows and columns is immaterial to the DBMS 8. Each table must have an attribute or a combination of attributes that uniquely identify each row → must have a PRIMARY KEY Keys  Each row in a table must be uniquely identifiable  Key is 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 look up (determine) the value of attribute B  The attribute B is functionally dependent on A if A determines B  The attribute whose value determines another is called the determinant or the key  A determines B can be written as A → B  e.g. STU_NUM → STU_LNAME Types of Keys  Key attribute  Any attribute that is part of a key  Primary key (PK) is an attribute or a combination of attributes that uniquely identifies any given entity (row)  Composite key  Composed of more than one attribute  Superkey  Any key that uniquely identifies each row  Candidate key  A superkey without unnecessary attributes / minimal superkey  Super key is an attribute, or set of attributes, that uniquely identifies a tuple within a relation. Candidate key is a minimal super key or a super key with no redundant attribute. Primary key is the candidate key that is selected to identify tuples uniquely within the key relation. SUPERKEY CANDIDATE KEY PRIMARY KEY Example vehicleID carPlateNo engineID carName 101 WTY 1234 9999876 Proton Pesona 102 WXY 4567 5644321 Perodua Alza 103 CDA 3389 6667889 Proton Pesona 104 MCD 1745 1277653 Honda City Superkey: vehicleID vehicleID, carPlateNo vehicleID, carPlateNo, engineID carPlateNo vehicleID, engineID vehicleID, carPlateNo, carName engineID vehicleID, carName vehicleID, engineID, carName carPlateNo, engineID carPlateNo, engineID, carName carPlateNo, carName vehicleID, carPlateNo, engineID, carName engineID, carName Example vehicleID carPlateNo engineID carName 101 WTY 1234 9999876 Proton Pesona 102 WXY 4567 5644321 Perodua Alza 103 CDA 3389 6667889 Proton Pesona 104 MCD 1745 1277653 Honda City Candidate key: vehicleID carPlateNo engineID Primary Key: vehicleID Example vehicleID carPlateNo engineID carName 101 WTY 1234 9999876 Proton Pesona 102 WXY 4567 5644321 Perodua Alza 103 CDA 3389 6667889 Proton Pesona 104 MCD 1745 1277653 Honda City Alternate key: carPlateNo engineID Alternate Key is a candidate key that was not chosen to be the primary key of the relation. Keys  Secondary key  Key used strictly for data retrieval purposes  Foreign key (FK)  An attribute whose values match primary key values in the related table Foreign Key (FK) is an attribute or group of attributes that serves as the primary key of one relation and also appears in another relation (foreign key in this relation). FK establishes the relationship among tables. Keys  Nulls  No data entry  Not permitted in primary key  Should be avoided in other attributes  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 Keys  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 Keys  Relational Diagram  Shows link between tables  A primary key of one table (VENDOR) appears as the foreign key in a related table (PRODUCT)  Keys  Relational Schema  A textual representation of the database tables  Each table is listed by its name followed by the list of its attributes in parentheses  The primary key attribute(s) is (are) underlined  Example: VENDOR (VEND_CODE, VEND_CONTACT, VEND_AREACODE, VEND_PHONE) PRODUCT(PROD_CODE, PROD_DESCRIPT, PROD_PRICE, PROD_ON_HAND, VEND_CODE) Keys Integrity Rules  Many RDBMS enforce integrity rules automatically  Safer to ensure that application design conforms to entity and referential integrity rules Integrity Rules  Integrity Constraint Entity Integrity Referential Integrity Primary Key Foreign Key Must match a primary key value of some tuple in its home Cannot be null Unique relation or the foreign key value must be wholly null Integrity Rules Integrity Rules  To avoid nulls, designers use flags (indicate the absence of some value)  eg. -99 The Data Dictionary and System Catalog  Data dictionary  Provides detailed description 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  Sometimes described as “the database designer’s database” because it records the design decisions about tables and their structures A Sample Data Dictionary The Data Dictionary and System Catalog  System catalog  Contains metadata  Detailed system data dictionary that describes all objects within the database ◼ Table names, table’s creator, creation date, number of columns in each table, data type for each column, index filenames, index creators, authorized users and access privileges.  Terms “system catalog” and “data dictionary” are often used interchangeably  Can be queried just like any user/designer-created table  Relationships within the Relational Database  1:M relationship  Relational modeling ideal  Should be the norm in any relational database design  1:1 relationship  Should be rare in any relational database design  M:N relationships  Cannot be implemented as such in the relational model  M:N relationships can be changed into two 1:M relationships The 1:M Relationship  Relational database norm  Found in any database environment The 1:M Relationship The 1:1 Relationship  One entity can be related to only one other entity, and vice versa  Sometimes means that entity components were not defined properly  Could indicate that two entities actually belong in the same table  As rare as 1:1 relationships should be, certain conditions absolutely require their use The 1:1 Relationship The 1:1 Relationship The M:N Relationship  Can be implemented by breaking it up to produce a set of 1:M relationships  Can avoid problems inherent to M:N relationship by creating a composite entity or bridge entity  Composite entity table must contain at least the primary keys of original tables  Linking table contains multiple occurrences of the foreign key values  Additional attributes may be assigned as needed The M:N Relationship The M:N Relationship The M:N Relationship The M:N Relationship ERD for Tiny College Relational Diagram for Tiny College EXERCISE 1. Give examples of 1:M, 1:1 and M:N relationships. 2. Identify type of relationship (relationship classification) from the following scenario: The KwikTite Corporation operates many factories. Each factory is located in a region, and each region can be ‘home’ to many KwikTite’s factories. Each factory has many employees, but each employee is employed by only one factory. Exercise discussion Data Redundancy Revisited  Data redundancy leads to data anomalies  Suchanomalies 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 Data Redundancy Revisited Data Redundancy Revisited Indexes  Orderly arrangement used to logically access rows in a table  Index key  Index’s reference point  Points to data location identified by the key  Unique index  Index in which the index key can have only one pointer value (row) associated with it  Each index is associated with only one table Indexes Summary  Tables are basic building blocks of a relational database  Keys are central to the use of relational tables  Keys define functional dependencies  Superkey  Candidate key  Primary key  Secondary key  Foreign key Summary  Each table row must have a primary key which uniquely identifies all attributes  Tables can be linked by common attributes. Thus, the primary key of one table can appear as the foreign key in another table to which it is linked  Good design begins by identifying appropriate entities and attributes and the relationships among the entities.  Those relationships (1:1, 1:M, and M:N) can be represented using ERDs.