Download Conceptual Modeling Using the Entity-Relationship Computer and more Summaries Computer Science in PDF only on Docsity!
2. Conceptual Modeling using the
Entity-Relationship Model
Contents
- Basic concepts: entities and entity types, attributes and keys, relationships and relationship types
- Entity-Relationship schema (aka ER diagram)
- Constraints on relationship types
- Design choices
- Enhanced Entity-Relationship model features
- Steps in designing an ER schema
- Translation of an ER schema to tables
What does Conceptual Design include?
Ideas −→ High-level design
Relational database schema
−→ Relational DBMS
- Entity-Relationship model is used in the conceptual design of a database (+ conceptual level, conceptual schema)
- Design is independent of all physical considerations (DBMS, OS,... ). Questions that are addressed during conceptual design:
- What are the entities and relationships of interest (mini- world)?
- What information about entities and relationships among entities needs to be stored in the database?
- What are the constraints (or business rules) that (must) hold for the entities and relationships?
- A database schema in the ER model can be represented pictorially (Entity-Relationship diagram)
Key attributes of an Entity Type
- Entities of an entity type need to be distinguishable.
- A superkey of an entity type is a set of one or more attributes whose values uniquely determine each entity in an entity set.
- A candidate key of an entity type is a minimal (in terms of number of attributes) superkey.
- For an entity type, several candidate keys may exist. During conceptual design, one of the candidate keys is selected to be the primary key of the entity type.
Relationships, Relationship Types, and Relationship Sets
- Relationship (instance): association among two or more entities, e.g., “customer ’Smith’ orders product ’PC42’ ”
- Relationship Type: collection of similar relationships An n-ary relationship type R links n entity types E 1 ,... , En. Each relationship in a relationship set R of a relationship type involves entities e 1 ∈ E 1 ,... , en ∈ En
R ⊆ {(e 1 ,... , en) | e 1 ∈ E 1 ,... , en ∈ En} where (e 1 ,... , en) is a relationship.
- Degree of a relationship: refers to the number of entity types that participate in the relationship type (binary, ternary,... ).
- Roles: The same entity type can participate more than once in a relationship type.
EMPLOYEES reports_to supervisor
subordinate
Role labels clarify semantics of a relationship, i.e., the way in which an entity participates in a relationship. ; recursive relationship.
Example of an Entity-Relationship Diagram
offers (^) Price
SAddress
Chain
CAddress
CUSTOMERS
orders
Account (^) PRODUCTS
SUPPLIERS
Quantity
FName LName Prodname
SName
Customers-Suppliers-Products Entity-Relationship Diagram
- Rectangles represent entity types
- Ellipses represent attributes
- Diamonds represent relationship types
- Lines link attributes to entity types and entity types to relationship types
- Primary key attributes are underlined
- Empty Circle at the end of a line linking an attribute to an entity type represents an optional (null) attribute (not mentioned in textbook) Not in the above diagram, but later in examples:
- Double Ellipses represent multi-valued attributes
Constraints on Relationship Types
Limit the number of possible combinations of entities that may participate in a relationship set. There are two types of constraints: cardinality ratio and participation constraints
Very useful concept in describing binary relationship types. For binary relationships, the cardinality ratio must be one of the following types:
EMPLOYEES worksïin^ DEPARTMENTS
Meaning: An employee can work in many departments (≥ 0 ), and a department can have several employees
EMPLOYEES worksïin DEPARTMENTS
Meaning: An employee can work in at most one department (≤ 1 ), and a department can have several employees.
Constraints on Relationship Types (cont.)
A many-one relationship type (and the counterpart one-many) is also often called a functional relationship.
Cardinality ratio of a relationship can affect the placement of a relationship attribute. E.g., in case of a many-one relationship type, one can place a relationship attribute at a participating entity type.
Participation constraint: specifies whether the existence of an entity e ∈ E depends on being related to another entity via the relationship type R.
- total: each entity e ∈ E must participate in a relationship, it cannot exist without that participation (total participation aka existence dependency).
EMPLOYEES worksïin^ DEPARTMENTS
- partial: default; each entity e ∈ E can participate in a relationship
Instead of a cardinality ratio or participation constraint, more precise cardinality limits (aka degree constraints in textbook) can be associated with relationship types:
E (^1) (min , max ) 1 1 R (min , max ) 2 2 E 2
Each entity e 1 ∈ E 1 must participate in relationship set R at least min 1 and at most max 1 times (analogous for e 2 ∈ E 2 ).
Frequently used cardinalities
Relationship (min 1 , max 1 ) (min 2 , max 2 ) pictorial notation
many-to-many (0, ∗) (0, ∗)
many-to-one (0, 1) (0, ∗)
one-to-one (0, 1) (0, 1)
Enhanced ER Modeling Concepts
Although most properties of entities and relationships can be expressed using the basic modeling constructs, some of them are costly and difficult to express (and to understand). That’s why there are some extensions to the ER model.
Subclasses, Superclasses, and Inheritance
- In some cases, an entity type has numerous subgroupings of its entities that are meaningful and need to be represented explicitly because of their significance to the DB application.
ISA
PERSON
GPA STUDENT Major
SSN Name
- Relationships and attributes of superclass are inherited to subclass (in particular primary key attribute(s)); subclass can have additional attributes and relationships
- An entity cannot exist merely by being a member of only a subclass.
Specialization
- Process of defining a set of subclasses of an entity type (top-down)
ISA
EMPLOYEES
SocialSN
HOURLY_EMPS
Name Address
CONTRACT_EMPS
Hours Wages Contractno
HOURLY EMPS is a subclass of EMPLOYEES and thus inherits its attributes and relationships (same for CONTRACT EMPS).
Generalization:
- Reverse process of specialization (bottom-up); identify common features of entity types and generalize them into single superclass (including primary key!)
ISA
CAR TRUCK
Tonage MaxSpeed Axels
NoOfPassengers
VEHICLE
VehicleNo (^) Price LicensePlate
Steps in Designing an Entity-Relationship Schema
[Step 1] Identify entity types (entity type vs. attribute)
[Step 2] Identify relationship types
[Step 3] Identify and associate attributes with entity and relationship types
[Step 4] Determine attribute domains
[Step 5] Determine primary key attributes for entity types
[Step 6] Associate (refined) cardinality ratio(s) with relationship types
[Step 7] Design generalization/specialization hierarchies including constraints (includes natural language statements as well)
Translation of ER Schema into Tables
- An ER schema can be represented by a collection of tables which represent contents of the database (instance).
- Primary keys allow entity types and relationship types to be expressed uniformly as tables.
- For each entity and relationship type, a unique table can be derived which is assigned the name of the corresponding entity or relationship type.
- Each table has a number of columns that correspond to the attributes and which have unique names. An attribute of a table has the same domain as the attribute in the ER schema.
- Translating an ER schema into a collection of tables is the basis for deriving a relational database schema from an ER diagram.
Translating Relationship Types into Tables
- A many-many relationship type is represented as a table with columns for the primary key attributes of the participating entity types, and any descriptive attributes of the relationship type. Example: Relationship type offers
Prodname SName Price PC42 Hal-Mart 2, MacIV Sears 2,
.........
Prodname and SName are the primary key attributes of the entity types SUPPLIERS and PRODUCTS.
- Translation of one-many and many-one (functional) and one- one relationship types into tables can be optimized ; no table for relationship type necessary!
Translating Subclasses/Superclasses into Tables
- Method 1: Form a table for the superclass and form a table for each subclass. Include the primary key attributes of the superclass in each such table.
Example: Employees(SocialSN, Name, Address) Hourly_Emps(SocialSN, Hours, Wages) Contract_Emps(SocialSN, ContractNo)
- Method 2: Form a table for each subclass and include all attributes of the superclass. Hourly_Emps(SocialSN, Name, Address, Hours, Wages) Contract_Emps(SocialSN, Name, Address, Contractno)
Method 2 has no table for the superclass EMPLOYEES.
- Method 3: Use null values Employees(SocialSN, Name, Address, Hours, Wages, ContractNo) Hourly employees will have a null value for ContractNo. Contract employees will have null values for Hours and Wages.
