Transcript chap01

David M. Kroenke
1
Database Concepts 1e
Chapter 1
Getting Started
© 2002 by Prentice Hall
1
Chapter Objectives
• Identify the purpose and scope of this book
• Survey the why, what, and how of database
processing
• Understand the reasons for using a database
(why)
• Learn the functions and components of
database systems (what)
• Know the three major steps in creating a
database (how)
© 2002 by Prentice Hall
2
Purpose of a Database
• The purpose of a database is to keep
track of things
• Unlike a spreadsheet, a database may
store information that is more
complicated than a simple list
© 2002 by Prentice Hall
3
Information Complexity: Redundancy
• In a list, each row is intended to stand
on its own. As a result, the same
information may be entered several
times
– For Example: A list of Projects may include
the Project Manager’s Name, ID, and
Phone Extension. If a particular person is
managing 10 projects, his/her information
would have to be entered 10 times
© 2002 by Prentice Hall
4
Redundancy Issues
• Redundancy increases input efforts
• Redundancy increases maintenance
efforts
• Redundancy wastes space and slows
down processing
• Redundancy of information may
introduce inconsistencies to the data
© 2002 by Prentice Hall
5
Information Complexity: Consistency
• In a list, each row stands own its own.
This makes it difficult to apply
consistent business rules
• Consider a company that provides
discounts for certain customers. To find
the appropriate discount would require
a search of previous customer orders.
This takes time and is prone to error
© 2002 by Prentice Hall
6
Information Complexity: Partial Data
• To record information in a list, the
record must already exist. This makes
it impossible to establish a business rule
before a business transaction has
occurred
• For example: An order must be entered
before a customer’s discount rate may
be saved
© 2002 by Prentice Hall
7
Information Complexity:
Relationships Among the Data
• In a list, it is difficult to store information
about more than one topic
• For example: A Project List may have
information about the Project, the Project
Manager, and the Customer
• As you can image, this list would require
many columns. However, the relationships
among the Project, Customer, and Project
Manager are important
© 2002 by Prentice Hall
8
Addressing the Information
Complexities
• Relational databases are designed to
address many of the information
complexity issues
© 2002 by Prentice Hall
9
Relational Databases
• A relational database stores information in
tables. Each informational topic is stored in
its own table
• In essence, a relational database will breakup a list into several parts. One part for each
topic in the list
• A Project List would be divided into a
CUSTOMER Table, a PROJECT Table, and a
PROJECT_MANAGER Table
© 2002 by Prentice Hall
10
Microsoft Access: A Database
Management System (DBMS)
• To create a relational database, you
must use a Database Management
System (DBMS) such as Microsoft
Access
© 2002 by Prentice Hall
11
Putting the Pieces Back Together
• In our relational database we broke apart our
list into several tables. Somehow the tables
must be joined back together
• In a relational database, tables are joined
together using the value of the data
• If a PROJECT has a CUSTOMER, the
Customer_ID is stored as a column in the
PROJECT table. The value stored in this
column can be used to retrieve specific
customer information from the CUSTOMER
table
© 2002 by Prentice Hall
12
Sounds like More Work, Not Less
• A relational database is more complicated
than a list
• However, a relational database minimizes
data redundancy, preserves complex
relationships among topics, and allows for
partial data
• Furthermore, a relational database provides a
solid foundation for user forms and reports
© 2002 by Prentice Hall
13
The User Forms versus Tables
• Although data is stored in several
tables, the user forms will look like lists
• This makes it easier for the user to
understand. However, in the
background the data will be stored in
several tables
© 2002 by Prentice Hall
14
A Closer Look at a Database System
• The four basic elements of a database
are:
– Users
– Database Applications
– Database Management System (DBMS)
– Database
© 2002 by Prentice Hall
15
Users
• A user of a database system will
provide
– Inputs
– Modifications
– Deletions
© 2002 by Prentice Hall
16
Database Applications
• A database application is a set of one or more
computer programs that serves as an
intermediary between the user and a DBMS
• A database application reads, writes, formats,
and/or modifies data. Applications produce:
– Forms
– Queries
– Reports
© 2002 by Prentice Hall
17
Functions of a Database Application
•
•
•
•
•
Create and process forms
Create and transmit queries
Create and process reports
Execute application logic
Provide control
© 2002 by Prentice Hall
18
Structured Query Language (SQL)
• Structured Query Language (SQL) reads
and writes requests for the DBMS
• Many database applications use SQL to
retrieve, format, report, insert, delete,
and/or modify data for users
© 2002 by Prentice Hall
19
Database Management System
(DBMS)
• A database management system
(DBMS) serves as an intermediary
between database applications and the
database
• The DBMS manages and controls
database activities
© 2002 by Prentice Hall
20
Functions of a DBMS
•
•
•
•
•
•
•
•
•
•
Create database
Create tables
Create supporting structures
Read database data
Update database data
Maintain database structures
Enforce rules
Control concurrency
Provide security
Perform backup and recovery
© 2002 by Prentice Hall
21
Commercial DBMS Products
•
•
•
•
Microsoft Access
Oracle’s Oracle
Microsoft’s SQL Server
IBM’s DB2
© 2002 by Prentice Hall
22
Referential Integrity Constraints
• The DBMS will enforce many constraints.
One particularly important constraint is
referential integrity
• Referential integrity ensures that the value of
a column in one table is valid based on the
values in another table
– If a 5 was entered as a CustomerID in the
PROJECT table, a Customer having a CustomerID
value of 5 must exist in the CUSTOMER table
© 2002 by Prentice Hall
23
The Database
• A database is a self-describing collection of
related records
• Self-describing
– The definition of the structure of the database is
contained within the database itself
– Data describing the structure of data is called
metadata
• As we’ve seen, tables within a relational
database are related to each other
© 2002 by Prentice Hall
24
Desktop Database Systems
• Desktop database systems typically:
– Have one application
– Have only a few tables
– Less than ten thousand records
– Are simple in design
– Involve only one computer
– Support one user at a time
© 2002 by Prentice Hall
25
Organizational Database Systems
• Organizational database systems typically:
–
–
–
–
–
–
–
Support several users simultaneously
Include more than one application
Involve multiple computers
Are complex in design
Have large data sets (more than 10,000 records)
Have many tables
Have many databases
© 2002 by Prentice Hall
26
Building a Data Model
• Requirements
• Design
• Implementation
© 2002 by Prentice Hall
27
Data Model Requirements and
Design
• Requirements
– Determine application(s) requirements
– Build data model
• Design
– Tables
– Relationships
– Indexes
– Other structures
© 2002 by Prentice Hall
28
Data Model Implementation
• Implementation
– Create database
– Create tables and relationships
– Create other structures
– Populate with data
– Build applications and test
© 2002 by Prentice Hall
29
Depicting the Data Model:
Entity-Relationship (E-R) Diagram
PROJECT
ProjID
ProjDesc
StartDate
0
N:1
© 2002 by Prentice Hall
|
CUSTOMER
CustID
CustName
30
Data Structure Diagram
|
0<
ProjID | ProjDesc | StartDate | CustID
CustID | CustName
© 2002 by Prentice Hall
31
David M. Kroenke
1
Database Concepts 1e
Chapter 1
Getting Started
© 2002 by Prentice Hall
32