Transcript Database Management System

Database Management Systems
Chapter 1
Introduction
1
Goal: Build a Business Application
Worst:
Program
SQL
Program
SQL
Best:
Design
Tools:
Database Design
SQL (queries)
Programming
Design
D
A
T
A
B
A
S
E
2
D
A
T
A
B
A
S
E
DBMS: Database Management System
 Database
 A collection of data stored in a standardized format,
designed to be shared by multiple users.
 Database Management System
 Software that defines a database, stores the data, supports
a query language, produces reports, and creates data entry
screens.
3
D
A
T
A
B
A
S
E
Drawbacks of old File methods
 Uncontrolled Duplication
 Wastes space
 Hard to update all files
 Inconsistent data
 Inflexibility
 Hard to change data
 Hard to change programs
 Limited data sharing
 Poor enforcement of standards
 Poor programmer productivity
 Excessive program maintenance
4
D
A
T
A
B
A
S
E
Advantages of Database Approach
 Minimal data redundancy.
 Data consistency.
 Integration of data.
 Sharing of data.
 Enforcement of standards.
 Ease of application development.
 Data independence.
5
D
A
T
A
B
A
S
E
Database Management Approach
 Data is most important
 Data defined first
 Standard format
 Access through DBMS
All Data
 Queries, Reports, Forms
 Application Programs
 3GL Interface
DBMS
 Data independence
 Change data definition
without changing code
 Alter code without
changing data
 Move/split data without
changing code
Program1
Queries
Program2
Reports
6
D
A
T
A
B
A
S
E
Modifying Data with DBMS
 Add cell number to
employee table
 Open table definition
 Add data element
 If desired, modify reports
 Use report writer
 No programming
 Existing reports,
queries, code will all run
as before with no
changes.
Field Name
Data Type
Description
EmployeeID
TaxpayerID
LastName
FirstName
...
Phone
...
Number
Text
Text
Text
Autonumber..
Federal ID
CellPhone
Text
Text
Cellular . . .
7
D
A
T
A
B
A
S
E
DBMS Components
 Database engine
 Storage
 Retrieval
 Update
 Query Processor
 Data dictionary
 Utilities
 Security
 Report writer
 Forms generator (input
screens)
 Application generator
 Communications
 3GL Interface
8
D
A
T
A
B
A
S
E
DBMS Engine, Security, Utilities
Product
ItemID Description
Order
887
Dog food
OrderID
ODate
Customer946 Cat food
9874
3-3-97
CustomerID Name
9888
3-9-97
1195
Jones
2355
Rojas
Data
Tables
Product
Customer
ItemID
Integer, Unique
CustomerID
Description Integer,
Text, 100
Unique
char
Name
Text, 50 char
Database
Engine
Data
Dictionary
User Identification
Access Rights
Security
Concurrency and
Lock Manager
Backup and
Recovery
Utilities
Administration
9
D
A
T
A
B
A
S
E
Database Tables (Access)
10
D
A
T
A
B
A
S
E
Database Tables (Oracle)
11
D
A
T
A
B
A
S
E
DBMS Query Processor
All Data
Database Engine
Data Dictionary
Query Processor
12
D
A
T
A
B
A
S
E
DBMS Report Writer
All Data
Database Engine
Data Dictionary
Query Processor
Report Writer
Report
Format
and Query
13
D
A
T
A
B
A
S
E
Report Writer (Oracle)
14
D
A
T
A
B
A
S
E
DBMS Input Forms
All Data
Database Engine
Data Dictionary
Query Processor
Form Builder
Input
Form
Design
15
D
A
T
A
B
A
S
E
DBMS Components
All Data
Communication
Network
Database Engine
Data Dictionary
Security
3GL
Connector
Query Processor
Form
Report
Builder
Writer
Application
Generator
Program
16
D
A
T
A
B
A
S
E
Examples of Commercial Systems
 Oracle
 Ingres
 Informix (Unix)
 DB2, SQL/DS (IBM)
 Access (Microsoft)
 SQL Server (Microsoft +)
 Many older (Focus, IMS, ...)
 Many limited PC (dBASE, Paradox, …)
17
D
A
T
A
B
A
S
E
Hierarchical Database
Customers
Customer
Order
Items Ordered
Orders
Items
Item Description
998 Dog Food
764 Cat Food
Quantity
12
11
To retrieve data, you
must start at the top
(customer). When you
retrieve a customer, you
retrieve all nested data.
18
D
A
T
A
B
A
S
E
Network Database
Entry point
Customer
Order
Items
Ordered
Items
Entry point
19
D
A
T
A
B
A
S
E
Relational Database
Customer(CustomerID, Name, …
Order(OrderID, CustomerID, OrderDate, …
ItemsOrdered(OrderID, ItemID, Quantity, …
Items(ItemID, Description, Price, …
20
D
A
T
A
B
A
S
E
Why don’t all developers use a DBMS?
 Most new projects (in last 5 years) do use a DBMS
 Need specialized personnel
 Programmers
 Designers/Analysts
 Database administrators
 Need to define data for organization
 Cost
 PC:
 Large:
$400 - $2000
$100,000 +
21
D
A
T
A
B
A
S
E
DBMS approach
 Applications change a lot, but same data.
 Need for ad hoc questions and queries.
 Need to reduce development times.
 Need shared data.
 Improve quality of data.
 Enable users to do more development.
22
D
A
T
A
B
A
S
E
Database System Design
Customer(CustomerID, Name, Address, …)
SalesPerson(EmployeeID, Name, Commission, … )
Order(OrderID, OrderDate, CustomerID, EmployeeID, … )
OrderItem(OrderID, ItemID, Quantity, Price, … )
Item(ItemID, Description, ListPrice, …)
User views
of data.
Conceptual
data model.
Class diagram that
shows business
entities, relationships,
and rules.
Implementation
(relational)
data model.
List of nicely-behaved
tables. Use data
normalization to
derive the list.
Physical
data
storage.
Indexes and storage
methods to improve
performance.
23
D
A
T
A
B
A
S
E
The Need for Design
 Goal: To produce an information system that adds
value for the user
 Reduce costs
 Increase sales/revenue
 Provide competitive advantage
 Objective: To understand the system
 To improve it
 To communicate with users and IT staff
 Methodology: Build models of the system
24
D
A
T
A
B
A
S
E
Initial Steps of Design
1. Identify the exact goals of the system.
2. Talk with the users to identify the basic forms and reports.
3. Identify the data items to be stored.
4. Design the classes (tables) and relationships.
5. Identify any business constraints.
6. Verify the design matches the business rules.
25
D
A
T
A
B
A
S
E
Definitions
Entity:
Something in the real world that we wish to describe
or track.
Class:
Description of an entity, that includes its attributes
(properties) and behavior (methods).
Object:
One instance of a class with specific data.
Property:
A characteristic or descriptor of a class or entity.
Method:
A function that is performed by the class.
Association: A relationship between two or more classes.
Pet Store Examples
Entity:
Class:
Object:
Property:
Method:
Association:
Customer, Merchandise, Sales
Customer, Merchandise, Sale
Joe Jones, Premium Cat Food, Sale #32
LastName, Description, SaleDate
AddCustomer, UpdateInventory, ComputeTotal
Each Sale can have only one Customer.
26
D
A
T
A
B
A
S
E
Associations
 General
 Objects related to objects
 One-to-one
 One-to-many
 Many-to-many
 An employee can work in
only one department
 Many departments can work
on many different products
(1:1)
(1:M)
(M:N)
 Relationships represent
business rules
 Objects related to properties
 An employee can have only
one name
 Many employees can have
the same last name
 Sometimes common-sense
places 
 Sometimes unique to an
organization
 Users often know current
relationships, rarely future
Breed
Supplier1
*
sent
to
Purch.
Order
Cust.
1
1
*
Animal
*
Sale
places 
Emp
*
*
Tasks
performs 
27
D
A
T
A
B
A
S
E
Why Normalization?
 Need standardized data definition
 Advantages of DBMS require careful design
 Define data correctly
 It especially makes it easier to expand database later
 Method applies to most models and most DBMS
 Similar to Entity-Relationship
 Goal: Define tables carefully
 Save space
 Minimize redundancy
 Protect data
28
D
A
T
A
B
A
S
E
Definitions
 Relational database: A collection of tables.
 Table: A collection of columns (attributes) describing an entity.
Individual objects are stored as rows of data in the table.
 Property (attribute): a characteristic or descriptor of a class or entity.
 Every table has a primary key.
 The smallest set of columns that uniquely identifies any row
 Primary keys can span more than one column (concatenated keys)
 We often create a primary key to insure uniqueness (e.g., CustomerID,
Product#, . . .) called a surrogate key.
Primary key
Rows/Objects
EmployeeID TaxpayerID
12512
888-22-5552
15293
222-55-3737
22343
293-87-4343
29387
837-36-2933
Properties
Class: Employee
Employee
LastName
Cartom
Venetiaan
Johnson
Stenheim
FirstName
Abdul
Roland
John
Susan
HomePhone
(603) 323-9893
(804) 888-6667
(703) 222-9384
(410) 330-9837
Address
252 South Street
937 Paramaribo Lane
234 Main Street
8934 W. Maple
29
D
A
T
A
B
A
S
E
First Normal Form Problems (Data)
TransID
1
2
3
4
RentDate
4/18/02
4/30/02
4/18/02
4/18/02
CustID
3
7
8
3
Phone
502-777-7575
615-888-4474
615-452-1162
502-777-7575
LastName
Washington
Lasater
Jones
Washington
FirstName
Elroy
Les
Charlie
Elroy
Address
95 Easy Street
67 S. Ray Drive
867 Lakeside Drive
95 Easy Street
City
Smith's Grove
Portland
Castalian Springs
Smith's Grove
State
KY
TN
TN
KY
ZipCode
42171
37148
37031
42171
 1NF splits repeating
groups
 Still have problems
 Replication
 Hidden dependency:
 If a video has not been
rented yet, then what
is its title?
TransID
1
1
2
2
2
3
3
3
4
4
4
4
VideoID
1
6
8
2
6
9
15
4
3
8
13
17
Copy#
2
3
1
1
1
1
1
1
1
1
1
1
Title
2001: A Space Odyssey
Clockwork Orange
Hopscotch
Apocalypse Now
Clockwork Orange
Luggage Of The Gods
Fabulous Baker Boys
Boy And His Dog
Blues Brothers
Hopscotch
Surf Nazis Must Die
Witches of Eastwick
Rent
$1.50
$1.50
$1.50
$2.00
$1.50
$2.50
$2.00
$2.50
$2.00
$1.50
$2.50
$2.00
30
D
A
T
A
B
A
S
E
Second Normal Form Definition
Depends on both TransID and VideoID
RentalLine(TransID, VideoID, Copy#, Title, Rent)
Depend only on VideoID
 Each non-key column must
depend on the entire key.
 Only applies to
concatenated keys
 Some columns only depend
on part of the key
 Split those into a new table.
 Dependence (definition)
 If given a value for the key
you always know the value
of the property in question,
then that property is said to
depend on the key.
 If you change part of a key
and the questionable
property does not change,
then the table is not in 2NF.
31
D
A
T
A
B
A
S
E
Second Normal Form Example
RentalLine(TransID, VideoID, Copy#, Title, Rent)
VideosRented(TransID, VideoID, Copy#)
Videos(VideoID, Title, Rent)
 Title depends only on VideoID
 Each VideoID can have only one title
 Rent depends on VideoID
 This statement is actually a business rule.
 It might be different at different stores.
 Some stores might charge a different rent for each
video depending on the day (or time).
 Each non-key column depends on the whole key.
32
D
A
T
A
B
A
S
E
Second Normal Form Example (Data)
VideosRented(TransID, VideoID, Copy#)
TransID
1
1
2
2
2
3
3
3
4
4
4
4
VideoID
1
6
2
6
8
4
9
15
3
8
13
17
Copy#
2
3
1
1
1
1
1
1
1
1
1
1
Videos(VideoID, Title, Rent)
VideoID
1
2
3
4
5
6
7
8
Title
2001: A Space Odyssey
Apocalypse Now
Blues Brothers
Boy And His Dog
Brother From Another Planet
Clockwork Orange
Gods Must Be Crazy
Hopscotch
Rent
$1.50
$2.00
$2.00
$2.50
$2.00
$1.50
$2.00
$1.50
(Unchanged)
RentalForm2(TransID, RentDate, CustomerID, Phone,
Name, Address, City, State, ZipCode)
33
D
A
T
A
B
A
S
E
Third Normal Form Definition
Depend on TransID
RentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode)
Depend only on CustomerID
 Each non-key column must
depend on nothing but the
key.
 Some columns depend on
columns that are not part of
the key.
 Split those into a new table.
 Example: Customers name
does not change for every
transaction.
 Dependence (definition)
 If given a value for the key
you always know the value
of the property in question,
then that property is said to
depend on the key.
 If you change the key and
the questionable property
does not change, then the
table is not in 3NF.
34
D
A
T
A
B
A
S
E
35