chapter 2
Download
Skip this Video
Download Presentation
Chapter 2

Loading in 2 Seconds...

play fullscreen
1 / 35

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


  • 473 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

slide7
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

slide19
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
  • 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
  • Points to location
  • Makes retrieval of data faster

Figure 2.31

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

ad