Logical Database Design (1 of 3)IntroductionA Database with RedundancyUpdate AnomaliesA Better DesignCardinality Ratio1:1 RelationshipsSlide 8Slide 91:M RelationshipsSlide 111:M Relationship Design ThoughtM:N RelationshipsSlide 14Slide 15Functional Dependencies (FDs)An Example of FDsProperties of FDsWhich FD is satisfied?Closure of FDsCandidate Key & Trivial FDsF+: An Example *Inference Rules for FDsCompute FD Closure F+Additional AxiomsClosure of AttributesLogical Database Design (1 of 3)John OrtizLecture 6 Logical Database Design (1) 2IntroductionThe logical design is a process of refining DB schema based on functional dependencies.Why is a logical design needed?Relations translated from ER may have introduced data redundancy which leads to inconsistencies and update anomalies. What is involved in logical design?Check for normal formsIf necessary, decompose relationsLecture 6 Logical Database Design (1) 3A Database with RedundancyAssume all students of same major are assigned to the same academic advisor.SID Name Major GPA Advisor Office 2011 John CS 3.4 Smith 3345 1235 Carl CS 3.2 Smith 3345 1003 Ken Math 3.5 Johnson 1120 1034 Bill Math 2.5 Johnson 1120 2005 Mary CS 2.9 Smith 3345 2078 Frank Math 4.0 Johnson 1120 1922 Scott Chem 3.45 Ford 2525 Students(FD): major  Advisor, OfficeWhat problems does it have?Lecture 6 Logical Database Design (1) 4Update AnomaliesInsertion anomaly: How to add a new major?Modification anomaly: What would happen if we change office of Smith in the first tuple?Deletion anomaly: What would happen if Scott is deleted?SID Name Major GPA Advisor Office 2011 John CS 3.4 Smith 3345 1235 Carl CS 3.2 Smith 3345 1003 Ken Math 3.5 Johnson 1120 1034 Bill Math 2.5 Johnson 1120 2005 Mary CS 2.9 Smith 3345 2078 Frank Math 4.0 Johnson 1120 1922 Scott Chem 3.45 Ford 2525 StudentsLecture 6 Logical Database Design (1) 5A Better DesignDecompose Students into two relationsMajor Advisor Office CS Smith 3345 Math Johnson 1120 Chem Ford 2525 Major_AdvisorSID Name Major GPA 2011 John CS 3.4 1235 Carl CS 3.2 1003 Ken Math 3.5 1034 Bill Math 2.5 2005 Mary CS 2.9 2078 Frank Math 4.0 1922 Scott Chem 3.45 StudentsDecomposition can remove redundancyIt may also cause problems if not done carefully.Lecture 6 Logical Database Design (1) 6Cardinality RatioThe number of relationship instances that an entity can participate in.There are three: One-to-one (1:1)One-to-Many (1:M)Many-to-many (M:N)Note: “1” may mean either zero or one and “many” may mean zero to the DBMS maximumLecture 6 Logical Database Design (1) 71:1 RelationshipsA one-to-one relationship simply means that for any single instance of one entity, it may participate in zero or one instance in the related entityE.g. Employee manages DepartmentAn employee may or may not be a department manager, however, each department needs one managerEmployee has partial participation, generally a department would have total participationLecture 6 Logical Database Design (1) 81:1 RelationshipsThe foreign key should be placed in the entity with total participationIf both have total participation, the location of the foreign key is arbitraryAdditionally, if both participations are total, the two entities may be mergedWhy would you not want to do this?Access RestrictionsSpecial AttributesReferenced by another entityPerformance in Distributed DatabasesLecture 6 Logical Database Design (1) 91:1 RelationshipsAccess RestrictionsSpecial AttributesReferenced by another entitySalary Med RecsEmp_IDAIRLINE EMPLOYEES11Flying HoursEmp_ID1Pilots only!Confidential!Tool Bag ID ColorEmp_ID Job StartDate Address Tool Bag IDStorage Bin Tool Bag IDMaint only!Lecture 6 Logical Database Design (1) 101:M RelationshipsA one-to-many relationship means that for any single instance of one entity, it may participate in zero or more instances in the related entity, but the related entity may only participate in zero or one instance.Subordinate to supervisor. Each subordinate generally will have one supervisor, but each supervisor usually may have zero or more employees.Not absolute! Sometimes a subordinate may have >1 supervisors – consider this in design!Lecture 6 Logical Database Design (1) 111:M RelationshipsThe foreign key always goes in the “many” part of the relationship!This is counter-intuitive, be careful!The FK must always reference a PK in the related entity or be null. referential integrityWhat results from placing the FK in the “one” part of the relationship?Can part of a PK also be a FK?That coupled with the “existence dependancy” indicates which type of entity?Lecture 6 Logical Database Design (1) 121:M Relationship Design ThoughtConsider the attribute “class” of a STUDENT entity. Given that all possible values are “Freshman”, “Sophomore”, “Junior”, “Senior”, or “Grad Student.” Is there any reason to place it in its own separate entity?Lecture 6 Logical Database Design (1) 13M:N RelationshipsA many-to-many relationship means that for any single instance of one entity, it may participate in zero or more instances in the related entity, and the related entity may participate in zero or more instances as well.Subordinate to supervisor. Consider allowing each subordinate to have zero or more supervisors.Now where does the FK go?Lecture 6 Logical Database Design (1) 14M:N RelationshipsActually, there are two FKs, one for each entity.These two keys are placed in their own entity.Together they make up the PK of that entity.In this case, all FKs must reference a valid PK in the related entity – why?This entity may be called a link table, join table, junction table, or simply many-to-many relationship.Lecture 6 Logical Database Design (1) 15M:N RelationshipsThe link table may contain it’s own attributes.For example, an evaluation by a supervisor.That evaluation may NOT go in the subordinate entity, because there may be others from different supervisors.It can not go in the supervisor entity because there may be more than one subordinate.Only the subordinate/supervisor entities together can uniquely determine that evaluation.Consider course grade vs. G.P.A.Lecture 6 Logical Database Design (1) 16Functional