Dbms 4 Carol Erm

ENTITY
RELATIONSHIP
MODELING
Source :
Coronel, C. & Morris, S. 2019. Database Systems:
Design, Implementation, and Management (13th Ed).
Cengage Learning Inc. Canada.
Objectives
• Identify the main characteristics of entity relationship components

• Describe how relationships between entities are defined, refined, and
incorporated into the database design process
• See how ERD components affect database design and implementation
• Understand that real-world database design often requires the reconciliation of
conflicting goals
4.1. ENTITY RELATIONSHIP MODEL
• Data modeling is the first step in the database design journey, serving as a bridge between real-world objects
and the database model that is implemented in the computer. Therefore, the importance of data-modeling
details, expressed graphically through entity relationship diagrams (ERDs), cannot be overstated.
• Chapter 2 → Data Model
• Chapter 3 → Relational Database Model as the basis of ERD.
• ERD depicts Entities / Objects, Attributes and Relationship
• Chapter 2 → ERD uses various notation :
• 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
Entity
• An entity is an object of interest to the end user.
• In Chapter 2, you learned that, at the ER modeling level, an entity actually refers to the entity set
and not to a single entity occurrence.
• entity in the ERM corresponds to a table—not to a row—in the relational

environment.
• The ERM refers to a table row as an entity instance or entity occurrence. In the
• Chen, Crow’s Foot, and UML notations, an entity is represented by a rectangle that contains
the entity’s name. The entity name, a noun, is usually written in all capital letters.
Attributes (Atribut / karakteristik)
• Attributes are characteristics of entities.
• the STUDENT entity includes the
attributes STU_LNAME, STU_FNAME, and STU_INITIAL, among many others. In
• the original Chen notation, attributes are represented by ovals and are connected to the
entity rectangle with a line.
• Each oval contains the name of the attribute it represents.
Required and Optional Attributes
• Required and Optional Attributes
• A required attribute is an attribute that must have a value;
• it cannot be left empty.
• As shown in Figure 4.1, the two boldfaced attributes in the Crow’s Foot notation indicate that
data entry will be required.
STU_LNAME and STU_FNAME require data entries because all students are assumed
to have a last name and a first name.
• Optional attribute I
• is an attribute that does not require a value;
• it can be left empty However, students might not have a middle name,
and perhaps they do not yet have a phone number and an email address.
• Therefore, those attributes are not presented in boldface in the entity box.
DOMAINS
• Domains Attributes have a domain.
• A domain is the set of possible values for a given attribute.
• For example,
• the domain for a grade point average (GPA) attribute is written (0,4) because the lowest
possible GPA value is 0 and the highest possible value is 4.
• The domain for a gender attribute consists of only two possibilities: M or F (or some other
equivalent code).
• The domain for a company’s date of hire attribute consists of all dates that fit in a range (e.g.,
company startup date to current date).
• Attributes may share a domain. For instance, a student address and a professor
address share the same domain of all possible addresses. In fact, the data dictionary may
let a newly declared attribute inherit the characteristics of an existing attribute if the
same attribute name is used. For example, the PROFESSOR and STUDENT entities may
each have an attribute named ADDRESS and could therefore share a domain.
Identifiers (Primary Keys)
• identifiers—one or more attributes that uniquely identify each entity instance.
• In the relational model, entities are mapped to tables, and
• the entity identifier is mapped as the table’s primary key (PK). Identifiers are underlined in the
ERD.
• Key attributes are also underlined in a frequently used shorthand
notation for the table structure, called a relational schema, that uses the following format:
TABLE NAME (KEY_ATTRIBUTE 1, ATTRIBUTE 2, ATTRIBUTE 3, … ATTRIBUTE K)
• For example, a CAR entity may be represented by CAR (CAR_VIN, MOD_CODE, CAR_YEAR,
CAR_COLOR)
Each car is identified by a unique vehicle identification number, or CAR_VIN.
Composite Identifiers
• Ideally, an entity identifier is composed of only a single attribute. For example, the table in Figure
4.2 uses a single-attribute primary key named CLASS_CODE.
• However, it is possible to use a composite identifier, a primary key composed of more than one attribute.
• For instance, the Tiny College database administrator may decide to identify each CLASS entity instance
(occurrence) by using a composite primary key of CRS_CODE and CLASS_SECTION instead of using CLASS_
CODE. Either approach uniquely identifies each entity instance.
• Given the structure of the CLASS table shown in Figure 4.2,
• CLASS_CODE is the primary key,
• the combination of CRS_CODE and CLASS_SECTION is a proper candidate key.
• If the CLASS_CODE attribute is deleted from the CLASS entity, the candidate key (CRS_CODE and
CLASS_SECTION) becomes an acceptable composite primary key
• If the CLASS_CODE in Figure 4.2 is used as the primary key, the CLASS entity may
be represented in shorthand form as follows:
CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME,
ROOM_CODE, PROF_NUM)
• On the other hand, if CLASS_CODE is deleted, and the composite primary key is
the
combination of CRS_CODE and CLASS_SECTION, the CLASS entity may be
represented as follows:
CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM)
Composite and Simple Attributes
• Composite and Simple Attributes Attributes are classified as simple or composite.
• A composite attribute, not to be confused with a composite key, is an attribute that can be
further subdivided to yield additional attributes.
• For example, the attribute ADDRESS can be subdivided into street, city, state, and zip code.
• Similarly, the attribute PHONE_NUMBER can be subdivided into area code and exchange
number.
• A simple attribute is an attribute that cannot be subdivided.
• For example, age, sex, and marital status would
be classified as simple attributes.
• To facilitate detailed queries, it is wise to change composite attributes into a series of simple
attributes.
• The designer must recognize that these are composite attributes and determine the correct
way to decompose the composite into simple attributes.
Single-Valued Attributes
• Single-Valued Attributes A single-valued attribute is an attribute that can have only a single value.
• For example, a person can have only one Social Security number, and
• a manufactured part can have only one serial 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 single-valued, but it is
• a composite attribute because it can be subdivided into
• the region in which the part was produced (SE),
• the plant within that region
(08), the shift within the plant (02), and
• the part number (189935).
Multivalued Attributes
• Multivalued Attributes 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 for the roof, body, and trim.
• In the Chen ERM, multivalued attributes are shown by a double line connecting the attribute to
the entity.
• The Crow’s Foot notation does not identify multivalued attributes.
• The ERD in Figure 4.3 contains all of the components introduced thus far; note that
• CAR_VIN is the primary key, and
• CAR_COLOR is a multivalued attribute of the CAR entity.
• In the ERD models in Figure 4.3, the CAR entity’s foreign key (FK) has been typed as
MOD_CODE.
• This attribute was manually added to the entity.
• Actually, proper use of database modeling software will automatically produce the FK when the
relationship is defined.
• In addition, the software will label the FK appropriately and write the FK’s
implementation details in a data dictionary.
Implementing Multivalued Attributes
• Implementing Multivalued Attributes Although the conceptual model can handle
M:N relationships and multivalued attributes, you should not implement them in the
RDBMS.
• Remember from Chapter 3 that in the relational table, each column and row
intersection represents a single data value. So, if multivalued 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 component of the
original multivalued attribute.
• For example, the CAR entity’s attribute CAR_COLOR
• can be split to create the new attributes CAR_TOPCOLOR,
• CAR_BODYCOLOR,
• and CAR_TRIMCOLOR, which are then assigned to the CAR entity. (See Figure 4.4.)
• its adoption can lead to major structural problems
in the table. It is only acceptable if every instance will have the same number of values for
the multivalued attribute, and no instance will ever have more values.
• However, even in this case, it is a gamble that new changes in the environment will never create a situation
where an instance would have more values than before. For example, if additional color
components—such as a 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 components. This
new entity allows the designer to define color for different sections of the car (see Table 4.1).
• Then, this new CAR_COLOR entity is related to the original CAR entity in a 1:M relationship.
Note !
• If you are used to looking at relational diagrams such as the ones produced by
Microsoft Access, you expect to see the relationship line in the relational diagram
drawn from the PK to the FK.
• However, the relational diagram convention is not necessarily
reflected in the ERD.
• In an ERD, the focus is on the entities and the relationships between
them, rather than how those relationships are anchored graphically.
• In a complex ERD that includes both horizontally and vertically placed entities, the placement of
the relationship lines is largely dictated by the designer’s decision to improve the readability of the
design.
(Remember that the ERD is used for communication between designers and end users.)
Derived Attributes
• Derived Attributes Finally, a derived attribute is an attribute whose value is calculated
(derived) from other attributes.
• The derived attribute need not be physically stored within the database; instead, it can be derived
by using an 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.
• If you use Microsoft Access, you would use the formula INT((DATE() – EMP_DOB)/365).
• In Microsoft SQL Server, you would use DATEDIFF(“DAY”, EMB_DOB, GETDATE())/365, where
DATEDIFF is a function that computes the difference
between dates.
• If you use Oracle, you would use TRUNC((SYSDATE – EMP_DOB)/365,0).
Derived Attributes
• Derived attributes are sometimes referred to as computed attributes.
• Computing a derived attribute can be as simple as adding two attribute values located on the same
row, or it can be the result of aggregating the sum of values located on many table rows
(from the same table or from a different table). The decision to store derived attributes in database tables
depends on the processing requirements and the constraints placed on a particular application. The designer
should be able to balance the design in accordance
with such constraints.
• Table 4.2 shows the advantages and disadvantages of storing (or not storing) derived attributes in the
database.
Derived Attributes
4-1c Relationships
• Recall from Chapter 2 that a relationship is an association between entities.
• The entities that participate in a relationship are also known as participants, and
• each relationship is identified by a name that describes the relationship.
• The relationship name is an active or passive verb;
• for example, a STUDENT takes a CLASS,
• a PROFESSOR teaches a CLASS,
• a DEPARTMENT employs a PROFESSOR,
• a DIVISION is managed by an EMPLOYEE, and an AIRCRAFT is flown by a CREW
4-1c Relationships
• Relationships between entities always operate in both directions.
• To define the relationship between the entities named CUSTOMER and INVOICE,
• • A CUSTOMER may generate many INVOICEs.
• Each INVOICE is generated by one CUSTOMER.
• Because you know both directions of the relationship between CUSTOMER and INVOICE, it is easy to see that this
relationship can be classified as 1:M.
• The relationship classification is difficult to establish if you know only one side of the
relationship.
• For example, if you specify that:
A DIVISION is managed by one EMPLOYEE.
• You don’t know if the relationship is 1:1 or 1:M.

• Therefore, you should ask the question “Can an employee manage more than one division?” If the answer is yes, the
relationship is 1:M, and the second part of the relationship is then written as:
An EMPLOYEE may manage many DIVISIONs.
• If an employee cannot manage more than one division, the relationship is 1:1, and
the second part of the relationship is then written as:
An EMPLOYEE may manage only one DIVISION
4-1d Connectivity and Cardinality
• You learned in Chapter 2 that entity relationships may be classified as one-to-one, one-to-many, or many-to-
many.
• You also learned how such relationships were depicted in the Chen and Crow’s Foot notations.
• 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.
• Crow’s Foot modeling notation do not depict the specific cardinalities on the ER
diagram itself because the specific limits described by the cardinalities cannot be implemented directly
through the database design. Correspondingly,
• Crow’s Foot ER modeling tools do not print the numeric cardinality range in the diagram; instead, you
can add it as text if you want to have it shown. When the specific cardinalities are not
included on the diagram in Crow’s Foot notation, cardinality is implied by the use of the
symbols shown in Figure 4.7, which describe the connectivity and participation (discussed next). The numeric
cardinality range has been added using the Microsoft Visio
text drawing tool.
connectivity
The classification of the relationship between entities.
Classifications include 1:1, 1:M, and M:N.
cardinality
A property that assigns a specific value to connectivity and expresses the range of allowed entity
occurrences associated with a single occurrence of the related entity
• However, keep in mind that the DBMS cannot handle the implementation of the cardinalities at
the table level—that capability is provided by the application software or by triggers.
• Connectivities and cardinalities are established by concise statements known as business rules, which were
introduced in Chapter 2.
• Business rules, derived from a precise and detailed description of an organization’s data environment → also
establish the ERM’s entities, attributes, relationships, connectivities, cardinalities, and constraints.
• Business Rules define the ERM’s components, making sure that all appropriate business rules are identified is
an important part of a database designer’s job.
• NOTES : The placement of the cardinalities in the ER diagram is a matter of convention.
• Chen notation places the cardinalities on the side of the related entity.
• The Crow’s Foot and UML diagrams place the cardinalities next to the entity to which they apply
4-1e Existence Dependence
• existence-dependent
A property of an entity whose existence depends on one or more other entities. In such an environment, the
existence-independent table must be created and loaded first because the existence dependent key cannot
reference a table that does not yet exist.
• For example, if an employee wants to claim one or more dependents
for tax-withholding purposes, the relationship “EMPLOYEE claims DEPENDENT”
would be appropriate. In that case, the DEPENDENT entity is clearly existence dependent on the EMPLOYEE
entity because it is impossible for the dependent to exist apart from the EMPLOYEE in the database
4-1e Existence Dependence
• Existence independent
A property of an entity that can exist apart from one or more related entities. Such a table
must be created first when referencing an existence-dependent table
• If an entity can exist apart from all of its related entities, then it is existence independent, and it is referred to as a strong
entity or regular entity.
• For example, suppose that the XYZ Corporation uses parts to produce its products. Furthermore, 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.
• NOTES :
• The concept of relationship strength is not part of the original ERM.
• Instead, this concept applies directly to Crow’s Foot diagrams. Because Crow’s Foot diagrams
are used extensively to design relational databases, it is important to understand relationship strength as it affects
database implementation.
• The Chen ERD notation is oriented toward conceptual modeling and therefore does not distinguish between weak
and strong relationships.
4-1f Relationship Strength
• The concept of relationship strength is based on how the primary key of a related entity
is defined.
• To implement a relationship, the primary key of one entity (the parent entity, normally on the
“one” side of the one-to-many relationship) appears as a foreign key in the related entity (the child
entity, mostly the entity on the “many” side of the one-to-many relationship). Sometimes, the
foreign key also is a primary key component in
• the related entity. For example, in Figure 4.5, the CAR entity primary key (CAR_VIN)
appears as both a primary key component and a foreign key in the CAR_COLOR entity.
In this section, you will learn how various relationship strength decisions affect primary
key arrangement in database design.
Weak (Non-Identifying) Relationships
• Weak (Non-Identifying) Relationships A weak relationship, also known as a non-identifying
relationship, exists if the primary key of the related entity does not contain a primary key
component of the parent entity.
• By default, relationships are established by having the primary key of the parent entity appear as a
foreign key (FK) on the related entity (also known as the child entity).
• For example, suppose the 1:M relationship between COURSE and CLASS is defined as:
COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT)
CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM)
• In this example, the CLASS primary key did not inherit a primary key component
from the COURSE entity. In this case, a weak relationship exists between COURSE and
CLASS because CRS_CODE (the primary key of the parent entity) is only a foreign key
in the CLASS entity.
• Figure 4.8 shows how the Crow’s Foot notation depicts a weak relationship by placing
a dashed relationship line between the entities. The tables shown below the ERD illustrate how
such a relationship is implemented.
In other words, the CLASS primary key did inherit a primary key component from the COURSE entity.
(Note that the CRS_CODE in CLASS is also the FK to the COURSE entity.)
Strong (Identifying) Relationships
• A strong (identifying) relationship exists when
the primary key of the related entity contains a primary key component of the parent entity.
• For example, suppose the 1:M relationship between COURSE and CLASS is defined as:
COURSE (CRS_CODE, DEPT_CODE, CRS_DESCRIPTION, CRS_CREDIT)
CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM)
• In this case, the CLASS entity primary key is composed of CRS_CODE and CLASS_
SECTION. Therefore, a strong relationship exists between COURSE and CLASS
because CRS_CODE (the primary key of the parent entity) is a primary key component
in the CLASS entity.
In other words, the CLASS primary key did inherit a primary key component from the COURSE entity.
Notes !
• Keep in mind that the order in which the tables are created and loaded is very
important.
• For example, in the “COURSE generates CLASS” relationship, the COURSE table
must be created before the CLASS table.
• After all, it would not be acceptable to have the CLASS table’s foreign key refer to a
COURSE table that did not yet exist.
• In fact, you must load the data of the “1” side first in a 1:M relationship to avoid
the possibility of referential
integrity errors, regardless of whether the relationships are weak or strong.
4-1g Weak Entities
• a weak entity is
one that meets two conditions:
1. The entity is existence-dependent; 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
• For example: a company insurance policy insures an employee and any dependents.
For the purpose of describing an insurance policy,

• an EMPLOYEE might or might not have a DEPENDENT,
• but the DEPENDENT must be associated with an EMPLOYEE.
• Moreover, the DEPENDENT cannot exist without the EMPLOYEE → that is, a person cannot get
insurance coverage as a dependent unless the person is a dependent of an employee.
DEPENDENT is the weak entity in the relationship “EMPLOYEE has DEPENDENT.”
4-1g Weak Entities
4-1g Weak Entities
• WEAK ENTITY has a Strong relationship with parent
• PK of Weak Entity will be PK from Parent (strong entity)
• EMPLOYEE (EMP_NUM, EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_DOB, EMP_HIREDATE)
DEPENDENT (EMP_NUM, DEP_NUM, DEP_FNAME, DEP_DOB)
Not Weak Entity
• In other words, the CLASS primary key did inherit a primary key component from the COURSE entity.
• CLASS has an existence dependence with COURSE
• COURSE & CLASS → Weak Relationship
• But CLASS is not Weak entity because PK of CLASS is not derived from COURSE
• CLASS (CLASS_CODE, CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM)
Weak Entity
• In other words, the CLASS primary key did inherit a primary key component from the COURSE entity.
• COURSE & CLASS → STRONG Relationship
• CLASS IS WEAK ENTITY because PK of CLASS is derived from COURSE
• CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME, ROOM_CODE, PROF_NUM)
4-1h Relationship Participation
• OPTIONAL dan MANDATORY participation in entity relationship
• OPTIONAL, the existence of entity occurrence (row) can be ZERO (0)
• MANDATORY, , the existence of entity occurrence (row) must be at least 1
It is important that you clearly understand the distinction between mandatory and
optional participation in relationships. Otherwise, you might develop designs in which
awkward and unnecessary temporary rows (entity instances) must be created just to
accommodate the creation of required entities.
4-1i Relationship Degree
• A relationship degree indicates the number of entities or participants associated with a
relationship.
• A unary relationship exists when an association is maintained within a single entity.
• A binary relationship exists when two entities are associated.
• A ternary relationship exists when three entities are associated.
• Although higher degrees exist, they are rare and are not specifically named. (For example, an
association of four entities is described simply as a four-degree relationship.)
• relationship degree
The number of entities or participants associated with a relationship. A relationship degree can be
unary, binary, ternary, or higher.
• unary relationship
An ER term used to describe an association within an entity. For example, an EMPLOYEE might
manage another EMPLOYEE . An employee within the EMPLOYEE entity is the manager for one or more
employees within that entity. In this case, the existence of the “manages” relationship means that EMPLOYEE
requires another EMPLOYEE to be the manager—that is, EMPLOYEE has a relationship with itself
binary relationship
An ER term for an association (relationship) between two entities. For example, PROFESSOR
teaches CLASS.
• ternary relationship
An ER term used to describe an association (relationship) between three entities. For example, a
DOCTOR prescribes a DRUG for a PATIENT.
• A DOCTOR writes one or more PRESCRIPTIONs.
• A PATIENT may receive one or more PRESCRIPTIONs.
• A DRUG may appear in one or more PRESCRIPTIONs. (To simplify this example, assume that the business rule states
that each prescription contains only one drug. In short, if a doctor prescribes more than one drug, a separate
prescription must be written for each drug.)
4-1j Recursive Relationships
• Recursive relationship
A relationship found within a single entity type. For example, an EMPLOYEE is married to an EMPLOYEE or a
PART is a component of another PART
• a recursive relationship is one in which a relationship can exist between occurrences of the same entity set.
• 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.”
• 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 COURSE may be a prerequisite to many other COURSEs,
and each COURSE may have many other COURSEs as
prerequisites.”
• One common pitfall when working with unary relationships is to confuse participation with referential
integrity (the FK )
• Referential integrity deals with the correspondence of values in the foreign key with
values in the related primary key
• both participation and referential integrity involve the values used as primary keys and foreign keys to
implement the relationship.
• Referential integrity requires that the values in the foreign key correspond to values in the primary key.
In one direction, participation considers whether the foreign key can contain a null.
4-1k Associative (Composite) Entities
• M:N relationships are a valid construct
at the conceptual level, and therefore
are found frequently during the ER
modeling process.
• However, implementing the M:N
relationship, particularly in the
relational model, requires the use of an
additional entity
• The ER model uses the associative
entity to represent an M:N relationship
between two or more entities. This
associative entity, also called a
composite or bridge entity, is in a 1:M
relationship with the parent entities
and is composed of the primary key
attributes of each parent entity
4-1k Associative (Composite) Entities
• STUDENT & CLASS → M:N → decomposed into 2 1:M with associative entity → ENROLL
• OPTIONALITY → ENROLL
• The relationship between STUDENT and ENROLL is shown to be 1:M, with the “M” on the ENROLL side and ,
while the “1” is located on the STUDENT side.
• The relationship between CLASS and ENROLL is 1:M. Note that in Figure 4.25, the “M” is located on the
ENROLL side, while the “1” is located on the CLASS side.
4-2 developing an ER diagram
The steps (Iterative process)
1. Create a detailed narrative of the organization’s description of operations.
2. Identify the business rules based on the description of operations.
3. Identify the main entities and relationships from the business rules.
4. Develop the initial ERD
5. Identify the attributes and primary keys that adequately describe the entities.
6. Revise and review the ERD
• The process is repeated until the end users and designers agree that the ERD is a fair representation
of the organization’s activities and functions.
• During the design process, the database designer does not depend simply on interviews to help
define entities, attributes, and relationships. A surprising amount of information can be gathered by
examining the business forms and reports that an organization uses in its daily operations.
Contoh
Info 1 :
• Tiny College (TC) 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 and 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.
Info 2 :
• Each school comprises several departments.
• For example, the school of business has an accounting department, a management/marketing
department, an economics/ finance department, and a computer information systems department.
• Note again the
cardinality rules: The smallest number of departments operated by a school is one, and the largest
number of departments is indeterminate (N).
• On the other hand, each department belongs to only a single school; thus, the cardinality is expressed by
(1,1).
That is, the minimum number of schools to which a department belongs is one, as is the maximum
number. Figure 4.26 illustrates these first two business rules.
Contoh
Contoh
Info 3 :
• Each department may offer courses. For example, the management/marketing department offers courses
such as Introduction to Management, Principles of Marketing, and Production Management.
• The ERD segment for this condition is shown in Figure 4.27. Note that this relationship is based on the
way Tiny College operates. For example, if Tiny College had some departments that were classified as
“research only,” they would not offer courses; therefore, the COURSE entity would be optional to the
DEPARTMENT entity.
4-2 developing an ER diagram - Contoh
Info 4 :
• The relationship between COURSE and CLASS was illustrated in Figure 4.9. Nevertheless, it is worth
repeating that a CLASS is a section of a COURSE.
• That is, a department may offer several sections (classes) of the same database course.
• Each of those classes is taught by a professor at a given time in a given place.
• In short, a 1:M relationship exists between COURSE and CLASS.
• Additionally, each class is offered during a given semester.
• SEMESTER defines the year and the term that the class will be offered. Note that this is different from the
date when the student actually enrolls
in a class. For example, students are able to enroll in summer and fall term classes near the end of the
spring term. It is possible that the Tiny College calendar is set with semester beginning and ending dates
prior to the creation of the semester class schedule so CLASS is optional to SEMESTER. This design will
also help for reporting purposes, for example, you could answer questions such as: what classes were
offered X semester? Or, what classes did student Y take on semester X? Because a course may exist in
Tiny College’s course catalog even when it is not offered as a class in a given semester, CLASS is optional
to COURSE. Therefore, the relationships between SEMESTER, COURSE, and CLASS look like Figure 4.28.
Info 5 :
• 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.
Info 6 :
• 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.
• Info 7:
• A student may enroll in several classes but take each class only once during any given
enrollment period. For example, during the current enrollment period, a student may decide
to take five classes—Statistics, Accounting, English, Database, and History— but that student
would not be enrolled in the same Statistics class five times during the enrollment period!
Each student may enroll in up to six classes, and each class may have up to 35 students, thus
creating an M:N relationship between STUDENT and CLASS. Because a CLASS can initially exist
at the start of the enrollment period even though no students have enrolled in it, STUDENT is
optional to CLASS in the M:N relationship. 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.
However, note that the optional symbol is shown next to ENROLL. If a class exists but has no
students enrolled in it, that class does not 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. (Visio
Professional does not automatically generate such cardinalities, but you can use a text box to
accomplish that task.)
Info 8:
• 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.
•
Info 9:
• 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.)
Info 10:
• 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 rooms at all.
•
Entities :
CROW’S ROOT Notation ERD
UML – NOTATION
• Although we focus on Crow’s Foot notation to develop our diagram,
• UML notation is also popular for conceptual and implementation modeling.
• Figure 4.36 shows the conceptual UML class diagram for Tiny College. Note that this class diagram
depicts the M:N relationship between STUDENT and CLASS.
• Figure 4.37 shows the implementation-ready UML class diagram for Tiny College (note that the
ENROLL composite entity is shown in this class diagram).
• The UML class diagrams in Figures 4.36 and 4.37 show the entity and attribute names but do not
identify the primary key attributes.
• The reason goes back to UML’s roots. UML class diagrams are an object-oriented modeling
language, and therefore do not support the notion of “primary or foreign keys” found mainly in
the relational world. Rather, in the object-oriented world, objects inherit a unique object identifier
at creation time. For more information, see Appendix G, Object-Oriented Databases.
UML – NOTATION
→ ENROLL
4-3 database design Challenges: Conflicting Goals
• Design standards. The database design must conform to design standards. Such standards guide
you in developing logical structures that minimize data redundancies, thereby minimizing the
likelihood that destructive data anomalies will occur. Avoiding nulls to the greatest extent
possible. In fact, you have learned that design standards govern the presentation of all
components within the database design. In short, design standards allow you to work with well-
defined components and to evaluate the interaction of those components with some precision.
Without design standards, it is nearly impossible to formulate a proper design process, to evaluate
an existing design, or to trace the likely logical impact of changes in design.
• Processing speed. In many organizations, particularly those that generate large numbers of
transactions, high processing speeds are often a top priority in database design. High processing
speed means minimal access time, which may be achieved by minimizing the number and
complexity of logically desirable relationships. For example, a “perfect” design might use a 1:1
relationship to avoid nulls, while a design that emphasizes higher transaction speed might combine
the two tables to avoid the use of an additional relationship, using dummy entries to avoid the
nulls. If the focus is on data-retrieval speed, you might also be forced to include derived attributes
in the design.
4-3 database design Challenges:
Conflicting Goals
• Information requirements. The quest for timely information might be the focus of
database design. Complex information requirements may dictate data transformations, and they
may expand the number of entities and attributes within the design.
Therefore, the database may have to sacrifice some of its “clean” design structures and
high transaction speed to ensure maximum information generation. For example,
suppose that a detailed sales report must be generated periodically. The sales report
includes all invoice subtotals, taxes, and totals; even the invoice lines include subtotals. If the sales
report includes hundreds of thousands (or even millions) of invoices,
computing the totals, taxes, and subtotals is likely to take some time. If those computations had
been made and the results had been stored as derived attributes in the INVOICE and LINE tables at
the time of the transaction, the real-time transaction speed might have declined. However, that
loss of speed would only be noticeable if there were many simultaneous transactions. The cost of a
slight loss of transaction speed at the front end and the addition of multiple derived attributes is
likely to pay off when the sales reports are generated (not to mention that it will be simpler to
generate the queries).

Dbms 4 Carol Erm

Uploaded by

Copyright:

Available Formats

Dbms 4 Carol Erm

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Dbms 4 Carol Erm

Uploaded by

Copyright:

Available Formats

ENTITY

• Identify the main characteristics of entity relationship components

• entity in the ERM corresponds to a table—not to a row—in the relational

• You don’t know if the relationship is 1:1 or 1:M.

For the purpose of describing an insurance policy,

You might also like