Chapter 2: The Relational Database Model

1. Chapter 2 The Relational Database Model

2. In this chapter, you will learn: • That the relational database model takes a logical view of data • That the relational database model’s basic components are entities and their attributes, and relationships among entities • How entities and their attributes are organized into tables • 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 Hachim Haddouti and Rob & Coronel, Ch2

3. Logical View of Data • Relational Database • Designer focuses on logical representation rather than physical • Use of table advantageous • Structural and data independence • Related records stored in independent tables • Logical simplicity • Allows for more effective design strategies Hachim Haddouti and Rob & Coronel, Ch2

4. Logical View of Data (con’t.) • Entities and Attributes • Entity is a person, place, event, or thing about which data is collected • Attributes are characteristics of the entity • Tables • Holds related entities or entity set • Also called relations • Comprised of rows and columns Hachim Haddouti and Rob & Coronel, Ch2

5. Table Characteristics • Two-dimensional structure with rows and columns • Rows (tuples) represent single entity • Columns represent attributes • Row/column intersection represents single value • Tables must have an attribute to uniquely identify each row Hachim Haddouti and Rob & Coronel, Ch2

6. Table Characteristics (con’t.) • Column values all have same data format • Each column has range of values called attribute domain • Order of the rows and columns is immaterial to the DBMS Hachim Haddouti and Rob & Coronel, Ch2

7. Keys • One or more attributes that determine other attributes • Key attribute • Composite key • Functional dependence ( knowing STU_NUM you can determine STU_LNAME) • Entity integrity • Uniqueness • No ‘null’ value in key ( NULL= 0?) • Example: AUI ID#, Invoice#, CIN# Hachim Haddouti and Rob & Coronel, Ch2

8. Keys (con’t.) • Superkey • Uniquely identifies each entity in a table • Candidate key • Minimal superkey • Primary key • Candidate key to uniquely identify all other attributes in a given row • Secondary key • Used only for data retrieval • Foreign key (logical pointer) • Values must match primary key in another table Hachim Haddouti and Rob & Coronel, Ch2

9. Keys (con’t.) Hachim Haddouti and Rob & Coronel, Ch2

10. Integrity Rules • Entity integrity • Ensures all entities are unique • Each entity has unique primary key (not null) • Referential integrity • Foreign key must have null value or match primary key values (every non-null FK must reference an existing primary key) • Makes it impossible to delete row whose primary key has mandatory matching foreign key values in another table • No dangling references ( Links in HTML?) Hachim Haddouti and Rob & Coronel, Ch2

11. Relational Database Operators • Relational algebra determines table manipulations • Basic operators • SELECT • PROJECT • JOIN • Other operators • INTERSECT • UNION • DIFFERENCE • PRODUCT Hachim Haddouti and Rob & Coronel, Ch2

12. Union Combines all rows • Set-oriented union, duplicates will be eliminated • (columns and domains must be identical), same Attribute characteristics Hachim Haddouti and Rob & Coronel, Ch2

13. Intersect Yields rows that appear in both tables Figure 2.6 Hachim Haddouti and Rob & Coronel, Ch2

14. Difference Yields rows not found in other tables Figure 2.7 • Set-oriented difference • columns and domains must be identical, same Attribute characteristics Hachim Haddouti and Rob & Coronel, Ch2

15. Product (Cartesian product) Yields all possible pairs from two tables Hachim Haddouti and Rob & Coronel, Ch2

16. Select Yields a subset of rows based on specified criterion Figure 2.9 Hachim Haddouti and Rob & Coronel, Ch2

17. Project Yields all values for selected attributes (vertical subset of a table) Hachim Haddouti and Rob & Coronel, Ch2

18. Join Information from two or more tables is combined by equal AGENT_CODE Hachim Haddouti and Rob & Coronel, Ch2

19. Natural Join Process • Links tables by selecting rows with common values in common attribute(s) • Three-stage process • Product creates one table • Select yields appropriate rows • Project yields single copy of each attribute to eliminate duplicate columns Hachim Haddouti and Rob & Coronel, Ch2

20. Other Joins • EquiJOIN • Links tables based on equality condition that compares specified columns of tables • Does not eliminate duplicate columns • Join criteria must be explicitly defined • Theta JOIN • EquiJOIN that compares specified columns of each table using operator other than equality one • Outer JOIN • Matched pairs are retained • Unmatched values in other tables left null • Two types: right and left Hachim Haddouti and Rob & Coronel, Ch2

21. Data Dictionary and System Catalog • Data dictionary • Provides detailed account of all tables found within database • Metadata • Attribute names and characteristics • System catalog • Detailed data dictionary • System-created database • Stores database characteristics and contents • Tables can be queried just like any other tables • Automatically produces database documentation Hachim Haddouti and Rob & Coronel, Ch2

22. Indexes • Points to location • Makes retrieval of data faster Figure 2.31 Hachim Haddouti and Rob & Coronel, Ch2

23. Example 1 Identify and describe the components of the database table shown below using correct terminology. Hachim Haddouti and Rob & Coronel, Ch2

24. Example 1 cont. • ·   1 entity set: EMPLOYEE. • ·   5 attributes: EMP_NUM, EMP_LNAME, etc. • ·10 entities: the workers Friedman, Olansky, Fontein, and Cruazona. • ·one primary key: the attribute EMP_NUM because it identifies each row uniquely. • two foreign keys: the attribute DEPT_CODE, which probably references a department to which the employee is assigned and the attribute JOB_CODE which probably references another table in which you would find the description of the job and perhaps additional information pertaining to the job. Hachim Haddouti and Rob & Coronel, Ch2

25. Example1 cont. • Cutomer_no is superkey, (Customer_no, Customer_Name) is also superkey for the entity set CUSTOMER • The combination of primary keys of the participating entity sets forms a super key of a relationship set. • (customer-id, account-number) is the super key of depositor • Concept of superkey is not sufficient PS: depositor (customer-name, account-number) Hachim Haddouti and Rob & Coronel, Ch2

26. Example 2: Banking branch (branch-name, branch-city, assets) customer (customer-name, customer-street, customer-only) account (account-number, branch-name, balance) loan (loan-number, branch-name, amount) depositor (customer-name, account-number) borrower (customer-name, loan-number) • Stands for PROJECT,  for SELECT, &  for Union & INTERSECTION Hachim Haddouti and Rob & Coronel, Ch2

27. Example 2 Queries • Find all loans of over DH1200 amount> 1200 (loan) • Find the loan number for each loan of an amount greater than DH1200 loan-number (amount> 1200 (loan)) Hachim Haddouti and Rob & Coronel, Ch2

28. Example 2 Queries • Find the names of all customers who have a loan, an account, or both, from the bank customer-name (borrower)  customer-name (depositor) • Find the names of all customers who have a loan and an account at bank. customer-name (borrower)  customer-name (depositor) Hachim Haddouti and Rob & Coronel, Ch2

29. Example 2 Queries customer-name ( amount> 1200 (borrower.loan-number = loan.loan-number(borrower x loan))) • Find the names of all customers who have a loan at the Ifrane branch of an amount greater than DH1200. • Find the names of all customers who have a loan at the branch IFRANE. customer-name (branch-name=“IFRANE” (borrower.loan-number = loan.loan-number(borrower x loan))) Hachim Haddouti and Rob & Coronel, Ch2