1、Database Design.School of softwareChapter 6 Advanced Database Designs任课教师：何婧任课教师：何婧2Database Design.School of softwareObjectivesnHow to describe Derived attribute.nRecognize when a database model requires a recursive relationship.nHow to describe complex relationship.nHow to identify and solve probl
2、ems in an ER model called connection traps.nRecognize when it is appropriate to use generalization/specialization hierarchies.3Database Design.School of software6.1 AttributesnAttribute can be classified as being:simple or composite(复杂复杂)single-valued or multi-valuedor derived（来源）（来源）4Database Desig
3、n.School of softwareAttributesnDerived attributeAttribute that represents a value that is derivable from value of a related attribute,or set of attributes,not necessarily in the same entity.5Database Design.School of softwareAttributesnDerived attribute(SQL Server)create table emp(empid int not null
4、 primary key,salary money not null,bonus money not null default 0,total_salary as(salary+bonus);6Database Design.School of softwareAttributesnDerived attributeinsert into emp(empid,salary,bonus)values(100,$5000.00,$1000.00);insert into emp(empid,salary,bonus)values(101,$3000,$500);select*from emp;10
5、05000.00001000.00006000.00001013000.0000500.00003500.00007Database Design.School of softwareAttributesnDerived attributecreate table emp_tax(taxid int not null primary key,empid int not null foreign key references emp(empid),accumulation_fund money,medicare money,salary money)8Database Design.School
6、 of softwareAttributesnDerived attributecreate trigger trg_for_emp_taxon emp_taxafter insertas begin declare total_salary money,salary_result money;select total_salary=total_salary from emp,inserted where emp.empid=inserted.empid;select salary_result=total_salary-accumulation_fund-medicare from inse
7、rted;update emp_tax set salary=salary_result;end;9Database Design.School of softwareAttributesnDerived attributeinsert into emp_tax(taxid,empid,accumulation_fund,medicare)values(001,100,$120,$80);select*from emp_tax;1 100 120.000080.00005800.000010Database Design.School of software6.2 Recursive Rela
8、tionshipsExhibit 5-1:Enrollment Database DesignOriginal Design of the Enrollment Database11Database Design.School of softwareMentor Recursive RelationshipsExhibit 5-2:Mentor Relationships for Enrollment DatabaseInstructor 11 mentors instructors 22 and 33.Instructor 33 mentors instructor 44.12Databas
9、e Design.School of softwareImplementing a Recursive RelationshipExhibit 5-3:Data Relationships for MentorRecursive RelationshipA foreign key(INSTRUCTOR$id)is added to the INSTRUCTOR table,which is a copy of the primary key of that same table.13Database Design.School of softwareER Diagram with a Recu
10、rsive RelationshipExhibit 5-4:Enrollment Database Diagramwith Recursive RelationshipWith the foreign key added,there is now a one-to-many relationship between instructors.14Database Design.School of softwareBill-of-Materials Exhibit 5-5:Bill-of-Materials ExampleA bill-of-materials is a structure tha
11、t shows a relationships between products.This is a many-to-many relationship:a product can have many sub-products,and a product can be a sub-product in many larger products.15Database Design.School of softwareBill-of-Materials in Table FormExhibit 5-6:Bill of Materials TableTrying to represent a man
12、y-to-many recursive relationship does not work since you could not determine how many component columns would be needed,and there will be a large number of empty cells.16Database Design.School of softwareER Diagram for a Many-to-Many Recursive RelationshipExhibit 5-7:Bill-of Materials ER DiagramAs w
13、ith all many-to-many relationships,a many-to-many recursive relationship is represented with an associative table.In this case,however,the associative table is linked back to the same table(PRODUCT),with both halves of its primary key being foreign keys from the PRODUCT table.17Database Design.Schoo
14、l of softwareMany-to-Many Recursive Relationship DataExhibit 5-8:Bill-of-Materials Database Tables18Database Design.School of software6.3 Complex relationshipExhibit 5-14:Summer Reading Fun Step-by-Step DesignnMultiplicity is the number(or range)of possible occurrences of an entity type in an n-ary
15、relationship when other(n-1)values are fixed.19Database Design.School of softwareComplex relationship individual examples20Database Design.School of softwareComplex relationship multiplicity21Database Design.School of softwareComplex relationship ER diagram22Database Design.School of softwareComplex
16、 relationship representation as tables23Database Design.School of softwaren联系之间存在重叠，如何表达联系之间的联系？n实例：职工参加项目，并在此过程中可能使用机器Complex Relationship-聚集职工职工项目项目参加参加并并使用使用姓名姓名 工种工种名称名称类型类型机器名机器名型号型号方案方案1 1工时工时机器机器职工号职工号项目号项目号机器号机器号工时工时e1j1m13e1j1m23e1j2m34e2j1m15e3j2null4e4j2null4e5j2null6e6j2null524Database D
17、esign.School of software聚集职工号机器号e1m1e1m2e1m3e2m1职工号项目号工时e1j13e1j24e2j15e3j24e4j24e5j26e6j25职工职工项目项目参加参加姓名姓名 工种工种名称名称类型类型机器名机器名型号型号方案方案2 2工时工时使用使用机器机器e1在j1项目中使用什么机器？25Database Design.School of softwaren聚集是一种抽象，通过它联系被作为高层实体集n实体集A与B以及它们的联系可被看成实体集，并与另一实体集C发生联系聚集ABR1R2C26Database Design.School of softwar
18、e聚集职工职工项目项目参加参加姓名姓名 工种工种名称名称类型类型机器名机器名型号型号工时工时使用使用机器机器27Database Design.School of software聚集职工号职工号项目号项目号工时工时e1j13e1j24e2j15e3j24e4j24e5j26e6j25职工号项目号机器号e1j1m1e1j1m2e1j2m3e2j1m128Database Design.School of software6.4 Problems with ER modelsnProblems may arise when designing an ER model called connect
19、ion traps.(连接陷阱连接陷阱)nOften due to a misinterpretation of the meaning of certain relationships.nTwo main types of connection traps are called fan traps and chasm traps.29Database Design.School of softwareProblems with ER modelsnFan trap（扇形陷阱）（扇形陷阱）Two entities have a 1:*relationship that fan out from
20、 a third entity,but the two entities should have a direct relationship between them to provide the necessary information.30Database Design.School of softwareAn example of a fan trap31Database Design.School of softwareResolving the fan trap32Database Design.School of softwareResolving the fan trap33D
21、atabase Design.School of softwareProblems with ER modelsnChasm trap（深坑陷阱）（深坑陷阱）A model suggests the existence of a relationship between entities,but the pathway does not exist between certain entity occurrences.34Database Design.School of softwareAn example of a chasm trap35Database Design.School of
22、 softwareResolving the chasm trap36Database Design.School of software6.5 Supertype/Subtype HierarchiesExhibit 5-9:Equipment Entity and DataThe first four columns apply to all equipment but the last four apply to only some equipment,which results in empty cells.37Database Design.School of softwareEqu
23、ipment Supertype/Subtype HierarchyExhibit 5-10:Equipment Supertype/SubtypeHierarchySupertype Entity:General entity with the common field.Subtype Entites:specialized entities with unique fields.Partial Specialization:Instances of the supertype dont have to belong to a subtype.Total Specialization:Ins
24、tances of the supertype must belong to a subtype.Disjoint rule:Supertype may belong to,at most,one subtype.Overlap rule:Supertype may belong to multiple subtypes.38Database Design.School of softwareSupertype/Subtype ExampleExhibit 5-11:STUDENT,FACULTY andSTAFF tablesAll three tables share four commo
25、n fields.39Database Design.School of softwarePerson Supertype/Subtype HierarchyStep 1:TablesStep 2:Fields and KeysStep 3:Recognize Common Fields-Start by identifying three different tables.-Add the fields and designate the primary keys.-Make sure that the fields actually store the same data for each
26、 entity.Exhibit 5-12:Person Supertype/SubtypeHierarchy40Database Design.School of softwarePerson Supertype/Subtype HierarchyStep 4:Create Supertype/Subtype HierarchyStep 5:Determine Total/Partial Specialization and Disjoint/Overlap RuleExhibit 5-12:Person Supertype/SubtypeHierarchyTotal Specializati
27、onOverlap41Database Design.School of softwareMore Complex Supertype/Subtype ExampleExhibit 5-13:Athlete/SGA HierarchySubtypes not only contain different data,but can also be involved in different relationships with other tables.Here,an athlete student subtype is involved in a relationship with a tea
28、m.Partial SpecializationOverlap42Database Design.School of softwareAllStaff table holding details of all staff 43Database Design.School of softwareStaff entity with subclasses representing job roles44Database Design.School of softwareShared subclass and a subclass with its own subclass45Database Des
29、ign.School of softwareVehicle entity into vehicle types 46Database Design.School of softwareComplex Design Example:Summer Reading FunExhibit 5-14:Summer Reading Fun Step-by-Step DesignnProblem StatementThe Summer Reading Fun program is an educational program intended to help children improve their r
30、eading skills over the summer.You are helping the program directors design a database that will improve their program management.To make the program seem less like school,there are no tests.Students improve their skills by reading books rather than taking tests.Book are selected according to the rea
31、ding skill they will help students improve.For each skill,The database must store an identifier and a description.Some skill have prerequisites.47Database Design.School of softwareComplex Design Example:Summer Reading FunExhibit 5-14:Summer Reading Fun Step-by-Step DesignnProblem StatementThe title
32、of each book and authors first and last names must be stored.Books need to be classified as either fiction or nonfiction.For fiction books,the style must be stored.For nonfiction books,the topic is stored.The skill addressed by each book need to be stored.A book may address several skills.Certain in
33、formation about each students must be tracked,including first and last names,grade,and school.Keep track of what books each student has read.The date on which student completes the book.Track information about which skills each student has gained.48Database Design.School of softwareComplex Design Ex
34、ample:Summer Reading FunExhibit 5-14:Summer Reading Fun Step-by-Step DesignStep 1:Tables-The problem statement refers to five entities:students,books,skills,fiction books and non-fiction books.49Database Design.School of softwareComplex Design Example:Summer Reading FunStep 2:Relationships-Fiction a
35、nd Non-fiction are subtypes of books.-Since there are no other types of books,we have total specialization.-The disjoint rule is in effect since a book cannot be both fiction and non-fiction.-A student has many skills and a skill is related to many students.-A student reads many books and a book is
36、read by many students.-A book can be used to develop many skills and a skill can be developed in many books.Exhibit 5-14:Summer Reading Fun Step-by-Step Design50Database Design.School of softwareComplex Design Example:Summer Reading FunStep 3:Resolve Many-to-Many Relationships-Associative Entities a
37、re created to resolve the many-to-many relationships.-There is also a many-to-many recursive relationship between skills:some skills are related to other skills as prerequisites.-The recursive relationship between skills is resolved with the PREREQ associative entity.Exhibit 5-14:Summer Reading Fun
38、Step-by-Step Design51Database Design.School of softwareComplex Design Example:Summer Reading FunStep 4:Fields-The fields identified in the problem statement are added to the tables.Exhibit 5-14:Summer Reading Fun Step-by-Step Design52Database Design.School of softwareComplex Design Example:Summer Re
39、ading FunStep 5:Keys-Primary keys are created for STUDENT,SKILL,and BOOK.-Primary keys from tables related to associative entities are copied into the associative tables as foreign keys:the pair of foreign keys becomes the primary key of the associative tables.-The primary key in the BOOK supertype
40、table is copied into the subtype tables as foreign keys,which also serve as the primary keys of those tables.Exhibit 5-14:Summer Reading Fun Step-by-Step Design53Database Design.School of softwareComplex Design Example:Summer Reading FunData TypesExhibit 5-15:Summer Reading Fun ER DiagramWith Data T
41、ypes54Database Design.School of softwareComplex Design:Swampland Real EstateExhibit 5-16:Swampland Real Estate Step-by-Step DesignStep 1:Tables-The problem statement refers to seven entities:property,agency,area,outlet,client and the single-family and condo property types.55Database Design.School of
42、 softwareComplex Design:Swampland Real EstateStep 2:Relationships-Clients are related to properties in two ways:as a buyer and as a seller.-Singlefamily and condo are subtypes of books.-Since there are other types of property,we have partial specialization.-The disjoint rule is in effect since a pro
43、perty cannot be both single family and condo.-Clients can also refer other clients to the agency,so there is a recursive relationship between clients.Exhibit 5-16:Swampland Real Estate Step-by-Step Design56Database Design.School of softwareComplex Design:Swampland Real EstateStep 3:Resolve Many-to-M
44、any Relationships-The many-to-many relationship between properties and outlets is resolved by adding the PROPERTYOUTLET associative table.Exhibit 5-16:Swampland Real Estate Step-by-Step Design57Database Design.School of softwareComplex Design:Swampland Real EstateStep 4:Fields-The fields identified
45、in the problem statement are added to the tables.Exhibit 5-16:Swampland Real Estate Step-by-Step Design58Database Design.School of softwareComplex Design:Swampland Real EstateStep 5:Keys-Each one-to-many relationship is represented by duplicating the primary key from the one side of the relationship
46、 into the table on the many side as a foreign key.-The primary keys of the tables linked to the associative are duplicated into the associate table as foreign keys.-These two foreign keys are combined with the ad date to be the primary key of the associative table.-The client recursive relationship
47、is resolved by duplicating the primary key into the same table as a foreign key.-The primary key in the PROPERTY supertype table is copied into the subtype tables as foreign keys,which also serve as the primary keys of those tables.Exhibit 5-16:Swampland Real Estate Step-by-Step Design59Database Des
48、ign.School of softwareComplex Design:Swampland Real EstateExhibit 5-17:Swampland Real Estate ER Diagram Data Types60Database Design.School of softwarePractice Exercise 6:Event Planning SheetExhibit 5-18:The Event Planning Sheet61Database Design.School of softwareExhibit 5-19:A Recipe CardPractice Exercise 6:Recipe Card62Database Design.School of softwarePractice Exercise 6:Second Recipe CardExhibit 5-20:A Second Recipe Card
