Bài giảng Enterprise information systems - Chapter 6: Relational Database Design: Converting Conceptual Models to Relational Databases

Chapter 6Relational Database Design: Converting Conceptual Models to Relational DatabasesChapter Learning ObjectivesConvert a conceptual business process level REA model into a logical relational model Convert a logical relational model into a physical implementation using Microsoft AccessExplain the difference between conceptual, logical, and physical database modelsEnter transaction data into a relational databaseInterpret a physical database implementation in Microsoft Access to determine what must have been the underlying logical modelInterpret a logical relational model to determine what the underlying conceptual model must have beenRecognize and implement various application level controls to facilitate the integrity of data entered into a relational database 2Database Model LevelsA Conceptual model represents reality in an abstracted form that can be used in developing an information system in a wide variety of formats (e.g. relational, object-oriented, flat-file, etc.)It is hardware and software independentIt is independent of any logical model typeA Logical model represents reality in the format required by a particular database model (e.g. relational or object-oriented)Is still hardware and software independentDepends on the chosen logical model typeA Physical model is created specifically for a particular database software packageIs dependent on hardware, software, and on the chosen logical model type3Relational Database ModelThe relational model is a type of logical database model that was conceived by E.F. Codd in 1969 The relational model is based on set theory and predicate logicIt is well formalized, so its behavior is predictableA relational database consists of tables (relations) that are linked together via the use of primary and foreign keysA FOREIGN KEY in a table is a primary key from a different table that has been posted into the table to create a link between the two tables4Relational Database ModelRelational database tables are made up of rows and columnsRows are called the table extension or tuplesThe ordering of rows in a table does not matterColumns are called the table intension or schemaThe ordering of columns in a table does not matterAll values in a column must conform to the same data format (e.g. date, text, currency, etc.)Each cell in a database table (a row-column intersection) can contain only one value no repeating groups are allowed5Foreign Key ExampleSalespersonIDName123456Fred654321FrancisSaleIDDateAmountSalesperson061401A6/14$4,218123456061401B6/14$6,437654321061501A6/15$1,1126543216Relational Database ModelSome principles of the relational modelEntity IntegrityA primary key in a table must not contain a null valueGuarantees uniqueness of entities and enables proper referencing of primary key values by foreign key valuesReferential IntegrityA value for a foreign key in a table must eitherBe null (blank)Match exactly a value for the primary key in the table from which it was postedOne Fact, One PlaceFact = a pairing of a candidate key attribute value with another attribute valueFacts are found in the extensional data7Referential Integrity Example8One Fact-One Place ViolationsOne fact in multiple places112233449One Fact-One Place ViolationsMultiple facts in one place12345Each value of each attribute in a row is paired with the primary key, so if any cell has two or more attribute values, by definition there are multiple facts in one place (also known as a repeating group)10Converting Conceptual to Relational Step 1: Create a separate table to represent each entity in the conceptual model1A: Each attribute of the entity becomes a column in the relational table2A: Each instance (member) of the entity set will become a row in the relational tableSteps 2-4 (detailed in the next few slides) involve determining whether each relationship in the conceptual model should be represented as a separate table or as a posted foreign keyRedundancy and Load are important determinantsRedundancy = one fact in multiple places or multiple facts in one placeLoad = the percentage of non-null values in a columnParticipation Cardinalities communicate some of the information regarding redundancy and load11Relationship ConversionMaximum CardinalitiesThe general rule is to post into a “1” entity tableThis avoids “repeating groups” redundancyYou can NEVER post into an “N” entityThis causes “repeating groups” redundancyMinimum CardinalitiesThe general rule is to post into a “1” (mandatory) entity tableThis avoids null values in the foreign key columnThis rule should be violated in some circumstances (to be discussed soon)12Step 2: Create a separate table to represent each many-to-many relationship in the conceptual model, I.e., for the following participation cardinality patterns (0,N)-(0,N) (0,N)-(1,N) (1,N)-(0,N) (1,N)-(1,N) You must create a separate table to represent the relationshipThe primary keys of the related entity tables are posted into the relationship table to form its primary key. This kind of primary key is called a composite or concatenated primary key This avoids redundancyThere are no exceptions to this rule!!! If you post a foreign key in either direction, redundancy will be a problem for many-to-many relationshipsRelationship Conversion13Example: Many-Many RelationshipsStudent#NameAddress1TonyCleveland2EmilyNew York3LeighBirminghamCourse#NameAcg4401AISAcg3101FAR 1Course# *Acg4401, Acg3101Acg4401, Acg3101Acg4401, Acg3101Student#*1, 2, 31, 2, 314Example: Many-Many RelationshipStudent#NameAddress1TonyCleveland2EmilyNew York3LeighBirminghamCourse#NameAcg4401AISAcg3101FAR 1Student#Course#1Acg44011Acg31012Acg44012Acg31013Acg310115Relationship ConversionStep 3: For participation cardinality pattern (1,1)-(1,1), consider whether the two entities are conceptually separate or whether they should be combinedIf they should remain separate, then 3A: Post the primary key from one entity’s table into the other entity’s table as a foreign key3B: It doesn’t matter which entity’s primary key is posted into the other entity’s table, but DO NOT post bothDO NOT make a separate table Redundancy is automatically avoided and load is not an issue when you post a foreign key into either table in a (1,1)-(1,1) relationship16Example: (1,1)-(1,1)SaleIDDateAmountS16/12$10S26/12$15S36/13$12CR-IDDateAmountCR16/12$10CR26/12$15CR36/13$12CR-ID *CR1CR2CR3S-ID *S1S2S3Choose ONE of these; DO NOT do both!!!SaleCash Receiptyields(1,1)(1,1)SaleIDDateAmountCR-IDDateAmount17Relationship ConversionStep 4: For remaining relationships that have (1,1) participation by one entity set, post the related entity’s primary key into the (1,1) entity’s table as a foreign keyI.e., for the following participation cardinality patterns (0,N)-(1,1) (1,N)-(1,1) (1,1)-(0,N) (1,1)-(1,N) (0,1)-(1,1) (1,1)-(0,1)Do NOT make a separate tablePost a foreign key INTO the (1,1) entity’s table from the other entity’s tableRedundancy is avoided and load is not an issue if you follow this instructionIf you post the opposite direction, either redundancy [for N maximums] OR load [for 0 minimums] will be a problem18Example 1: Posting into a (1,1)SaleCustomerIs-to(1,1)(0,N)SaleIDDateAmountCustIDNameCity(1,N)orSaleIDDateAmountS16/12$10S26/12$15S36/13$12CincinnatiStevenC2Walnut CreekHeatherC1AddressName Cust-IDCust-ID*C1C2C1C3DaveCincinnatiS2S1, S3S-ID*19Example 2: Posting into a (1,1)SaleCash Receiptyields(1,1)(0,1)SaleIDDateAmountCR-IDDateAmountSaleIDDateAmountS16/12$10S26/12$15S36/13$12CR-IDDateAmountCR16/12$10CR26/12$15CR36/13$12CR46/13$1,000CR-ID*CR1CR2CR3S-ID*S1S2S320Relationship ConversionStep 5: For remaining relationships that have (0,1) participation by one or both of the entities, consider loadI.e., for the following participation cardinality patterns (0,N)-(0,1) (1,N)-(0,1) (0,1)-(0,N) (0,1)-(1,N) (0,1)-(0,1)The rule for maximum cards requires posting into a (0,1) or making a separate table; you CANNOT post into the (0,N) or (1,N)The rule for minimum cards says you really shouldn’t post into the (0,1) because it will create null values that waste valuable space in the databaseHowever, if a separate table would waste more space, then it is better to follow the maximum rule and break the minimum rule5A: Post the related entity’s primary key into the (0,1) entity’s table as a foreign key for any relationships for which that results in a high load5B: Create a separate table for any relationships for which posting a foreign key results in low loadNote: For (0,1)-(0,1), step 5A, post whichever direction results in highest load; if neither direction yields high load, then follow step 5B21Example: Load ConsiderationsSome cash disbursements (13/26) pay for purchasesIf we post Receiving Report# into Cash Disbursement, 13 out of 26 will be non-nullThis is a medium loadMight be worth breaking minimum ruleConsider other posting optionMost purchases (14/18) result in cash disbursementsIf we post Check# into Purchase, 14 out of 18 will be non-nullThis is a high loadWorth breaking the minimum ruleConclusion: post Check# into Purchase table to represent the “pays for” relationship22Example: Load considerationsFew purchases (3/18) result in purchase returnsIf we post Purchase Return Slip# into Purchase, only 3 out of 18 will be non-nullThis is low loadMust either make a separate table or consider posting the other directionCan’t post receiving report# into purchase return because one purchase return slip # can be associated with multiple purchasesConclusion: Make a separate table to represent the “allowance for” relationship23Relationship Attribute PlacementIf relationship becomes a separate table, then relationship attributes are placed in that tableIf relationship can be represented by a posted foreign key, relationship attribute is posted alongside the foreign key24Relationship Attribute PlacementIf relationship becomes a separate table, then relationship attributes are placed in that tableIf relationship can be represented by a posted foreign key, relationship attribute is posted alongside the foreign key25Fixing One Fact Multiple PlacesWhat facts are in multiple places in this table?Reverse engineer to get the ER model that this table must represent Is the ER model that results in this table correct?What SHOULD the ER model have been instead?What is the correct relational model?EmpIDEmpNamePayrateHours WorkedDept#DeptName8532Andy$1336D423Audit7352Jennifer$1445D423Audit215Arlie$2050D777ISAAS4332Craig$1860D821Tax74Steven$2264D821TaxEmployee26Fixing One Fact Multiple PlacesEmployeeDepartmentAssigned toEmplIDEmpnameHoursWorkedPayrateDeptIDDeptNameEmpIDEmpNamePayrateHours WorkedDept#8532Andy$1336D4237352Jennifer$1445D423215Arlie$2050D7774332Craig$1860D82174Steven$2264D821Dept#DeptNameD423AuditD777ISAASD821TaxEmployeeDepartment27Fixing Multiple Facts in One PlaceWhat facts are in multiple places?How could this be avoided?Warehouse#AddressQOHW1123 Oak2,14,784W2456 Pine4,23,873Product#DescriptionStdCostQOHAB12Granddaddy$5,0002,4BC445Mama$3,00014,23DD2Littlebabe$100784,873Warehouse#Product#W1AB12W1BC445W1DD2W2AB12W2BC445W2DD2InventoryWarehouseInventoryInWarehouse28Fixing Multiple Facts in One PlaceWarehouse#AddressW1123 OakW2456 PineProduct#DescriptionStdCostAB12Granddaddy$5,000BC445Mama$3,000DD2Littlebabe$100Warehouse#Product#QOHW1AB122W1BC44514W1DD2784W2AB124W2BC44523W2DD2873InventoryWarehouseInventoryInWarehouse29Physical Implementation using Microsoft Access30Creating an Access Database31Creating Access Tables32Connecting Access Tables33Concatenated Primary Keys34Relationships between tables versus relationships between entities35Relational Database Design SummaryThe relational model is based on set theory and predicate logic and the resultant relations (tables) can be manipulated for information retrieval purposes if they are properly constructed To create well-behaved tables, follow the rules we discussedConversion rules for cardinality patternsOne Fact-One PlaceThink at the data (extensional) level!!When creating physical databases, use the conceptual and logical models to help you realize the important issues and potential pitfalls36Chapter 6End of Chapter