Practical Database Concepts
Lecture 3 & 4: Entity Relationship Modeling Santha Sumanasekara
March 2020
Overview of the lecture
– Why Entity–Relationship Model?
– Basic elements of the Entity–Relationship model
– Concepts
– Build an Entity–Relationship Model
– Convert ER model to Relational Database Model
– Using Tools
– LucidChart
– Oracle Data Modeler
Overview of the lecture
The COMPANY database keeps track of a
company’s employees, departments, and projects.
Suppose that after the requirements gathering and
Why Entity–Relationship Model?
analysis phase, the database designers provide the following description of the miniworld — the part of the company to be represented in the database.
The company is organized into departments.
Each department has a unique name, a unique
number, and a particular employee who manages
the department. We keep track of the start date
when that employee began managing the
department. Data ? Requirements
A department may have several locations.
A department controls a number of projects, each of which has a unique name, a unique number, and a single location.
Database model
We store each employee’s name, social security number address, salary, sex, and birth date. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department.
We keep track of the number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each
employee.
So, what fills the gap in the process?
– A conceptual model is a concise and precise description of the data requirements and includes detailed descriptions of entities, their attributes, relationships, and constraints.
– Entity-Relationship model is a widely used conceptual model tool.
Database Entity- Database
Requirements Relationship Model Model
Entity-Relationship Model
– The ER model is well-suited to data modelling for use with databases.
– It is fairly abstract, and is easy to discuss and explain.
– ER models are readily translated to relations.
– ER modelling is based on two concepts:
– Entities, that is, things.
– Relationships, that is, associations or interactions between entities.
– We use UML notation to graphically represent ER models.
Many Notations – Not standardised
Source: en.wikipedia.org/wiki/Rail_gauge_in_Australia
Many Notations – Not standardised
– There are many different notations used. – ChenNotation
– Crow’sFootNotation
– Barker’sNotation
– InformationEngineers’Notation – UMLNotation
Further Reading: http://www.vertabelo.com/blog/technical- articles/comparison-of-erd-notations
Entity
– The basic concept of the ER model is the entity, which represents a group
of ‘objects’ in the ‘real world’ with the same properties.
– Can be objects with physical existence or objects with a conceptual (or ‘abstract’) existence
– Examples:
Physical Entity Types
Conceptual Entity Types
Vehicle
inspection
Employee
works
Product
sale
Diagrammatic representation
– Each entity type is shown as a rectangle labelled with the name of the entity
Strong and Weak Entities
– A strong entity does not depend on another entity. They have a primary key
that uniquely identifies individual entities within the entity set.
– Weak Entities do depend on some other entity. They may or may not have a primary key, but cannot uniquely identify records on their own. (They need to borrow the primary key from a supporting (identifying) entity.
Strong and Weak Entities
Attributes
– A property of an entity or a relationship – Simple Attributes
– Composed of a single component – Composite Attributes
– Composed of multiple components – Multi-valued Attributes
– Holds multiple values for each occurrence of an entity – Relationship Attributes
– Attributes can be assigned to a relationship, i.e. a property of the relationship.
Attributes
Attribute of a relationship
Composite Attributes
Primary Key Attribute
Multi-valued Attribute
Relationships
– A relationship type is a set of associations between one or more
participating entity types.
In this example, there are two relationships between Employee and Department.
Degree of a Relationship – The number of participating entity types in a relationship.
Both of these are binary relationships.
– We mostly encounter binary relationships, where two entity types participate in the relationship.
directional arrow normally associated with the name is omitted. For example, the ternary
relationship called Registers is shown in Figure 11.7. This relationship is also shown in Figure 11.1.
A relationship of degree four is called quaternary. As we do not have an example of
such a relationship in Figure 11.1, we describe a quaternary relationship called Arranges Degree of a Relationship
with four participating entity types, namely Buyer, Solicitor, FinancialInstitution, and Bid in
Figure 11.8. This relationship represents the situation where a buyer, advised by a soli-
citor and supported by a financial institution, places a bid.
– When three entity types are participating, it is called a Ternary Relationship.
Multiplicity Constraints
– We now examine the constraints that may be placed on entity types that
participate in a relationship. – Multiplicity
– The number (or range) of possible occurrences of an entity type that may be associated within a relationship.
– Three common associations:
– One-to-one
– One-to-many
– Many-to-many
A department has many employees
One Employee manages one department.
– This form of constraints is called the Cardinality of a relationship.
Employees work on many projects. Of course projects have many employees
Cardinality of a Relationship
One Employee manages one department.
A department has many employees
Employees work on many projects. Of course projects have many employees
Cardinality doesn’t reflect the full picture.
– Cardinality is only one aspect of the multiplicity.
– The other aspect is the Participation.
– Participation determines whether all or only some entity occurrences participate in a relationship. – mandatory or optional.
– Do all (or some) employees assigned to a department?
– Can all projects have assigned employees?
– Do all employees work on projects?
– All of these are derived from business rules.
Cardinality
“One Dept is managed by one employee”
“all depts are managed” (Mandatory participation)
“One employee manages one dept”
“not all employees manage depts” (optional participation
Participation
Composition vs Aggregation
Composition:
Exactly one (Strong ownership)
eg Each Department belongs to exactly one Company
Think: what happens to the department when the company is dissolved?
Aggregation: (Weak ownership)
eg A keyboard can only be connected to one computer (at a time)
Think: what happens to the keyboard when the computer is dismantled?
Second Example
“Depts have many employees”
“Not all depts have employees” (Optional participation)
“One employee assigned to one dept”
“not all employees assigned to depts” (optional participation
Complete ER Model for the Company Database
Inheritance
Inheritance allow attributes and associations to be shared
Eg. all Employees have common details (whether they are fulltime or parttime) Subclasses (child classes) inherit from the superclass (parent class)
– one way inheritance only
Inheritance
There are four types of subclasses: Complete or partial.
Complete: Every object is a member of a subclass only (parent class is abstract)
Partial: Some objects can be a member of the parent class alone (parent class is concrete)
Disjoint or Overlapping.
Overlapping: Objects can be a member of two or more subclasses
eg in C++ programming (typically not supported)
Disjoint: Objects can only be a member of one subclass alone.
eg. Java programming (typically the only mode supported)
Design Choices — Is it an attribute or an entity?
– For example, an employee has a home address, which is typically represented as an attribute. But the address represents a house (entity).
– A rule of thumb is that an entity should be modelled as an entity only if it is primary, that is, of direct interest.
– Most companies have an interest in their employees, but not in their residences.
– Secondly, are we recording additional related information about the entity? (the house address, number of bedrooms, size etc)
– Finally, is there a many relationship? Employees have one primary house address (that the employer wants to keep track of), but what if we wanted all your house addresses? An entity will easily support this.
Design Choices — Is it an entity or a relationship?
– This question can be surprisingly tricky.
– For example, a transaction (such as hiring a video) can be described as a relationship, but can also be described as an entity because it represents a definite event.
– Also, is their any attributes that describe the relationship? E.g. timestamp of the transaction.
– One important deciding factor is how clearly the model can be understood.
Mapping ER Model to Relational Model
Step 1: Map Strong Entities
– For each regular (strong) entity type, create a relation holding all the simple
attributes in the entity.
– The primary key of the entity type becomes the primary key of the relation.
– Composite attributes should be separated into their component (simple) attributes.
– Example:
Employee (SSN, fname, minit, lname, bdate, address, gender, salary)
Step 2: Map Weak Entities
– For each weak entity type, create a relation holding all the simple attributes
in the entity.
– Add the primary key attributes of the owner entity into this new relation.
– These primary key attributes along with the partial key attributes form the primary key for this new relation.
– Example:
Dependent (ESSN*, Name, gender, bdate, relationship)
ESSN is the primary key of the owner entity
Name is the partial key of the weak entity
Together, they form the primary key of the new relation.
Step 3: Map 1:1 Relationships
– Cardinality cannot be used to here (1:1)
– Determined by the Participation
– Three possible scenarios
– Mandatory participation on both sides
– Mandatory participation on one side
– Optional participation on both sides
Step 3: Map 1:1 Relationships with Mandatory Participation on both sides
Let’s say we have an additional business rule that state each project must have a consultant (not an employee) and a consultant can audit one and only one project. Also, projects are not audited by more than one consultant.
Step 3: Map 1:1 Relationships with Mandatory Participation on both sides
– In this case we should combine the entities involved into one relation and choose one of the primary keys of the original entities to be the primary key of the new relation.
Project (Pnumber, Pname, Plocation, ConsultantEMail, ConsultantName, ConsultantTelephone)
Step 3: Map 1:1 Relationships with Mandatory Participation on one side
– In this case we are able to identify the parent and child entities for the 1:1 relationship using the participation constraints.
– The entity that has optional participation in the relationship is designated as the parent entity, and the entity that has mandatory participation in the relationship is designated as the child entity
– A copy of the primary key of the parent entity (i. e. the partially participating entity) is placed in the relation representing the child entity.
– If there are attributes associated with the relationship, copy them in the same direction.
– E.g:
“ManagedBy” is a 1:1 relationship. Employee entity is in an optional
participation. So, copy primary key of the Employee into Dept relation.
Department (Dnumber, Dname, MGR_SSN*, MGR_StartDate)
Primary Key of Employee (which is partially participating)
It becomes a foreign key referencing to the other relation
Attributes of the “managed_by relationship
Step 3: Map 1:1 Relationships with Optional Participation on both sides
– In this case we cannot determine a definitive direction you can copy the primary key as a foreign key.
– Unless you can find more about the semantics of this relationship, the mapping will be arbitrary.
– E.g. Consider an additional business rule that states that some projects can have at-most one vehicle assigned. However, not all projects have vehicles. Also, each vehicle is assigned to one project only, but there can be some vehicles in the pool that’s not assigned to any project.
What’s the sensible way – Pnumber in Vehicle or Rego in Project?
Step 3: Map 1:1 Relationships with Optional Participation on both sides
– In this context, we can assume Project is of primary importance and quite central to this model, vehicles are of secondary importance. And, assume most of the projects have a vehicle assigned.
– So, it is logical to copy vehicle rego as a foreign key Project (Pnumber, Pname, Plocation, Rego*)
Step 4: Map 1:N Relationships
– 1:N relationships are mapped into relations with the help of a foreign key.
– For each 1:N relationship, identify the entity that is on the one side of the cardinality.
– Copy the primary key of the one-side entity to the relation representing the N- side entity.
– This will become a foreign key within the N-side.
– Example:
“AssignedTo” is a 1:N relationship. Department is on the 1-side. So, copy
primary key of the Department into Employee (N-side) relation.
Employee (SSN, fname, minit, lname, bdate, address, gender, salary, DNo*)
Primary Key of Dept (which is 1-side of the relationship)
Step 5: Map M:N Relationships
– For each M:N (many-to-many) relationship, create a new relation.
– Copy the primary key of each of the participating entities to the new relation.
– They — together – become the primary key of the new relation
– Each of these atrtributes also act as foreign keys referencing to their original relations.
– Any attributes of the relationship become simple attributes of the new relation.
WorksOn (ESSN*, Pno*, Hours)
ESSN is the primary key of
Employee, Pno is the primary key of Hours is an attribute
Project. Together they form the of the relationship. primary key of WorksOn relation
Step 6: Multi-valued Attributes
– For each multi-valued attribute, create a new relation.
– This new relation will include the corresponding attribute, along with the primary key of the owner entity.
– These attributes together forms the primary key of the new relation.
– Owners primary key acts as a foreign key referencing to the owner relation.
DeptLocations (DNo*, DLocation)
Dno is the primary key of the owner entity “Dept”.
DLocation was a multi-valued attribute within Dept Entiity.
Step 7: Map higher-degree relationships
– For higher-degree relationships, such as ternary relationships, create a new relation holding primary keys from all participating entities.
– They together forms the primary key of the new relation.
– Include (if any) simple attributes of the original relationship.
– Example:
Order (EmpNo*, ClientNo*, PartNo*, quantity)
A Special Case
– “SupervisedBy” is somewhat a different relationship.
– The both ends of this relationship is the same “Employee” entity.
– Such relationships are called recursive relationships.
– One “supervisor” employee can supervise a number of “direct reports, or supervised” employees. Each “direct reports, or supervised” employee is supervised by one “supervisor” employee. So, this is still a 1:N relationship.
– Therefore, we can apply same principle applied in Step 4. “Supervisor” employee is in the 1-side. So, supervisor SSN is copied as the foreign key and inserted to the same relation.
A Special Case
– “SupervisedBy” is somewhat a different relationship.
– The both ends of this relationship is the same “Employee” entity.
– Such relationships are called recursive relationships.
– One “supervisor” employee can supervise a number of “direct reports, or supervised” employees. Each “direct reports, or supervised” employee is supervised by one “supervisor” employee. So, this is still a 1:N relationship.
– Therefore, we can apply same principle applied in Step 4. “Supervisor” employee is in the 1-side. So, supervisor SSN is copied as the foreign key and inserted to the same relation.
Complete Relational Model
Employee (SSN, fname, minit, lname, bdate, address, gender, salary, DNo*, SuperSSN*)
Department (DNo, Dname, MGR_SSN*, MGR_StartDate) Project (Pnumber, Pname, Plocation, Dno*)
Dependent (ESSN*, Name, gender, bdate, relationship) DeptLocations (DNo*, DLocation)
WorksOn (ESSN*, Pno*, Hours)
An Exercise….
– Consider the “Students are enrolled in courses” scenario. Wk 4 Q 2.1 (b).
– It is not as simple as we originally thought.
– Will require some design choices.
Next week….
– We start our activities around learning SQL programming!