Lecture 6

THE ENTITY RELATIONSHIP MODEL (ERM)
ENTITIES
The entity is an object of interest to the end user. Entity correspond to the table – not to a row- in the relational environment.
ATTRIBUTES
Attributes are characteristics of entities .the STUDENT entity includes, among many others, the attributes NAME, FNAME, and INITIAL.
ER can be represented by:
- The Chen notation favors conceptual modeling.
- The Crow s Foot notation favors a more implementation-
Oriented approach.
- The UML notation can be used for both conceptual and implementation modeling.
REQUIRED ATTRIBUTES
Is an attribute that must have a value; in other words, it cannot be left empty. This attributes represented by a boldface in the Crow s Foot notation.
OPTIONAL ATTRIBUTES
Is an attribute that does not require a value; therefore , it can be left empty and those attributes are not presented in boldface in the entity in the Crow s Foot notation as following figure.

DOMAINS
Domain is the set of possible values for a given attribute For example, the domain for grade point average attribute is written (0,4) because the lowest possible GPA value is 0 and the highest possible value is 4.
Attributes may share a domain. For instance, a student address share the same domain of all possible addresses.


Identifiers (Primary Keys)
Identifies, that is, one or more attributes that uniquely identify each entity instance. Such identifiers are mapped to primary keys (PKs) in tables. Identifiers are underlined in the ERD. For example, a CAR entity may be represented by:

CAR(CAR-VIN,MOD_CODE,CAR_YEAR,CAR_COLOR)

Composite Identifiers
That is, a primary key composed of more than one attribute, for instance.
CLASS(CRS-CODE,CLASS-SECTION,CLASS_TIME, ROOM_CODE, PROF_NUM).

Composite Attributes
Is an attribute that can be further subdivided to yield additional attributes. For example, the attribute ADDRESS can be subdivided into street, city, state.

Simple Attribute
Is an attribute that cannot be subdivided. For example, age, sex can be classified as simple attributes.

Single-valued Attributes
Is an attribute that can have only a single value . For example a person can have only one Social Security number. Keep in mind that a single-valued attribute is not necessarily a simple attribute. For instance, a part s serial number, such as SE-08-02-189935 is a single-valued but it is a composite attribute because it can be subdivided into the region .

Multivalued Attributes
Are attributes that can have many values. For instance, a person may have several college degrees, and a household may have several different phones, each with its own number. a car s color may be subdivided into many colors. The following figure show the ERD for car s color.

Implementing Multivalued Attributes
If mulivalued attributes exist ,the designer must decide on one of
two possible courses of action:
1. Within the original entity, create several new attributes, one for each of the original multivalued attribute s components as figure below. Although this solution seems to work, its adoption can lead to major structural problems in the table. For example, if additional color components-such as logo color- are added for some cars, the table structure must be modified to accommodate the new color section.

2. Create a new entity composed of the original multivalued attribute s component as in figure bellow .The new (independent)CAR_COLOR entity is then related to the original CAR entity in a 1:M relationship. This way yields several benefits: it s a more flexible, expandable solution, and it is compatible with the relational model.

Derived Attributes
An attribute may be classified as derived attribute is an attribute whose value is calculated (derived) from other attributes. The derived attribute not need to be physically stored within the database; instead, it can be derived by using algorithm. For example an employee s age, EMP_AGE, may be found by computing the integer value of the difference between the current date and the EMP_DOB. A derived attribute is indicated in the Chen notation by dashed line connecting the attribute and the entity, as follows figure

Derived attributes are some times referred to as computed attributes
The following table showes the advantages and disadvantages of storing(or not storing) derived attributes in the database.

stored Not stored
Advantage Saves CPU processing cycles
Save data access time
Data value is readily available
Can be used to keep track of historical data Saves storage space
Computation always yields current value
Disadvantage Requires constant maintenance to ensure
Derived value is current, especially if any values used in the calculation change Uses CPU processing cycles
Increases data access time
Adds coding complexity to queries


RELATIONSHIPS
A relationship is an association between entities. The relationship name is an active or passive verb; for example, STUDENT takes a CLASS, PROFESSOR teaches a CLASS. Relationships between entities always operate in both directions.
Connectivity and Cardinality
The term connectivity is used to describe the relationship classification.
Cardinality expresses the minimum and maximum number of entity occurrences associated with one occurrence of the related entity in the ERD, cardinality is indicated by placing the appropriate numbers beside the entities, using the format(x, y).The first value represents the minimum number of associated entities, while the second value represents the maximum number of associated entities as in following figure



EXISTANCE DEPENDENCE
An entity is said to be existence –dependent if it can exist in the database only when it is associated with another related entity occurrence.
In implementation terms, an entity is existence –dependent if it has a mandatory foreign key _that is, a foreign key attribute that can not be null.
If an entity can exist a part from one or more related entities, it is said to be existence_ independent .(some times designers refer to such an entity as a strong or regular entity) for example, suppose that the XYZ corporation uses parts to produce its products. Farther, suppose that some of those parts are produced in _house and other parts are bought from vendors. In that scenario , it is quite possible for a PART to exist independently from a VENDOR in the relationship "PART is supplied by VENDOR ",because at least some of the parts are not supplied by a vendor . Therefore , PART is existence _independent from VENDOR


RELATIONSHIP STRENGTH
The concept of relationship strength is based on how the primary key of a related entity is defined.
Weak (non-identifying ) relationships
A weak relationship also known as a non- identifying relationship, exist if the PK of the related entity does not contain a PK component of the parent entity . by default ,relationships are established by having the PK of the parents entity appear as an FK on the related entity. for example, suppose that COURSCE and CLASS entities are defined as
COURSE(CRS-CODE,DEPT_CODE,CRS_DESCRIPTION, CRS_CREDIT).
CLASS(CLASS-CODE,CRS_CODE,CLASS_SECTION, CLASS_TIME,ROOM_CODE, PROF_NAME).
In this case, a weak relationship exist between COURSE and CLASS because the CLASS_CODE is the CLASS entity s PK, while the CRS_CODE in CLASS is only an FK .
In this example, the CLASS PK did not inherit the PK component from the COURSE entity as figure bellow.

Strong (identifying) relationships
A strong relationship, also known as an identifying relationship, exists when the PK of the related entity contains a PK component of the parent entity. For example, the definitions of the COURSE and CLASS entities
COURSE(CRS-CODE,DEPT_CODE,CRS_DESCRIPTION, CRS_CERDIT)
CLASS(CRS-CODE,CLASS-SECTION,CIASS_TIME, ROOM_CODE, PROF_NUM)
Indicate that a strong relationship exists between COURSE and CLASS, because the CLASS entity s composite PK is composed of CRS_CODE + CLASS_SECTION. (Note that the CRS_CODE in CLASS is also the FK to the COURSE entity). As in figure bellow:

Weak Entities
A weak entity is one that meets two conditions:
1- The entity is existence dependent; that is, it cannot exist without the entity with which it has a relationship.
2- The entity has a primary key that is partially or totally derived from the parent entity in the relationship.
The figure bellow explain that weak entity

Relationship participation
Participation in an entity relationship is either optional or mandatory.
Optional participation means that one entity occurrence does not require a corresponding entity occurrence in a particular relationship. for example, in the "COURSE generates CLASS " relationship, you noted that at last some courses do not generate a class.
Existence of an optional entity indicates that the minimum cardinality is 0 for the optional entity, as illustrated in figure

Mandatory participation means that one entity occurrence requires a corresponding entity occurrence in a particular relationship. The existence of a mandatory relationship indicates that the minimum cardinality is 1 for the mandatory entity.
It is important to understand that the semantics of a problem might determined the type of participation in a relationship for example, suppose that tiny college offers several courses; each course has several classes.
Analyzing the CLASS in the entity s contribution to the" COURSE generates CLASS" relationship, it is easy to see that a CLASS can not exist without a COURSE therefore you can conclude that the COURSE entity is mandatory in the relationship. but two scenarios for the CLASS entity may be written, shown in figure bellow the different scenarios are a function of the semantics of the problem; that is they depend on how the relationship defined.
1- CLASS is optional. it is possible for the department to create the entity COURSE first and then create the CLASS entity after making the teaching assignments. In the real world. Such a scenario is very likely ; there may be courses for which sections (classes) have not yet been defined .In fact some courses are taught only one a year and do not generate classes each semester.

2- CLASS is mandatory this condition is created by the constraint that is imposed by the semantics of the statement "Each COURSE generates one or more CLASSess." In ER terms, each COURSE in the "generates" relationship must have at last one class. therefore , a CLASS must be created as the COURSE is created in order to comply with the semantics of the problem .

The table below shows the various cardinalities that are supported by the Crow s Foot notation

Relationship degree
A relationship degree indicates the number of entities or participants
Associated with a relationship.
Unary relationships
Exist when association is maintained within a single entity .An employee within the EMPLOYEE entity is the manager for one or more employees within that entity .

Binary relationships
A binary relationships exist when two entities are associated in a relationship .binary relationships are most common .

Ternary and Higher _Degree Relationships
Exist when three entities associated, for example ,note the relationships (and their consequences) in figure
. A DOCTOR writes one or more Prescriptions.
. A PATIENT may receive one or more PRESCRIPTIONS.
. A DRUG may appear in one or more PRESCRIPTIONS.

RECURSIVE RELATIONSHIP
A recursive relationship is one in which a relationship can exist between occurrences of the same entity set.(naturally ,such a condition is found within( a unary relationship) .
For example ,a 1:M unary relationship can be expressed by "an EMPLOYEE may manage many EMPLOYEES, and each EMPLOYEE is managed by one EMPLOYEE " and as long as polygamy is not legal, a 1:1 unary relationship may be expressed by "an EMPLOYEE may be married to one and only one other EMPLOYEE" finally the M:N unary relationship may be expressed by " A COURES may be a prerequisite to many other COURSES, and each COURSE may have many other COURSES as prerequisites " as shown in figure.

ASSOCIATIVE (COMPOSITE)ENTITIES
Associative entity (also known as a composite or bridge entity) is composed of the primary keys of each of the entities be connected.
In the following figure note that

A class may exist (at least at the start of registration)even though it contains no students. Therefore an optional symbol should appear on the STUDENT side of the M:N relationship between STUDENT and CLASS.

To be classified as a STUDENT, a person must be enrolled in at least one CLASS. therefore , CLASS is mandatory to STUDENT from a purely point of view ,however when a student is admitted to college, that student has not (yet) signed up for any classes. therefore, at least initially ,CLASS is optional to STUDENT.
Because the M:N relationship between STUDENT and CLASS is decomposed into two 1:M relationships through ENROLL, the optionalities must be transferred to ENROLL.

ER diagram
The process of data design is an iterative rather than linear or sequential process. The verb iterate means "to do again or repeatedly. "an iterative process is, thus, one based on repetition of processes and procedures. The following diagram explain that