Chapter Three

 Database Modeling

Daniel Tesfay
Introduction to ODL

o ODL(Object Definition Language) is intended to define object

types that can be implemented in a variety of programming
languages.

o This language's purpose is to define the structure of an Entity-

relationship diagram.
ER model

₋ The ER model describes data as entities, relationships, and attributes.

₋ The basic object that the ER model represents is an entity, which is a "thing"
in the real world with an independent existence. Example: employee
₋ Each entity has attributes the particular properties that describe it. For
example, an employee entity may be described by the employee's name, age,
address, salary, and job.
₋ A particular entity will have a value for each of its attributes.

3
Types of Attributes

o Composite versus Simple (Atomic) Attributes. Composite attributes can

be divided into smaller subparts, which represent more basic attributes
with independent meanings. Attributes that are not divisible are called
simple or atomic attributes. The value of a composite attribute is the
concatenation of the values of its constituent simple attributes.

o Single-Valued versus Multi-valued Attributes. Most attributes have a

single value for a particular entity; such attributes are called single-valued.
4
 Types of Attributes

o Stored versus Derived Attributes. For a particular person entity, the value of Age

can be determined from the current (today's) date and the value of that person's
Birth Date. The Age attribute is hence called a derived attribute and is said to be
derivable from the BirthDate attribute, which is called a stored attribute.
o Null Values. In some cases a particular entity may not have an applicable value for an

attribute. For example, a person with no college degree would have null for
College_Degrees.
o Complex Attributes. Is a result of composite and multi-valued attributes which are
5
nested in an arbitrary way
Example Company
Database
o We need to create a database schema design based on the following

(simplified) requirements of the COMPANY Database:

₋ The company is organized into DEPARTMENTs. Each department has a name,

number and an employee who manages the department. We keep track of the
start date of the department manager. A department may have several
locations.

₋ Each department controls a number of PROJECTs. Each project has a unique

name, unique number and is located at a single location.
Example Company Database (Contd.)

o We store each EMPLOYEE’s social security number, address, salary, sex, and
birthdate.
₋ Each employee works for one department but may work on several projects.
₋ We keep track of the number of hours per week that an employee currently
works on each project.
₋ We also keep track of the direct supervisor of each employee.
o Each employee may have a number of DEPENDENTs.
₋ For each dependent, we keep track of their name, sex, birthdate, and
relationship to the employee.
Entities and Attributes

o Entities are specific objects or things in real-world that are represented in the database.
₋ For example the EMPLOYEE Abebe Kebede, the Research DEPARTMENT, the ProductX
PROJECT
o Attributes are properties used to describe an entity.
₋ For example an EMPLOYEE entity may have the attributes Name, SSN, Address, Sex, BirthDate
o A specific entity will have a value for each of its attributes.
₋ For example a specific employee entity may have Name=‘Abebe Kebede', SSN='123456789',
Address =‘Hawassa, Tabor kifle ketema, kebele ___', Sex='M', BirthDate='09-JAN-55‘
o Each attribute has a value set (or data type) associated with it – e.g. integer, string, subrange,
enumerated type, …
Entity Types and Key
Attributes
o Entities with the same basic attributes are grouped or typed into an entity
type.
₋ For example, the entity type EMPLOYEE and PROJECT.
o An attribute of an entity type for which each entity must have a unique value
is called a key attribute of the entity type.
₋ For example, SSN of EMPLOYEE.
Entity Types and Key
Attributes
o A key attribute may be composite.
₋ VehicleTagNumber is a key for the CAR entity type with components
(Number, State).
o An entity type may have more than one key.
₋ The CAR entity type may have two keys:
• VehicleIdentificationNumber (popularly called VIN)
• VehicleTagNumber (Number, State), aka license plate number.

o Each key is underlined

 Entity Types and Key Attributes
o An entity type may also have no key, in which case it is called a weak entity type.
o Value Sets (Domains) of Attributes. Each simple attribute of an entity type is
associated with a value set (or domain of values), which specifies the set of values
that may be assigned to that attribute for each individual entity. Eg. If the range of
ages allowed for employees is between 16 and 70, we can specify the value set of
the Age attribute of EMPLOYEE to be the set of integer numbers between 16 and
70.
o Value sets are not displayed in ER diagrams. Value sets are typically specified
using the basic data types available in most programming languages, such as
11
integer, string, boolean, float, enumerated type, and so on.
ER Diagram Notations

12
ER Diagram
Relationships and Relationship Types

₋ A relationship relates two or more distinct entities with a specific meaning. For example,
EMPLOYEE Abebe Kebede works on the ProductX PROJECT or EMPLOYEE Zinash
Solomon manages the Research DEPARTMENT.
₋ Relationships of the same type are grouped or typed into a relationship type. For example, the
WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate, or the
MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs participate.
₋ The degree of a relationship type is the number of participating entity types. Both MANAGES
and WORKS_ON are binary relationships. Relationship types of degree 2 are called binary.
Relationship types of degree 3 are called ternary and of degree n are called n-ary
Role Names and Recursive Relationships

o The role name signifies the role that a participating entity from the entity type plays in each

relationship instance, and helps to explain what the relationship means. For example, in the

WORKS_FOR relationship type, EMPLOYEE plays the role of employee or worker and

DEPARTMENT plays the role of department or employer.

o In some cases the same entity type participates more than once in a relationship type in different roles.

Such relationship types are called recursive relationships. The SUPERVISION relationship type

relates an employee to a supervisor, where both employee and supervisor entities are members of the

same EMPLOYEE entity type. Hence, the EMPLOYEE entity type participates twice in

SUPERVISION: once in the role of supervisor (or boss), and once in the role of supervisee (or

subordinate). 15
Attributes of Relationship Types

o Relationship types can also have attributes, similar to those of

entity types.

o For many relationship types, some attributes may be determined

by the combination of participating entities in a relationship
instance, not by any single entity. Such attributes must be
specified as relationship attributes.

16
Refining the Company database schema by introducing
relationships

o By examining the requirements, six relationship types are identified

o All are binary relationships( degree 2)
o Listed below with their participating entity types:
₋ WORKS_FOR (between EMPLOYEE, DEPARTMENT)
₋ MANAGES (also between EMPLOYEE, DEPARTMENT)
₋ CONTROLS (between DEPARTMENT, PROJECT)
₋ WORKS_ON (between EMPLOYEE, PROJECT)
₋ SUPERVISION (between EMPLOYEE (as subordinate), EMPLOYEE (as supervisor))
₋ DEPENDENTS_OF (between EMPLOYEE, DEPENDENT)
 Cont…
o In the refined design, some attributes from the initial entity types are refined into
relationships:
₋ Manager of DEPARTMENT -> MANAGES
₋ Works_on of EMPLOYEE -> WORKS_ON
₋ Department of EMPLOYEE -> WORKS_FOR, etc
o In general, more than one relationship type can exist between the same
participating entity types
₋ MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and
DEPARTMENT
₋ Different meanings and different relationship instances.
Recursive Relationship
Type
o An relationship type whose with the same participating entity type in
distinct roles
₋ Example: the SUPERVISION relationship

o EMPLOYEE participates twice in two distinct roles:

₋ supervisor (or boss) role
₋ supervisee (or subordinate) role
o Each relationship instance relates two distinct EMPLOYEE entities:
₋ One employee in supervisor role
₋ One employee in supervisee role
Notation for Constraints on Relationships

o Cardinality ratio (of a binary relationship): 1:1, 1:N, N:1, or M:N

₋ SHOWN BY PLACING APPROPRIATE NUMBERS ON THE RELATIONSHIP EDGES.
o Participation constraint (on each participating entity type): total (called
existence dependency) or partial.
₋ TOTAL SHOWN BY DOUBLE LINE, PARTIAL BY SINGLE LINE.
o Alternative (min, max) notation
₋ Specifies that each entity e in E participates in at least min and at most max
relationship instances in R
₋ Default(no constraint): min=0, max=n
o NOTE: These are easy to specify for Binary Relationship Types.
Company ER Schema Diagram
using (min, max) notation
Constraints on Relationships
o Must have minmax, min0, max 1. example
₋ A department has exactly one manager and an employee can manage at most one
department.
• Specify (0,1) for participation of EMPLOYEE in MANAGES
• Specify (1,1) for participation of DEPARTMENT in MANAGES
₋ An employee can work for exactly one department but a department can have any
number of employees.
• Specify (1,1) for participation of EMPLOYEE in WORKS_FOR
• Specify (1,n) for participation of DEPARTMENT in WORKS_FOR
Problem with ER modeling tools

o DIAGRAMMING
₋ Poor conceptual meaningful notation.
₋ To avoid the problem of layout algorithms and aesthetics of diagrams, they prefer boxes and lines
and do nothing more than represent (primary-foreign key) relationships among resulting tables.(a
few exceptions)
o METHODOLGY
₋ lack of built-in methodology support.
₋ poor tradeoff analysis or user-driven design preferences.
₋ poor design verification and suggestions for improvement.
o The entity relationship model in its original form did not support the
specialization/generalization abstractions
Solution: EER (Enhanced-ER model)
23
EER Modeling concepts
o Includes all modeling concepts of basic ER
o classes (similar to entity types) as large rounded boxes with three sections:
₋ Top section includes entity type (class) name
₋ Second section includes attributes
₋ Third section includes class operations (operations are not in basic ER model)
o Relationships (called associations) represented as lines connecting the classes
₋ Other UML terminology also differs from ER terminology
o Used in database design and object-oriented software design
o Additional concepts: subclasses/superclasses, specialization/ generalization,
categories, attribute inheritance
o It includes some object-oriented concepts, such as inheritance
Specialization
₋ Is the process of defining a set of subclasses of a superclass
₋ The set of subclasses is based upon some distinguishing characteristics of
the entities in the superclass
₋ Example: {SECRETARY, ENGINEER, TECHNICIAN} is a specialization of
EMPLOYEE based upon job type.
• May have several specializations of the same superclass
o Generalization
₋ The reverse of the specialization process
₋ Several classes with common features are generalized into a superclass;
original classes become its subclasses
₋ Example: CAR, TRUCK generalized into VEHICLE; both CAR, TRUCK become
subclasses of the superclass VEHICLE.

25
Specialization and Generalization
o Eg. all person entities represented in the database are members
of the PERSON entity type, which is specialized into the subclasses
{EMPLOYEE, ALUMNUS, STUDENT}. This specialization is
overlapping; for example, an alumnus may also be an employee
and may also be a student pursuing an advanced degree. The
subclass STUDENT is the superclass for the specialization
{GRADUATE_STUDENT, UNDERGRADUATE_STUDENT}, while
EMPLOYEE is the superclass for the specialization
{STUDENT_ASSISTANT, FACULTY, STAFF}. Notice that
STUDENT_ASSISTANT is also a subclass of STUDENT. Finally,
STUDENT_ASSISTANT is the superclass for the specialization into
{RESEARCH_ASSISTANT, TEACHING_ASSISTANT}
Specialization /
Generalization
Framework Example
(UNIVERSITY)
UML class diagram for COMPANY database
schema
Constraints and Characteristics of
Specialization and Generalization

o the disjointness (or disjointedness) constraint, which specifies that

the subclasses of the specialization must be disjoint. If the subclasses
are not constrained to be disjoint, their sets of entities may be
overlapping; that is, the same (real-world) entity may be a member of
more than one subclass of the specialization.
o the completeness (or totalness) constraint, which may be total or
partial. A total specialization constraint specifies that every entity in
the superclass must be a member of at least one subclass in the
specialization. A single line is used to display a partial specialization,
which allows an entity not to belong to any of the subclasses.
Constraints and Characteristics of Specialization
and Generalization

o A subclass itself may have further subclasses

o Deleting an entity from a superclass implies that it is automatically deleted from all
the subclasses to which it belongs.
o Inserting an entity in a superclass implies that the entity is mandatorily inserted in
all predicate-defined (or attribute-defined) subclasses for which the entity satisfies
the defining predicate.
o Inserting an entity in a superclass of a total specialization implies that the entity is
mandatorily inserted in at least one of the subclasses of the specialization.
ER Diagram for Exercise 1
Lab Exercises
Instructions

o In the next three slides, you will find the conceptual database designs for

various departments, including Company Database, Sales Database, and

University Database. Based on these conceptual database designs, please
complete the following tasks:
1. Create the Relational Schema for each conceptual ER model.

2. Use ER/Studio to design the logical model.

3. Generate the Physical Model from the logical model.

4. Create the Database from the physical model by connecting to your database engine.
Company Database Conceptual Schema
Exercise: SalesDB
Database
o The SalesDB system is intended to manage sales transactions in a car dealership. The system must keep track of the

following:
o Customer Information:

1. The system should allow us to store details of customers who purchase cars, including their full name, phone number,

and email.

2. Customers may purchase multiple cars over time, so the system must associate each customer with their purchase

history.
o Car Inventory:

1. The system must maintain a record of all cars available for sale, including the car's make, model, year, and price.

2. Once a car is sold, it should be linked to the corresponding sales record and removed from the available inventory.
Exercise: SalesDB Database
conn..
1. Employee Records:
1. The system should track employees responsible for processing car sales. This includes storing

employee names, their position in the dealership, and contact details.

2. Each invoice must be linked to the employee who handled the transaction.

2. Invoices:
1. Every sale must generate an invoice that includes the date of purchase, the total amount, the

customer who purchased the car, the employee who handled the transaction, and the specific car

that was sold.

2. The system should ensure that each car sale is associated with exactly one invoice.
SalesDB Database Conceptual Schema
Exercise to University
Database
o End-User Description for a University Database System:

o The University Database System is designed to manage and

streamline academic and administrative processes for the
university. It provides functionalities to store and retrieve data
related to colleges, departments, instructors, courses, sections, and
students. Below is a description of the key components and their
functionalities:
University Database Conceptual Schema
Thank You!