Database Design Semester I Quiz

Database Design

• Semester I - Academic Year 2024-5
• Instructor: Denise Allen
• Email: [email protected]/[email protected]

Topics Covered

• Recap of Specialization/Generalization
• Design issues and resolution methods

Specialization/Generalization

• An entity set can contain subgroups of entities that differ in some way from other entities in the set.
• An entity set can have attributes not common to all entities.
• Example: A Person entity can be further specialized to Customer or Employee. This would entail the addition of specific attributes for Customer (Credit Rating) and Employee (Salary). The general entity Person includes attributes like Name, Street, City.
• Denoted by ISA.
• Generalization and Specialization are denoted by ISA relationship.
• Specialization takes the attributes of the general class, as well as its own specific attributes.

Generalization

• Common attributes exist between similar entities and are represented by a higher-level entity.
• The general entity is the super-entity, while the specialized entity is the sub-entity.
• Example: Person is a generalization of Employee and Customer.

Generalization/Specialization Constraints

• Attribute Inheritance: Attributes (e.g., ID, Name, DOB, Email) assigned to the general entity are also inherited by specialized entities (e.g., Employee, Customer).
• Participation Inheritance: Relationships like "borrows" are also inherited (e.g., Person borrows Book, and by extension, Employee and Customer).
• Overlapping Generalization/Specialization: A sub-entity can be of multiple types (e.g., a student can also be a teacher). No symbol for overlap is explicitly shown, and it is implied by the absence of the word "disjoint".
• Disjoint Generalization/Specialization: Sub-entities must be one type or another (e.g., a student cannot be a teacher too). Denoted by a symbol (disjoint) in the diagram.
• Total Generalization/Specialization: Every high-level entity must be associated with a lower-level entity set. This is indicated by a double line on the ISA triangle.
• Partial Generalization/Specialization: A high-level entity does not have to be associated with a lower-level entity. This is indicated by a single line on the ISA triangle.
• Complete vs Disjoint: Completeness and disjointness are independent. Specializations in the database can be: partial-overlapping, partial-disjoint, total-overlapping, total-disjoint,

Entity Set vs Relation Set

• Determining whether an object should be treated as an entity or a relationship does not always have a clear answer.
• Relationships occur between entities

Binary Vs. Non-Binary Relationships

• Relationships that seem non-binary (such as a child's father and mother) can be represented better using binary relationships.
• A ternary relation can be represented using binary relationships by introducing an artificial entity.

Placement of Relationship Attributes

• The cardinality ratio determines the placement of relationship attributes in the entity diagram.
• One-to-many (1:M): attributes reside on the many side.
• One-to-one (1:1): attributes on either side are possible.
• Many-to-many: Attributes reside on the entity created for the relationship set.

ER Design Methodologies

• A structured approach to designing ER diagrams:
  1. Identify entity sets.
  2. Identify attributes for each entity set.
  3. Identify relationships, including participating entity sets.
  4. Define binary relationship types.
  5. Define cardinality, participation, and constraints.
  6. Determine weak entity sets.
  7. Design the diagram.

Use of Entity Sets vs Attributes

• A phone can be represented as an entity or an attribute, depending on the need to capture further details about the phone (e.g., its type, location, other individuals sharing the phone)
• Attributes are better for scenarios where an entity only needs properties but not independent details.

Important Design Considerations:

• Avoid redundancy by utilizing relationships rather than duplicating attributes.
• Understanding completeness and disjointness constraints.
• Understanding entity set vs relation set distinctions.