Chapter 2
Download
1 / 35

The Relational Database Model Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel - PowerPoint PPT Presentation


  • 459 Views
  • Uploaded on

Chapter 2. The Relational Database Model Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel. In this chapter, you will learn: . That the relational database model takes a logical view of data

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'The Relational Database Model Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel' - bidelia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Chapter 2

Chapter 2

The Relational Database Model

Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel


In this chapter you will learn
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, 5th Edition, Rob & Coronel


Logical view of data
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, 5th Edition, Rob & Coronel


Logical view of data con t
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, 5th Edition, Rob & Coronel


Table characteristics
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, 5th Edition, Rob & Coronel


Table characteristics con t
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, 5th Edition, Rob & Coronel


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, 5th Edition, Rob & Coronel


Example tables
Example Tables

Figure 2.1

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Simple relational database
Simple Relational Database

Figure 2.2

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Keys con t
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, 5th Edition, Rob & Coronel


Integrity rules
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, 5th Edition, Rob & Coronel


Relational database operators
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, 5th Edition, Rob & Coronel


Union
Union

Combines all rows

Figure 2.5

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Intersect
Intersect

Yields rows that appear in both tables

Figure 2.6

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Difference
Difference

Yields rows not found in other tables

Figure 2.7

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Product
Product

Yields all possible pairs from two tables

Figure 2.8

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Select
Select

Yields a subset of rows based on specified criterion

Figure 2.9

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Project
Project

Yields all values for selected attributes

Figure 2.10

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Join

Information from two or more tables is combined

Figure 2.11

Figure 2.14

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Natural join process
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, 5th Edition, Rob & Coronel


Other joins
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

    • Right and left

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Divide
Divide

Requires user of single-column table and two-column table

Figure 2.17

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Data dictionary and system catalog
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, 5th Edition, Rob & Coronel


Relationships within relational database
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, 5th Edition, Rob & Coronel


Erd symbols
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, 5th Edition, Rob & Coronel


Example 1 m relationship
Example 1:M Relationship

Figure 2.18

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Example 1 m relationship1
Example 1:M Relationship

Figure 2.20

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Example m n relationship
Example M:N Relationship

Figure 2.23

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Example m n relationship1
Example M:N Relationship

Figure 2.24

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Converting m n relationship to two 1 m relationships
Converting M:N Relationship to Two 1:M Relationships

Figure 2.25

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Converting m n relationship to two 1 m relationships con t
Converting M:N Relationship to Two 1:M Relationships (con’t.)

Figure 2.26

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Converting m n relationship to two 1 m relationships con t1
Converting M:N Relationship to Two 1:M Relationships (con’t.)

Figure 2.27

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Converting m n relationship to two 1 m relationships con t2
Converting M:N Relationship to Two 1:M Relationships (con’t.)

Figure 2.28

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Data redundancy revisited
Data Redundancy Revisited (con’t.)

  • Foreign keys can reduce redundancy

  • Some redundancy is desirable

    • Called controlled redundancy

    • Speed

    • Information requirements

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


Indexes
Indexes (con’t.)

  • Points to location

  • Makes retrieval of data faster

Figure 2.31

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel


ad