© 2002 by prentice hall 1 getting started david m. kroenke database concepts 1e chapter 1 1
TRANSCRIPT
© 2002 by Prentice Hall 1
Getting Started
David
M.
Kro
enke
Database Concepts 1e Chapter 1
1
© 2002 by Prentice Hall 2
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 3
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 4
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 5
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 6
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 7
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 8
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 9
Addressing the Information Complexities
• Relational databases are designed to address many of the information complexity issues
© 2002 by Prentice Hall 10
Relational Databases
• A relational database stores information in tables. Each informational topic is stored in its own table
• In essence, a relational database will break-up 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 11
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 12
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 13
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 14
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 15
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 16
Users
• A user of a database system will provide– Inputs– Modifications– Deletions
© 2002 by Prentice Hall 17
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 18
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 19
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 20
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 21
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 22
Commercial DBMS Products
• Microsoft Access• Oracle’s Oracle• Microsoft’s SQL Server• IBM’s DB2
© 2002 by Prentice Hall 23
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 24
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 25
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 26
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 27
Building a Data Model
• Requirements• Design• Implementation
© 2002 by Prentice Hall 28
Data Model Requirements and Design
• Requirements– Determine application(s)
requirements– Build data model
• Design– Tables– Relationships– Indexes– Other structures
© 2002 by Prentice Hall 29
Data Model Implementation
• Implementation– Create database– Create tables and relationships– Create other structures– Populate with data– Build applications and test
© 2002 by Prentice Hall 30
Depicting the Data Model: Entity-Relationship (E-R) Diagram
PROJECTProjIDProjDescStartDate
CUSTOMERCustIDCustName
N:1 0 |
© 2002 by Prentice Hall 31
Data Structure Diagram
ProjID | ProjDesc | StartDate | CustID
CustID | CustName 0<
|
© 2002 by Prentice Hall 32
Getting Started
David
M.
Kro
enke
Database Concepts 1e Chapter 1
1