10
Chapter 3. Data Modeling Using the Entity-Relationship (ER) Model
- Design phases (See Figure 3.1, page 61 of the textbook)
1. Requirements collection and analysis (: usually done by system analyst)
- Interview with people who make database and who use database understand
- Review existing documentation the universe
- Study operating environment Þ of discourse
- Get a domain expert to help you (UOD)
2. Conceptual design (: usually done by database designer)
(1) Conceptual schema design (Use conceptual data model: ER model)
- Provide a stable description of DB
(2) Transaction design
- Identify the transactions that will occur
- Let customer agree with my conceptual design
3. Bill customer
4. Go to 1
5. Choose a DBMS
- Consider the followings: (1) size of DB
(2) number of users
(3) location of users (distributed)
(4) protection / security
(5) budget
(6) preexisting software / hardware / expertise
(7) standards
6. Data model mapping
- High-level data model → implementation data model
(ER model → Relational data model)
7. Schema refinement (: Physical design)
- Table reorganization
- Normalization
8. Tuning
- E-R (Entity-Relationship) Model
· Example: Figure 3.2 (p64 of the textbook)
· Components of the E-R model
(1) Entity: An object of interest
(ex) a particular person, a car, a house, an employee (: an object with physical
existence) or a company, a job, a university course (: an object with
conceptual existence)
(2) Relationship: Connection between entities
(ex) employee “works-on” project
(3) Attributes: The characteristics that describe an entity
(ex) an employee entity with employee’s name, age, address, salary, and job
- simple or composite
age name = first name + last name
- single-valued or multi-valued
age, SSN degree earned, color of a car
- derived or stored (:age can be derived from birthday)
age birthday
· Entity type: - Entities can be further categorized into distinct entity types (or entity
classes).
- Entity type is described by its name and attributes. All the entities in a
particular entity type share certain attributes.
(ex) - EMPLOYEE entity type with the attributes of name, age, and
SSN.
- COMPANY entity type with the attributes of name, headquarter,
and president.
· Entity set: The collection of all entities of a particular entity type.
· Key attribute: An attribute that uniquely identifying a single entity among all entities
within that entity type.
(ex) - name attribute of the COMPANY entity type
- SSN attribute of the EMPLOYEE entity type
· Weak entity type: An entity type that doesn’t have any key attributes of its own.
It depends on other entity’s existence.
(ex)
· Domain of an attribute specifies the set of values that may be assigned to an attribute
for each individual entity.
(ex) age: integer between 16 and 70
name: the set of strings of alphabetic characters separated by blank characters
GPA: [0, 4.0]
birthday: [1.1, 12.31]
· A relationship type R among n entity types E1, E2, …, En defines a set of associations
(or a relationship set) among entities from these types. (i.e. R E1 x E2 x … x En)
Each relationship that occurs is an instance of a relationship type.
· Degree of a relationship type is the number of participating entity types
· Connectivity: Every relationship has connectivity.
one-to-one (1:1) (ex)
one-to-many (1:N) (ex)
many-to-many (N:M) (ex)
· Cardinality: The specific number of entity occurrences associated with one
occurrence of the related entity (: decided by the policy of an organization).
· Relationship participation optional: don’t have to be involved with other entities
mandatory: have to be involved with other entities
· Goal of E-R model
(1) Translate different views of data among managers, users, and programmers to fit
into a common framework.
(2) Define data processing and constraint requirements in order to help us meet the
different views.
(3) Help implementing database.
- See Figure 3.14 (page 83 of the textbook) for the summary of ER diagram notation
- See Figure 3.20 (page 94 of the textbook) for an ER diagram for a UNIVERSITY
database schema.