Database Management Systems Chapter 2 Database Design Jerry

Database Management Systems Chapter 2 Database Design Jerry Post Copyright © 2003 1

D A T A B A S E Database System Design User views of data. Conceptual data model. Class diagram that shows business entities, relationships, and rules. Implementation (relational) data model. List of nicely-behaved tables. Use data normalization to derive the list. Physical data storage. Indexes and storage methods to improve performance. 2

D A T A B A S E The Need for Design ² Goal: To produce an information system that adds value for the user ª Reduce costs ª Increase sales/revenue ª Provide competitive advantage ² Objective: To understand the system ª To improve it ª To communicate with users and IT staff ² Methodology: Build models of the system 3

D A T A B A S E Designing Systems ² Designs are a model of existing & proposed systems ª They provide a picture or representation of reality ª They are a simplification ª Someone should be able to read your design (model) and describe the features of the actual system. ² You build models by talking with the users ª Identify processes ª Identify objects ª Determine current problems and future needs ª Collect user documents (views) ² Break complex systems into pieces and levels 4

D A T A B A S E Design Stages ² Initiation ª Scope ª Feasibility ª Cost & Time estimates ² Physical Design ª Table definitions ª Application development © Queries © Forms © Reports © Application integration ² Requirements Analysis ª User Views & Needs © Forms © Reports ª Processes & Events ª Objects & Attributes ² Conceptual Design ª Models © Data flow diagram © Entity Relationships © Objects ª User feedback ª Data storage ª Security ª Procedures ² Implementation ª ª Training Purchases Data conversion Installation ² Evaluation & Review 5

D A T A B A S E Initial Steps of Design 1. Identify the exact goals of the system. 2. Talk with the users to identify the basic forms and reports. 3. Identify the data items to be stored. 4. Design the classes (tables) and relationships. 5. Identify any business constraints. 6. Verify the design matches the business rules. 6

D A T A B A S E Entities/Classes Customer. ID Last. Name First. Name Phone Address City State ZIP Code Name Properties Add Customer Methods Delete Customer (optional for database) 7

D A T A B A S E Definitions ² Relational database: A collection of tables. ² Table: A collection of columns (attributes) describing an entity. Individual objects are stored as rows of data in the table. ² Property (attribute): a characteristic or descriptor of a class or entity. ² Every table has a primary key. ª The smallest set of columns that uniquely identifies any row ª Primary keys can span more than one column (concatenated keys) ª We often create a primary key to insure uniqueness (e. g. , Customer. ID, Product#, . . . ) called a surrogate key. Primary key Rows/Objects Employee. ID Taxpayer. ID 12512 888 -22 -5552 15293 222 -55 -3737 22343 293 -87 -4343 29387 837 -36 -2933 Properties Class: Employee Last. Name Cartom Venetiaan Johnson Stenheim First. Name Abdul Roland John Susan Home. Phone (603) 323 -9893 (804) 888 -6667 (703) 222 -9384 (410) 330 -9837 Address 252 South Street 937 Paramaribo Lane 234 Main Street 8934 W. Maple 8

D A T A B A S E Definitions Entity: Something in the real world that we wish to describe or track. Class: Description of an entity, that includes its attributes (properties) and behavior (methods). Object: One instance of a class with specific data. Property: A characteristic or descriptor of a class or entity. Method: A function that is performed by the class. Association: A relationship between two or more classes. Pet Store Examples Entity: Class: Object: Property: Method: Association: Customer, Merchandise, Sales Customer, Merchandise, Sale Jones, Premium Cat Food, Sale #32 Last. Name, Description, Sale. Date Add. Customer, Update. Inventory, Compute. Total Each Sale can have only one Customer. 10

D A T A B A S E Associations ² General ² Objects related to objects ª One-to-one ª One-to-many ª Many-to-many ª An employee can work in only one department ª Many departments can work on many different products (1: 1) (1: M) (M: N) ² Relationships represent business rules ² Objects related to properties ª An employee can have only one name ª Many employees can have the same last name ª Sometimes common-sense places ª Sometimes unique to an organization ² Users often know current relationships, rarely future Breed Supplier 1 * sent to Purch. Order Cust. 1 1 * Animal * Sale places Emp * * Tasks performs 11

D A T A B A S E Class Diagram. ² Class/Entity (box) ² Association/Relationship Customer 1… 1 ª Lines ª Minimum © 0: optional © 1: required ª Maximum © Arrows © 1, M 0…* Order 0…* 1…* Item. 12

D A T A B A S E Sample Association Rules (Multiplicity) ² An order must have exactly 1 customer, ª 1… 1 Minimum of 1 Maximum of 1 Customer 1… 1 ² And at least one item. ª 1…* Minimum of 1 Maximum many ² An item can show up on no orders or many orders. ª 0…* Optional (0) Maximum many 0…* Sale 0…* 1…* Item 13

D A T A B A S E N-ary Associations ² Associations can connect more than two classes. ² Associations can become classes. ª Events ª Many-to-many ª Need to keep data Component * Employee * * * Product ² Example has two many-to-many relationships. ª We know which components go into each product. ª We know which employees worked on a product. ² We need to expand the relationships to show which employees installed which components into each product. ª Each assembly entry lists one employee, one component, and one product. ª By appearing on many assembly rows, the many-tomany relationships can still exist. 14

D A T A B A S E N-ary Association Example Employee Name. . . 1 Component Comp. ID Type Name 1 * * Assembly * 1 Product. ID Type Name Assembly Employee. ID Comp. ID Product. ID Multiplicity is defined as the number of items that could appear if the other N-1 objects are fixed. Almost always “many. ” 15

D A T A B A S E Association Details: Aggregation Sale. Date Employee Item * contains * Description Cost Aggregation: the Sale consists of a set of Items being sold. 16

D A T A B A S E Association Details: Composition Bicycle Size Model Type … Wheels 1 built from 2 Rims Spokes … 1 1 1 Crank Item. ID Weight Two ways to display composition. 1 Stem Item. ID Weight Size Bicycle Size Model Type … Wheels Crank Stem Composition: aggregation where the components become the new object. 17

D A T A B A S E Association Details: Generalization Animal Date. Born Name Gender Color List. Price {disjoint} Mammal Litter. Size Tail. Length Claws Fish Fresh. Water Scale. Condition Spider Venomous Habitat 18

D A T A B A S E Inheritance Class name ² Class Definition-encapsulation ª Class Name ª Properties ª Methods Properties Methods Account. ID Customer. ID Date. Opened Current. Balance Open. Account Close. Account ² Inheritance Relationships ª ª Inheritance Generic classes Savings Accounts Checking Accounts Focus on differences Interest. Rate Minimum. Balance Polymorphism Overdrafts Most existing DBMS do not handle inheritance Pay. Interest Bill. Overdraft. Fees Close. Account Polymorphism 19

D A T A B A S E Multiple Parents Vehicle Human Powered Motorized On-Road Off-Road or Car Bicycle 20

D A T A B A S E Association Details: Reflexive Relationship manages worker * Employee 0… 1 manager A reflexive relationship is an association from one class back to itself. In this example, an employee can also be a manager of other employees. 21

D A T A B A S E Pet. Store Overview Class Diagram * Animal * 1 * Animal * Purchase 1 1 Supplier Employee 1 1 * Merchandise Purchase * Sale * 1 Customer * * Merchandise * 23

D A T A B A S E Pet Store Class Diagram: Access 24

D A T A B A S E Data Type Sizes Access SQL Server Oracle Text fixed variable Unicode memo Text Memo char, varchar nchar, nvarchar text CHAR VARCHAR 2 NVARCHAR 2 LONG Number Byte (8 bits) Integer (16 bits) Long (32 bits) (64 bits) Fixed precision Float Double Currency Yes/No Byte Integer Long NA NA Float Double Currency Yes/No tinyint smallint bigint decimal(p, s) real float money bit INTEGER NUMBER(38, 0) NUMBER(p, s) NUMBER, FLOAT NUMBER(38, 4) INTEGER Date/Time Interval Date/Time NA datetime smalldatetime interval year … DATE INTERVAL YEAR … Image OLE Object image LONG RAW, BLOB Auto. Number Identity rowguidcol SEQUENCES ROWID 26

D A T A B A S E Computed Attributes Denote computed values with a preceding slash (/). Employee Name Date. Of. Birth /Age Phone … {Age = Today - Date. Of. Birth} 27

D A T A B A S E Event Examples ² Business Event ª Item is sold. ª Decrease Inventory count. ² Data Event ª Inventory drops below preset level. ª Order more inventory. ² User Event ª User clicks on icon. ª Send purchase order to supplier. Trigger ON (Quantity. On. Hand < 100) THEN Notify Purchasing Manager 28

Event Triggers ² Business Process: Ship Product ª Trigger: Inventory Change ª Executes function/trigger in Inventory object. ² Object: Inventory ª Property: Current Inventory. ª Function: Update Inventory. ª Trigger: On Update, call Analyze function. ² Process: Analyze Inventory Order … Ship. Order … ª Function: Determine need to reorder. ª Trigger: Generate new order. Inventory … 1. Subtract(Prod, Subtract Qty sold) Analyze 1. 1 Analyze … (Product) low D A T A B A S E Purchase … Reorder … 1. 1. 1 Reorder (Product, quantity) 29

D A T A B A S E Design Importance: Large Projects ² Design is harder on large projects. ª Communication with multiple users. ª Communication between IT workers. ª Need to divide project into pieces for teams. ª Finding data/components. ª Staff turnover--retraining. ² Need to monitor design process. ª Scheduling. ª Evaluation. ² Build systems that can be modified later. ª Documentation. ª Communication/underlying assumptions and model. 30

D A T A B A S E Large Projects ² Project Teams ª Divide the work ª Fit pieces together ª Evaluate progress ² Standards ª ª ª Design Templates Actions Events Objects ² Project planning software ª Schedules ª Gantt charts ² CASE tools ² Groupware tools ª Track changes ª Document work ª Track revisions © Naming convention © Properties 31

D A T A B A S E CASE Tools ² Computer-Aided Software Engineering ª ª Diagrams (linked) Data Dictionary Teamwork Prototyping © Forms © Reports © Sample data ² Examples ª Rational Rose ª Sterling © COOL: Dat © COOL: Jex (UML) ª Oracle ª IBM ª Code generation ª Reverse Engineering 32

D A T A B A S E Rolling Thunder: Top-Level Sales Bicycle Assembly Employee Location Purchasing 33

D A T A B A S E Rolling Thunder: Sales Customer Bicycle: : Bicycle 1… 1 Customer. ID Phone First. Name Last. Name Address Zip. Code City. ID Balance. Due 1… 1 0…* Bicycle. ID … Customer. ID Store. ID … Retail Store Customer Transaction Customer. ID Transaction. Date Employee. ID Amount Description Reference 0…* Store. ID Store. Name Phone Contact. First. Name Contact. Last. Name Address Zip. Code City. ID 0… 1 34

D A T A B A S E Rolling Thunder: Bicycle Model. Type Description Paint. ID Color. Name Color. Style Color. List Date. Introduced Date. Discontinued Letter. Style. ID Description Bicycle 1… 1 0…* 1… 1 Serial. Number Customer. ID Model. Type Paint. ID Frame. Size Order. Date Start. Date Ship. Employee Frame. Assembler Painter Construction Water. Bottle. Braze. On Custom. Name Letter. Style. ID Store. ID Employee. ID Top. Tube Chain. Stay … Bicycle. Tube. Used 1… 1 1…* Serial. Number 1… 1 Tube. ID Quantity 0…* Bike. Parts Serial. Number Component. ID Substitute. ID Location Quantity Date. Installed Employee. ID 35

D A T A B A S E Rolling Thunder: Assembly 1… 1 Bicycle: : Bike. Parts Serial. Number Component. ID. . . Bicycle: : Bicycle. Tube. Used Serial. Number Tube. ID Quantity 0…* 1… 1 0…* Component. ID Manufacturer. ID Product. Number Road Category Length Height Width Description List. Price Estimated. Cost Quantity. On. Hand Tube. Material Tube. ID Material Description Diameter … 1… 1 0…* Group. Components Group. ID Component. ID 0…* Groupo Group. ID 1… 1 Group. Name Bike. Type Component. Name 1… 1 Component. Name Assembly. Order Description 36

D A T A B A S E Rolling Thunder: Purchasing Purchase. Order Purchase. ID Employee. ID Manufacturer. ID Total. List Shipping. Cost Discount Order. Date Receive. Date Amount. Due 1… 1 0…* Manufacturer 1… 1 Manufacturer. ID Manufacturer. Name 1… 1 Contact. Name Phone Address Zip. Code City. ID Balance. Due Manufacturer. Trans Purchase. Item Purchase. ID Component. ID Price. Paid Quantity. Received 1…* 0…* Manufacturer. ID Transaction. Date Reference Employee. ID Amount Description 0…* 1… 1 Assembly: : Component. ID Manufacturer. ID 0…* Product. Number 37

D A T A B A S E Rolling Thunder: Location Sales: : Customer. ID … City. ID 1… 1 0…* Sales: : Retail. Store. ID … City. ID City 1… 1 City. ID 1… 1 Zip. Code 1… 1 City State 1… 1 Area. Code Population 1990 Population 1980 Country Latitude Longitude 0…* 0… 1 Employee: : Employee. ID … City. ID Purchasing: : Manufacturer. ID … 0…* City. ID State. Tax. Rate State Tax. Rate 38

Rolling Thunder: Employee Bicycle: : Bicycle Serial. Number … Employee. ID Ship. Employee Frame. Assembler Painter 0…* 0…* Bicycle: : Bike. Parts Serial. Number Component. ID … Employee. ID 0…* Employee 0…* worker Employee. ID 1… 1 Taxpayer. ID 1… 1 Last. Name First. Name Home. Phone Address Zip. Code 0…* City. ID Date. Hired Date. Released Current. Manager 0… 1 manager Salary. Grade Salary Title Work. Area 1… 1 manages D A T A B A S E Purchasing: : Purchase. Order Purchase. ID … Employee. ID 39

D A T A B A S E Customer. ID Phone First. Name Last. Name Address Zip. Code City. ID Balance. Due Customer. Trans Customer. ID Trans. Date Employee. ID Amount Description Reference Bicycle Serial. Number Customer. ID Model. Type Paint. ID Frame. Size Order. Date Start. Date Ship. Employee Frame. Assembler Painter Construction Water. Bottle Custom. Name Letter. Style. ID Store. ID Employee. ID Top. Tube Chain. Stay Head. Tube. Angle Seat. Tue. Angle List. Price Sales. Tax Sale. State Ship. Price Frame. Price Component. List Retail. Store. ID Store. Name Phone Contac. First. Name Contact. Last. Name Address Zipcode City. ID State. Tax. Rate State Tax. Rate Customer City. ID Zip. Code City State Area. Code Population 1990 Population 1980 Country Latitude Longitude Rolling Thunder Combined Bicycle. Tube Model. Type Description Component. ID Serial. Number Tube. ID Quantity Groupo Bike. Tubes Serial. Number Tube. Name Tube. ID Length Comp. Group. Name Bike. Type Year End. Year Weight Model. Size Paint. ID Color. Name Color. Style Color. List Date. Introduced Date. Discontinued Model. Type MSize Top. Tube Chain. Stay Total. Length Ground. Clearance Head. Tube. Angle Seat. Tube. Angle Bike. Parts Serial. Number Component. ID Substitute. ID Location Quantity Date. Installed Employee. ID Letter. Style Employee. ID Taxpayer. ID Last. Name First. Name Home. Phone Address Zip. Code City. ID Date. Hired Date. Released Current. Manager Salary. Grade Salary Title Work. Area Letter. Style Description Purchase. Order Purchase. ID Employee. ID Manufacturer. ID Total. List Shipping. Cost Discount Order. Date Receive. Date Amount. Due Purchase. Item Purchase. ID Component. ID Price. Paid Quantity. Received Manufacturer. ID Manufacturer. Name Contact. Name Phone Address Zip. Code City. ID Balance. Due Component. ID Manufacturer. ID Product. Number Road Category Length Height Width Weight Year End. Year Description List. Price Estimated. Cost Quantity. On. Hand Tube. Material Tube. ID Material Description Diameter Thickness Roundness Weight Stiffness List. Price Construction Group. Compon Group. ID Component. Name Assembly. Order Description Manufacturer. Trans Manufacturer. ID Transaction. Date Employee. ID Amount Description Reference 40

D A T A B A S E Application Design ² Simple form based on one table (Animal). ² But also need lookup tables for Category and Breed. 42

D A T A B A S E Appendix: DB Design System ² http: //time-post. com/dbdesign ² Students and instructors need only an Internet connection and a Java-enabled Web browser. ² Instructor can sign up free by sending email to: jpost@timepost. com ² Instructors set up the class and select assignments. ² Students create accounts and work on the assignments. ² The system provides immediate feedback in the form of comments and questions for each proposed table. 43

D A T A B A S E Appendix: Typical Customer Order 44

D A T A B A S E Menu Appendix: DB Design Screen Column list Title box (can be moved) Drawing area Scroll bars to display more of the drawing area Status line Feedback window 45

D A T A B A S E Appendix: Adding a Table and a Key ² Right click in the main drawing window and 1 select the option to Add table. ² Right click the gray bar at the top of the table, 2 select the Rename table option and enter “Customer” ² Drag the Generate Key item onto the new Customer table. ² Right click on the new column name, select 4 the Rename option and enter “Customer. ID” 3 46

D A T A B A S E Appendix: Two Tables ² The Customer table has a generated key of Customer. ID ² Each column in the table represents data collected for each customer. ² Each column depends completely on the primary key. ² Each Order is identified by a unique Order. ID generated by the database system. ² The Customer. ID column is used because the customer number can be used to look up the corresponding data in the Customer table. 47

D A T A B A S E Appendix: Relationships—Linking Tables ² Drag the Customer. ID column from the Customer table and drop it on the Customer. ID column in the Orders table. ² For the Min value in Customer, select One instead of Optional. ² Click the OK button to accept the relationship definition. 48

D A T A B A S E Appendix: Creating Problems 49

D A T A B A S E Appendix: Detecting Problems (Grading) Double click a line to mark the errors. 50

D A T A B A S E Appendix: Testing a Change ² Attempted fix ª Make the relationship many-to-many ª Make Order. ID a key ² But, the score went down!!! 51

D A T A B A S E Appendix: A Solution ² The intermediate table Order. Item converts the many-to-many relationship into two one-to-many relationships. ² Both Order. ID and Item. ID are keys, indicating that each order can have many items, and each item can be sold on many orders. 52

D A T A B A S E Appendix: Data Types Right click the column names and set the data type. 53