All Powder Board and Ski Microsoft Access Workbook

  • Slides: 17
Download presentation
All Powder Board and Ski Microsoft Access Workbook Chapter 3: Database Tables Jerry Post

All Powder Board and Ski Microsoft Access Workbook Chapter 3: Database Tables Jerry Post Copyright © 2003

DBDesign: An Expert System http: //time-post. com/dbdesign l Benefits l l l Makes it

DBDesign: An Expert System http: //time-post. com/dbdesign l Benefits l l l Makes it easy to create database diagrams Saves data in central location, so changes can be made from almost any computer Provides immediate detailed feedback on the design Requirements l l Instructors must ask for a free account Instructors and students need a Java-enabled Web browser

Identifying Key Columns If you are uncertain about which columns to key. Write them

Identifying Key Columns If you are uncertain about which columns to key. Write them down and evaluate the business rules. Order. ID Customer. ID For a given order, can there ever be more than one customer? If yes, then key Customer. ID. In most businesses, only one customer per order, so do not key it. For a given customer, can there ever be more than one order? If yes, then key Order. ID, otherwise, do not key it. All businesses hope to get more than one order from a customer, so Order. ID must be key. Underline it. Since Order. ID is the only key, these columns belong in the Order table. Customer. Order(Order. ID Customer. ID, … )

Auto. Number

Auto. Number

Customer Skill Level Consider what happens if you (incorrectly) try to place Style and

Customer Skill Level Consider what happens if you (incorrectly) try to place Style and Skill. Level in the Customer table: Customer. ID, Last. Name, … Style, Skill. Level Business rule: Each customer can have one skill in many styles. Business rule: Each style can apply to more than one customer. Need a table with both attributes as keys. Customer. ID, Last. Name, … Style, Skill. Level But you cannot include Last. Name, First. Name and so on, because then you would have to re-enter that data for each customer skill.

Customer Style Skills Customer Style Customer. ID Last. Name First. Name Phone Address City

Customer Style Skills Customer Style Customer. ID Last. Name First. Name Phone Address City State ZIP Style. Description Customer. Skill Customer. ID Style Skill. Level Skill. Description

Creating Tables in Access Primary key Column name Data type Additional data type info

Creating Tables in Access Primary key Column name Data type Additional data type info

Data Types: Subtypes Selected column Number data type Subtype: Single

Data Types: Subtypes Selected column Number data type Subtype: Single

Constraints Selected column Acceptable values Message

Constraints Selected column Acceptable values Message

Simple Lookup Lists Selected column Lookup tab Combo box Value list Values in quotes

Simple Lookup Lists Selected column Lookup tab Combo box Value list Values in quotes and separated by commas

Database Table Auto. Number generated Lookup list

Database Table Auto. Number generated Lookup list

Define Relationships Add tables Check all three boxes Verify both columns Drag column and

Define Relationships Add tables Check all three boxes Verify both columns Drag column and drop Relationship line

Create Tables with SQL CREATE TABLE Customer ( Customer. ID Long, Last. Name Text(50),

Create Tables with SQL CREATE TABLE Customer ( Customer. ID Long, Last. Name Text(50), First. Name Text(50), Phone Text(50), Email Text(150), Address Text(50), State Text(50), ZIP Text(15), Gender Text(15), Date. Of. Birth Date, CONSTRAINT pk_Customer PRIMARY KEY (Customer. ID) )

Create Relationships with SQL CREATE TABLE Rental ( Rent. ID Long, Rent. Date, Customer.

Create Relationships with SQL CREATE TABLE Rental ( Rent. ID Long, Rent. Date, Customer. ID Long, Expected. Return Date, Payment. Method Text(50) CONSTRAINT pk_Rental PRIMARY KEY (Rent. ID) CONSTRAINT fk_Rental. Customer FOREIGN KEY (Customer. ID) REFERENCES Customer(Customer. ID) ) ON DELETE CASCADE ON UPDATE CASCADE Note: ON DELETE AND ON UPDATE are not supported with Access 2002, but hopefully will be supported with 2003.

Estimating Database Size Customer. ID Last. Name First. Name Phone Email Address State ZIP

Estimating Database Size Customer. ID Last. Name First. Name Phone Email Address State ZIP Gender Date. Of. Birth Long Text(50) Text(150) Text(50) Text(15) Date 4 30 20 24 50 50 2 14 10 8 Average bytes per customer 212 Customers per week (winter) *200 Weeks (winter) *25 Bytes added per year 1, 060, 000

Data Assumptions 200 customers per week for 25 weeks 2 skills per customer 2

Data Assumptions 200 customers per week for 25 weeks 2 skills per customer 2 rentals per customer per year 3 items per rental 20 percent of customers buy items 4 items per sale 100 manufacturers 20 models per manufacturer 5 items (sizes) per model

Database Table Sizes

Database Table Sizes