Transcript c16 - c-jump

Chapter 16
HAI! Adventure
Database:
Case Study in
Database Design
Strategy for Building a Database
• Perform a needs analysis
– How the database will be used, kinds of data input,
kinds of information output
• Approximate and Revise a Physical Design
• Implement the Physical Design
• Design the Logical Database
• Implement the Logical Database
• Implement the GUIs
• Evaluate usefulness and (possibly) revise
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-2
The HAI! Adventure Businesses
• Business will have shops at resort
locations around the world
• Each shop will offer equipment for a
single activity
• Company will run organized lessons
and tours
• Database design is for rentals and
activities only
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-3
The HAI! Adventure Businesses
• First business is Snow Machine Ski Rental
• Customer proceeds through stages:
– Selection. Customer gives receptionist name,
address, contact info
– Fitting. Specialist gets weight and ability level
information to set ski bindings. Equipment ID
numbers are recorded; equipment settings are
recorded; gear is given to customer
– Payment. Customer signs rental agreement
and pays
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-4
Perform a Needs Analysis
• What information is created and what
information is needed to transact
the business
• Created:
– personal information on customer
– technical information on equipment
• Needed:
– Content of rental agreement (association of
customer's personal data with equipment's
technical data)
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-5
Needs Analysis (cont'd)
• Specifically, contract must contain customer's
name, address, phone
• If customer returns, this info will be
repeated (redundancy)
• Need a table of customers (Clients)
• Need a table for adventure specialists (ASTeam)
• Rentals table records date, equipment, adjustments,
any special information
• Table now has three tables:
– Clients has relationship with Rentals
– ASTeam has relationship with Rentals
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-6
Approximate/Revise the DB Design
• Specify the Clients Table
–
–
–
–
–
–
–
–
–
–
–
–
–
–
Customer ID
First
Middle
Last
Birthdate
Street
City
State
ZIP
Country
Home_Phone
Mobile_Phone
Email
Local_Contact
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-7
Approximate/Revise
the DB Design (cont'd)
• Specify the ASTeam Table
–
–
–
–
–
–
–
–
–
–
–
Nickname
First
Middle
Last
Birthdate
SS_Number
Mobil_Phone
Email
Certified
Cert_Expire
Cert_Detail
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-8
Approximate/Revise
the DB Design (cont'd)
• Employee contact schema
– Same schema can be used to set up
AS_Home (for permanent employee
addresses) and AS_Local (for local
employee addresses)
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-9
Approximate/Revise
the DB Design (cont'd)
• Specify the Rentals Table
– Customer table key
– Specialists table key
– The rest of the necessary information about the
rental event
– Dependencies:
• Field for Ski? (Y/N) determines if the transaction is for
skis and bindings
• If it is Y, then Weight, Ability, and Settings fields must
be filled in
• These constraints will be enforced in the implementation
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-10
Approximate/Revise
the DB Design (cont'd)
• Specify the Relationships
– One-to-Many Relationships
•
•
•
•
Rents, Clients:Rentals relationship
Serves, ASTeam:Rentals relationship
Home_Of: AS_Home:ASTeam relationship
Sleeps_At: AS_Local:ASTeam relationship
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-11
Revise the Physical Design?
• Assess the table organization and see how
well it fulfills the needs of Snow Machine
• The database has little redundancy, but how
well will it work when the business expands to
other sports?
• Details of equipment rented could be moved
to a separate table
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-12
Revise the Physical Design? (cont'd)
• Ensure Unique Fields
– Add shop ID number field
• Introduce New Relationship
– By creating new table (Gear) for specific
equipment rented, we created a
many-to-one relationship between the two
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-13
Implement the Physical DB Design
• Define the Tables
• Define the Relationships
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-14
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-15
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-16
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-17
Design the Logical Database
• To give users views of the database:
– Analyze the business activity to identify the information
inputs and outputs
– Determine what information the specialists need from each
view, and where it is stored in the tables
– Define a conceptual query for a new table as a join-then-trim
process from existing tables
– Implement the conceptual query in SQL
– Define a GUI to display the information for the specialists
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-18
Design the Logical Database (cont'd)
• Three customer-related views:
– Sign-in: Receptionist establishes new rental and enters
data about customer into Clients table
– Fitting: In fitting room, specialist enters own name into
Rentals table and equipment into Gears table
– Agreement:Customer takes gear back to receptionist to sign
and pay. Price is entered. Information in Clients, Gear,
ASTeam, Rentals tables must be combined into contract
and printed
• Employee-related view:
– Hiring. New hiring, employee's information entered into three
tables: ASTeam, AS_Home, AS_Local
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-19
Analyze the Tables for the Views
• Logical Tables computed by queries
– Sign-in: Requires Rental_ID and Customer fields from
Rentals table; ASTeamer field; all fields of Clients table;
Ski? field from Gear table
– Fitting: All fields from Gear; Rental_ID and ASTeamer fields
from Rentals table; First and Last fields from Clients table
– Agreement: All fields from Rentals table; all fields from
Clients (except Email and Local_Contact); and Gear and
specialist's name field from ASTeam field
– Hiring: All fields from ASTeam, AS_Home and AS_Local
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-20
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-21
Create the View Queries
• Views will be created using queries
– Combine the base tables into a super table and
then retrieve the items needed for the view
• Sign-in Query:
– Begins a rental (establishes a new Rentals row)
– Needs Rental_ID and Customer.ASTeamer from
Rentals table; all the fields from Clients table, and
Ski? field from Gear table
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-22
Create the View Queries
• Fitting Query:
– Needs Rental_ID, ASTeamer, and Customer
fields from Rentals table; all fields from Gear table
• Agreement Query:
– All fields from first three tables except
Clients.Email and Clients.Local_Contract;
and name field from ASTeam
• Hiring Query:
– All the fields from the three tables: ASTeam,
AS_Home, AS_Local
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-23
Implement the
Logical Database Design
• Encode the Hiring Query
– Simply the join of three tables
• Conceptual natural join produces table with all
fields, but SQL is necessary to specify which
fields should be included (SELECT…FROM)
• Operator is INNER JOIN
• Give equality test that is the basis for the
INNER JOIN
• All references to fields use the table name.field
name syntax
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-24
Implement the
Logical Database Design (cont'd)
• Encode the Sign-in Query and the
Fitting Query
– Follow the same guidelines
• Encode the Agreement Query
– Uses all fields (but two) of three tables, and
only three fields of the other, so the SQL is not
conceptually difficult
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-25
Implementing The GUIs
• To make data entry friendlier
• Guidelines
– Arrange information sensibly
• Cluster related fields, orient in expected ways
– Avoid Clutter
• Eliminate duplicate fields, avoid unnecessary text,
lines, and visual effect
– Preload fields
• Fill a field with a default value or expected format
(like DD/MM/YYYY)
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-26
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-27
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-28
Extending a DB: Lessons and Tours
• Consider what changes are needed to move
into the lessons and tours business
• An Analogy:
– Think about organizing table as a class list
– Each group has a leader and a set of participants
– Problems:
• Data that should be independent is bound—
student list should not be ordered
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-29
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-30
The Activities Table
• Improved design: Create an entity that includes the
common features and properties of the lessons and
tours (scheduled activity)
• Fields:
–
–
–
–
–
Activity_ID
Date
Leader
Description
Limit (on number of participants)
• All activities look alike in the database because the
nature of the activity is specified in the description field
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-31
The Apply Table
• How will we create a participants table?
• Use the concept of registration to bind Clients
and Activities
• Like Rentals, but without the equipment table
• Fields:
– Shop_ID; Regist_ID; Date; Activity; Participant; Skill;
Payment
• If a person registered for more than one
activity, he or she has more than one row in
Apply table, independent
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-32
Establish Relationships
between Tables
• Creating the Activities and Apply tables for the
physical database naturally defines
some relationships
– Leads, the ASTeam:Activities relationship
– Offers, the Activities:Apply relationship
– Registers_For, the Clients:Apply relationship
• All one-to-many relationships
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-33
Establish Relationships
between Tables (cont'd)
• Create the participants list:
– Join Activities, Apply, and Customers
tables, and extract necessary fields
• All fields from Activities; participants' names
from Clients. Apply is only used for associating
Activities table entries to Clients table entries
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-34
A Class List Report
• Attends_Q query will create a table of all the
students who signed up for all the classes
• To create a list for one class, or one day
– Use Activity_ID or Date to select necessary rows
from Attends_Q table
– Just add a WHERE clause to Attends_Q query
that saves only those rows that meet the criterion
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-35
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-36
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-37
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-38
Copyright © 2006 Pearson Addison-Wesley. All rights reserved.
16-39