- Entities in the
ER-diagram:
- Entity in the
ER-diagram defines (= represents)
entity type
in the real world
- The entity in the
ER-diagram defines
the attributes (= properties)
of the entity type
- Each entity instance in the entity type has the same attributes
- Entity in the
ER-diagram defines (= represents)
entity type
in the real world
- Example: the
employee
entity
- The entity
employee in the
ER-diagram:
means:
- Every
employee has the
following attributes:
- SSN (key), address, bdate, sex, name (fname, minit, lname), and salary
- Every
employee has the
following attributes:
- The entity
employee in the
ER-diagram:
- Obvious fact:
- The employee entity is used to store information about the employees in the company
- Example:
representing
(= storing information) of
employees
- The following are
information in
2 employee instances of
the employee entity schema:
Name SSN BDate Address Sex Salary =============================================================================== John B Smith 123456789 09-JAN-55 731 Fondren, Houston, TX M 30000.00 James E Borg 888665555 10-NOV-27 450 Stone, Houston, TX M 55000.00
The following diagram shows that the information of these employees are represented by the employee entity schema in the ER-diagram:
- The following are
information in
2 employee instances of
the employee entity schema:
- The following is
a visualization on
how
the Department entity
is used to store the
information on
2 departments:
The 2 departments are:
- Administration (department name), whose department number is 4 and is located in NY, Miami and Houston
- Research (department name), whose department number is 5 and is located in Atlanta and Denver
- Relationships in the
ER-diagram:
- Relationships in the
ER-diagram defines (= represents)
connections between
2 or more entities in the
real world
- The relationship in the
ER-diagram contains
the key (= identifying) attributes
of each participating entity
- Each relationship instance represents a connection between the participating entities
- Relationships in the
ER-diagram defines (= represents)
connections between
2 or more entities in the
real world
- Example: the
works_for
relationship
- The relationship
works_for in the
ER-diagram:
means:
- Some
employee works for a
some department
- Because:
- An employee is
identified by
its key attribute SSN
- A department is identified by its key attribute depratment number
- An employee is
identified by
its key attribute SSN
- A "works_for" connection
(between employee and
department) is
represented as follows:
( SSN of employee, department number of department ) meaning: Employee with social security number `SSN' works for department 'department number'
- Some
employee works for a
some department
- The relationship
works_for in the
ER-diagram:
- Important fact:
- A relationship instance (= a "connection" between entities) is represented by identifying the entities that are involved
- Example:
representing
the works_for
relationship
- We have
2 employees in
the company:
Name SSN BDate Address Sex Salary =============================================================================== John B Smith 123456789 09-JAN-55 731 Fondren, Houston, TX M 30000.00 James E Borg 888665555 10-NOV-27 450 Stone, Houston, TX M 55000.00
We have 2 departments in the company:
dname dnumber locations ==================================================== Research 5 Atlanta, Denver Administration 4 NY, Miami, Houston
Suppose that:
- John B Smith works for the Research department
- James E Borg works for the Administration department
We can represent the "works-for" information as follows:
Note that we only use the keys in each entity schema to represent a relationship
(Other attributes are not necessary and when included will cause duplication of infomation !!!)
- We have
2 employees in
the company:
- Question 1:
- Why can't (or don't) we
include the
rest of the
information from
employee's or
department's
in the relationship ???
For example: the relationship Works_for include the names of the employee and department:
(123456789 John B Smith, 5 Research) (888665555 James E Borg, 4 Administration)
- Answer:
- Remember that
we also have the
Employee and
Department entities
in the ER-model:
and they store the information on employee and department !!!
- If you
include the
details of the
employee and
department
again inside
the Works-for
relation:
- You will store duplicate information on employees and departments !!!!
Duplicate information is hard to maintain (consistency) and must be avoided !!!
- Remember that
we also have the
Employee and
Department entities
in the ER-model:
- Why can't (or don't) we
include the
rest of the
information from
employee's or
department's
in the relationship ???
- Question 2:
- Why
can't we
use employee name
(and department name)
as relationship data ???
For example:
John B Smith Research James E Borg Administration
Answer:
- Names are
not guaranteed to be
unqiue
- SSN are always unqiue
- Names are
not guaranteed to be
unqiue
- Why
can't we
use employee name
(and department name)
as relationship data ???
- Can you figure out:
who (name) is working on
which project (name)
and for how many hours a week ???
Answer:
- John Smith works on the printer project for 5 hrs a week
- James Borg works on the printer project for 4 hrs a week
- John Smith works on the mouse project for 8 hrs a week