carey christinason. gary gross. lenor soliz. tues. ar

1
CIS 218

• Introduction to Databases

2
What is a Database?

• A collection of related information
• A repository of data about
• Customers
• Products
• Orders
• ???

3
Where are Databases Used?

• Schools
• Stores
• Restaurants
• ???

4
Why use a Database?

• Accessibility
• Flexibility
• Efficiency
• Integrity
• Usability

5
Problems with Lists
Key Terms Redundancy the duplication of
information
6
List Modification Issues
7
A Relational Database

• A relational database breaks a list into several
  tables
• One table for each theme or entity
• A table is made up of columns rows
• The previous list may be divided into
• STUDENT table
• ADVISER table
• DEPARTMENT table

8
(No Transcript)
9
Putting the Pieces Together Again

• A relational database breaks a list into several
  tables
• Tables are linked by sharing a column in common

10
Linking Tables
LinkingColumn
IDColumn
11
Sounds Like More Work!

• A relational database is more complex than a list
• Advantages
• Minimizes redundancy
• Preserves complex relationships
• Allows for partial data
• Provides a foundation for forms and reports

12
A Relational Database Solves the Problems of Lists
Deleted Student row No Adviser data lost
Changed Advisor No inconsistent data
Inserted Advisor No Student data required
13
The Design Process

• Define mission statement objectives
• Analyze the current database
• Create data structures
• Establish table relationships
• Determine business rules

14
Mission

• Mission statement
• What is the purpose of the database?
• Should be succinct
• Mission objectives
• What tasks can users perform against the
  database?
• Defines general tasks

15
The purpose of the New Starz Talent Agency
database is to maintain the data we generate, and
to supply information that supports the
engagement services we provide to our
entertainers.
Maintain complete entertainer information. Track
all engagements. Report on monthly and annual
sales.
The purpose of Mikes Bikes database is to
maintain the data we need to support our retail
sales business and our customer-service
operations.
Maintain complete inventory information. Maintain
complete customer information. Maintain complete
supplier information. Maintain complete employee
information. Track all customer sales.
The purpose of the Whatcom County Hearing
Examiners database is to maintain the data the
examiners office uses to make decisions on
land-use requests submitted by citizens of
Whatcom County.
Keep track of all land-use requests and
decisions. Report on requests, decisions, and
time-to-process.
16
Interview Guidelines

• Introductions
• State your intentions
• Keep it small
• Prepare in advance
• Ask open-ended questions
• Take notes!

17
The Initial Interview

• How would you describe the purpose of your
  organization to a new client?
• What would you say is the single most important
  reason for your organization?
• What kind of work do you do on a regular basis?
• How would you define your job description?
• What things do you keep track of?
• What kinds of reports do you generate?

18
Analysis

• Analyze the current database
• Study current forms and reports
• Interview users and management
• Compile an initial list of fields

19
Data Structures

• Make sure each table represents a single theme
• Define keys
• Establish field specifications

20
Relationships

• Identify relationships
• Identify type and degree of participation
• Create relationships
• Enforce integrity

21
Business Rules

• Document limitations
• Define requirements
• Enforce validation

22
SQL Example
SELECT AdviserEmail FROM ADVISER WHERE AdviserNam
e Baker
23
SELECT StudentName, Email FROM STUDENT,
ADVISER WHERE AdviserName Baker AND
STUDENT.Adviser ADVISER.AdviserNum
24
What are the Components of a Database System?
25
Personal Database Systems
26
What are Database Applications?

• Programs that serve as an intermediary between
  the user and the DBMS
• Purpose
• Create and process forms
• Process user queries
• Create and process reports
• Execute application logic

27
What is a DBMS?

• Database Management System
• Functions
• Create databases and tables
• Create and maintain supporting structures
• Read database data
• Modify database data (insert, update, delete)
• Enforce rules
• Control concurrency
• Provide security
• Perform backup and recovery

28
Enterprise-class Database Systems

• MySQL
• Oracle
• Microsoft SQL Server
• IBM DB2

29
(No Transcript)
30
(No Transcript)
31
Keywords

• Relational model
• Relational database
• Table
• ID Column
• Structured Query Language

• Metadata
• Database Management System
• Database application
• Personal vs. enterprise-class database system

32
The Pathfinder Database

• Pathfinder K-8 is a public alternative school in
  West Seattle. Pathfinder has recently begun
  offering an after school program for its
  students, and needs to keep track of the
  after-school classes, teachers, students,
  families, and payments.

33
A Sample List