INSTITUT D’ENSEIGNEMENT SUPÉRIEUR DE

RUHENGERI-INES-RUHENGERI

INSTITUTE OF APPLIED SCIENCES

FACULTY OF APPLIED FUNDAMENTAL SCIENCES (AFS)

DEPARTMENT OF COMPUTER SCIENCE(DCS)

Accredited by Ministerial Order N° 005/2010/MINEDUC of 16 June 2010

B.P. 155, Ruhengeri, Rwanda,
Tel.: +250 788 90 30 30, +250 788 90 30 32,
Email: inesruhengeri@yahoo.fr,
Website: www.ines.ac.rw

DATABASE MANAGEMENT SYSTEMS

Lecturer: NSENGIYUMVA Emmanuel
Computer Science Department, Level 2
31-May-2022

`
INDICATIVE CONTENT

Introduction: Concept & Overview of DBMS, Data Models, Database Languages, Database
Administrator, Database Users, Three Schema architecture of DBMS.

Entity-Relationship Model: Basic concepts, Design Issues, Mapping Constraints, Keys, Entity-
Relationship Diagram, Weak Entity Sets, Extended E-R features.

Relational Model: Structure of relational Databases, Relational Algebra, Relational Calculus,

Extended Relational Algebra Operations, Views, Modifications of the Database.

SQL and Integrity Constraints: Concept of DDL, DML, DCL. Basic Structure, Set operations,
Aggregate Functions, Null Values, Domain Constraints, Referential Integrity Constraints,
assertions, views, Nested Sub queries, Database security application development using SQL,
Stored procedures and triggers.

Relational Database Design : Functional Dependency, Different anomalies in designing a

Database., Normalization using functional dependencies, Decomposition, Boyce-Codd Normal
Form, 3NF, Normalization using multi-valued dependencies, 4NF, 5NF

Page |2
UNIT 2:

ENTITY-RELATIONSHIP MODEL:

BASIC CONCEPTS,
An Entity–relationship model (ER model) describes the structure of a database with the help of a
diagram, which is known as Entity Relationship Diagram (ER Diagram). An ER model is a
design or blueprint of a database that can later be implemented as a database. The main
components of E-R model are: entity set and relationship set.

What is an Entity Relationship Diagram (ER Diagram)?

An ER diagram shows the relationship among entity sets. An entity set is a group of similar
entities and these entities can have attributes. In terms of DBMS, an entity is a table or attribute
of a table in database, so by showing relationship among tables and their attributes, ER diagram
shows the complete logical structure of a database. Lets have a look at a simple ER diagram to
understand this concept.

A simple ER Diagram:

In the following diagram we have two entities Student and College and their relationship. The
relationship between Student and College is many to one as a college can have many students
however a student cannot study in multiple colleges at the same time. Student entity has
attributes such as Stu_Id, Stu_Name & Stu_Addr and College entity has attributes such as

Page |3
Col_ID & Col_Name.

Here are the geometric shapes and their meaning in an E-R Diagram. We will discuss these terms
in detail in the next section(Components of a ER Diagram) of this guide so don’t worry too much
about these terms now, just go through them once.

Rectangle: Represents Entity sets.

Ellipses: Attributes
Diamonds: Relationship Set
Lines: They link attributes to Entity Sets and Entity sets to Relationship Set
Double Ellipses: Multivalued Attributes
Dashed Ellipses: Derived Attributes
Double Rectangles: Weak Entity Sets
Double Lines: Total participation of an entity in a relationship set

Components of a ER Diagram

Page |4
As shown in the above diagram, an ER diagram has three main components:
1. Entity
2. Attribute
3. Relationship

1. Entity

An entity is an object or component of data. An entity is represented as rectangle in an ER

diagram.
For example: In the following ER diagram we have two entities Student and College and these
two entities have many to one relationship as many students study in a single college. We will
read more about relationships later, for now focus on entities.

Page |5
Weak Entity:
An entity that cannot be uniquely identified by its own attributes and relies on the relationship
with other entity is called weak entity. The weak entity is represented by a double rectangle. For
example – a bank account cannot be uniquely identified without knowing the bank to which the
account belongs, so bank account is a weak entity.

2. Attribute

An attribute describes the property of an entity. An attribute is represented as Oval in an ER

diagram. There are four types of attributes:

1. Key attribute
2. Composite attribute
3. Multivalued attribute
4. Derived attribute

1. Key attribute:

Page |6
A key attribute can uniquely identify an entity from an entity set. For example, student roll
number can uniquely identify a student from a set of students. Key attribute is represented by
oval same as other attributes however the text of key attribute is underlined.

2. Composite attribute:

An attribute that is a combination of other attributes is known as composite attribute. For

example, In student entity, the student address is a composite attribute as an address is composed
of other attributes such as pin code, state, country.

Page |7
3. Multivalued attribute:

An attribute that can hold multiple values is known as multivalued attribute. It is represented
with double ovals in an ER Diagram. For example – A person can have more than one phone
numbers so the phone number attribute is multivalued.

4. Derived attribute:

A derived attribute is one whose value is dynamic and derived from another attribute. It is
represented by dashed oval in an ER Diagram. For example – Person age is a derived attribute as
it changes over time and can be derived from another attribute (Date of birth).

E-R diagram with multivalued and derived attributes:

ENTITY-SET AND KEYS

Keys are very important part of Relational database model. They are used to establish and
identify relationships between tables and also to uniquely identify any record or row of data
inside a table.

A Key can be a single attribute or a group of attributes, where the combination may act as a key.

To avoid all this, Keys are defined to easily identify any row of data in a table.

Let's try to understand about all the keys using a simple example.

Page |8
student_id name phone age

1 Akon 9876723452 17

2 Akon 9991165674 19

3 Bkon 7898756543 18

4 Ckon 8987867898 19

5 Dkon 9990080080 17

Super Key

Super Key is defined as a set of attributes within a table that can uniquely identify each record
within a table. Super Key is a superset of Candidate key.

In the table defined above super key would include student_id, (student_id, name), phone etc.

Confused? The first one is pretty simple as student_id is unique for every row of data, hence it
can be used to identity each row uniquely.

Next comes, (student_id, name), now name of two students can be same, but
their student_id can't be same hence this combination can also be a key.

Similarly, phone number for every student will be unique, hence again, phone can also be a key.

So they all are super keys.

Page |9
Candidate Key

Candidate keys are defined as the minimal set of fields which can uniquely identify each record
in a table. It is an attribute or a set of attributes that can act as a Primary Key for a table to
uniquely identify each record in that table. There can be more than one candidate key.

In our example, student_id and phone both are candidate keys for table Student.

 A candiate key can never be NULL or empty. And its value should be unique.
 There can be more than one candidate keys for a table.
 A candidate key can be a combination of more than one columns(attributes).

Primary Key

Primary key is a candidate key that is most appropriate to become the main key for any table. It
is a key that can uniquely identify each record in a table.

For the table Student we can make the student_id column as the primary key.

P a g e | 10
Composite Key

Key that consists of two or more attributes that uniquely identify any record in a table is
called Composite key. But the attributes which together form the Composite key are not a key
independentely or individually.

In the above picture we have a Score table which stores the marks scored by a student in a
particular subject.

In this table student_id and subject_id together will form the primary key, hence it is a composite
key.

Secondary or Alternative key

The candidate key which are not selected as primary key are known as secondary keys or
alternative keys.

P a g e | 11
Non-key Attributes

Non-key attributes are the attributes or fields of a table, other than candidate
key attributes/fields in a table.

RELATIONSHIP

The association among entities is called a relationship. For example, an employee works_at a
department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships.

RELATIONSHIP SET

A set of relationships of similar type is called a relationship set. Like entities, a relationship too
can have attributes. These attributes are called descriptive attributes.

DEGREE OF RELATIONSHIP

The number of participating entities in a relationship defines the degree of the relationship.

 Binary = degree 2

 Ternary = degree 3

 n-ary = degree

MAPPING CARDINALITIES

Cardinality defines the number of entities in one entity set, which can be associated with the
number of entities of other set via relationship set.

 One-to-one − One entity from entity set A can be associated with at most one entity of
entity set B and vice versa.

P a g e | 12
  One-to-many − One entity from entity set A can be associated with more than one
entities of entity set B however an entity from entity set B, can be associated with at
most one entity.

 Many-to-one − More than one entities from entity set A can be associated with at most
one entity of entity set B, however an entity from entity set B can be associated with
more than one entity from entity set A.

P a g e | 13
  Many-to-many − One entity from A can be associated with more than one entity from B
and vice versa.

ER Diagram Representation

Let us now learn how the ER Model is represented by means of an ER diagram. Any object, for
example, entities, attributes of an entity, relationship sets, and attributes of relationship sets, can
be represented with the help of an ER diagram.

ENTITY

Entities are represented by means of rectangles. Rectangles are named with the entity set they
represent.

P a g e | 14
ATTRIBUTES

Attributes are the properties of entities. Attributes are represented by means of ellipses. Every
ellipse represents one attribute and is directly connected to its entity (rectangle).

If the attributes are composite, they are further divided in a tree like structure. Every node is
then connected to its attribute. That is, composite attributes are represented by ellipses that are
connected with an ellipse.

Multivalued attributes are depicted by double ellipse.

P a g e | 15
Derived attributes are depicted by dashed ellipse.

P a g e | 16
Kinds of Entities

You should also be familiar with different kinds of entities including independent entities,
dependent entities and characteristic entities. These are described below.

Independent entities

Independent entities, also referred to as kernels, are the backbone of the database. They
are what other tables are based on. Kernels have the following characteristics:

 They are the building blocks of a database.

 The primary key may be simple or composite.

 The primary key is not a foreign key.

 They do not depend on another entity for their existence.

If we refer back to our COMPANY database, examples of an independent entity include the
Customer table, Employee table or Product table.

Dependent entities

Dependent entities, also referred to as derived entities, depend on other tables for their
meaning. These entities have the following characteristics:

 Dependent entities are used to connect two kernels together.

 They are said to be existence dependent on two or more tables.
 Many to many relationships become associative tables with at least two foreign keys.
 They may contain other attributes.
 The foreign key identifies each associated table.

P a g e | 17
 There are three options for the primary key:

 Use a composite of foreign keys of associated tables if unique

 Use a composite of foreign keys and a qualifying column
 Create a new simple primary key

Characteristic entities

Characteristic entities provide more information about another table. These entities have the
following characteristics:

 They represent multivalued attributes.

 They describe other entities.
 They typically have a one to many relationship.
 The foreign key is used to further identify the characterized table.

Options for primary key are as follows:

 Use a composite of foreign key plus a qualifying column

 Create a new simple primary key. In the COMPANY database, these might include:

-Employee (EID, Name, Address, Age, Salary) – EID is the simple primary key.

-EmployeePhone (EID, Phone) – EID is part of a composite primary key. Here, EID is also a
foreign key.

Extended Entity-Relationship (EE-R) Model

Or Enhanced Entity Relationship Model (EER Model)

EER MODEL

EER is a high-level data model that incorporates the extensions to the original ER model.

P a g e | 18
These concepts are used when the comes in EER schema and the resulting schema diagrams
called as EER Diagrams.

Features of EER Model

 EER creates a design more accurate to database schemas.

 It reflects the data properties and constraints more precisely.

 It includes all modeling concepts of the ER model.

 Diagrammatic technique helps for displaying the EER schema.

 It includes the concept of specialization and generalization.

It is used to represent a collection of objects that is union of objects of different of different

entity types.

A. Sub Class and Super Class

Sub class and Super class relationship leads the concept of Inheritance.

The relationship between sub class and super class is denoted with symbol.

1. Super Class
Super class is an entity type that has a relationship with one or more subtypes.
An entity cannot exist in database merely by being member of any super class.
For example: Shape super class is having sub groups as Square, Circle, Triangle.

2. Sub Class
 Sub class is a group of entities with unique attributes.
 Sub class inherits properties and attributes from its super class.
For example: Square, Circle, Triangle are the sub class of Shape super class.

P a g e | 19
B. Specialization and Generalization

1. Generalization
Generalization is the process of generalizing the entities which contain the properties of all the
generalized entities.

It is a bottom approach, in which two lower level entities combine to form a higher level entity.
 Generalization is the reverse process of Specialization.
 It defines a general entity type from a set of specialized entity type.
 It minimizes the difference between the entities by identifying the common features.

For example:

In the above example, Tiger, Lion, Elephant can all be generalized as Animals.
2. Specialization
Specialization is a process that defines a group entities which is divided into sub groups based
on their characteristic.

P a g e | 20
It is a top down approach, in which one higher entity can be broken down into two lower level
entity.
It maximizes the difference between the members of an entity by identifying the unique
characteristic or attributes of each member.
It defines one or more sub class for the super class and also forms the superclass/subclass
relationship.
For example

In the above example, Employee can be specialized as Developer or Tester, based on what role
they play in an Organization.

C. Category or Union
Category represents a single super class or sub class relationship with more than one super
class.
It can be a total or partial participation.
For example Car booking, Car owner can be a person, a bank (holds a possession on a Car) or a
company. Category (sub class) → Owner is a subset of the union of the three super classes →
Company, Bank, and Person. A Category member must exist in at least one of its super classes.

P a g e | 21
D. Aggregation
Aggregation is a process that represent a relationship between a whole object and its component
parts.
It abstracts a relationship between objects and viewing the relationship as an object.
It is a process when two entity is treated as a single entity.

P a g e | 22