2 Chapter 2 The Relational Database Model Database

2 Chapter 2 The Relational Database Model Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel

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 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 2

2 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 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 3

2 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 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 4

2 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 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 5

2 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 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 6

2 Keys • One or more attributes that determine other attributes – Key attribute – Composite key • Full functional dependence • Entity integrity – Uniqueness – No ‘null’ value in key Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 7

2 Example Tables Figure 2. 1 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 8

2 Simple Relational Database Figure 2. 2 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 9

2 Keys (con’t. ) • Superkey – Uniquely identifies each entity • 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 – Values must match primary key in another table Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 10

2 Integrity Rules • Entity integrity – Ensures all entities are unique – Each entity has unique key • Referential integrity – Foreign key must have null value or match primary key values – Makes it impossible to delete row whose primary key has mandatory matching foreign key values in another table Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 11

2 Relational Database Operators • Relational algebra determines table manipulations • Key operators – SELECT – PROJECT – JOIN • Other operators – – – INTERSECT UNION DIFFERENCE PRODUCT DIVIDE Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 12

2 Union Combines all rows Figure 2. 5 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 13

2 Intersect Yields rows that appear in both tables Figure 2. 6 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 14

2 Difference Yields rows not found in other tables Figure 2. 7 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 15

2 Product Yields all possible pairs from two tables Figure 2. 8 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 16

2 Select Yields a subset of rows based on specified criterion Figure 2. 9 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 17

2 Project Yields all values for selected attributes Figure 2. 10 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 18

2 Join Information from two or more tables is combined Figure 2. 11 Figure 2. 14 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 19

2 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 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 20

2 Other Joins • Equi. JOIN – 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 – Equi. JOIN 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 – Right and left Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 21

2 Divide Requires user of single-column table and two-column table Figure 2. 17 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 22

2 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 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 23

2 Relationships within Relational Database • Relationship classifications – 1: 1 – 1: M – M: N • E-R Model – ERD Maps E-R model – Chen – Crow’s Feet Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 24

2 ERD Symbols • Rectangles represent entities • Diamonds represent the relationship(s) between the entities • “ 1” side of relationship – Number 1 in Chen Model – Bar crossing line in Crow’s Feet Model • “Many” relationships – Letter “M” and “N” in Chen Model – Three pronged “Crow’s foot” in Crow’s Feet Model Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 25

2 Example 1: M Relationship Figure 2. 18 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 26

2 Example 1: M Relationship Figure 2. 20 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 27

2 Example M: N Relationship Figure 2. 23 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 28

2 Example M: N Relationship Figure 2. 24 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 29

2 Converting M: N Relationship to Two 1: M Relationships Figure 2. 25 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 30

2 Converting M: N Relationship to Two 1: M Relationships (con’t. ) Figure 2. 26 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 31

2 Converting M: N Relationship to Two 1: M Relationships (con’t. ) Figure 2. 27 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 32

2 Converting M: N Relationship to Two 1: M Relationships (con’t. ) Figure 2. 28 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 33

2 Data Redundancy Revisited • Foreign keys can reduce redundancy • Some redundancy is desirable – Called controlled redundancy – Speed – Information requirements Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 34

2 Indexes • Points to location • Makes retrieval of data faster Figure 2. 31 Database Systems: Design, Implementation, & Management, 5 th Edition, Rob & Coronel 35