a guide to sql seventh edition
Download
Skip this Video
Download Presentation
A Guide to SQL, Seventh Edition

Loading in 2 Seconds...

play fullscreen
1 / 43

A Guide to SQL, Seventh Edition - PowerPoint PPT Presentation


  • 126 Views
  • Uploaded on

A Guide to SQL, Seventh Edition. Objectives. Introduce Premiere Products, a company whose database is used as the basis for many of the examples throughout the text Introduce Henry Books, a company whose database is used as a case that runs throughout the text

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'A Guide to SQL, Seventh Edition' - kolina


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
objectives
Objectives
  • Introduce Premiere Products, a company whose database is used as the basis for many of the examples throughout the text
  • Introduce Henry Books, a company whose database is used as a case that runs throughout the text
  • Introduce Alexamara Marina Group, a company whose database is used as an additional case that runs throughout the text

A Guide to SQL, Seventh Edition

what is a database
What is a Database?
  • Database: A structure containing categories of information and relationships between these categories
  • Categories: sales reps, customers, orders, and parts
  • Relationships between categories: sales rep to customer and customer to parts ordered

A Guide to SQL, Seventh Edition

the premier products database
The Premier Products Database
  • Premier Products is a distributor of appliances, housewares, and sporting goods
  • Manual system no longer feasible for managing customer, order, and inventory data
  • Database management system will allow for current, accurate data with useful reports

A Guide to SQL, Seventh Edition

required data for sales reps
Required Data for Sales Reps
  • Number
  • Last name
  • First name
  • Address
  • Total commission
  • Commission rate

A Guide to SQL, Seventh Edition

required data for customer
Required Data for Customer
  • Customer number
  • Name
  • Address
  • Current balance
  • Credit limit
  • Sales rep number

A Guide to SQL, Seventh Edition

required data for parts
Required Data for Parts
  • Part number
  • Description
  • Number of units on hand
  • Item class
  • Number of the warehouse where the item is stored
  • Unit price for each part in inventory

A Guide to SQL, Seventh Edition

components of a sample order
Components of a Sample Order
  • Heading (top of the order form)
    • Order number and date
    • Customer number, name, address
    • Sales rep number and name
  • Body (center of the order form)
    • One or more order lines or line items
  • Footer (bottom of the order form)
    • Order total

A Guide to SQL, Seventh Edition

components of a line item
Components of a Line Item
  • Part number
  • Part description
  • Number of units for the part ordered
  • Quoted price for the part
  • Total, or extension, the result of multiplying the number ordered by the quoted price

A Guide to SQL, Seventh Edition

items stored for each order
Items Stored for Each Order
  • Order number
  • Date of the order
  • Customer number
  • Customer name, address and sales rep information are stored with the customer information
  • Sales rep name is stored with sales rep information

A Guide to SQL, Seventh Edition

items stored for each order12
Items Stored for Each Order
  • Order number, part number, number of units ordered
  • Quoted price
  • Part description is stored with information on parts
  • Order total is not stored but is calculated each time order is displayed or printed

A Guide to SQL, Seventh Edition

sample rep table
Sample Rep Table

A Guide to SQL, Seventh Edition

rep table example
Rep Table Example
  • Three sales reps in the table identified by number
    • Sales rep number: 20
    • Name: Valerie Kaiser
    • Address: 624 Randall, Grove, FL, 33321
    • Total commission: $20,542.50
    • Commission rate: 5% (0.05)

A Guide to SQL, Seventh Edition

sample customer table
Sample Customer Table

A Guide to SQL, Seventh Edition

customer table example
Customer Table Example
  • Ten customers are identified by number
    • Number: 148
    • Name: Al’s Appliance and Sport
    • Address: 2837 Greenway, Fillmore, FL, 33336
    • Current balance: $6,550.00
    • Credit limit: $7,500.00
    • Sales rep: 20 (Valerie Kaiser)

A Guide to SQL, Seventh Edition

part table example
Part Table Example
  • Ten parts are listed by part number
    • Part number: AT94
    • Description: Iron
    • Units on hand: 50
    • Item class: HW (housewares)
    • Warehouse: 3
    • Price: $24.95

A Guide to SQL, Seventh Edition

sample order table
Sample Order Table

A Guide to SQL, Seventh Edition

order table example
Order Table Example
  • Seven orders listed by order number
    • Order number: 21608
    • Order date: 10/20/2007
    • Customer: 148 (Al’s Appliance and Sport)

A Guide to SQL, Seventh Edition

order line table example
Order Line Table Example
  • Nine order line items listed by order number
    • Order number: 21608
    • Part number: AT94 (iron)
    • Number ordered: 11
    • Quoted price: $21.95

A Guide to SQL, Seventh Edition

alternate order table example
Alternate Order Table Example
  • Displays identical data in one table
  • Each table row contains all the order lines for each order
  • Fifth row, order 21617 has two order lines
    • Part BV06, Qty 2, Quoted price $794.95 each
    • Part CD52, Qty 4, Quoted price $150.00 each

A Guide to SQL, Seventh Edition

issues with alternate order table
Issues with Alternate Order Table
  • Difficult to track information between columns
  • Other issues
    • How much room is allowed for multiple entries?
    • What if an order has more order lines than you have allowed room for?
    • For a given part, how do you determine which orders contain order lines for that part?

A Guide to SQL, Seventh Edition

benefits of order lines table
Benefits of Order Lines Table
  • Table is less complicated when separated
  • No multiple entries
  • Number of order lines is not limited
  • Finding every order for a given part is simple

A Guide to SQL, Seventh Edition

henry books database
Henry Books Database
  • Ray Henry owns Henry Books, a bookstore chain
  • Data is to be stored in a database
  • Needs forms and reports to work with the data
  • Gathers a variety of information on branches, publishers, authors, and books

A Guide to SQL, Seventh Edition

branch information
Branch Information
  • Henry gathers specific information on each branch
    • Number
    • Name
    • Location
    • Number of employees

A Guide to SQL, Seventh Edition

publisher information
Publisher Information
  • Henry gathers specific publisher information
    • Identifying code
    • Name
    • City

A Guide to SQL, Seventh Edition

author information
Author Information
  • Henry gathers specific information for each author
    • Author number
    • Last name
    • First name

A Guide to SQL, Seventh Edition

book information
Book Information
  • Henry gathers specific information for each book
    • Book ID code
    • Title
    • Publisher
    • Type of book
    • Price
    • Paperback information

A Guide to SQL, Seventh Edition

additional information
Additional Information
  • Henry gathers specific information in the WROTE table
    • Book code
    • Author number
    • Sequence, for books with multiple authors
  • This table relates books and authors

A Guide to SQL, Seventh Edition

inventory information
Inventory Information
  • Henry gathers specific information in the INVENTORY table
    • Book code
    • Branch number
    • On hand quantity
  • This table indicates the number of copies currently on hand at a particular branch

A Guide to SQL, Seventh Edition

alexamara marina database
Alexamara Marina Database
  • Alexamara Marina Group offers in-water storage to boat owners
  • Has two properties
  • Also provides boat repair and maintenance services
  • Uses database to store information for managing operations

A Guide to SQL, Seventh Edition

marina information
Marina Information
  • MARINA table stores data on the two marinas
    • Marina number
    • Name
    • Street Address
    • City, State, and Zip

A Guide to SQL, Seventh Edition

boat owner information
Boat Owner Information
  • The marina also stores information on the boat owners in the OWNERS table
    • Owner number
    • Last and First names
    • Street Address
    • City, State, and Zip

A Guide to SQL, Seventh Edition

slip information
Slip Information
  • Information on the slips in each marina is stored in the MARINA_SLIP table
    • Slip ID, Marina Number, Slip Number
    • Length
    • Rental fee
    • Boat name and Boat type
    • Owner number

A Guide to SQL, Seventh Edition

service information
Service Information
  • Maintenance service category information is stored in the SERVICE_CATEGORY table
  • Information on the services requested is stored in the SERVICE_REQUEST table
    • Stores service category, slip information, description and status, estimated hours, hours spent, and next service date

A Guide to SQL, Seventh Edition

summary
Summary
  • Premiere Products
    • Require rep, customer, parts, orders and order lines information
  • Henry Books
    • Requires branch, publisher, author, book, inventory and author sequence information
  • Alexamara Marina Group
    • Requires marina, owners, slips, service categories and service request information

A Guide to SQL, Seventh Edition

ad