Transcript DBW03Access
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 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, … ) AutoNumber 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 Creating Tables in Access Primary key Data type Column name Additional data type info Data Types: Subtypes Selected column Number data type Subtype: Single Constraints Selected column Acceptable values Message Simple Lookup Lists Selected column Lookup tab Combo box Value list Values in quotes and separated by commas Database Table AutoNumber generated Lookup list Define Relationships Add tables Check all three boxes Verify both columns Drag column and drop Relationship line Create Tables with SQL CREATE TABLE Customer ( CustomerID Long, LastName Text(50), FirstName Text(50), Phone Text(50), Email Text(150), Address Text(50), State Text(50), ZIP Text(15), Gender Text(15), DateOfBirth Date, CONSTRAINT pk_Customer PRIMARY KEY (CustomerID) ) Create Relationships with SQL CREATE TABLE Rental ( RentID Long, RentDate Date, CustomerID Long, ExpectedReturn Date, PaymentMethod Text(50) CONSTRAINT pk_Rental PRIMARY KEY (RentID) CONSTRAINT fk_RentalCustomer FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ) 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 CustomerID LastName FirstName Phone Email Address State ZIP Gender DateOfBirth Long Text(50) Text(50) Text(50) Text(150) Text(50) Text(50) Text(15) 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 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 212 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,060,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 5,689,120