CpSc 3220 Designing a Database Rockoff Ch 19 Murach Ch 16 A database system is modeled after a real-world system Real-world system Database system Tables People Documents Columns Rows Facilities Other systems Murach's PHP and.
Download ReportTranscript CpSc 3220 Designing a Database Rockoff Ch 19 Murach Ch 16 A database system is modeled after a real-world system Real-world system Database system Tables People Documents Columns Rows Facilities Other systems Murach's PHP and.
CpSc 3220 Designing a Database Rockoff Ch 19 Murach Ch 16 A database system is modeled after a real-world system Real-world system Database system Tables People Documents Columns Rows Facilities Other systems Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 2 Two approaches to database design Top down. Put all data elements into one big group and then split that group into tables Bottom up. Identify elements that should become tables, find the attributes they should have and then find how they relate to each other Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 3 The six basic steps for designing a data structure Step 1: Step 2: Step 3: Step 4: Step 5: Step 6: Identify the data elements Subdivide each element into its smallest useful components Identify the tables and assign columns Identify the primary and foreign keys Review whether the data structure is normalized Identify the indexes Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 4 An invoice that can be used to identify data elements Acme Fabrication, Inc. Custom Contraptions, Contrivances and Confabulations 1234 West Industrial Way East Los Angeles California 90022 800.555.1212 fax 562.555.1213 Part No. Qty. CUST345 12 457332 50173 7 4375 Invoice Number: I01-1088 Invoice Date: 10/05/10 Net 30 www.acmefabrication.com Terms: Description Unit Price Design service, hr Extension 100.00 1200.00 79.90 559.30 Duct tape, black, yd 1.09 4768.75 4.79 9.58 75.00 525.00 125.00 250.00 Baling wire, 25x3ft roll 328771 2 Rubber tubing, 100ft roll CUST281 7 Assembly, hr CUST917 2 Testing, hr Sales Tax Your salesperson: Accounts receivable: 245.20 Ruben Goldberg, ext 4512 Inigo Jones, ext 4901 $7,557.83 PLEASE PAY THIS AMOUNT Thanks for your business! Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 5 The data elements identified on the invoice Vendor name Vendor address Vendor phone number Vendor fax number Vendor web address Invoice number Murach's PHP and MySQL, C16 Invoice date Invoice terms Item part number Item quantity Item description Item unit price Item extension Vendor contact name Vendor contact ext. Vendor AR contact name Vendor AR contact ext. Invoice total © 2010, Mike Murach & Associates, Inc. Slide 6 A name that’s divided into first and last names Vendor sales contact name Ruben Goldberg Vendor sales contact first name Vendor sales contact last name Ruben Goldberg Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 7 An address that’s divided into street address, city, state, and zip code Vendor address 1234 West Industrial Way, East Los Angeles, California 90022 Street and number City State Zip 1234 West Industrial Way East Los Angeles California 90022 Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 8 Possible tables and columns for an A/P system Vendors table Vendor name Vendor address Vendor city Vendor state Vendor zip code Vendor phone number Vendor fax number Vendor web address Vendor contact first name Vendor contact last name Vendor contact phone Vendor AR first name Vendor AR last name Vendor AR phone Terms* Account number* Data elements that were added *Data element related to two or more entities Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 9 Possible tables and columns for an A/P system (continued) Invoices table Invoice number* Invoice date Terms* Invoice total Payment date Payment total Invoice due date Credit total Account number* Murach's PHP and MySQL, C16 Invoice line items table Invoice number* Item part number Item quantity Item description Item unit price Item extension Account number* Sequence number © 2010, Mike Murach & Associates, Inc. Slide 10 The relationships between the tables in the accounts payable system vendors invoices invoiceLineItems vendorID vendorName vendorAddress vendorCity vendorState vendorZipCode vendorPhone vendorContactFirstName vendorContactLastName terms accountNo invoiceID vendorID invoiceNumber invoiceDate invoiceTotal paymentTotal creditTotal terms invoiceDueDate paymentDate accountNo invoiceID invoiceSequence accountNo lineItemDescription itemQuantity itemUnitPrice lineItemAmount Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 11 Two tables with a many-to-many relationship employees memberships committees employeeID firstName lastName employeeID committeeID committeeID committeeName Linking table Two tables with a one-to-one relationship employees employeePhotos employeeID firstName lastName employeeID employeePhoto Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 12 Operations that can violate referential integrity This operation… Violates referential integrity if… Delete a row from the primary key table The foreign key table contains one or more rows related to the deleted row Insert a row in the foreign key table The foreign key value doesn’t have a matching primary key value in the related table Update the value of a foreign key The new foreign key value doesn’t have a matching primary key value in the related table Update the value of a primary key The foreign key table contains one or more rows related to the row that’s changed Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 13 About indexes An index provides a way for a database management system to locate information more quickly. MySQL automatically creates an index for a primary key. You can create composite indexes of two or more columns. Because indexes must be updated each time you add, update, or delete a row, don’t create more indexes than you need. When to create an index When the column is a foreign key When the column is used frequently in search conditions or joins When the column contains a large number of distinct values When the column is updated infrequently Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 14 A table might not be Normal A table that contains repeating columns A table that contains redundant data Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 15 The seven normal forms First (1NF) Second (2NF) Third (3NF) Boyce-Codd (BCNF) Fourth (4NF) Fifth (5NF) Domain-key (DKNF) or Sixth (6NF) Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 16 The benefits of normalization Since a normalized database has more tables than an unnormalized database, and since each table has an index on its primary key, the database has more indexes. That makes data retrieval more efficient. Since each table contains information about a single entity, each index has fewer columns (usually one) and fewer rows. That makes data retrieval and insert, update, and delete operations more efficient. Each table has fewer indexes, which makes insert, update, and delete operations more efficient. Data redundancy is minimized, which simplifies maintenance and reduces storage. Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 17 The accounts payable system in third normal form vendors invoices invoiceLineItems vendorID vendorName vendorAddress vendorCity vendorState vendorZipCode vendorPhone vendorContactFirstName vendorContactLastName defaultTermsID defaultAccountNo invoiceID vendorID invoiceNumber invoiceDate invoiceTotal paymentTotal creditTotal termsID invoiceDueDate paymentDate invoiceID invoiceSequence accountNo lineItemAmount lineItemDescription terms generalLedgerAccounts accountNo accountDescription termsID termsDescription termsDueDays Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 18 The invoice data with a column that contains repeating values The invoice data with repeating columns Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 19 The invoice data in first normal form Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 20 The invoice data in first normal form with keys added Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 21 The invoice data in second normal form Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 22 The AP system in second normal form invoices invoiceLineItems invoiceID vendorName vendorAddress vendorCity vendorState vendorZipCode vendorPhone vendorContactFirstName vendorContactLastName InvoiceNumber invoiceID invoiceSequence accountNo invoiceLineItemDescription itemQuantity itemUnitPrice lineItemAmount invoiceDate invoiceTotal paymentTotal creditTotal terms invoiceDueDate paymentDate accountNo Questions about the structure 1. 2. 3. 4. Does the vendor information depend only on the invoice_id column? Does the terms column depend only on the invoice_id column? Does the account_no column depend only on the invoice_id column? Can the invoice_due_date and line_item_amount columns be derived from other data? Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 23 The AP system in third normal form vendors invoices invoiceLineItems vendorID vendorName vendorAddress vendorCity vendorState vendorZipCode vendorPhone vendorContactFirstName vendorContactLastName defaultTermsID defaultAccountNo invoiceID vendorID invoiceNumber invoiceDate invoiceTotal paymentTotal creditTotal termsID invoiceDueDate paymentDate invoiceID invoiceSequence accountNo lineItemAmount lineItemDescription terms generalLedgerAccounts accountNo accountDescription termsID termsDescription termsDueDays Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 24 The accounts payable system in fifth normal form vendors invoices invoiceLineItems vendorID vendorName vendorAddress vendorZipCode vendorAreaCodeID vendorPhone vendorContactFirstName vendorContactLastName defaultTermsID defaultAccountNo invoiceID vendorID invoiceNumber invoiceDate invoiceTotal paymentTotal creditTotal termsID invoiceDueDate paymentDate invoiceID invoiceSequence accountNo lineItemQty lineItemUnitPrice lineItemDescriptionID lineItemDescriptionID invoiceLineItemDescription zipCodes zipCode city state generalLedgerAccounts terms areaCodes areaCodeID areaCode Murach's PHP and MySQL, C16 lineItemDescriptions accountNo accountDescription termsID termsDescription termsDueDays © 2010, Mike Murach & Associates, Inc. Slide 25 When to denormalize When a column from a joined table is used repeatedly in search criteria, you should consider moving that column to the primary key table if it will eliminate the need for a join. If a table is updated infrequently, you should consider denormalizing it to improve efficiency. Because the data remains relatively constant, you don’t have to worry about data redundancy errors once the initial data is entered and verified. Include columns with derived values when those values are used frequently in search conditions. If you do that, you need to be sure that the column value is always synchronized with the value of the columns it’s derived from. Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 26 MySQL Workbench... Lets you create and edit diagrams. Lets you define the tables, columns, and indexes for a database. Lets you define the relationships between the tables in a database. Lets you generate a diagram from a SQL creation script. Lets you generate a SQL creation script from a diagram. How to install MySQL Workbench 1. Go to the MySQL Workbench web site at: http://wb.mysql.com/ 2. Download the version for your system. Run the installer or setup file and respond to the prompts. Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 27 The Home page for MySQL Workbench Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 28 MySQL Workbench Murach's PHP and MySQL, C16 © 2010, Mike Murach & Associates, Inc. Slide 29