Database Design and Administration: ER Model

Database Design and Administration

• The database design process consists of six steps:
  • Requirement Analysis
  • Conceptual Database Design (ER)
  • Logical Database Design (Relational)
  • Schema Refinement
  • Physical Database Design
  • Application and Security Design

Entity-Relationship Model

• The Entity-Relationship (ER) model is a popular conceptual data model used in database design.
• It describes the data to be stored and the constraints over the data.

Entities and Attributes

• An entity is an object in the real world that is distinguishable from other objects.
• An entity is described using a set of attributes whose values are used to distinguish one entity from another of the same type.
• An entity set is a collection of entities of the same type.
• All entities in a given entity set have the same attributes, but the values may differ.
• Attribute domain refers to the set of possible values for an attribute.

ER Diagrams

• ER diagrams can be used to present the ER model graphically.

Key

• A candidate key is a minimal set of attributes whose values uniquely identify an entity in the set.
• There can be more than one candidate key.
• The primary key should be chosen based on real-life possibilities rather than the current set of data.

Relationships

• A relationship is an association among two or more entities.
• A relationship can be represented by an ER diagram.
• A relationship set can be seen as a set of n-tuples.

Ternary Relationships

• A ternary relationship is a relationship between three entities.
• It can be represented by an ER diagram.

Recursive Relationships

• A recursive relationship is a relationship between two entities in the same entity set.

Key Constraints (Mapping Constraints)

• The mapping of a binary relationship can be classified into four cases:
  • One-to-one
  • One-to-many
  • Many-to-one
  • Many-to-many

One-to-One Relationship

• In a one-to-one relationship, an entity in A is related to at most one entity in B, and an entity in B is related to at most one entity in A.

One-to-Many Relationship

• In a one-to-many relationship, an entity in B can be associated with at most one entity in A, and an entity in A can be associated with one or more than one entity in B.### Key Constraints

• A child has a key constraint in a mother-of relationship set, indicated by an arrow in the E-R diagram.

• Intuitively, the arrow means that given a child entity, we can uniquely determine the mother-of relationship.

One-to-Many (1:N)

• In a one-to-many relationship, an entity in A is associated with one or more entities in B.
• Example: a mother has multiple children, but a child has only one mother.

Many-to-One (N:1)

• Similar to one-to-many, but an entity in A is associated with one entity in B.
• Example: an employee works in one department, but a department can have multiple employees.

Many-to-Many (M:N)

• An entity in A is associated with any number of entities in B, and an entity in B is associated with any number of entities in A.
• No restriction in the mapping, e.g., a customer can have multiple loans, and a loan can be associated with multiple customers.

Participation Constraint

• Imposes requirements on whether an entity participates in a relationship.
• Total Participation: each entity in the entity set must participate in at least one relationship.
• Partial Participation: an entity in the entity set may not participate in a relationship.

Participation Constraint Examples

• Every department is required to have a manager (total participation).
• Every employee must work for some department (total participation).
• Some employees may not manage departments (partial participation).

Entities

• There are two kinds of entities: strong and weak.
• Strong entity: has a primary key and can be distinguished from other entities in the same set.
• Weak entity: lacks a primary key and may not be distinguished from others without associations with entities in other sets.

Weak Entity Example

• Dependent entity set: lacks a primary key, and pname does not identify a dependent uniquely.
• The primary key of the employee (identifying owner) is required to identify a dependent entity.
• A partial key is a set of attributes that uniquely identify a weak entity for a given owner entity.

Class Hierarchies

• Allow classification of entities in an entity set into subclasses.
• Attributes are inherited by the entity set in the subclass.
• A class hierarchy can be viewed as a specialization or generalization.
• Two kinds of constraints can be specified with respect to ISA hierarchies: overlap constraints and covering constraints.

ISA Hierarchies Constraints

• Overlap constraints: determine whether two subclasses are allowed to contain the same entity.
• Covering constraints: determine whether the entities in the subclasses collectively include all entities in the superclass.