Transcript All Powder Board and Ski

All Powder Board and Ski
SQL Server Workbook
Chapter 3: Database Tables
Jerry Post
Copyright © 2004
DBDesign: An Expert System

http://time-post.com/dbdesign
 Benefits




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


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 down and evaluate the business rules.
OrderID
CustomerID
For a given order, can there ever be more than one customer?
If yes, then key CustomerID.
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 OrderID, otherwise, do not key it.
All businesses hope to get more than one order from a customer,
so OrderID must be key. Underline it. Since OrderID is the only key,
these columns belong in the Order table.
CustomerOrder(OrderID CustomerID, … )
Identities: Introduction




Sometimes it is best to let the DBMS generate a guaranteed
unique key value
The table must contain a single column primary key that is
assigned a numeric data type, such as NUMBER(38)
Later, you will create an identity to generate the numbers
Later, you will create a trigger to automatically obtain and use the
generated key value
Customer Skill Level
Consider what happens if you (incorrectly) try to place Style and
SkillLevel in the Customer table:
CustomerID, LastName, … Style, SkillLevel
CustomerID, LastName, … Style, SkillLevel
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.
CustomerID, LastName, … Style, SkillLevel
But you cannot include LastName, FirstName and so on, because
then you would have to re-enter that data for each customer skill.
Customer Style Skills
Customer
Style
CustomerID
LastName
FirstName
Phone
Address
City
State
ZIP
Style
StyleDescription
CustomerSkill
CustomerID
Style
SkillLevel
SkillLevel
SkillLevel
SkillDescription
Action
Action
If necessary, create a new database.
Create Customer table with enterprise manager.
Enter column names.
Select data types.
Assign the primary key.
Exit and save the table.
Creating Tables with the Editor
Data type
Primary key
Column name
Data size
Table list
Constraints
CHECK constraint type
Unique name
Right-click
Acceptable values
Action
Action
Right-click Gender and select Check
constraint
Select the Check tab
Enter check condition:
Upper(Gender) IN (‘FEMALE’, ‘MALE’,
‘UNIDENTIFIED’).
Enter unique name: CK_Customer_Gender
Close the form and save the changes
Test the constraint with sample data
Create Tables with SQL
CREATE TABLE Customer
(
CustomerID
int IDENTITY(1,1) NOT NULL,
LastName
nvarchar(25),
FirstName
nvarchar(25),
Phone
nvarchar (25),
Email
nvarchar (120),
Address
nvarchar (50),
Citynvarchar (50),
State
nvarchar (25),
ZIP
nvarchar (15),
Gender
nvarchar (15),
DateOfBirth
datetime,
CONSTRAINT pk_Customer PRIMARY KEY (CustomerID),
CONSTRAINT ck_Customer_Gender
CHECK (Upper(Gender) IN ('FEMALE', 'MALE', 'UNIDENTIFIED'))
)
Action
Action
Start the Windows Wordpad program
Type the CREATE TABLE commands
Save the file as ProductCategory.sql
Start the SQL Query Analyzer program
Select the Powder database
Log in as the All Powder developer
Open the ProductCategory file
Click the Execute Query button to run it
Table in SQL: ProductCategory
CREATE TABLE ProductCategory
(
Category
nvarchar(50),
CategoryDescription
nvarchar(250),
CONSTRAINT pk_ProductCategory PRIMARY KEY (Category)
)
Relationships: Department and Employee
Department
Employee
1…1
Department
Description
Reference Table
1…*
EmployeeID
TaxpayerID
LastName
FirstName
Address
Phone
City
State
ZIP
Department
Foreign Key
Relationship: Foreign Key Constraint
Create both tables
Reference table
Column in the
Department table
Cascade is
important
Column in the
Employee table
Action
Action
Create the Department table
Be sure the Department column is keyed
Create the Employee table
Set EmployeeID as a primary key constraint
Create a new diagram and add both tables
Drag the Department column from the Department to
the Employee table
Verify the columns match
Check the update boxes
Relationships in SQL
CREATE TABLE Department
(
Department
nvarchar(50),
Description
nvarchar(150),
CONSTRAINT pk_Department PRIMARY KEY (Department)
)
Go
CREATE TABLE Employee
(
EmployeeID
int,
TaxpayerID
nvarchar(50),
LastName
nvarchar(25),
FirstName
nvarchar(25),
Address
nvarchar(50),
Phonenvarchar(25),
City
nvarchar(50),
State
nvarchar(15),
ZIP
nvarchar(15),
Department
nvarchar(50)
DEFAULT ‘Sales’,
CONSTRAINT pk_Employee PRIMARY KEY (EmployeeID),
CONSTRAINT fk_DepartmentEmployee FOREIGN KEY (Department)
REFERENCES Department(Department)
ON DELETE CASCADE
)
Go
Diagrams in SQL Server
Estimating Database Size
CustomerID
LastName
FirstName
Phone
Email
Address
City
State
ZIP
Gender
DateOfBirth
int
nvarchar(50)
nvarchar(50)
nvarchar(50)
nvarchar(150)
nvarchar(50)
nvarchar(50)
nvarchar(50)
nvarchar(15)
nvarchar(15)
datetime
Average bytes per customer
Customers per week (winter)
Weeks (winter)
Bytes added per year
4
30
20
24
50
50
2
14
10
8
280
*200
*25
1,400,000
Action
Action
Create a spreadsheet
Enter table names as rows
Add columns for: Bytes, Rows, Totals
Calculate the bytes per table row
Estimate the number of rows
Compute the table and overall totals
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
Table
Bytes
Rows
Comments
Customer
280
5000 200 per week * 25 weeks
CustomerSkill
68
10000 2 per customer
Rental
44
10000 2 per customer
RentItem
78
30000 3 items per rental
PaymentMethod
20
5 basic methods
Sale
152
1000 20 pct of customers buy
SaleItem
36
4000 4 items per sale average
Employee
248
35 Est. per year
Department
70
5 basic list
BindingStyle
120
15 main ones
Manufacturer
262
100 should be enough
ItemModel
174
2000 20 per manufacturer
Inventory
48
10000 5 per model average
ProductCategory
70
25 should be enough
SkiBoardStyle
110
50 should be enough
SkillLevel
74
10 10 main skill levels
Total
1,400,000
680,000
440,000
2,340,000
100
152,000
144,000
8,680
350
1,800
26,200
348,000
480,000
1,750
5,500
740
6,029,120