Easy Access assignment need help review the requirement

129189

9780538469685_ppt_ch04formi375summer14_1.ppt

Home >Information Systems homework help >Easy Access assignment need help review the requirement

Database Systems:
Design, Implementation, and Management
Ninth Edition

Chapter 4

Entity Relationship (ER) Modeling

Database Systems, 9th Edition

Objectives

In this chapter, students will learn:
The main characteristics of entity relationship components
How relationships between entities are defined, refined, and incorporated into the database design process
How ERD components affect database design and implementation

Database Systems, 9th Edition

The Entity Relationship Model (ERM)

ER model forms the basis of an ER diagram
ERD represents conceptual database as viewed by end user
ERDs depict database’s main components:
Entities
Attributes
Relationships

Database Systems, 9th Edition

Entities

Refers to entity set and not to single entity occurrence
Corresponds to table and not to row in relational environment
In Chen and Crow’s Foot models, entity is represented by rectangle with entity’s name
Entity name, a noun, written in capital letters

Database Systems, 9th Edition

Attributes

Characteristics of entities
Chen notation: attributes represented by ovals connected to entity rectangle with a line
Each oval contains the name of attribute it represents
Crow’s Foot notation: attributes written in attribute box below entity rectangle

Database Systems, 9th Edition

Attributes (cont’d.)

Required attribute: must have a value
Optional attribute: may be left empty
Domain: set of possible values for an attribute
Attributes may share a domain
Identifiers: one or more attributes that uniquely identify each entity instance
Composite identifier: primary key composed of more than one attribute

Database Systems, 9th Edition

Attributes (cont’d.)

Composite attribute can be subdivided
Simple attribute cannot be subdivided
Single-value attribute can have only a single value
Multivalued attributes can have many values

Database Systems, 9th Edition

Attributes (cont’d.)

M:N relationships and multivalued attributes should not be implemented
Create several new attributes for each of the original multivalued attributes’ components
Create new entity composed of original multivalued attributes’ components
Derived attribute: value may be calculated from other attributes
Need not be physically stored within database

Database Systems, 9th Edition

Relationships

Association between entities
Participants are entities that participate in a relationship
Relationships between entities always operate in both directions
Relationship can be classified as 1:M
Relationship classification is difficult to establish if only one side of the relationship is known

Database Systems, 9th Edition

Connectivity and Cardinality

Connectivity
Describes the relationship classification
Cardinality
Expresses minimum and maximum number of entity occurrences associated with one occurrence of related entity
Established by very concise statements known as business rules

Database Systems, 9th Edition

Existence Dependence

Existence dependence
Entity exists in database only when it is associated with another related entity occurrence
Existence independence
Entity can exist apart from one or more related entities
Sometimes such an entity is referred to as a strong or regular entity

Database Systems, 9th Edition

Relationship Strength

Weak (non-identifying) relationships
Exists if PK of related entity does not contain PK component of parent entity
Strong (identifying) relationships
Exists when PK of related entity contains PK component of parent entity

Database Systems, 9th Edition

Fig 4.8 shows how the Crow’s Foot notation depicts a week relationship by placing a dashed relationship line between the entities. The tables shown below the ERD illustrate how such a relationship is implemented.

Database Systems, 9th Edition

Figure 4.9 depicts the strong (identifying) relationship with a solid line between the entities. Whether the relationship between COURSE and CLASS is strong or weak depends on how the CLASS entity primary key is defined.

Database Systems, 9th Edition

Weak Entities

Weak entity meets two conditions
Existence-dependent
Primary key partially or totally derived from parent entity in relationship
Database designer determines whether an entity is weak based on business rules

Database Systems, 9th Edition

Relationship Participation

Optional participation
One entity occurrence does not require corresponding entity occurrence in particular relationship
Mandatory participation
One entity occurrence requires corresponding entity occurrence in particular relationship

Database Systems, 9th Edition

The different scenarios are a function of the semantics of the problem; that is, they depend on how the relationship is defined.

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 once a year and do not generate classes each semester.

Database Systems, 9th Edition

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 CLASSes." In ER terms, each COURSE in the "generates" relationship must have at least one CLASS. Therefore, a CLASS must be created as the COURSE is created, in order to comply with the semantics of the problem.

Database Systems, 9th Edition

Associative (Composite) Entities

Also known as bridge entities
Used to implement M:N relationships
Composed of primary keys of each of the entities to be connected
May also contain additional attributes that play no role in connective process

Database Systems, 9th Edition

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

You might argue ta that to be classified as a STUDENT, a person must be enrolled in at least one CLASS.

Therefore, CLASS is mandatory to 0 STUDENT from a purely conceptual 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.

Database Systems, 9th Edition

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. (See Figure 4.25.) In other words, it now becomes possible for a class not to occur in ENROLL if no student has as signed up for that class. Because a class need not occur in ENROLL, the ENROLL entity becomes optional to CLASS. And because the ENROLL entity is created before any students have signed up for a class, the ENROLL entity is also optional to STUDENT, at least initially.

Database Systems, 9th Edition

Developing an ER Diagram

Database design is an iterative process
Create detailed narrative of organization’s description of operations
Identify business rules based on description of operations
Identify main entities and relationships from business rules
Develop initial ERD
Identify attributes and primary keys that adequately describe entities
Revise and review ERD

Database Systems, 9th Edition

Tiny college is divided into several schools: business, arts and sciences, education, and applied sciences. Each school is administered by a dean who is a professor. Each professor can be the dean of only one school, and a professor is not required to be the dean of any school. Therefore, a 1:1 relationship exists between professor ands school.

Note that the cardinality can be expressed by writing (1,1) next to the entity PROFESSOR and (0,1) next to the entity SCHOOL.

Each school comprises several departments. Note again the cardinality rules: The smallest number of departments operated by a school is one, and the largest number of department is indeterminate (N). On the other hand, each department belongs to only a single school; thus, the cardinality is expressed by (1,1).

Database Systems, 9th Edition

Each department may offer courses. Tiny college had some departments that were classified as “research only”. Those departments would not offer courses. Therefore, the course entity would be optional to the department entity.

Database Systems, 9th Edition

1:M relationship exists between COURSE and CLASS. However, because a course may exist in Tiny College's course catalog even when it is not offered as a class in a current class schedule, CLASS is

optional to COURSE. Therefore, the relationship between COURSE and CLASS looks like Figure 4.28.

Database Systems, 9th Edition

Each department should have one or more professors assigned to it. One and only one of those professors chairs the department, and no professor is required to accept the chair position. Therefore, DEPARTMENT is optional to PROFESSOR in the "chairs" relationship. Those relationships are summarized in the ER segment shown in Figure 4.29.

Database Systems, 9th Edition

Each professor may teach up to four classes; each class is a section of a course. A professor may also be on a research contract and teach no classes at all. The ERD segment in Figure 4.30 depicts those conditions.

Database Systems, 9th Edition

This M:N relationship must be divided into two 1:M relationships through the use of the ENROLL entity, shown in the ERD segment in Figure 4.31. But note that the optional symbol is shown next to ENROLL. If a class exists but has no students enrolled in it, that class doesn't occur in the ENROLL table. Note also that the ENROLL entity is weak: it is existence-dependent, and its (composite) PK is composed of the PKs of the STUDENT and CLASS entities. You can add the cardinalities (0,6) and (0,35) next to the ENROLL entity to reflect the business rule constraints, as shown in Figure 4.31.

Database Systems, 9th Edition

Each department has several (or many) students whose major is offered by that department. However, each student has only a single major and is, therefore, associated with a single department. (See Figure 4.32.) However, in the Tiny College environment, it is possible—at least for a while—for a student not to declare a major field of study. Such a student would not be associated with a department; therefore, DEPARTMENT is optional to STUDENT. It is worth repeating that the relationships between entities and the entities themselves reflect the organization's operating environment. That is, the business rules define the ERD components.

Database Systems, 9th Edition

Each student has an advisor in his or her department; each advisor counsels several students. An advisor is also a professor, but not all professors advise students. Therefore, STUDENT is optional to PROFESSOR in the "PROFESSOR advises STUDENT" relationship. (See Figure 4.33.)

Database Systems, 9th Edition

As you can see in Figure 4.34, the CLASS entity contains a ROOM_CODE attribute. Given the naming conventions, it is clear that ROOM_CODE is an FK to another entity. Clearly, because a class is taught in a room, it is reasonable to assume that the ROOM_CODE in CLASS is the FK to an entity named ROOM. In turn, each room is located in a building. So the last Tiny College ERD is created by observing that a BUILDING can contain many ROOMs, but each ROOM is found in a single BUILDING. In this ERD segment, it is clear that some buildings do not contain (class) rooms. For example, a storage building might not contain any named at all.

Database Systems, 9th Edition

Database Design Challenges:
Conflicting Goals

Database designers must make design compromises
Conflicting goals: design standards, processing speed, information requirements
Important to meet logical requirements and design conventions
Design is of little value unless it delivers all specified query and reporting requirements
Some design and implementation problems do not yield “clean” solutions

Database Systems, 9th Edition

Summary

Entity relationship (ER) model
Uses ERD to represent conceptual database as viewed by end user
ERM’s main components:
Entities
Relationships
Attributes
Includes connectivity and cardinality notations

Database Systems, 9th Edition

Summary (cont’d.)

Connectivities and cardinalities are based on business rules
M:N relationship is valid at conceptual level
Must be mapped to a set of 1:M relationships

Database Systems, 9th Edition