CONCEPTUAL DESIGN AND ER MODEL

Chapter outline

1. What is an ER model?
2.  Phases of database design
3.  Entities, entity sets, attributes, and keys
4.  Components of ER model
5.  Relationships, relationship sets, and structural constraints
6.  Weak entities
7.  Enhanced Entity-Relationship model (EER model)
8.  Conceptual design with the ER model

What is an ER model?

1. The entity-relationship data model, a popular high-level conceptual data model, allows us to describe the data involved in a real-world enterprise in terms of objects (entities) and their relationships and is widely used to develop an initial database design.

Phases of database design
1. Requirement analysis      database requirement
2. Conceptual database design                  ER model  
- The goal of this phase is to create a simple description of the data that closely matches how users and developer think of the data
3. Logical database design        database schema
4. Schema refinement     normalized relation
5. Physical database design
6. Application and security design

Type of attributes

1. Composite versus simple (atomic) attributes.

                Simple attribute is an attribute composed of a single component with an independent existence.
Composite attribute is an attribute composed of multiple component, each with an independent existence.    

2. Single-valued versus multivalued attributes.

                Single-valued attribute is an attribute that holds a single value for a single entity.
Multivalued attribute is an attribute that holds multiple values for a single entity.

3. Stored versus derived attributes

Derived attribute is an attribute that represents a value that is derivable from the value of a related attribute or set of attributes, not necessarily in the same entity.

4. Null values (Null attributes)

                In some case, a particular entity may not have an applicable value for an attribute

Keys
1. Superkey is a set of one or more attributes that, taken collectively, allows us to identify uniquely an entity in the entity set.
2. Candidate key is a minimal set of attributes whose values uniquely identify an entity in the set.
3. Primary key is the candidate key selected to be primary key.
4. Composite key is a candidate key that consists of two or more attributes.  (combined key)

Relationships, relationship sets,  and structural constraints

1. A relationship is an association among two or more entities.
2. A relationship set is a set of similar relationships.
3. Degree of relationship is the number of participating entities in a relationship : unary, binary, ternary, quarternary, n-ary

Binary relationship

A relationship can have descriptive attributes. Ex since attribute.
Descriptive attribute is used to record information about the relationship, rather than about any one of the participating entities.

Ternary relationship

Recursive relationship
 (Unary relationship)
                Recursive relationship (Unary relationship) is a relationship where the same entity participates more than once in different roles
Structural constraints
1. Cardinality constraints
Cardinality ratio describes the number of possible relationships for each participating entity.
The cardinality ratio between entities is a function of the policies established by an enterprise.
- 1:1, 1:N, M:N

1. 1:1 - an entity in A is associated with at most one entity in B, and vice versa. Ex
2. 1:N - an entity in A is associated with any number of entities in B. An entity in B, however, can be associated with at most one entity in A. Ex
3. M:N - an entity in A is associated with any number of entities in B, and vice versa. Ex

2. Participation constraints
Participation constraint determines whether the existence of an entity depends upon it being related to another entity through the relationship.
- Total participation
- Partial participation

Weak entities

1. A strong entity set is an entity set that has a primary key.
2. A weak entity set is an entity set that does not have a primary key. It can be identified uniquely only by considering some of its attributes conjunction with the primary key of another entity, which is called the identifying owner. Ex
3. The following restrictions must hold:     

                - 1:N. The relationship set is called the identifying relationship set of the weak entity set
- The weak entity set must have total participation in the identifying relationship set

Enhanced Entity-Relationship model (EER model)

1. Class hierarchies

1. Superclass is an entity set that includes distinct subclasses that require to be represented in a data model
2. Subclass is an entity set that has a distinct role and is also a member of superclass.
3. Sometimes, it is natural to classify the entities in an entity set into subclasses because these subclasses have some different attributes from the superclass.

Class hierarchies

1. Specialization is the process of identifying subsets of an entity set (the superclass) that share some distinguishing characteristics. (top-down)
2. Generalization consists of identifying some common characteristics of a collection of entity sets and creating a new entity set that contain entities possessing these common characteristics. (bottom-up)
3. Constraints on ISA hierarchies

                - 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.

1. Two reasons for identifying subclasses

                - want to add descriptive attributes for entities in subclasses.
- want to identify the set of entities that participate in some relationship.

Enhanced Entity-Relationship model (EER model)
Aggregation

1. Limitation of ER model is that it is not possible to express relationships among relationships.
2. Aggregation is an abstraction through which relationships are treated as higher-level entities. (Use a dash box)
3. Ex A department can sponsor several projects.

                    A project can be sponsored by several departments.
A department that sponsors a project might assign employees to monitor the sponsorship.

Conceptual design with the ER model

• Entity versus attribute ex address, telno, works_in (from/to)
• Entity versus relationship ex manages (since, dbudget)
• Binary versus ternary relationship ex  employees/dependents/policies and covers
• Aggregation versus ternary relationship ex sponsors/monitors
• Strong entity or weak entity
• ISA hierarchy