CS457 Syllabus & Progress

Example Design: Company Conceptual Model

Discovering the information need of all users

Fact:

The Entity-Relationship approach to discovering the information need of its users:

Identify the all independent entities (= agents) in the database model
Identify the relationships betweens each entity

The Entity-Relationship design technique

Overview:

We gather information need from all users through an exploratory interview process
Using the gathered information, we identify the entities (= important agents) in the database
Then using the gathered information, we identify the relationships (= interactions) between the entiies

We will illustrate the Entity-Relationship model design technique using an example next....

Information on the company's information need:

Below, you will find a list of fact about a company (this example is taken directly from the text book)
NOTE: in real life, these information are gathered by interviewing the users of the database.

Information on the company:

The company is organized into departments
Each department has a unique name, a unique number, and is managed by one employee
Company keeps track of the start date when that employee began managing the department (e.g., for bonus reward purposes)
A department may have several locations (i.e., spread out)
A department controls a number of projects, each of which has a unique name, a unique number, and a single location (where the project is performed)
Company maintains each employee's name, social security number (SSN), address, salary, sex, and birth date.
An employee is assigned to ("works for") one department but may work on several projects, which are not necessarily controlled by the same department (that the employee is assigned to).
Company maintains the number of hours per week that an employee works on each of his/her projects.
Each employee has one direct supervisor (which is also an employee of the company.
Company keeps information of the dependents of the employee (e.g., for benefit calculation purpose), but the information on dependents are less detailed than those for employees.
Company stores for each dependent: the first name, sex, birth date and the relationship to the employee.

OK, how do we discern entity types (or entities for short) in this description ?

Identifying an entity

Property of an entity:

An entity in a database is a VIT (= very important thing)

How to spot an entity:

Something that has a key attribute is always an entity
(It's something important that we want to identify it uniquely)
Something that has multiple related attributes is often (usually) an entity

Step 1 in the ER design process: Identify the entities

I have highlighted some clues in the description to help you spot the entities in the company database:

The company is organized into departments
Each department has a unique name, a unique number, and is managed by one employee
Company keeps track of the start date when that employee began managing the department (e.g., for bonus reward purposes)
A department may have several locations (i.e., spread out)
A department controls a number of projects, each of which has a unique name, a unique number, and a single location (where the project is performed)
Company maintains each employee's name, social security number (SSN), address, salary, sex, and birth date.
Note for foreign students:
An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department (that the employee is assigned to).
Company maintains the number of hours per week that an employee works on each of his/her projects.
Each employee has one direct supervisor (which is also an employee of the company).
Company keeps information of the dependents of the employee (e.g., for benefit calculation purpose), but the information on dependents are less detailed than those for employees.
Company stores for each dependent: the first name, sex, birth date and the relationship to the employee.
Assumption:

You should arrive at the following set of "entities":

Step 2 in the ER design process: Identify the properties/attributes of entities

Comment:

From the description above we can distinguish the following attributes:

Departments (have)
- Department Name (key attribute) [from 2]
- Department number (key attribute) [from 2]
- Managed by an employee [from 2]
- Manager start date (simple attribute) [from 3]
- Locations (multi-valued attribute) [from 4]
- Controls projects (multi-valued attribute) [from 5]
Entity definition:
{...} means multi-valued attribute
Projects (have)
- Project Name (key attribute) [from 5]
- Project number (key attribute) [from 5]
- Location where project is performed (simple) [from 5]
Entity definition:
Employees (have)
- Employee Name (compound attribute - FName, MI, LName) [from 6]
- SSN (key attribute) [from 6]
- Address (compound attribute) [from 6]
- Salary (simple attribute) [from 6]
- Sex (simple attribute) [from 6]
- Birth Date (simple attribute) [from 6]
- Works for a Department (simple) [from 7]
- Works on Projects (multi-valued composite) {(Project, Hours)} [from 7 and 8]
- Has a Supervisor (simple) [from 9]
- Has Dependents (multiple) [from 10]
Entity definition:
Dependents (have)
- First Name (simple attribute) [from 11]
- Sex (simple attribute) [from 11]
- Birth Date (simple attribute) [from 11]
- Relationship to employee (simple attribute) [from 11]
Entity definition:

Summary: the entity schemas found

Department( DName, DNumber, Managed-by(Employee, StartDate), {Locations}, Controls({Projects}) ) Project(PName, PNumber, Location) Employee(SSN, Name, Addr, Sal, Sex, BDate, Works-for(Department), Works-on({Proj, #hours}), Supervised-by(Employee), Cares-for({Dependent})) Dependent(FName, Sex, BDate, RelationToEmp) (Underlined attributes are key attributes)

Note:

Some of the above items in the entity schemas (the things that are highlighted in yellow) are not attributes but relationships !
We will discuss the relationship concept later and we will then correct it....
Notice that the entity Depedent has no key (underlined) !!!

Weak entity

Consider the entities that we have identified:

Important observation:

The entity Dependent does not have a key attribute !!!
In other words:

Example: suppose employees John and Kate has a daughter and coincidentally have the same name and bdate:

Employee John's dependent: Mary, F, 1-1-2000, daughter Employee Kate's dependent: Mary, F, 1-1-2000, daughter

Weak entity:

Comment:

A weak enity will pose problems for the database system !!!
Later (when we discuss relationships), we will learn how to distinguish 2 weak entity instances that have the same attribute values

Step 3: Determining the Relationships --- will be discussed in next webpage

The final step in the ER-modeling design is:

Identify the relationships between entities
What we need to do is:
(Remember I said previously that some attributes in the entity schema that we made are not really attributes ---- they are relationships)

Attribute:
Not an attribute:
Such "attributes" are not really attributes, but they are in fact:
Whenever an attribute in an entity refers to another entity, there exists a relationship between the referencing and referenced entities
Such an attribute can be "embedded" in either relation (e.g., Department's "controlled projects" and Projects's "controlling department" represent the same relationship - but from different perspective).
Rather than "embedding relationships as attributes", the relationships are made explicit in the ER-modelling technique
We will look at how to make relationships explicit in notes on a separate webpage....