Concepts of Database Management Sixth Edition

1
Concepts of Database ManagementSixth Edition
Chapter 1 Introduction to Database Management
2
Objectives

• Premiere Products company
• Basic database terminology
• Advantages and disadvantages of databases

Concepts of Database Management
2
3
Objectives (continued)?

• Henry Books company
• Alexamara Marina Group company

Concepts of Database Management
3
4
Premiere Products Background

• Premiere Products
• Distributor of appliances, houseware, and
  sporting goods
• Uses spreadsheet software to maintain important
  data
• Recent growth has made spreadsheet approach
  problematic

Concepts of Database Management
4
5
Premiere Products Background (continued)?
FIGURE 1-1 Sample orders spreadsheet
Concepts of Database Management
5
6
Premiere Products Background (continued)?

• Problems using spreadsheet
• Redundancy
• Difficulty accessing related data
• Limited security
• Size limitations

Concepts of Database Management
6
7
Premiere Products Background (continued)?

• Information Premiere Products needs to maintain
• Sales Reps
• Sales rep number, last name, first name, address,
  total commission, commission rate
• Customers
• Customer number, name, address, current balance,
  credit limit, number of customers sales rep
• Parts Inventory
• Part number, description, number units on hand,
  item class, warehouse number, unit price

Concepts of Database Management
7
8
Premiere Products Background (continued)?
FIGURE 1-2 Sample order
Concepts of Database Management
8
9
Database Background

• Database
• Data stored in a certain way that allows
• Different categories of information
• Relationships
• Fast access to data
• Entity
• Person, place, object, event, or idea
• Entities for Premiere Products sales reps,
  customers, orders, and parts

Concepts of Database Management
9
10
Database Background (continued)?

• Attribute
• Characteristic or property of an entity
• Example Customer has name, street, city, etc.
• May also be called a field or column
• Relationship
• Association between entities
• One-to-many relationship
• Each rep is associated with many customers
• Each customer is associated with a single rep

Concepts of Database Management
10
11
Database Background (continued)?
FIGURE 1-3 Entities and attributes
Concepts of Database Management
11
12
Database Background (continued)?
FIGURE 1-4 One-to-many relationship
Concepts of Database Management
12
13
Database Background (continued)?

• Database
• Data stored in a certain way that allows
• Multiple types of entities
• Attributes of those entities
• Relationships between the entities
• Fast access
• Entity All column headings / A row in a table
• Attribute A column heading / A cell in a row

Concepts of Database Management
13
14
Premiere Products Background (repeated)?
FIGURE 1-1 Sample orders spreadsheet
Concepts of Database Management
14
15
Database Background (continued)?
FIGURE 1-5 Sample data for Premiere Products
Concepts of Database Management
15
16
Database Background (continued)?
FIGURE 1-5 Sample data for Premiere Products
(continued)?
Concepts of Database Management
16
17
Database Background (continued)?
FIGURE 1-5 Sample data for Premiere Products
(continued)?
Concepts of Database Management
17
18
Database Background (continued)?
FIGURE 1-6 Alternative Orders table structure
Concepts of Database Management
18
19
Premiere Products Database - QA

• What are the numbers of customers represented by
  Valerie Kaiser?
• What is the name of the customer that placed the
  order 21610, and what is the name of the rep who
  represents this customer?
• List all the parts that appear in order 21610.
  For each part, give the description, number
  ordered and quoted price.
• Why is QuotedPrice column part of the OrderLine
  table? Can't you use the part number to look up
  the price in the Part table?

20
Database Background (continued)?

• Entity-relationship (E-R) diagram
• Visual way to represent a database
• Rectangles
• Lines

Concepts of Database Management
20
21
Database Background (continued)?
FIGURE 1-7 E-R diagram for the Premiere Products
database
Concepts of Database Management
21
22
Database Management Systems

• Database management system (DBMS)?
• Program, or collection of programs, through which
  users interact with a database
• Popular DBMSs Access, Oracle, DB2, MySQL, and
  SQL Server
• Premiere Products decides to use Access
• Database design

Concepts of Database Management
22
23
Database Management Systems (continued)?
FIGURE 1-8 Using a DBMS directly
FIGURE 1-9 Using a DBMS through another program
Concepts of Database Management
23
24
Database Management Systems (continued)?

• Forms
• Screen objects used to maintain, view, and print
  data from a database
• Reports
• Paper information created based on users answers
  about the desired content and appearance of each
  report

Concepts of Database Management
24
25
Database Management Systems (continued)?
FIGURE 1-10 Part form
Concepts of Database Management
25
26
Database Management Systems (continued)?
FIGURE 1-11 Orders form
Concepts of Database Management
26
27
Database Management Systems (continued)?
FIGURE 1-12 Parts report
Concepts of Database Management
27
28
Advantages of Database Processing

• Getting more information from the same amount of
  data
• Sharing data
• Balancing conflicting requirements (backup vs
  speed)?
• Database administrator or database administration
  (DBA) person or group in charge of the database
• Controlling redundancy
• Facilitating consistency (because of 4.)?

Concepts of Database Management
28
29
Advantages of Database Processing (continued)?

• Improving integrity
• Integrity constraint a rule that data must
  follow in the database
• Expanding security
• Security prevention of unauthorized access
• Increasing productivity
• Providing data independence
• Data independence can change structure of a
  database without changing the programs that
  access the database

Concepts of Database Management
29
30
Disadvantages of Database Processing

• Larger file size
• Increased complexity
• Greater impact of failure
• More difficult recovery

Concepts of Database Management
30
31
Introduction to Henry Books Database Case

• Henry Books
• Book store chain operated by Ray Henry
• Henry decided to use a database to gather and
  store information on
• Branches of his store
• Publishers
• Authors
• Books

Concepts of Database Management
31
32
Introduction to Henry Books Database Case
(continued)?
FIGURE 1-15 Sample branch and publisher data for
Henry Books
Concepts of Database Management
32
33
Introduction to Henry Books Database Case
(continued)?
FIGURE 1-15 Sample branch and publisher data for
Henry Books (continued)?
Concepts of Database Management
33
34
Introduction to Henry Books Database Case
(continued)?
FIGURE 1-16 Sample author data for Henry Books
Concepts of Database Management
34
35
Introduction to Henry Books Database Case
(continued)?
FIGURE 1-17 Sample book data for Henry Books
Concepts of Database Management
35
36
Introduction to Henry Books Database Case
(continued)?
Sequence Order of author on the cover
FIGURE 1-18 Sample data that relates books to
authors and books to branches for
Henry Books
Concepts of Database Management
36
37
Introduction to Henry Books Database Case
(continued)?
FIGURE 1-18 Sample data that relates books to
authors and books to branches for
Henry Books (continued)?
Concepts of Database Management
37
38
Henry Books Database - QA

• Who published Jazz?
• Which books did Jove Publications publish?
• Who wrote Black House?
• Which books did Toni Morrison write?

39
Introduction to Henry Books Database Case
(continued)?
FIGURE 1-19 E-R diagram for the Henry Books
database
Concepts of Database Management
39
40
Introduction to the Alexamara Marina Group
Database Case

• Alexamara Marina Group offers in-water boat
  storage to owners
• Provides boat slips that boat owners can rent on
  an annual basis
• Two marinas Alexamara East and Alexamara Central
• Provides boat repair and maintenance services
• Database used to store data

Concepts of Database Management
40
41
Introduction to the Alexamara Marina Group
Database Case (continued)?
FIGURE 1-20 Sample marina data for Alexamara
Marina Group
Concepts of Database Management
41
42
Introduction to the Alexamara Marina Group
Database Case (continued)?
FIGURE 1-21 Sample owner data for Alexamara
Marina Group
Concepts of Database Management
42
43
Introduction to the Alexamara Marina Group
Database Case (continued)?
FIGURE 1-22 Sample data about marina slips for
Alexamara Marina Group
Concepts of Database Management
43
44
Introduction to the Alexamara Marina Group
Database Case (continued)?
FIGURE 1-23 Sample data about service categories
for Alexamara Marina Group
Concepts of Database Management
44
45
Introduction to the Alexamara Marina Group
Database Case (continued)?
FIGURE 1-24 Sample data about service requests
for Alexamara Marina Group
Concepts of Database Management
45
46
Introduction to the Alexamara Marina Group
Database Case (continued)?
FIGURE 1-24 Sample data about service requests
for Alexamara Marina Group
(continued)?
Concepts of Database Management
46
47
Alexamara Marina - QA

• Who owns Axxon II?
• What are the names of the boats owned by Bill
  Anderson?
• What is the name of the boat that had a fuse
  blown on its port motor, and who owns this boat.
• What service has been performed on the boad named
  Gypsy?

48
Introduction to the Alexamara Marina Group
Database Case (continued)?
FIGURE 1-25 E-R diagram for the Alexamara Marina
Group database
Concepts of Database Management
48
49
Summary

• Problems with nondatabase approaches to data
  management redundancy, difficulties accessing
  related data, limited security features, limited
  data sharing features, and potential size
  limitations
• Entity person, place, object, event, or idea for
  which you want to store and process data
• Attribute, field, or column characteristic or
  property of an entity
• Relationship an association between entities

Concepts of Database Management
49
50
Summary (continued)?

• One-to-many relationship each occurrence of
  first entity is related to many occurrences of
  the second entity and each occurrence of the
  second entity is related to only one occurrence
  of the first entity
• Database structure that can store information
  about multiple types of entities, attributes of
  entities, and relationships among entities
• Premiere Products requires information about
  reps, customers, parts, orders, and order lines
• Entity-relationship (E-R) diagram represents a
  database visually by using various symbols

Concepts of Database Management
50
51
Summary (continued)?

• Database management system (DBMS) program
  through which users interact with a database
  lets you create forms and reports quickly and
  easily, and obtain answers to questions about the
  data
• Advantages of database processing getting more
  information from the same amount of data, sharing
  data, balancing conflicting requirements,
  controlling redundancy, facilitating consistency,
  improving integrity, expanding security,
  increasing productivity, and providing data
  independence

Concepts of Database Management
51
52
Summary (continued)?

• Disadvantages of database processing larger file
  size, increased complexity, greater impact of
  failure, and more difficult recovery
• Henry Books needs to store information about
  branches, publishers, authors, books, inventory,
  and author sequence
• Alexamara Marina Group needs to store information
  about marinas, owners, marina slips, service
  categories, and service requests

Concepts of Database Management
52