Download

Chapter 2






Advertisement
/ 35 []
Download Presentation
Comments
bidelia
From:
|  
(116) |   (0) |   (0)
Views: 96 | Added:
Rate Presentation: 1 0
Description:
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
Chapter 2

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

Download Policy: Content on the Website is provided to you AS IS for your information and personal use only and may not be sold or licensed nor shared on other sites. SlideServe reserves the right to change this policy at anytime. 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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -




Chapter 2Slide 1

Chapter 2

The Relational Database Model

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

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

Logical view of dataSlide 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

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

Logical view of data con tSlide 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

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

Table characteristicsSlide 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

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

Table characteristics con tSlide 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

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

The relational database model database systems design implementation and management fifth edition rob and coronelSlide 7

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 tablesSlide 8

Example Tables

Figure 2.1

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

Simple relational databaseSlide 9

Simple Relational Database

Figure 2.2

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

Keys con tSlide 10

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 rulesSlide 11

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 operatorsSlide 12

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

UnionSlide 13

Union

Combines all rows

Figure 2.5

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

IntersectSlide 14

Intersect

Yields rows that appear in both tables

Figure 2.6

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

DifferenceSlide 15

Difference

Yields rows not found in other tables

Figure 2.7

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

ProductSlide 16

Product

Yields all possible pairs from two tables

Figure 2.8

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

SelectSlide 17

Select

Yields a subset of rows based on specified criterion

Figure 2.9

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

ProjectSlide 18

Project

Yields all values for selected attributes

Figure 2.10

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

The relational database model database systems design implementation and management fifth edition rob and coronelSlide 19

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 processSlide 20

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 joinsSlide 21

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

DivideSlide 22

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 catalogSlide 23

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 databaseSlide 24

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 symbolsSlide 25

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 relationshipSlide 26

Example 1:M Relationship

Figure 2.18

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

Example 1 m relationship1Slide 27

Example 1:M Relationship

Figure 2.20

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

Example m n relationshipSlide 28

Example M:N Relationship

Figure 2.23

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

Example m n relationship1Slide 29

Example M:N Relationship

Figure 2.24

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

Converting m n relationship to two 1 m relationshipsSlide 30

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 tSlide 31

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 t1Slide 32

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 t2Slide 33

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 revisitedSlide 34

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

IndexesSlide 35

Indexes

  • Points to location

  • Makes retrieval of data faster

Figure 2.31

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


Copyright © 2014 SlideServe. All rights reserved | Powered By DigitalOfficePro