DATABASE

Database Design Using the E-R Model

Design Phases

• Initial Phase
    - Characterize fully the data needs of the prospective database users.

• Second Phase: Choosing a Data Model
    - Apply the concepts of the chosen data model.
    - Translate the requirements into a conceptual schema of the database.
    - A fully developed conceptual schema indicates the functional requirements of the enterprise.
    - Describe the kinds of operations (or transactions) that will be performed on the data.

• Final Phase: Moving from an Abstract Data Model to Implementation
    - Logical Design: Decide on the database schema.
      - Required to find a “good” collection of relation schemas, which involves:
        1. Business Decision: Determine what attributes should be recorded in the database.
        2. Computer Science Decision: Decide on relation schemas and how attributes should be distributed among them.
    - Physical Design: Decide on the physical layout of the database.

Design Alternatives

• Ensure avoidance of two major pitfalls in database schema design:
    - Redundancy:
      - A bad design may lead to repeat information, which can cause data inconsistency among various copies of information.
    - Incompleteness:
      - A flawed design may create difficulties or impossibility in modeling certain aspects of the enterprise.
• Avoiding bad designs is insufficient; there may be many good designs from which to choose.

Design Approaches

• Entity Relationship Model (E-R Model)
    - Models an enterprise as a collection of entities and relationships.
    - Entity:
      - Defined as a “thing” or “object” in the enterprise that is distinguishable from other objects.
      - Described by a set of attributes.
    - Relationship:
      - An association among several entities, represented diagrammatically by an entity-relationship diagram.
• Normalization Theory (Chapter 7)
    - Formalizes what designs are bad and tests for them.

Entity Sets

• Entity:
    - An object that exists and is distinguishable from others.
    - Example: Specific person, company, event, plant.
• Entity Set:
    - A set of entities of the same type that share the same properties.
    - Example: Set of all persons, companies, trees, holidays.
• Each entity is represented by a set of attributes.
    - Example 1: instructor = (ID, name, salary)
    - Example 2: course = (course_id, title, credits)
• A subset of the attributes forms a primary key, uniquely identifying each member of the set.

Representing Entity Sets in E-R Diagrams

• Graphical Representation:
    - Rectangles represent entity sets.
    - Attributes are listed inside the entity rectangle.
    - Underlining indicates primary key attributes.

Relationship Sets

• Relationship:
    - An association among several entities.
    - Example: advisor relationship between entities.
• Relationship Set:
    - A mathematical relation among n ≥ 2 entities, each drawn from entity sets.
    - Example: (44553, 22222) is a member of the advisor relationship set.
• Visual Representation:
    - Diamonds represent relationship sets in ER diagrams.

Example of Pictorial Representation

• A line drawn between related entities signifies their relationship, for instance:
    - 44553 (Peltier) with advisor 22222 (Einstein).

Attributes Associated with Relationship Sets

• Attributes can also be tied to a relationship set.
    - Example: For the relationship set advisor, an attribute like date can track when the student started being associated with the advisor.

Degree of Relationship Sets

• Most relationship sets in databases are binary:
    - Involves two entity sets (degree two).
    - Example: Students work on research projects guided by instructors.
• Ternary relationships (involving three entity sets) are less common, such as projects involving instructors, students, and projects.

Complex Attributes

• Attribute Types:
    - Simple and composite attributes.
    - Single-valued and multivalued attributes.
    - Example of a multivalued attribute: phone_numbers.
• Derived Attributes:
    - Attributes that can be computed from other attributes, e.g., age from date_of_birth.
• Domain:
    - The set of permitted values for each attribute.

Composite Attributes

• Definition: Composite attributes allow division into subparts (component attributes).
    - Example: name can be divided into first_name, middle_initial, last_name.

Representation of Complex Attributes in ER Diagrams

• In an ER diagram, attributes are depicted hierarchically to show composite structures.

Mapping Cardinality Constraints

• Expresses the number of entities to which another entity can be associated via a relationship set.
• Most useful in binary relationship sets:
    - Types of Mapping Cardinalities:
      - One to one
      - One to many
      - Many to one
      - Many to many

Examples of Mapping Cardinalities

• One-to-One:
    - Association where each entity in set A is related to at most one entity in set B.
• One-to-Many:
    - An association where an entity in set A can relate to multiple entities in set B, while each entity in B connects to at most one entity in A.
• Many-to-One:
    - An entity in A relates to at most one in B, while B can relate to multiple in A.
• Many-to-Many:
    - Entities in A relate to multiple entities in B and vice versa.

Total and Partial Participation

• Total Participation:
    - Indicated by a double line; every entity in the entity set participates in at least one relationship.
    - Example: All students must have an associated instructor.
• Partial Participation:
    - Some entities may not participate in any relationship.
    - Example: Some instructors may not act as advisors for students.

Notation for More Complex Constraints

• A line may show minimum and maximum cardinality in the form l..h, indicating:
    - Minimum value of 1 indicates total participation.
    - Maximum value of 1 indicates at most one relationship.
    - A maximum value of * indicates no limit.

Weak Entity Sets

• Weak Entity Set:
    - Its existence is dependent on another entity (the identifying entity).
    - Uses an identifying entity and additional attributes called a discriminator to uniquely identify it.
• Strong Entity Set:
    - An entity set that isn't weak.
    - Every weak entity is associated with an identifying entity.

Expressing Weak Entity Sets in E-R Diagrams

• A weak entity is depicted with a double rectangle.
• The discriminator of a weak entity is underlined with a dashed line.
• The relationship connecting the weak entity to its identifying entity is shown with a double diamond.

Redundant Attributes

• An example involving entity sets can illustrate redundancy.
    - Example: student (ID, name, tot_cred, dept_name) and department (dept_name, building, budget) where dept_name in student is redundant due to relationship representation.

E-R Diagram for a University Enterprise

• Represents various entities like courses, instructors, and students with their respective attributes and relationships:
    - Entities with attributes such as:
      - Student: ID, name, tot_cred
      - Course: course_id, title, credits
      - Instructor: ID, name, salary
    - Relationship Sets like advisor, stud_dept, teaches, among others.

Representation of Entity Sets with Composite Attributes

• Composite attributes are represented in the schema with separate attributes for each component.
    - Example: instructor(ID, first_name, middle_initial, last_name, street_number, street_name, apt_number, city, state).

Representing Relationship Sets in Schemas

• A many-to-many relationship is represented as a schema with attributes for the primary keys of both entity sets involved and any descriptive attributes.
    - Example: The schema for the relationship set advisor could be modeled as advisor = (s_id, i_id).

Extended E-R Features

• Specialization:
    - A top-down process, creating sub-groupings within an entity set that differ from others.
    - Depicted by a triangle labeled ISA (e.g., instructor “is a” person).
    - Attribute Inheritance: Lower-level entity sets inherit all attributes and relationships from higher-level entity sets.

Specialization Examples

• Overlapping: e.g., employee vs. student.
• Disjoint: e.g., instructor vs. secretary.
• Different types of participation: total vs. partial.

Generalization

• A bottom-up design process that combines multiple entity sets sharing features into a higher-level entity set.
• Specialization and generalization are inverses of each other and depicted similarly in E-R diagrams.

Design Issues

• Address common design issues within database modeling and how they impact database structure.

Summary of Symbols Used in E-R Notation

• A comprehensive guide to symbols depicting various relationships, participation, and attributes in E-R notation.

Alternative E-R Notations

• Overview of different notational systems like Chen, IDE1FX, and others to denote relationships and entities in database design.