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.

The Relational Database Model, that the entity relationship model (ERM) forms the basis of an ERD. The
ERD represents the conceptual database as viewed by the end user.

Entity relationship digram

-depict the database’s main components: entities, attributes, and relationships

Entity

- object of interest to the end user

- an entity actually refers to the entity set and not to a single entity occurrence in Entity
Relationship Model (ERM). It corresponds to a table—not to a row—in the relational
environment
- Written in capital letters inside the rectangle

Attributes

- characteristics of entities

Note:

Chen Model:

1. attributes are represented by ovals and are connected to the entity rectangle with a line.
2. oval contains the name of the attribute it represents
Crow’s Foot Notation

1. attributes are written in the attribute box below the entity rectangle

Because the Chen representation consumes more space, software vendors have adopted the Crow’s
Foot attribute display.

2 kinds of Attributes

1. required attribute
- attribute that must have a value; in other words, it cannot be left empty
- Example: Figure 4.1 : 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.

2. optional attribute
- an attribute that does not require a value; therefore, it can be left empty

Domain

- Attributes have a domain

- the set of possible values for a given attribute.
- Example: The domain for a gender attribute consists of only two possibilities: M or F (or
some other equivalent code)
- Attributes may share a domain.
- Example: For instance, a student address and a professor address share the same domain of
all possible addresses

Identifiers (Primary Keys)

- ERM uses identifiers—one or more attributes that uniquely identify each entity instance.
- Identifiers are underlined in the ERD.
- Example: CAR (CAR_VIN, MOD_CODE, CAR_YEAR, CAR_COLOR) Each car is identified by a
unique vehicle identification number, or CAR_VIN.
Composite Identifier

- Ideally, an entity identifier is composed of only a single attribute (not definition)

- a primary key composed of more than one attribute.

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, and 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 com bination 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)

Note that both key attributes are underlined in the entity notation
A composite attribute,

- is an attribute that can be further subdivided to yield additional attributes.

- Example:
1. For example, the attribute ADDRESS can be subdivided into street, city, state, and zip
code.
2. PHONE_NUMBER can be subdivided into area code and exchange number

simple attribute

- an attribute that cannot be subdivided.

- Example: Age , sex, and marital status would be classified as simple attributes

Note:

1. The database designer must always be on the lookout for composite attributes.
- It is common for business rules to use composite attributes to simplify policies,
- Example: , a user at Tiny College might need to know a student’s name, address, and phone
number. The designer must rec ognize that these are composite attributes and determine
the correct way to decompose the composite into simple attributes
- To facilitate detailed queries

Single-Valued Attributes

- an attribute that can have only a single value

- Example: person can have only one Social Security number
- Note: Keep in mind that a single-valued attribute is not necessarily a simple attribute.
- Example: a part’s serial number (such as SE-08-02-189935) is single-valued, but it is a com
posite 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

- attributes that can have many values

- Example: a person may have several college degrees, and a household may have several
different phones, each with its own number.
 - Note:
1. In the Chen ERM, multivalued attributes are shown by a double line connecting the
attribute to the entity.
2. The Crow’s Foot notation does not identify multivalued attributes.
3. ; note that CAR_VIN is the primary key, and CAR_COLOR is a multivalued attribute of the
CAR entity

Implementing Multivalued Attributes

So, if multivalued attri butes 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.)
Note:

A. adoption can lead to major structural problems in the table.

B. Data dependence
- additional color components—such as a logo color—are added for some cars
- table structure must be modified to accommodate the new color section.
- cars that do not have such color sections generate nulls for the nonexistent components, or
their color entries for those sections are entered as N/A to indicate “not applicable.”
C. Although you have seen solution 1 applied, it is not always acceptable.

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.
you can now assign as many colors as necessary without having to change the table structure. The ERM
shown in Figure 4.5 reflects the components listed in Table 4.1. This is the preferred way to deal with
multivalued attributes. Creating a new entity in a 1:M relationship with the original entity yields several
benefits: it is a more flexible, expandable solution, and it is compatible with the relational model

Derivative Attribute

- attribute whose value is calculated (derived) from other attributes.

- need not be physically stored within the database
- can be derived by using an algorithm
- Example : Age attribute might be derived by subtracting the birth date from the current
date. the total cost of an order can be derived by multiplying the quantity ordered by the
unit price
- computed attributes a.k.a
- The decision to store derived attributes in database tables depends on the processing
requirements and the constraints placed on a particular application.

Note:

1. Chen Notation
a. derived attribute is indicated by a dashed line that connects the attribute and the entity

2. Crow’s Foot notation does not have a method for distinguishing the derived attribute from other
attributes.