rkmaruyama.netClass420ppt202RelationalDatabase.ppt by ert554898


									Relational Database
  I. Relational Database
1. Introduction
2. Database Models
3. Relational Database
4. Entity-Relationship Models
5. RDB Design Principles
       1. Introduction
 Is a collection of related data
 Is persistent

Database Management System
 Software system to add, delete, search,
  and modify the data in the DB
 To provide various ways to view the data in
  the DB
  2. Database Models
Flat Database
Hierarchical DB
Network DB
Relational DB
          Flat Database
Flat Database (Library DB)
 Publisher name is repeated
 Publisher phone number is repeated
 Author phone is repeated
 If publisher address is included, … ?

Update Anomalies
   To modify publisher phone, all need to be
Insertion Anomaly
   A new publisher, with no data about book,
    author, and other details which are not
    available, cannot be included.
Deletion Anomaly
   If a book is deleted, its publisher data, or
    author data, can also be eliminated (e.g.,
    last book in the list)
Break up Flat-file DB into Separe
Tables (Library DB)
   Hierarchical DB Model
                           Agency Database


           Entertainers                            Clients

                                     Engagements               Payments

                                                                              Back to Ref Ing

(This and remaining slides on DB Models are adapted from Database Design for Mere
Mortals, Michael Hernandez, Addison Wesley, 1999)
    Hierarchical DBM
One table acts as a root of an inverted
tree; other tables are branches
Each child table may have only one
parent table
Each parent table may have many child
To access any data, must start at root
      Hierarchical DBM
       Advantages &
   Data retrieve is fast, because table structure is
    explicitly linked
   Referential integrity is built in
   Difficult to store data in child table when no
    corresponding record exists in parent tables. E.g.,
    a new entertainer cannot be entered in the DB
    until a specific Agent is assigned
   Difficult to model complex relationships
   Difficult to add new tables
  Referential Integrity
Each record in a child table is linked to an
existing record in the parent table. E.g.,
every Payment record is associated with a
particular Client.
If a record is deleted in a parent table,all
associated record in child tables are also
deleted. E.g., If a particular Client record is
deleted, all related records in Payments and
Engagements are deleted—no orphans.
       Network Database
                  Agency Database
           represents                manages

       Clients                         Entertainers
   makes       schedules          performs       plays

Payments                Engagements             Musical Styles
       Network DBM
Invented to address problems with the
Hierarchical DB Model
A child table can have more than one parent
Can go up or down the structure. E.g., to
answer “Who was the agent that booked a
particular engagement?”, start with
Engagements, Clients, then to Agents.
    Relational Database
Collection of tables
   e.g., books, authors, publishers, clients
Each cell in a table is atomic
   i.e., no formulas, pointers, but single data item
Tables are linked by common values in
selected columns
   i.e., not by pointers
Books Table
Publishers Table
  Basic Terminology
  Data & Information
These are data values
Kashimata 25 October 31 25000
This is information
Customer’s last Name: Kashimata
Age: 25
Birthday: October 31
Savings Account Balance: $25,000
     Basic Terminology
Null, zero, and blank
   Age = Null
    age is undefined
   Age = 0
    age is 0
   MiddleName = Null
    middle name is undefined
   MiddleName = “”
    person has no middle name
   MiddleName = “ “
    no middle name, takes up 1 byte
 Basic Terminology
Table, Record, Field
Formal   Informal Non-relational

relation table     database

tuple    row       record

attribute column   field
    Basic Terminology
Table (Books Table)
 Represents an entity (category) of objects
  with its (relevant) attributes
 Consists of rows (records) and columns
 In the Books table, each record is an
  instance of the Books category.
 Each field represents an attribute
   Basic Terminology
   Attributes (fields)
To provide specific information about
To help identify individual entities--e.g.,
ISBN for books, SSN for employee,
Sales_ID for sales.
To describe relationship between entity
entities in different entity classes
For example...
    Your Turn. Identify
Entity Classes for School Database
 Students
 Faculty
 Staff
 Buildings
 Rooms
 Course
 Class
Identify Attributes...

Entity Classes For a Bank Database
 Customers
 Account

 Employee

 Transaction
     Basic Terminology
   Set of attributes to identify a record
    uniquely in a set--e.g., to distinguish one
    book from all others. ISBN, ISBN+TITLE,
Key or Candidate Key
   Minumum superkey. E.g., ISBN
Primary Key
   A Key that is chosen for a particular table
          Primary Key
Employee Class    Candidate Key
   FirstName        SSN
   LastName         FirstName
   SSN               +LastName +
   PhoneNumber       DateOfBirth
   DateOfBirth      E-mail?
   E-mail           E-mail + DateOfBirth
                  Primary Key
                     SSN
 Types of Relationships
     (Library DB)
Publishers        Books   How are the tables
                          associated with each
                          other, so that
                          information can be
        Authors           extracted from
                          multiple tables?
Types of Relationships
1 to many (most common)
   A publisher may publish many books, but each
    book may have only one publisher
may to many (undesirable)
   A book may be written by many authors, and an
    author may write many books.
1 to 1 (not as common)
   One author has at most one secretary, and each
    secretary works for only one author
Publishers           Books
                             One record in
                 ∞           Publishers can be
                             related to many
                             records in Books

Publishers           Books   But, each record in
                 1           Books can be
                             associated with
                             only one record in
Authors           Books
          1               One record in Authors
                          can be related to many
                          records in Books

Authors           Books
          ∞   1           And, each record in
                          Books can be associated
                          with many records in
Authors           Secretaries
          1                     One record in Authors can
                                be related to only one
                                record in Secretaries

Authors           Secretaries
              1                 And, each record in
          1                     Secretaries can be
                                associated with only
                                record in Authors
Database Type
   Library
 Books
 Publishers

One-to-Many Relationship?
   Yes
Database Type
   Order Tracking
 Customers
 Orders

One-to-Many Relationship?
   Yes
Database Type
   School Scheduling
 Classes
 Students

One-to-Many Relationship?
   No (Many-to-Many)
           Data Integrity
Table-level integrity
   E.g., no duplicate records (by defining a primary
Field-level integrity
   E.g., do all “State” field require 2 characters?
   Each field contains a single atomic value?
    (e.g., Author field should not contain 2 authors
Relationship-level integrity
   E.g., no many-to-many relationships
   Prevent “orphans.” e.g., if a publisher record is
    deleted, a book record is left without a publisher.

To top