CHAPTER 3 THE RELATIONAL DATA MODELS PREPARED BY DR MARJAN BINTI MOHD NOOR LEARNING OBJECTIVES On completion of this chapter you should be able to Describe the relational database model s logical structure Identify the relational model s basic components and explain the structure contents and characteristics of a relational table Use relational database operators to manipulate relational table contents Explain the purpose and components of the data dictionary and system catalog Identify appropriate entities and then the relationships among the entities in the relational database model Describe how data redundancy is handled in the relational database model Explain the purpose of indexing in a relational database Relational model View data logically rather than physically Entity set Contains group of related entities Table Two dimensional structure composed of rows and columns Persistent representation of logical relation A LOGICAL VIEW OF DATA Table Structural and data independence Resembles a file conceptually Relational Database Model It is easier to understand than hierarchical and network models Logical view of relational database based on relation Relation thought of as a table TABLES AND THEIR CHARACTERISTICS CHARACTERISTICS OF A RELATIONAL TABLES A table is perceived as a two dimensional structure composed of rows and columns All values in a column must conform to the same data format Each table row tuple represents a single Each column has a specific range of values entity occurrence within the entity set known as the attribute domain Each table column represents an attribute The order of the rows and columns is and each column has a distinct name immaterial to the DBMS Each intersection of a row and column represents a single data value Each table must have an attribute or combination of attributes that uniquely identifies each row KEYS AND DEPENDENCIES Keys consist of one or more attributes that determine other attributes Primary key attribute or combination of attributes that uniquely identifies a row Ensure that each row in a table is uniquely identifiable Establish relationships among tables and to ensure the integrity of the data Determination If you know the value of attribute A you can determine the value of attribute B FUNCTIONAL INDEPENDENCE FULL FUNCTIONAL DEPENDENCE Attribute B functionally dependent on A if all rows in table that agree in value for A also agree in value for B used to refer to functional dependencies in which the entire collection of attributes in the determinant is necessary for the relationship Continued STU NUM STU LNAME Determinant Dependent STU NUM STU LNAME STU FNAME STU GPA STU NUM STU GPA STU LNAME STU FNAME STU INIT STU PHONE STU HRS STU HRS STU CLASS TYPES OF KEYS Composite key Key attribute Key that is composed of more than one attribute Attribute that is a part of a key Key that can uniquely identify any row in the table Superkey Candidate key Minimal superkey Foreign Key Referential Integrity An attribute whose values match primary key FK contains a value that refers to an existing valid values in the related table tuple row in another relation Secondary key Key used strictly for data retrieval purposes TYPES OF KEYS ENTITY INTEGRITY NULLS condition in which each row in the table has its own unique identity All of the values in the primary key must be unique No key attribute in the primary key can contain a null 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 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 TYPES OF KEYS Table 3 3 Relational Database Keys Key Type Definition Superkey An attribute or combination of attributes that uniquely identifies each row in a table Candidate key A minimal irreducible superkey a superkey that does not contain a subset of attributes that is itself a superkey Primary key A candidate key selected to uniquely identify all other attribute values in any given row cannot contain null entries Foreign key An attribute or combination of attributes in one table whose values must either match the primary key in another table or be null Secondary key An attribute or combination of attributes used strictly for data retrieval purposes INTEGRITY RULES Relational database integrity rules are very important to good database design Relational database management systems RDBMSs enforce integrity rules automatically Much safer to make sure the application design conforms to entity and referential integrity rules Table 3 4 Integrity Rules Entity Integrity Description Requirement All primary key entries are unique and no part of a primary key may be null Referential Integrity Description Purpose Example Requirement Purpose Example Each row will have a unique identity and foreign key values can properly reference primary key values No invoice can have a duplicate number nor can it be null in short all invoices are uniquely identified by their invoice number A foreign key may have either a null entry as long as it is not a part of its table s primary key or an entry that matches the primary key value in a table to which it is related every non null foreign key value must reference an existing primary key value It is possible for an attribute not to have a corresponding value but it will be impossible to have an invalid entry the enforcement of the referential integrity rule makes it impossible to delete a row in one table whose primary key has mandatory matching foreign key values in another table A customer might not yet have an assigned sales representative number but it will be impossible to have an invalid sales representative number INTEGRITY RULES FLAGS Special codes used to indicate the absence of some value CONSTRAINTS NOT NULL constraint placed on a column to ensure that every row in the table has a value for that column UNIQUE constraint restriction placed on a column to ensure that no duplicate values exist for that column RELATIONAL ALGEBRA Theoretical way of manipulating