© 2002 by prentice hall 1 getting started david m. kroenke database concepts 1e chapter 1 1

© 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