ۥ-  _0((*****,****(+ )+ 3+ ?.(g.$///////40\_0_0_i +Introduction to Database Design Presented by: John Betz John Betz is a Microsoft Access Product Manager in the Database and Development Tools division at Microsoft. He worked formerly as a systems engineer on UNIX-based RDBMS environments. ask title "What is the TITLE of this course? Use this format for capitalization: How to Capitalize the Title"Introduction to Database Designask coursenumber "What is the COURSE NUMBER? (Example: EZ101)"AC101 Overview Database design is a complex subject, no matter how easy some people think it is. This session only scratches the surface, but it is a good scratch. A properly designed database is a model of a business, or some thing in the real world. Like their physical model counterparts, data models enable you to get answers about the facts that make up the objects being modeled. Its the questions that need answers that determine which facts need to be stored in the data model. In the relational model, data is organized in tables that have the following characteristics: every record has the same number of facts; every field contains the same type of facts in each record; there is only one entry for each fact; no two records are exactly the same; the order of the records and fields is not important. At the end of this reading, you should have a basic understanding of problems resulting from poor database design, be familiar with the Domain/Key normal form, understand a process for designing a relational database, and be aware of the tools used in Microsoft Access to support integrity constraints in a database. Why Design? Accurate design is crucial to the operation of a reliable and efficient information system. Microcomputer technology is now so advanced that the impact of a poor design may not show up as early as in the past; however, when the problems appear they can be severe. Although Microsoft Access and Microsoft FoxPro are powerful and easy to use, they have historically not lent themselves well to ad hoc design. The design of a database has to do with the way data is stored and how that data is related. The design process is performed after you determine exactly what information needs to be stored and how it is to be retrieved. The more carefully you design, the better the physical database meets users needs. In the process of designing a complete system, you must consider user needs from a variety of viewpoints. Problems Resulting from Poor Design A myriad of problems can manifest themselves as a result of poor database design: The database and/or application may not function properly. Data may be unreliable or inaccurate. Performance may be degraded. Flexibility may be lost. The following section explains some common problems resulting from poor database design. The problems can be grouped under two categories: redundant data and modification anomalies. Redundant Data Consider the following table that stores data about products and suppliers. This seemingly harmless table contains many potential problems. Prod ID Description Supplier Address City Region Country  34 Sasquatch Ale Bigfoot Breweries 3400 - 8th Avenue, Suite 210 Bend OR USA  27 Schoggi Schokolade Heli Swaren GmbH Tiergartenstrae 5 Berlin  Germany  68 Scottish Longbreads Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  42 Singaporean Fried Mee Leka Trading 471 Serangoon Loop, Singapore  Singapore  20 Sir Rodneys Marmalade Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  21 Sir Rodneys Scones Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  61 Sirop drable Forts drables 148 rue Chasseur Ste-Hyacinthe Qubec Canada  46 Spegesild Lyngbysild Lyngbysild Fiskebakken 10 Lyngby  Denmark  35 Steeleye Stout Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  Suppose you wanted to add another part? 37 Lumbermans Lager Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  Disk space is wasted by duplicating data about the supplier. Every time a new product is entered for a particular supplier, all of the supplier data has to be repeated. Imagine the problems if several suppliers supply hundreds of products each. Modification Anomaly What if BigFoot Breweries moves to Portland? How many rows have to change in order to ensure that the new address is recorded? Prod ID Description Supplier Address City Region Country  34 Sasquatch Ale Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  27 Schoggi Schokolade Heli Swaren GmbH Tiergartenstrae 5 Berlin  Germany  68 Scottish Longbreads Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  42 Singaporean Fried Mee Leka Trading 471 Serangoon Loop, Singapore  Singapore  37 Lumbermans Lager Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  20 Sir Rodneys Marmalade Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  21 Sir Rodneys Scones Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  61 Sirop drable Forts drables 148 rue Chasseur Ste-Hyacinthe Qubec Canada  46 Spegesild Lyngbysild Lyngbysild Fiskebakken 10 Lyngby  Denmark  35 Steeleye Stout Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  Again, imagine the issues surrounding modifications of hundreds of rows of data for one supplier. When changes are made, they must be made to all copies of the data. Think about the confusion that results from changing only a subset of the duplicate data. Deletion Anomaly Suppose you no longer carried product 42 and decided to delete that row from the table? Prod ID Description Supplier Address City Region Country  34 Sasquatch Ale Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  27 Schoggi Schokolade Heli Swaren GmbH Tiergartenstrae 5 Berlin  Germany  68 Scottish Longbreads Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  42 Singaporean Fried Mee Leka Trading 471 Serangoon Loop, Singapore  Singapore  20 Sir Rodneys Marmalade Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  21 Sir Rodneys Scones Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  61 Sirop drable Forts drables 148 rue Chasseur Ste-Hyacinthe Qubec Canada  46 Spegesild Lyngbysild Lyngbysild Fiskebakken 10 Lyngby  Denmark  35 Steeleye Stout Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  Now, looking at the remaining data below, what is the address of Leka Trading? Prod ID Description Supplier Address City Region Country  34 Sasquatch Ale Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  27 Schoggi Schokolade Heli Swaren GmbH Tiergartenstrae 5 Berlin  Germany  68 Scottish Longbreads Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  20 Sir Rodneys Marmalade Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  21 Sir Rodneys Scones Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  61 Sirop drable Forts drables 148 rue Chasseur Ste-Hyacinthe Qubec Canada  46 Spegesild Lyngbysild Lyngbysild Fiskebakken 10 Lyngby  Denmark  35 Steeleye Stout Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  A deletion anomaly means that we lose more information than we want. We lose facts about more than one subject with one deletion. Insertion Anomaly Next, you want to add a new supplier, StarStruck, but you have not yet ordered any products from that supplier. What do you add? Prod ID Description Supplier Address City Region Country  34 Sasquatch Ale Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  27 Schoggi Schokolade Heli Swaren GmbH Tiergartenstrae 5 Berlin  Germany  68 Scottish Longbreads Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  42 Singaporean Fried Mee Leka Trading 471 Serangoon Loop, Singapore  Singapore  20 Sir Rodneys Marmalade Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  21 Sir Rodneys Scones Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  61 Sirop drable Forts drables 148 rue Chasseur Ste-Hyacinthe Qubec Canada  46 Spegesild Lyngbysild Lyngbysild Fiskebakken 10 Lyngby  Denmark  35 Steeleye Stout Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  ??? ?????? StarStruck, Inc. 101 Mariposa Seattle WA USA  This situation is called an insertion anomaly. Negatively stated, we cannot add a fact about one subject until we have additional data about another subject. Domain/Key Normal Form Relational theorists have classified database schemas that have inconsistencies based on the anomalies to which they are susceptible. You may have encountered discussions about different forms, such as first normal form or third normal form. One of the unique normalization forms was proposed by R. Fagin, in 1981, and is used as the basis of this presentation. Fagin ascertained that if the tables in your database are in Domain/Key Normal Form (D/KNF), then they are free of modification anomalies. To understand D/KNF there are four terms that must be understood: dependency, key, domain, and restriction. Dependency A dependency is a relationship that may exist between two columns. Given the value of one column, you are able to determine the value of another column. Lets use the table in the previous examples. Given the product number, we are able to determine the product description. This is a dependency: descriptions are dependent on product numbers. Given a suppliers name, are we able to determine the product description? Not necessarily. In the case of BigFoot Breweries, this supplier has a number of products associated with it. Therefore, in the above tables, description is not a dependency of suppliers. To detect a dependency, ask yourself this question: In this table, does the value of one column determine ALL POSSIBLE values of another column? ProductID determines Description? YES   Supplier determines Address? YES   Supplier determines ProductID? NO  Key Most tables should have a column or a combination of columns that uniquely identifies a row of data. A column is key if all other columns in a row are dependent on it. At first glance, it may appear that the ProductID in our example uniquely identifies a row of data. But ProductID 34 identifies the supplier as BigFoot Breweries, as do part numbers 35, and 37. Therefore, the column ProductID is not the key. In this table, we have a complex key, derived from ProductID, Description, and Supplier. Domain A domain is the set of values a column can have. Every column has a domain, which has both physical and logical properties. Physical Description. The physical part of a domain is the type of information about that column. In our example, Supplier is defined as TEXT 40. Because of this definition, the physical description of the domain is the set of TEXT data with 40 or fewer characters. Similarly, the physical description for the domain of ProductID is expressed as INTEGER. This results in data of nine or fewer numbers. Logical Description. The logical part of the domain is the set of information associated with that fact. Supplier addresses are not in the same domain as customer addresses, although they have the same physical property of TEXT 60. Consider the value 7124 E. 41st Place. Is this value in the domain of supplier address? To be in this domain it must have fewer than 60 characters and be a suppliers address. Restriction A restriction is a limitation of some type on the values in a table. A dependency is a type of restriction. Stating that Description is dependent on ProductID is a restriction. Keys are a type of restriction. When a column is a key, it means that all other columns in that table are dependent on the key. Remember that a key can be a combination of columns. A domain is another type of restriction. When defining the physical and logical properties of a column, we restrict the data in that column. Restriction is a general term. There are many other ways to restrict data in a table. Below are some examples: Invoice date must be formatted as MM/DD/YY. ProductID must begin with the number 100. Suppliers must be TEXT with 40 or fewer characters. Tax Total must be CURRENCY with values between $1.00 and $9,999,999.99. The Normalizing Process Normalizing the database ensures that the structure of the database allows changes to be made without incurring unexpected consequences. The role of normalization is to maintain stable, reliable data through good database design. The goal of good database design is to ensure that all restrictions are logical consequences of domain and key restrictions. Tables, like paragraphs, should have a single theme. The table in the anomalies examples has two themes: Information about products Information about the suppliers of products The way to manage this information most efficiently is to split the table into two tables: a table of products and a table of suppliers. Products Prod ID Description Supplier  34 Sasquatch Ale Bigfoot Breweries  27 Schoggi Schokolade Heli Swaren GmbH & Co. KG  68 Scottish Longbreads Specialty Biscuits, Ltd.  42 Singaporean Hokkien Fried Mee Leka Trading  20 Sir Rodneys Marmalade Specialty Biscuits, Ltd.  21 Sir Rodneys Scones Specialty Biscuits, Ltd.  61 Sirop drable Forts drables  46 Spegesild Lyngbysild  35 Steeleye Stout Bigfoot Breweries   Suppliers Supplier Address City Region Country  Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend OR USA  Heli Swaren GmbH & Co. KG Tiergartenstrae 5 Berlin  Germany  Specialty Biscuits, Ltd. 29 Kings Way Manchester  UK  Leka Trading 471 Serangoon Loop, Suite #402 Singapore  Singapore  Forts drables 148 rue Chasseur Ste-Hyacinthe Qubec Canada  Lyngbysild Lyngbysild Fiskebakken 10 Lyngby  Denmark  Now you can add products without duplications, change supplier locations without changing several rows, and not lose information if you delete a part. If you wish, you can always bring the original table back using a query with a join on Supplier. A Method of Database Design As you have seen, database design plays a major role in the stability and the reliability of your data. In this section, we show you the process of designing a database. To help illustrate the design process, a database named Rags is created for a fictitious wholesale clothing manufacturer called Unlimited Rags. Although there are a number of rules that can be followed in designing a database structure, the design process is as much an art as it is a science. Follow these rules when at all possible, but not to the point where the database loses the functionality that is so important to the user. Doing a paper design first has several advantages: Saves time, money, and problems Makes system more reliable; avoids potential data-modification problems Serves as a blueprint for discussion Helps in estimating costs and size A good design should have the following objectives: Meet the users needs Solve the problem Be free of modification anomalies Have a reliable and stable database, where the tables are as independent as possible Be easy to use Design of the Database Model The design of the database structure requires the following steps: 1. List the objects. 2. List the facts about the objects. 3. Turn the objects and facts into tables and columns. 4. Determine the relationship among objects. 5. Determine the key columns. 6. Determine the linking columns. 7. Determine the constraints. 8. Evaluate the design model. 9. Implement the database. Step 1: List the Objects Make a list of all objects. An object is a single theme, similar to a paragraph. At Unlimited Rags the objects are: Customer  Ship Rate   Product  Invoice   Employee  Dependent  Step 2: List the Facts About the Objects There is a great deal of information associated with every object. In this step, you should list the facts about an object and then eliminate the facts that are not important to the solution of the problem. The customer object, for example, can have many facts associated with it: company name, address, city, founders, number of employees, stock price. In this case, it is not important to keep information about the number of employees, stock price, or founders. Unlimited Rags needs only the information it will use now and possibly in the future. Object Important Facts About the Object  Employee employee, name, birth date, gender, SSN, marital status  Customer company name, address, city, state, zip, contact, title  Invoice date, salesperson, customer, quantity, shipping charge, tax, freight  Product product name, description, cost, markup  Dependent name, date of birth  Ship Rates state, rates  Step 3: Turn the Objects and Facts into Tables and Columns Objects automatically become tables, and facts become columns once the column domains are determined. Recall that a domain is a set of values that a column can have. Every column has a domain, which has both physical and logical properties. For example, the column for employee last name is defined as TEXT 15. TEXT 15 is the physical property of the column. Because of this definition, its domain is the set of all employee last names with 15 characters or less. If a column is used to link two or more tables, the domains must be the same and the columns should be given the same name. If the logical description differs (for example, employee last name and customer last name), the columns are not the same and should not share the same name. The following is a list of the preliminary tables, columns, and domains for Unlimited Rags: Table: CUSTOMER Table: PRODUCT Name Type Length  Name Type Length  COMPANY TEXT 45  PRODNAME TEXT 30  CADD1 TEXT 30  PRODDESC TEXT 50  CADD2 TEXT 30  PRODCOST CURR   CCITY TEXT 25  PMARKUP NUMB   CSTATE TEXT 2      CZIP TEXT 10  Table: DEPENDENT  CAC TEXT 3  Name Type Length  CTELPH TEXT 7  DLAST TEXT 15  CONTACT TEXT 30  DFIRST TEXT 10  TITLE TEXT 30  DDOB D/T   Table: INVOICE Table: EMPLOYEE Name Type Length  Name Type Length  INVDATE D/T   ESSN TEXT 11  REQDATE D/T   ELASTN TEXT 15  SHIPNAME TEXT 45  EFIRSTN TEXT 10  SHIPADDR TEXT 30  EDOB D/T   SHIPCITY TEXT 25  EGENDER TEXT 1  SHIPZIP TEXT 10  EMARITAL TEXT 1  INVTOTAL CURR   EADDR1 TEXT 30      EADDR2 TEXT 20    ECITY TEXT 25  Table: SHIP RATE  ESTATE TEXT 2  Name Type Length  EZIP TEXT 10  SHIPST TEXT 2  EAC TEXT 3  SHIPRATE NUMB   EHOMEPH TEXT 7  Often it helps in the design stages to draw boxes to represent the tables. In later steps you can then fill in key columns and draw the relationships among the tables.  Step 4: Determine the Relationship Among Objects To determine the relationship among the objects, take each object and look at how that object may be related to another. Keep in mind that not every relationship existing between objects is important. The relationships that are important are those that allow you to model the database after the real-world situation that the database represents. One-to-one relationships. For any given row in Table A, there is only one row in Table B. For any given row in Table B, there is only one row in Table A. There are no one-to-one relationships in the Rags database. An example of a one-to-one relationship is that of employee data and private employee data. General information, such as employee name, address, and start date, is kept in one table, and to ensure privacy, personal information, such as salary, is kept in another table. One-to-many relationships. For any given row in Table A, there are many rows in Table B. For any given row in Table B, there is only one row in Table A. The relationship between an employee and an employees dependents is one-to-many, because one employee may have many dependents, but a dependent is related to only one employee. The relationship between customers and invoices is also one-to-many. One invoice is related to one customer, but a customer can have many invoices. Many-to-many relationships. For any given row in Table A, there are many rows in Table B. For any given row in Table B, there are many rows in Table A. There is a many-to-many relationship between the product table and the invoice table. A product can be associated with many different invoices and an invoice can contain many different products. In the case of the Rags database, we are attempting to model an environment that is based on sales transactions. Take the example of products and customers: Although in some circumstances we may be interested in the relationship between customers and products, in a sales transaction, the customer is related to a product only when a sale occurs. Therefore, a customer is related to an invoice, and the invoice carries the relationship to a product. The first step in determining the type of relationship between tables is to list every table and to see how it relates to any others: Customer is related to invoice. Customer is not related to any other table in the list. Employee is related to dependent. Employee (sales) is related to invoice. Product is related to invoice. An effective method to find the type of relationship is to ask whether a specific record in Table A can point to (is linked to) one or to many rows in Table B, and then reverse the tables and ask the question again. Does a customer record point to one or many invoices? Many Does an invoice row link with one or many customers? One The relationship between the tables is one-to-many. A sales employee writes one or many invoices? Many An invoice is written by one or many employees? One The relationship between employee and invoice is also one-to-many. A product can be a line item on one or many invoices? Many Can an invoice be linked to one or many products? Many The relationship between product and invoice is many-to-many. The Ship Rate table illustrates that a table can be included in a database and not need to be relationally linked to any other table. Step 5: Determine the Key Columns A key can be an account number, social security number, part number, license number, or any other numeric value or combination of characters that are unique. A complex key is one that is derived from more than one column. Microsoft Access supports complex keys directly. No other row in the table can have the value of the key column(s). Other tables may share the same set of key information. If a company name is universally unique, it is used as a unique row identifier. However, if there is any possibility another company could have the same name, then it is not unique and must not be employed as a key column. Do not use any column as a key where the possibility exists for a duplicate. A key column cannot contain null values. By definition, all key columns should be indexed. Because text names are usually not unique and cannot be used in math operations, it is useful to make key columns a sequential numeric value. In many cases, it is easier to develop your own unique row identifier. If you want automatic numbering for invoice numbers or employee ID numbers, COUNTER data type in Microsoft Access is a good choice for a physical description for the domain of a key column. Most of the tables in the final Rags database contain columns with a COUNTER or NUMBR data type for the unique row identifier. Each key is also indexed, and duplicates are not allowed. Database performance is enhanced with a single numeric column as the key.  Step 6: Determine the Linking Columns If you have been careful about designating key columns, you also have determined the linking columns. Links provide a way to tie information (rows) in one table to another table. If a table has a key column, that column can generally serve as the link. Tables are linked together through their key columns. However, the placement of the key is important, and where the link is placed depends on the type of relationship between the tables. To determine the placement of the links, you must first know the type of relationship among the objects or tables. Once you know the type of relationship among tables, it is much easier to determine where to place the linking column to tie two tables together. Note that not all tables need to be linked relationally. Employees must be linked with dependents, but you would not link employees with ship rates or products. Linking in a one-to-one relationship. In one-to-one relationships the link should be the most stable column or should be from the table where the key column is created. The most stable is the column least likely to change. If an automatic numbering system is being used, then use that column as the linking column. Linking in a one-to-many relationship. In one-to-many relationships the linking column should come from the one table. The key column from the employee table (one side) should be placed in the dependent table (many side). When the key empid is placed in the dependent table, it is referred to as a foreign key in the dependent table. Linking in a many-to-many relationship. The many-to-many relationship causes problems when attempting to retrieve data and when relating a value in one table to its corresponding value in the other table. It is important to understand this relationship to be able to recognize and control this situation when it arises. A classic many-to-many relationship is product and invoice. A product can be an item on many different invoices and an invoice can have many products associated with it. But which key will we use for a link? If invid is placed in the product table, then all of the product data would have to be repeated for each invoice that contains that product. If prodid is placed in the invoice table, then the invoice information has to be repeated for each product contained in the invoice. This leads to redundant data, and the potential for invalid data is increased. Performance may suffer. The solution to many-to-many relationships is to create an intersection table. This table should contain the key columns from both tables. This is illustrated in the following diagram.  Step 7: Determine the Relationship Constraints Often the information we get from a database comes from more than one table. For example, if we want to know who the parent of a particular dependent is, the name is determined by using the value in empid to look up the correct row in the employee table. The question of who the parent is can be answered only if there is a row in the employee table with an empid value corresponding to that in the dependent table. To ensure the integrity of the data in our database, our model should require, for example, that no row can be added to the dependent table, unless there is already a corresponding row in the employee table. This requirement is known as a relationship constraint. In this case, a constraint must exist on the dependent table that ensures that the employee (parent) exists. If you are creating an invoice, you must have a customer to bill. An entry in the customer table must exist before the invoice can be written. In this case, a constraint must exist on the invoice table to ensure that the customer exists. There are at least four methods to implement relationship constraints: Built-in controls in the DBMS Data entry and access procedures Programming Implementation of rules Microsoft Access has certain referential integrity constraint mechanisms built into the engine. With Microsoft FoxPro, the relationship constraints must be handled programmatically. In Microsoft Access, rules at the database or form level can be employed to enforce column domains (for example, accept values less than 200, or text value must be F or M) or in any other operation where you want a data entry test to be performed. Step 8: Evaluate the Design The next step in the design process is the evaluation of the design. In this step, you should look for any design flaws that could cause the data to be unreliable, unstable, or redundant. Every table should be evaluated by asking the following questions: 1. Does each table have a single theme? It should. Each column should be a fact about the key. 2. Does each table have a key column(s)? It should. 3. Are there any dependencies? Only logical consequences of the key should exist. 4. Are the domains unique among tables? Do not mix domains unless the column is common between tables. 5. Are the restrictions domain or key? 6. Is the table easy to use? Evaluation of the Customer Table CUSTID COMPANY   CADD1   CADD2   CCITY   CSTATE   CZIP   CAC   CTELPH   CONTACT   TITLE   The table has a single theme: customers. The table has a key: custid. The table does not have any dependencies that are not logical consequences of the key. Given custid, a company and company address can be uniquely determined. Given a company, we cannot determine any particular custid. Given a state, we cannot determine any particular custid. Therefore, the customer table does not have any dependencies. The column names are not used in any other tables except for custid, which is a foreign key in the invoice table. The restrictions are domain or key. Step 9: Implement the Design Once the database had been designed on paper, the next step is to implement the design in Microsoft Access. When defining tables in Microsoft Access, it is extremely important to keep your paper design in mind. Designing a database on the fly can cause problems that may be quite difficult to recover from. (Remember the anomalies earlier in this chapter.) In Microsoft Access 2.0, there are two tools that will help you complete the implementation of your design. The Table Wizard can be used to generate a variety of common tables. The graphical system relationships window can be used to set up relationships and key dependencies. Note, that while using the Table Wizard ensures proper relational design, the eight steps prior to implementation remain important and should be completed prior to constructing the tables. The following is a list of the final tables, columns, and domains for Unlimited Rags, including linking columns: Table: CUSTOMER Table: PRODUCT Name Type Length  Name Type Length  CUSTID COUNTER  PRODID COUNTER  COMPANY TEXT 45  PNAME TEXT 30  CADD1 TEXT 30  PDESCRIP TEXT 50  CADD2 TEXT 30  PCOST CURR   CCITY TEXT 25  PMARKUP NUMB   CSTATE TEXT 2      CZIP TEXT 10    CAC TEXT 3  Table: SHIP RATE  CTELPH TEXT 7  Name Type Length  CONTACT TEXT 30  SHIPST TEXT 2  TITLE TEXT 30  SHIPRATE NUMB   Table: INVOICE Table: TRANSACTION Name Type Length  Name Type Length  INVID COUNTER  INVID NUMB   CUSTID NUMB   PRODID NUMB   INVDATE D/T   TQTY NUMB   REQDATE D/T   TDISC NUMB   SHIPNAME TEXT 45  TPRICE CURR   SHIPADDR TEXT 30      SHIPCITY TEXT 25      SHIPST TEXT 2      SHIPZIP TEXT 10      INVTOTAL CURR        Table: EMPLOYEE Table: DEPENDENT Name Type Length  Name Type Length  EMPID COUNTER  EMPID NUMB    ESSN TEXT 11  DLAST TEXT 15  ELASTN TEXT 15  DFIRST TEXT 10  EFIRSTN TEXT 10  DDOB D/T   EDOB D/T       EGENDER TEXT 1      1EMARITAL TEXT 1      EADDR1 TEXT 30      EADDR2 TEXT 20      ECITY TEXT 25      ESTATE TEXT 2      EZIP TEXT 10      EAC TEXT 3      EHOMEPH TEXT 7      Summary By following the nine-step design process, the problems of data redundancy, changing multiple occurrences of data, and deletion and insertion anomalies can be avoided. It is well worth the time spent in the design process to ensure a reliable and flexible system. Design to the point where redundancy is eliminated or controlled. As you design your database, keep in mind the following list of common database errors to avoid: Trash-tableputting everything in the same table No unique row identifier (key column or columns) No linking or common columns Mixing logical and physical descriptions of domains Putting the linking column in the wrong table Restrictions not enforced Many-to-many relationships without intersecting tables Suggested Reading An Introduction to Database Systems. Vol. I, Vol, II., C.J. Date, Addison-Wesley Publishing Co., 1983. Database Processing, Fundamentals, Design, Implementation, Third Edition. David M. Kroenke and Kathleen A. Dolan, SRA, 1988. Advanced Database Techniques. Daniel Martin, The MIT Press, 1985. page 2 symbol 183 \f "Symbol" Title Microsoft Techsymbol 183 \f "Symbol"Ed '94 Microsoft Techsymbol 183 \f "Symbol"Ed '94 Title symbol 183 \f "Symbol" page 1 page 18 symbol 183 \f "Symbol" ref coursenumberAC101 ref titleIntroduction to Database Design Microsoft Techsymbol 183 \f "Symbol"Ed '94 Microsoft Techsymbol 183 \f "Symbol"Ed '94 ref coursenumberAC101 ref titleIntroduction to Database Design symbol 183 \f "Symbol" page 19 stv} @ h v,stx8hst x8h :N-,&& 1&0 R & &$TNPPMicrosoft PowerPoint & TNPPP & &TNPP 0 8 145&.L---L. & & X---X  & &0|---|0 & &< T---T < & &p --- p & &  T---T  & & qTimes-.  2 @CUSTOMERhgQ`o`g & &)qTimes߃-.  2 PRODUCTXhohhg` & &qTimes-.  2 4@EMPLOYEE`Y`oh`` & &xqTimes߃-.  2 ( INVOICE8gho8h` & &qTimes-.  2 ( DEPENDENTh`X`hg`h` & &qTimes߃-.  2  SHIP RATEQo8X$hh`` & &TNPP & --"System-]^Y_f]M:!Q/.&& H 1&2N  & &$TNPPMicrosoft PowerPoint & TNPPP & &TNPP 2  f145&(np---pn( & &#, k---k, # & & T---T  & & |T---T|  & &--- & &$ ---$  & &A0qTimesz-.  2 CUSTOMERhgQ`o`g & &5$qTimes-.  2 PRODUCTXhohhg` & &qTimesz-.  2 0INVOICE8gho8h` & &qTimesy-.  2 EMPLOYEE`Y`oh`` & &qTimesz-.  2 x DEPENDENTh`X`hg`h` & &FqTimesy-.  2 x SHIP RATEQo8X$hh`` & &1| pArialz-.  *2 "Key is CUSTID (COUNTER)`PH( H(hh`X(h(0hphhX`h0 & &-pArialy-.  "2 "COMPANY may not behpx``h`(xPH(PP((PQpArialz-.  2 ="unique.PP PPP( & &,R pArialy-.  *2 Key is PRODID (COUNTER)`PH( H(`hph(h(0hphhX`h0 & &|pArialz-.  $2 PDESCRIP may not be`h``hh(`(xPH(PP((QPpArialy-.  2 ,unique.PP PPP( & &iXpArialz-.  (2 (Key is INVID (COUNTER)`PH( H((h`(h(0hphhX`h0 & &:pArialy-.  (2 0Key is EMPID (COUNTER)`PH( H(`x`(h(0hphhX`h0 & &;qpArialz-.  *2 t0Could use SSN but it ishPP P(PHP(`ah(PP(( (( HpArialy-.  2 !0TEXT.X``X( & &pArialz-.  .2 Complex key. All columns.hPxP PH(HPH(((` (HP QwQH( & &*gpArialy-.  .2 dComplex key. All columns.hPxP PH(HPH(((` (HP QwQH( & &TNPP & --"System- ]X ^t`&Change Password...zC&han:1Y:2+!  1&J  & &$TNPPMicrosoft PowerPoint & TNPPP & &TNPP  D :145&--- & &( ---(  & &L(---(L & &@---@ & &--- & &@( ---( @ & &( ---(  & &qTimesw-.  2 \CUSTOMERhgQ`o`g & &$qTimesGw-.  2 DPRODUCTXhohhg` & &qTimesw-.  2 INVOICE8gho8h` & &A0qTimesGw-.  2 EMPLOYEE`Y`oh`` & &:A0qTimesw-.  2 t TRANSACTION`hghQgh`8oh & &qTimesGw-.  2 X DEPENDENTh`X`hg`h` & &2qTimesw-.  2 L SHIP RATEQo8X$hh`` & &pArialGw-.  *2 LXKey is CUSTID (COUNTER)`PH( H(hh`X(h(0hphhX`h0 & &mc pArialw-.  "2 XCOMPANY may not behpx``h`(xPH(PP((PQpArialGw-.  2 Xunique.PP PPP( & &N xpArialw-.  *2 (Key is PRODID (COUNTER)`PH( H(`hph(h(0hphhX`h0 & &IpArialGw-.  $2 PDESCRIP may not be`h``hh(`(xPH(PP((QPpArialw-.  2 unique.PP PPP( & &=,pArialGw-.  (2 Key is INVID (COUNTER)`PH( H((h`(h(0hphhX`h0 & &IV8pArialw-.  (2 LKey is EMPID (COUNTER)`PH( H(`x`(h(0hphhX`h0 & & WpArialGw-.  *2 LCould use SSN but it ishPP P(PHP(`ah(PP(( (( HpArialw-.  2 ULTEXT.X``X( & &y+ pArialGw-.  +2 Complex key. PRODID andhPxP PH(HPH(((`hph(h(PQPpArialw-.  2 INVID.(h`(h( & & pArialGw-.  .2 xComplex key. All columns.hPxP PH(HPH(((` (HP QwQH( & &BpArialw-.  .2 l|Complex key. All columns.hPxP PH(HPH(((` (HP QwQH( & &T--"System----'- $L & &8,hH--H$J8-T--'- $`+A & &--XL-l--'- $ & &<-- H-L--'- $5  & & H-- -`--'- $A_ & &TNPP & --22DDDDtDDDDDstu;=)6f g n o m ? O rtuw$&}H,¾   8,lnprkm  ÿ{wsokg&tv#%nprt1qsprtvÿ{wsok(!#wyprVXZ\  TV.A  ÿ{wsok) W Y !!Z!\!!! " "b"d"""##C#E###_&k&^)k))))))))))b+k+++[,],r,-..n/r///12245555555$6&6Y6[6666ÿC6666!7#7>7@7g7i7u77777&8(8c8e88888-9/9):F:<==>>>&@@@@@@@@AA-AUCCCCC D DZD\DDDDDDD ESHuHHHHHHHII@IBI[I]IoIIIIIINIIIIIJJ=JQJSJgJiJyJ{JJJJJJJJJJJJJKKKK+K-K@KBKUKWKiKkK}KKKKKKKKKKKKKKKKLLL"L$L6L8LJLLLLLL*MNNjPPJRdRUVVPVXVuV}VVVVVVVVWY?ZbZ`[[```8`ccdd$fJfjjkjmjjnZo q'q(rsssssst tttt t(t*t1t3t=t?tJtLtUtWtvv^zzzzzzzz!{#{E{G{k{m{{{{{{{{{{{{{||8|:|`|t|v|||||||||}}>}@}\}^}z}|}}O5\}}}}}}}} ~ ~ ~"~C~E~i~k~~~~~~~~~"$>@Y[tvzԂ<uփ "$:;QS[]klQÄĄƄDŽ݄߄%&56LNfg}ʅ˅ͅ΅ F9st\)6? m # L l ? O  "1Dcimrýwwwwwww 6+X pJJJJJJJJJJJJJJ 'rtx #:H]hjuw{xppppppp + p + p + p + p  $&*:L^mu}xppppppp + p + p + p + p HL_r,5BLU[clnrԕ~~~~~~~G??? 6+X pJJJJJJJJJJJJJJ >(X s  <<+ p  0JYegkmq Ըphhhhhhh + p + p + p + p $!9Sbnptvz#%)4@[cenxppppppp + p + p + p + p npt1:GQZ`hqswܝOGGGGGGG 6+X pJJJJJJJJJJJJJJ >+X p  + p 5O^jlprv!xppppppp + p + p + p + p !#'<Veqswy}!1Dagkpxppppppp + p + p + p + p pr*GMQVX\pzqqqqqqqULLLLLLL + p 6+X pJJJJJJJJJJJJJJ h>+X p 9HTVZ\`u ۿwooooooo + p + p + p + p  &AIKTVZj}.A ytoibbbbbbb >+X p  + p + p   + H N R W Y ] q !!!!-!B!M!O!Z!yyyyyyy]UUUUUUU + p + p + p 6+X pJJJJJJJJJJJJJJ Z!\!`!x!!!!!!!!!!!"" " """1"C"R"Z"b"d"h"s""""""xppppppp + p + p + p + p """"""""## ####1#:#>#C#E###%_&k&()^)k)))ytojje```YSS>+X p  + p + p)))))))))))*++[,-.//111-2Z22223Z444555555ƶzzulddd xl Xl Xl X$55555556$6&6*6?6Y6[6_6~6666666666ŽslllXQQQ ( CL ( CL ( CL ( CL ( CL ((CL 6667!7#7'727>7@7D7T7g7i7u777777777777ʶ}}}}}RKKKKK *(@g  x"((CL  ( CL ( CL ( CL777888&8(8B8Q8]8_8c8e8s8888888888888 99"9$9-9ªleeeeee (g  (g  (g  (g  (g -9/99):F:;<<<F=n=====>t>>>>>$?\????? @&@@@@@@@@@tmf|UlAn |U0(@g #@@@AA-AUC]CCCCCCC D DDZD\DeDDDDDDDDĭtnna[[NHH  l@  l@  l@  l@  l@ l@  lAn |UlAn DD EFGSHuH{HHHHHHHHHHHHHHHHHHHHHH~xxxq_YYRLLLlR 4lXR      l@ HHHHIIIIIII#I)I-I/I8I>I@IBIJIPISIUIWIYI[Iͻlff_YYYR4lXR  lR lR [I]IcIiImIoIIIIIIIIIIIIIIIIIIIIſpjjc]]]VD>lR 4lXR    lR lR IIIIIIIIIJ J JJJJJ=JCJIJQJSJYJ_JgJxxpibbZ    4lXR  lR gJiJrJwJyJ{JJJJJJJJJJJJJJJJJJJļ~wwo]VVNG@@   lR     lR     4lXR  JJJJJJJJJKK KKKK"K(K+K-K6K4lXR      lR      l0R     LLLL*MNjPJRSiUUVNVsVVVWWXJXXXXlmnn o0o?oZop q'qq(rr  4 :rrsxsssssssssstt t ttttt t"t(t*t,t1t3t5t=t?tuoob\\O lvf  lvf  lvf  lvf  lvf  lvf  lvf  lvf  x?tAtJtLtNtUtWtttunvvvxy^zzzzzzzzzzzzzzzzzzžrkkkkk[UU l  4lXR       lvf  lvf  zzzzzzz{{ { {{{!{#{*{0{4{6{={C{E{G{N{T{X{Z{c{i{k{||uoooo   lR     lR     lR    k{m{u{{{~{{{{{{{{{{{{{{{{{{{ž}}vpjA; (l@R    lR    lR    4lXR  {{{{{{{{{{{|||||| |$|&|0|6|8|૥yyrllll   lR     4lXR      8|:|`|f|l|t|v|||||||||||||||||||||ſpjjjjjjYSSSSSSSAlR  l  4lXR      h4lXR  ||||||||||}}}}}}}"}(},}.}6}<}>}@}J}P}T}V}X}Z}\}^}h}n}ý{uuuuc]] lR    4lXR     lR  lR  "n}r}t}v}x}z}|}}}}}}}}}}}}}}}}}}}}}}}}}}}˹~~~~]WR l0R     lR    lR    lR    }}~ ~ ~~~ ~"~)~2~4~;~A~C~E~L~R~V~X~_~e~i~k~s~y~}~~~~~૥zhbb[UUUN    lR     l  4lXR      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ۦ{{tnnnn\VVO  lR    lR    4lXR     lR ~ "$,268:<>@GMQSUWY[cilnprtýysslffff   lR    lR    lR    lR  $tv|րz9jԂüpkffaaaaaaa\ l0R     lR    lR    lR $Ԃ<[]VX       h Body TextCODEcaption Bullet List 2 Bullet List Table TitlebylineBioBullet List BodyGraphic Table Text Table Head Number List _Table SLUGj$<F   & ! ,&*j+& * 0  sD/F N V^xx Fx xp p  x ((     <~ #1F]X _ i  Tms Rmn `Symbol Helv 0CourierTimes New Roman Arial MS Serif MS Sans SerifTimes Helvetica System0Courier New AvantGardeITC BookmanHelvetica-NarrowNewCenturySchlbk PalatinoZapfChanceryPZapfDingbats 0ModernRoman @Script WingdingsMicrosoft Logo Arial NarrowBook AntiquaBookman Old Style Century GothicCentury Schoolbook@Monotype CorsivaMonotype Sorts0MS LineDraw PAlgerian Arial Rounded MT BoldPBraggadocio Britannic Bold@Brush Script MT PColonna MT PDesdemonaFootlight MT Light Impact PKino MT Wide Latin@Matura MT Script Capitals PPlaybill MSIcons MS Dialog0LinePrinter0Lucida Sans Typewriter0Letter Gothic MSSmall Fonts4:&& "#:QRk%5LMf}~!9999!!9999!BioStarttitle coursenumber5:#$L]g"\D:\WINWORD\TEMPLATE\TECH-ED.DOTIntroduction to Database Design Kurt Smith Kurt Smith