Ch5: ER Diagrams - Part 2TopicsCrow’s Foot Cardinality NotationClassification of CardinalitiesOne-to-One (Functional) RelationshipOne-to-Many (Functional) RelationshipMany-to-Many RelationshipSlide 8Existence DependencyOne-to-Many Mandatory Participation (Existence Dependent) RelationshipMany-to-Many Mandatory Participation (Existence Dependent) RelationshipSummary of CardinalitiesUnderstanding RelationshipsCardinality of Non-Binary RelationshipsChallenges with Cardinality of Non-Binary RelationshipsAssociative Entity Types for M-way RelationshipsPractice QuestionStrong and Weak Entity TypesIdentification Dependency in Crow’s Foot NotationSuperclasses and SubclassesWhen to use Generalization Hierarchies?When to use Generalization Hierarchies? Using Attribute InheritanceUsing Attribute InheritanceSlide 24Specialization ExampleUsing Attribute Inheritance: The ResultGeneralization and SpecializationGeneralization ExampleConstraints on Generalization and SpecializationConstraints ExampleConstraints QuestionProblems with ER ModelsModeling TrapsFan TrapFan Trap ExampleChasm TrapsChasm Trap ExampleGood Design PracticesGood Design Practices Avoiding Redundancy ExampleGood Design Practices Entity versus Attribute ExampleGood Design Practices Weak Entity SetsSimplificationsMany-to-Many Relationship SimplificationSimplifying Higher Degree RelationshipsCh5: ER Diagrams - Part 2Much of the material presented in these slides was developed by Dr. Ramon Lawrence at the University of IowaTopicsMin. and Max. CardinalityUnderstanding RelationshipsM-way relationshipsEquivalence between M-N and 1-M relationshipsIdentification dependencyGeneralization HierarchiesModeling GuidelinesCrow’s Foot Cardinality NotationClassification of CardinalitiesMaximum cardinality based1-1 A maximum cardinality of 1 is a functional relationship1-Ma functional relationship in one directionM-NOne-to-One (Functional) RelationshipRelationship explanation: A department may have only one manager. A manager(employee) may manage only one department.One-to-Many (Functional) RelationshipRelationship explanation: A project may be associated with at most one department. A department may have multiple projects.Many-to-Many RelationshipClassification of CardinalitiesMinimum cardinality basedMandatory: existence dependentOptionalExistence DependencyParticipation determines whether all or only some entity instances participate in a relationship.Participation can either be optional or mandatory.If an entity's participation in a relationship is mandatory (also called total participation), then the entity's existence depends on the relationship.Called an existence dependency.One-to-Many Mandatory Participation (Existence Dependent) Relationship Relationship explanation: A project must be associated with one department. A department may have zero or more projects.Many-to-Many Mandatory Participation (Existence Dependent) RelationshipSummary of CardinalitiesUnderstanding RelationshipsM-way relationshipsEquivalence between M-N and 1-M relationshipsIdentification dependencyCardinality of Non-Binary RelationshipsThe cardinality in a complex relationship of an entity type is the number of possible occurrences of that entity-type in the n-ary relationship when the other (n-1) values are fixed.Example: A supplier may provide zero or more parts to a project. A project may have zero or more suppliers, and each supplier may provide to the project zero or more parts.Challenges with Cardinality of Non-Binary RelationshipsThe minimum cardinality for N-ary relationships (i.e., participation) is ambiguous. Example:Constraint: A project has at least 1 supplier per part.Initial idea: Cardinality 1..* beside Supplier should force there to be a Supplier for each Part/Project combination.Problem: Two different interpretations of Part/Project combination results in unforeseen consequences:Actual tuple: All entities must always participate (as have actual tuples) so minimum values for all entities would be 1.Potential tuple: 1 beside Supplier implies always a Supplier for every Part/Project combination. Not true in practice.Bottom line: We will avoid problem of specifying participation constraints for N-ary relationships. One way to avoid it is convert a relationship into an entity with N binary relationships.Associative Entity Types for M-way RelationshipsPartNoPartName PartSuppNoSuppNameSupplierAssociativeentity typeProjNoProjNameProject UsesPart-UsesSupp-UsesProj-UsesPractice QuestionConsider the university database developed before. Write cardinalities into the ER diagram given that:A department must offer at least 2 courses and no more than 20 courses. Courses are offered by only one department.A course may have multiple sections, but always has at least one section.A student may enroll for courses (but does not have to).A professor may be in multiple departments (at least 1), and a department must have at least 3 professors.A section is taught by at least one professor, but may be taught by more than one. A professor does not have to teach.A student may only enroll in a course (and in a single section) once. (Not keeping track of history of student enrollments.)Strong and Weak Entity TypesA strong entity type is an entity type whose existence is not dependent on another entity type.A strong entity type always has a primary key of its own attributes that uniquely identifies its instances.A weak entity type is an entity type whose existence is dependent on another entity type; i.e., it is Identification DependentA weak entity type does not have a set of its own attributes that uniquely identifies its instances.A common example of strong and weak entity types are employees and their dependents:An employee is a strong entity because it has an employee number to identify its instances.A dependent (child) is a weak entity because the database does not store a key for each child, but rather they are identified by the parent's employee number and their name.Identification Dependency in Crow’s Foot NotationBldgIDBldgNameBldgLocationBuildingRoomNoRoomCapacityRoomContainsIdentification Dependency Symbols: Solid relationship line for identifyingrelationships Diagonal lines in the corners denoteweak entities.Partial KeySuperclasses and SubclassesJava code:public class SavingsAccount extends BankAccountUML