Relational Database

1
Relational Database

• Concept of Tables with Rows Columns
• Use of Generic SQL, Database Software
• Data
• As a resource
• Storage
• Access
• Database Management Systems (DBMS)
• Some Commercial Relational Databases
• Microsoft Access
• Oracle
• IBM DB2

2
Relational Database Concept and Operations

• Relational Database Concept
• Tables - Rows - Columns
• Relationship assumed - all elements of a row
  belong together
• Operations
• Project - Choose Columns
• Select - Select rows with defined criteria
• Sort or Order the rows
• Add/delete rows and columns
• Modify rows, columns, data cell content
• Calculate new column from other column(s)

3
Join 2 tables on common column (identifier)

• Identifiers - columns which express a
  characteristic of a row
• e.g. if a row contains demographic information,
  "home state" is something about that person
• Social security number is used to uniquely
  identify a person
• Identifier usually unique in one table, but not
  in the other

4
Relational Database Operators

• SELECT
• Construct a new table by taking a subset of rows
  according to a criteria
• PROJECT
• Construct a new table by taking a subset of
  columns as specified
• JOIN
• Construct a new table by COMBINING or LINKING 2
  existing tables based on each table having a
  common column (field), called an IDENTIFIER
• Each row in the new table is formed by JOINING
  together a row from each of the original tables
• Where the two rows from the original tables have
  the same values for that common field
  (IDENTIFIER)
• This common field is often a UNIQUE IDENTIFIER in
  one of the tables, and is referred to as a KEY
  FIELD

5
Selection Criteria (Row selection)

• Criterion
• A comparison of the values in a column (field) in
  the database to a value supplied by the user
• Can be very complicated, but must be capable of
  being written such that it is unambiguous
• Equals EQ
• Less than lt LT
• Greater than gt GT
• Less than or equal to lt LE
• Greater than or equal to gt GE
• Not equal to ltgt NE
• Contains CONTAINS
• AND BOTH Criteria must be met
• OR EITHER or BOTH Criteria must be met

6
ORDERING

• Indexing Changes the order of displayed results
• Sorting Changes the order of the rows in the
  database (creates new table)
• e.g. Display students in alphabetical order
• dBase
• USE StudInfo
• SORT on Student Sortlist
• USE Sortlist
• LIST

7
Process to Determine Query Steps

• Write desired output, naming columns in order
• Indicate any criteria for including or excluding
  records
• Indicate any sort requirements
• Enter potential Field name and corresponding File
  name for each desired output column
• Determine whether one or more tables will be
  required (JOIN needed) and if so, which columns
  (FIELDS) will be used as the IDENTIFIERS for each
  JOIN
• Write the Query sequence, beginning with the
  first JOIN (if needed), then with subsequent
  JOINS (if needed), being careful to NAME any
  tables that are created from JOINs.
• Write the appropriate PROJECT statement
• Write the appropriate SELECT statement(s)
• Write the appropriate ORDER (SORT) statement

8
Customer Demographic Data Table

• Data Fields
• Customer-ID
• Bill-to-street-address
• Bill-to-city
• Bill-to-state
• Bill-to-ZIPCode
• Contact-person
• Contact-phone

9
Customer Activity Data Table

• Data Fields
• OrderID
• CustID
• Order date
• Order amount
• Account-balance-on-order-date
• Orders-outstanding-on-order-date
• Sales-Year-to-dat -on-order-date

10
Joining the Customer Demographics and Activity
Tables

• Customers
• Customer-ID
• Bill-to-street-address
• Bill-to-city
• Bill-to-state
• Bill-to-ZIPCode
• Contact-person
• Contact-phone

• Activity
• OrderID
• CustID
• Order date
• Order amount
• Account-balance-on-order-date
• Orders-outstanding-on-order-date
• Sales-Year-to-date-on-order-date

1
N

• Customer-ID is a unique identifier of the
  Customers table.
• OrderID is a unique identifier of the Activity
  table, while CustID is a non-unique identifier of
  the customer in the Activity table

11
e.g., JOIN Customer Activity Demographic Data

• Identifiers
• "CustID" in Customer Activity Table
• Not necessarily unique
• "Customer-ID" in Customer Demographic Table
• Unique
• JOIN Customer Activity and Customer Demographic
• ON "Customer-ID" "CustID"

12
Result of JOIN of Activity Demographic Tables

• A new Table is created, call it ACTDEMO
• ACTDEMO includes all columns of both the Activity
  and Demographic Tables
• (Identifier column only included once)
• ACTDEMO includes only those rows where there is a
  match of values in the identifier columns
• Data Fields
• Customer-IDCustID
• Bill-to-street-address
• Bill-to-city
• Bill-to-state
• Bill-to-ZIPCode
• Contact-person
• Contact-phone
• Order date
• Order amount
• Account-balance-on-order-date
• Orders-outstanding-on-order-date
• Sales-Year-to-date-on-order-date

13
Database Management System (DBMS)

• DBMS Definition
• A computer-based system to MANAGE a database or
  collection of databases or files.
• Uses
• Enable users to access and manipulate the
  database
• Increase ability to construct application systems
  requiring database access
• Help the Database Administrator perform data
  support functions

14
DBMS Objectives

• Make data easy to access and change, by making
  data independent of the programs used
• Protect data integrity reduce duplication
  (redundancy)
• Provide for multiple access to data
• Define and separate the logical (user view) and
  physical characteristics (computer view) of data
• Provide ways of managing file directories to
  access and respond to data requests
• Provide for data security

15
DBMS Elements

• Data Dictionary
• Complete description of the characteristics of a
  database
• May or may not be automated
• Data Definition Language (DDL)
• DBMS software to describe the relationship
  between logical (user) and physical (computer)
  views of data
• Data Manipulation Language (DML)
• DBMS software employed by users and programmers
  to manipulate data in the database
• Query Language
• High level language used to retrieve specific
  information from the database files