Lecture 4 - Conceptual Modelling Summarized PDF

DATABASE DESIGN Semester I – Academic Year 2024-5 Denise Allen Email: [email protected]/[email protected] TOPICS THAT WILL BE COVERED ○ Specialization / Generalization (Recap) ○ Design Issues and how to resolve them Generalization / Specialization SPECIALIZATION Definition: The process...

DATABASE DESIGN Semester I – Academic Year 2024-5 Denise Allen Email: [email protected]/[email protected] TOPICS THAT WILL BE COVERED ○ Specialization / Generalization (Recap) ○ Design Issues and how to resolve them Generalization / Specialization SPECIALIZATION Definition: The process of designating sub-groupings within an entity set GENERALIZATION / SPECIALIZATION ○ An entity set may include sub-groupings of entities that are distinct in some way from other entities in the set ○ An entity set may have attributes that are not shared by all the entities in the entity set EXAMPLE: GENERALIZATION / SPECIALIZATION ○ Person (General) - name, street, city ○ A person may be further classified as: Customer – Credit Rating Employee – Salary ○ Denoted by: NB – Specializations take the attributes of the general class as well as their own specific attributes. GENERALIZATION / SPECIALIZATION GENERALIZATION ○ This is a relationship where common attributes exist between similar entities and are represented by a higher level entity ○ The general entity can be considered the super- entity while the specialized entity can be considered the sub-entity. ○ For example Person would be considered a generalization of employee and customer GENERALIZATION / SPECIALIZATION ○ Attribute Inheritance ○ Participation Inheritance ○ Generalization Constraints Overlapping Disjoint Total Generalization Partial Generalization GENERALIZATION / SPECIALIZATION ID Author Name Book_id DOB Title Person Borrow Book Email IS A Employee Customer Salary Credit Rating Attribute Inheritance ○ Person ( ID, Name, DOB, Email) It Also means that Employee and Customer both will get ID, Name, DOB, Email when implementation comes around. Participation Inheritance ○ Person borrows Book It Also means that Employee and Customer can borrow a book. GENERALIZATION CONSTRAINTS ○ Overlapping generalization / specialization ○ This means sub entity A can also be of type sub entity B at the same time. ○ Denoted by (the absense of the word disjoint): ○ Example - A Student can be a Teacher as well. GENERALIZATION CONSTRAINTS ○ Disjoint Generalization / Specialization ○ This means that sub entities must be one or the other. Can not be both. ○ Denoted by ○ Example: A student can only be a student. A teacher can only be a teacher. No Student can be a teacher. GENERALIZATION / SPECIALIZATION ID Author Name Book_id DOB Title Person Borrow Book Email Disjoint IS A Employee Customer Salary Credit Rating GENERALIZATION CONSTRAINTS ○ Partial Generalization ○ This means a high-level entity can exist by itself and does not have to be a specialized entity ○ Denoted by a single line on top of the “IS A” triangle GENERALIZATION CONSTRAINTS ○ Total Generalization / Specialization ○ This means a high-level entity must also be a specialized entity ○ Denoted by: ○ Notice a double line on top of the “IS A” triangle GENERALIZATION / SPECIALIZATION ID Author Name Book_id DOB Title Person Borrow Book Email IS A Employee Customer Salary Credit Rating Complete vs Disjoint ○ Completeness and Disjointness DO NOT depend on each other. ○ Therefore specializations may be: partial-overlapping; partial-disjoint; total-overlapping or total-disjoint. Complete vs Disjoint ○ when a total completeness constraint is in place, an entity inserted into a higher-level entity set must also be inserted into at least one of the lower-level entity sets. ○ An entity that is deleted from a higher-level entity set must also be deleted from all the associated lower-level entity sets to which it belongs. REVIEW Specialisation and Generalisation Specialisation and generalisation define a containment relationship between a higher-level entity set and one of more lower-level entity sets. REVIEW Specialisation is the result of taking subsets of a higher-level entity set to form a lower-level entity set. Generalization is the result of taking the union of two or more disjoint (lower-level) entity sets to produce a higher-level entity sets Inheritance - The attributes of higher-level entity sets are inherited by lower-level entity sets. REVIEW - AGGREGATION AGGREGATION this is an abstraction in which relationships sets (along wit their associated entity sets) are treated as higher level entity sets, and can participate in relationships with other entities. DESIGN ISSUES Removing Redundant Attributes in Entity Sets ○ the use of the unique identifier of one entity set as an attribute of another entity set, instead of using a relationship. Removing Redundant Attributes in Entity Sets For example, in our university E-R model, it is incorrect to have deptName as an attribute of student even though it may be presented as an attribute in the scenario for student. Having an attribute deptName as well as a relationship assigned would result in duplication of information. Removing Redundant Attributes in Entity Sets CORRECT Removing Redundant Attributes ○ Another related mistake that students sometimes make is to designate the ‘primary-key’ (unique identifier) attributes of the related entity sets as attributes of the relationship set. INCORRECT Removing Redundant Attributes For example, ○ ID (the unique identifier of student) and Code (the unique identifier of Course) should not appear as attributes of the relationship enrolls. This should not be done since the ‘unique identifier’ attributes are already implicit in the relationship set. USE OF ENTITY SETS VS ATTRIBUTES ○ It can be argued that a phone is an entity in its own right with attributes phone number and location. ○ the location may be the office or home where the phone is located, with this point of view, we do not add the attribute phone number to the Patient. USE OF ENTITY SETS VS ATTRIBUTES ○ Rather, we create: A phone entity set with attributes phone number and location. A relationship set pat_phone, denoting the association between patients and the phones that they have.  USE OF ENTITY SETS VS ATTRIBUTES ○ Treating a phone as an attribute phoneNumber implies that patients have precisely one phone number each. Treating a phone as an entity Phone permits patients to have several phone numbers (including zero) associated with them. ○ However, we could instead easily define phone number as a multivalued attribute to allow multiple phones per patient. USE OF ENTITY SETS VS ATTRIBUTES ○ BUT MISS…… couldn’t we instead define phone number as a multivalued attribute to allow multiple phones per patient.  USE OF ENTITY SETS VS ATTRIBUTES ○ Treating a phone as an entity better models a situation where one may want to keep extra information about a phone, such as its location, or its type (mobile, IP phone, or plain old phone), or all who share the phone. ○ Thus, treating phone as an entity is more general than treating it as an attribute and is appropriate when the generality may be useful. What constitutes an attribute and what constitutes an entity set? Entity Set vs relationship set ○ It is not always clear whether an object is best expressed as an entity or a relationship set. ○ Possible guideline is to designate a relationship set to describe an action that occurs between entities.  Binary Vs. Non-Binary Relationships ○ Some relationships that appear to be non-binary may be better represented using binary relationships. E.g. A ternary relationship parents, relating a child to his/her father and mother, is best replaced by two binary relationships, father and mother ○ Using two binary relationships allows partial information (e.g. only mother being known) ○ 􏰁 􏰂 Converting Binary Vs. Non-Binary Relationships ○ In general, any non-binary relationship (n-ary, for n > 2) can be represented using binary relationships by creating an artificial entity set E. ○ Replace R between entity sets A, B and C by an entity set E, and three relationship sets: RA, relating E and A (m:1 relationship set from E to A) RB, relating E and B (m:1 relationship set from E to B) RC, relating E and C (m:1 relationship set from E to C)  Converting Binary Vs. Non-Binary Relationships ○ Create a special identifying attribute for E ○ Add any attributes of R to E 􏰁 Placement of Relationship Attributes ○ The cardinality ratio affects the placement of relationship attributes. One-to-many: attributes of a 1:M relationship can be placed only on the entity set of the many side of the relationship. One-to-one: attribute of the ‘relationship’ can be associated with either one of the entity sets. Many-to-Many: the relationship attributes can only be placed on the relationship set. They will be a part of the entity set to be created for the relationship set. ER Design Methodologies 1. Identify all entity sets 2. Identify all attributes (different types) belonging to each entity set 3. Identify relationships and participating entity sets 4. Identify relationship sets and attributes of relationship sets. 5. Define binary relationship types 6. Define cardinality, participation constraints, discriminators (determining weak entities). 7. Design the diagram Summary of Symbols Used in ER Notation THE END