Understanding Relational Database Concepts

1
Understanding Relational Database Concepts

• Professor Larry Heimann
• Carnegie Mellon University
• 88-272 Lecture Notes Fall 1999

2
Todays Hidden Agenda

• Announcements and such
• Why Relational Databases
• Types of Relations
• Keys and Key Rings
• Integrity in Relational Databases
• Types of Data

3
Now OpeningMonadnock Book Store

• What functions does the Monadnock Book Store need
  to be a successful venture?
• Advantages to using a computer for record keeping

4
What Type of Database for MBS?

• Flat file database is easy to understand and
  set-up -- even for the computer novices.
• What would a flat file database look like for
  MBS?
• What are some of the problems with using a
  flat-file database for MBS operations?

5
A Possible Flat File Database for MBS
6
An Alternative Relational Databases

• Relational databases consist of a series of data
  tables linked together so that data can later be
  combined and extracted as needed.
• Some terminology
• table / file
• column / field
• row / record

tblAuthor
AutID AutFirstName AutLastName 0150 Larry Heiman
n 0151 Charles Hummel 0152 Michael Behe 0153 Mar
c Orkand
7
Codds RuleCondensed Version

• represent all information in the database as
  tables
• keep the logical representation of data
  independent from its physical storage
  characteristics
• use SQL for structuring, querying, and changing
  information in the database
• support the main relational operations
  (selection, projection, join) and set up
  operations such as union, intersection,
  difference, and division
• support views, which allow the user to specify
  alternative ways of looking at data in tables
• provide a method for differentiating between
  unknown values (nulls) and zero or blank
• support mechanisms for integrity, authorization,
  transactions, and recovery

8
A Glimpse at the MBS RDB
tblBookAuthor
tblAuthor
tblSaleDetail
AutID ISBN
AutID AutFirstName AutLastName AutBioSketch AutIn
terview
1
8
SalesID ISBN SalesQuantity
8
8
8
tblSales
SalesID SalesDate CustID ShipDate
1
tblBook
8
EditionNumber CopyrightYear ISBN PubID RetailPric
e Rating CoverPicture
tblCustomer
CustID CustFirstName CustLastName CustAddress Cus
tCity CustState CustZIP CustPhone CustEmail Credit
CardType CreditCardNum CardExp
1
1
1
8
tblPublisher
PubID PubName PubAddress PubCity PubState PubZIP
PubPhone PubFax
1
9
Relations in the MBS Database

• One-to-One Relations
• One-to-Many Relations
• Many-to-Many Relations
• These relationships are represented different
  ways depending on the type of model being used.

10
Keys in a Relational Database

• How can we be sure we are accessing the correct
  records?
• Primary Keys
• Composite Keys
• Foreign Keys

11
Integrity -- Important in People and in Databases

• 1st Type Entity Integrity
• 2nd Type Domain Integrity
• 3rd Type Referential Integrity

12
Potential Problem Areas

• Bad input data
• Operator error
• Mechanical failure
• Malice
• Data Redundancy

13
What Kind of Data Can We Store?

• Exact numerics
• Approximate numerics
• character strings
• bit strings
• datetimes
• intervals