A Data Model is a set of concepts that can be used to describe the structure of a DB ( the Data Types, the Relationships and Constraints that must hold for the data).

• High Level, or Conceptual

ER Models, Functional Models

• Representational Models

Relational, Network, Object Oriented

• Low Level (Physical)

Storage systems (Data Structures, Access Paths)

A small company wants to store its data in a DB. You are hired to design the DB. The first step is Requirements Collection:

• The company is organized into departments. Each department has a name, number, and a manager. Each department may be located in one or more places. We also keep track of the start date of the department manager.

• Each department controls a number of projects. Each project is has a codename, a number, and is located in a single place.

• We store the employee information. This includes their social security number, address, salary, sex, and birth date. Each employee works for one department, but may work on many projects. We allocate the number of hours an employee works on a given project per week. We also keep track of the direct supervisor of each employee.

• Each employee may have a number of dependents. For each dependent, we store information about where they work, name, sex, birth date, and relationship to the employee.

Question: How do we go from this information to creating a Database ?

Answer: By first creating the ER Model:

We shall define the steps of this construction as we go along in this lecture.

1. Entities are specific objects or things in the Universe of Discourse. Examples are: Employees, Departments, and Projects that we are keeping information about.

2. We would like to store some information about each entity. This information is in the form of properties that define the entity. These descriptors are called Attributes. Example: Employees have attributes such as SSN (social security number), Sex etc.

3. In ER Models, each entity must have a value for each of its Attributes.

The figures below show examples of typical entities, their attributes, and possible attribute values.

Attributes can be of different types:

1. Simple

Each entity has a single, ATOMIC value (cannot be sub-divided into two or more meaningful parts).

Examples: SSN, CourseNumber.

 

2. Composite

The attribute is composed of several components.

 

Example: Address

 

 



 

 

3. Multi-valued

An entity may have multiple values for the same attribute.

 

Examples: Color of a car, High School attended in an ARR record.

 

4. Derived

The value of some attributes can be determined by the values of some other attributes. We shall see examples of this later.

Entities with the same attributes are grouped together in an entity type. For example, if all employees of a firm have the same attributes, then Employee can be an entity type.

An attribute of an entity type for which the entity must have a UNIQUE value is called a KEY ATTRIBUTE.

Example: Vehicle ID for a car.

A key attribute can be composite.

Example: Car-Registration( License Plate Number, State)

An entity type may have more than one key.

Example: The entity CAR has two key attributes, VehicleID, and Registration( License Plate Number, State).

1. A relationship relates two or more distinct entities with a specific meaning. For example: James Wong manages the Research department (what are the entities types here ? What is the relation type ?)

 

2. Relationships of the same type are grouped into a relationship type. For example: "Employee X works on project Y" defines a relation. Here, works-on is a relationship type, in which entity types Employee and Project participate.

 

3. The degree of the relationship type is the number of participating entity types.

 

4. More than one relationship type can exist between same participating entity types. For example: Employee X manages Project Y, and Employee E works-on Project P.

1. Relationships can have their own attributes. For example, in the figure below, works-on can have an attribute hours-per-week, describing the number of hours an employee works on a project per week.

 



 

2. Roles

A relationship can relate two entities of the same entity type. For example: Employee X supervises Employee Y.

 



 

 

3. Structural constraints on Relationships

1. Cardinality ratio (for binary relationships). A binary relationship can be 1:1, 1:N, N:1, or N:M

 

 

2. Participation constraint: A participation constraint can be specified on each participating entity of a relationship type. If the Entity has total participation constraint, then every instance of the entity type must exist in the relationship. For example: a company may require that every Employee must work for at least one department. If this is so, we say that that the relation works-for has a total participation constraint on Employee.

1. Weak Entity Types

Based on the above definitions, we can now draw ER diagrams for any given database situation. To do so, we use the following graphical notation.

Using this notation, if we draw the ER diagram for our company example, we should get something like the following:

Relationships of degree two are also called binary relations.

It is possible, though not frequent, to have relations of higher degrees, such as three (ternary relations). In general, a relationship of degree n is called an n-ary relationship type.

Below is an example of a ternary relationship:

Here, the relationship type SUPPLY implies that a supplier, s, supplies some quantity of part, p, to a project, j.

One may ask: do we need n-ary relationships at all, or is it possible to represent any n-ary relationship as a set of binary relationships ? The answer is that in general, this may not be straightforward, or even possible. For example, in the above case, we could relate the entities by means of three binary relationships as shown below:

Here, the three relations are described as follows:

USES( j, p) implies project j uses part p;

SUPPLIES( j, s) implies supplier s supplies some part to project j;

CAN_SUPPLY( s, p) implies that supplier s can supply part p.

However, the semantics (meaning) of this ER diagram is not quite the same as that of the ternary SUPPLY relationship of the previous figure. For example, there may be three pairs, (s1, p1), (j1, s1) and (j1, p1), but this does NOT necessarily imply that there exists an instance of type SUPPLY( s1, p1, j1). (Why ?).