Transcript Authors

Relational Database
I. Relational Database
1. Introduction
2. Database Models
3. Relational Database
4. Entity-Relationship Models
5. RDB Design Principles
1. Introduction
Database
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)
Redundancy
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
modified
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)
Solution
Break up Flat-file DB into Separe
Tables
Tables (Library DB)
Hierarchical DB Model
Agency Database
Agents
Entertainers
Clients
Schedules
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
Characteristics
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
tables
To access any data, must start at root
table
Hierarchical DBM
Advantages &
Disadvantages
Advantages:


Data retrieve is fast, because table structure is
explicitly linked
Referential integrity is built in
Disadvantages:



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
Model
Agency Database
Agents
represents
manages
Clients
makes
schedules
Payments
Entertainers
performs
Engagements
plays
Musical Styles
Network DBM
Characteristics
Invented to address problems with the
Hierarchical DB Model
A child table can have more than one parent
table
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
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
record
row
attribute column
field
Basic Terminology
Table
Table (Books Table)
Represents an entity (category) of objects
with its (relevant) attributes
 Consists of rows (records) and columns
(fields)
 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
entity
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
Attributes..
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
Keys
Superkey

Set of attributes to identify a record
uniquely in a set--e.g., to distinguish one
book from all others. ISBN, ISBN+TITLE,
ISBN+TITLE+AUTHOR
Key or Candidate Key

Minumum superkey. E.g., ISBN
Primary Key

A Key that is chosen for a particular table
Primary Key
Employee Class






FirstName
LastName
SSN
PhoneNumber
DateOfBirth
E-mail
Candidate Key




SSN
FirstName
+LastName +
DateOfBirth
E-mail?
E-mail + DateOfBirth
Primary Key

SSN
Types of Relationships
(Library DB)
Publishers
Authors
Books
How are the tables
associated with each
other, so that
information can be
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
One-To-Many
Relationship
Publishers
Books
1
One record in
Publishers can be
related to many
records in Books
∞
Publishers
Books
1
1
But, each record in
Books can be
associated with
only one record in
Publishers
Many-to-Many
Relationship
Authors
Books
1
Authors
∞
∞
One record in Authors
can be related to many
records in Books
Books
1
And, each record in
Books can be associated
with many records in
Authors
One-to-One
Relationship
Authors
Secretaries
1
One record in Authors can
be related to only one
record in Secretaries
1
Authors
Secretaries
1
1
And, each record in
Secretaries can be
associated with only
record in Authors
Example
Database Type

Library
Tables
Books
 Publishers

One-to-Many Relationship?

Yes
Why?
Example
Database Type

Order Tracking
Tables
Customers
 Orders

One-to-Many Relationship?

Yes
Why?
Example
Database Type

School Scheduling
Tables
Classes
 Students

One-to-Many Relationship?

No (Many-to-Many)
Why?
Data Integrity
Table-level integrity

E.g., no duplicate records (by defining a primary
key)
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.