Chapter 2
Download

Chapter 2







Advertisement
/ 35 []
Download Presentation
Comments
bidelia
From:
|  
(116) |   (0) |   (0)
Views: 88 | Added: 06-10-2012
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 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -




Slide 1

Chapter 2

The Relational Database Model

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

Slide 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

Slide 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

Slide 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

Slide 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

Slide 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

Slide 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

Slide 8

Example Tables

Figure 2.1

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

Slide 9

Simple Relational Database

Figure 2.2

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

Slide 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

Slide 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

Slide 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

Slide 13

Union

Combines all rows

Figure 2.5

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

Slide 14

Intersect

Yields rows that appear in both tables

Figure 2.6

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

Slide 15

Difference

Yields rows not found in other tables

Figure 2.7

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

Slide 16

Product

Yields all possible pairs from two tables

Figure 2.8

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

Slide 17

Select

Yields a subset of rows based on specified criterion

Figure 2.9

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

Slide 18

Project

Yields all values for selected attributes

Figure 2.10

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

Slide 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

Slide 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

Slide 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

Slide 22

Divide

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

Figure 2.17

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

Slide 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

Slide 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

Slide 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

Slide 26

Example 1:M Relationship

Figure 2.18

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

Slide 27

Example 1:M Relationship

Figure 2.20

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

Slide 28

Example M:N Relationship

Figure 2.23

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

Slide 29

Example M:N Relationship

Figure 2.24

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

Slide 30

Converting M:N Relationship to Two 1:M Relationships

Figure 2.25

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

Slide 31

Converting M:N Relationship to Two 1:M Relationships (con’t.)

Figure 2.26

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

Slide 32

Converting M:N Relationship to Two 1:M Relationships (con’t.)

Figure 2.27

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

Slide 33

Converting M:N Relationship to Two 1:M Relationships (con’t.)

Figure 2.28

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

Slide 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

Slide 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