Relational DBMS

 Database Management System (DBMS): is a software that used to define, create and maintain database and

provides control access to the data.

 Relational Database Management System (RDBMS): it is an advanced version or extension of a DBMS.

 A relational database management system (RDBMS) is a collection of programs and capabilities that enable IT

teams and others to create, update, administer and otherwise interact with a relational database.

 RDBMSes store data in the form of tables, with most commercial relational database management systems

using Structured Query Language (SQL) to access the database.

 The RDBMS is the most popular database system among organizations across the world.
 Conti…
Differences between DBMS and RDBMS include:

 Database structure: In a DBMS, data is kept in a hierarchical form, whereas an RDBMS utilizes a table where the

headers are used as column names and the rows contain the corresponding values.

 ACID implementation: DBMSes do not use the atomicity, consistency, isolation and durability (ACID) model for

storing data. On the other hand, RDBMSes base the structure of their data on the ACID model to ensure consistency.

 Store data: in DBMS data it stores data as a file, whereas in RDBMS, it stores data in a tabular form.

 Support of database normalization: An RDBMS can be normalized, but a DBMS cannot.

 Relationship: in DBMS, no relationship between data, but in RDBMS, data is stored in the form of table which are

related to each other.

 Advantages of RDBMS

 Easy to add new data to the system or alter existing tables while ensuring consistency with the previously

available content.

 Updating data is more efficient since the changes only need to be made in one place.

 Database administrators can easily maintain, control and update data in the database.

 Backups also become easier since automation tools included in the RDBMS automate these tasks.

 The table format used in RDBMSes is easy to understand and provides an organized and structural manner

through which entries are matched by firing queries.

 UNIT THREE
Conceptual Database Design- E-R Modeling

Database Development Life Cycle

 The major phases or steps of the database system development life cycle:

1. Planning: that is identifying information gap in an organization and propose a database solution to solve the

problem.

2. Analysis: that concentrates more on fact finding about the problem or the opportunity. Feasibility analysis,

requirement determination and structuring are performed at this phase.

3. Design: in database system development more emphasis is given to this phase. The phase is further divided into three sub-

phases.

A. Conceptual Design

B. Logical Design

C. Physical Design
 Cont…

A. Conceptual Design: concise description of the data, data type, relationship between data and constraints
on the data.
 There is no implementation or physical detail consideration.
 Used to elicit and structure all information requirements.
B. Logical Design: a higher-level conceptual abstraction with selected specific database model to implement
the data structure.
 It is particular DBMS independent and with no other physical considerations
 Data normalization
C. Physical Design: physical implementation of the upper-level design of the database with respect to
internal storage and file structure of the database for the selected DBMS.
 Continue…

4. Implementation: the testing and deployment of the designed database for use.

5. Operation and Support: administering and maintaining the operation of the database system and providing

support to users.

Basic concepts of E-R model

 E-R modeling, database designers represent their data model by E-R diagrams.

 E-R diagrams enable designers and users to express their understanding of what the planned

database is intended to do and how it might work, and to communicate about the database through

a common language.
 Continue…

Graphical Representations in ER Diagramming

Entity is represented by a rectangle containing the name of the entity

Strong Entity: It can exist without any other entity.

Example: Customer(customerid, name, surname)

Weak Entity: A weak entity is one that can only exist when owned by another one.

Example: a ROOM can only exist in a BUILDING.

Attributes are represented by ovals and are connected to the entity by a line.

A derived attribute is indicated by a dotted line. (……..) Ovals

 Continue…

Primary Keys are underlined

Relationships are represented by Diamond shaped symbols

 Strong Relationship is a relationship between two strong Entities.

 Weak Relationship is a relationship between Weak and Strong Entities.

 Continue…

Before working on the conceptual design of the database, one has to know and answer the

following basic questions.

 What are the entities and relationships in the enterprise?

 What information about these entities and relationships should we store in the database?

 What are the integrity constraints that hold? Constraints on each data with respect to

update, retrieval and store.

 Represent this information pictorially in ER diagrams, then map ER diagram into a

relational schema.
 Continue…

Developing an E-R Diagram

 Designing conceptual model for the database is not a one linear process but an iterative activity where the

design is refined again and again.

 To identify the entities, attributes, relationships, and constraints on the data, there are different set of methods

used during the analysis phase.

• Interviewing end users individually and in a group

• Questionnaire survey

• Direct observation

• Examining different documents

 Relational Constraints

Example 1: Build an E-R Diagram for the following information:

A student record management system will have the following two basic data object categories with their own
features or properties: Students will have an Id, Name, Dept, Age, GPA and Course will have an Id, Name, Credit
Hours. Whenever a student enroll in a course in a specific Academic Year and Semester, the Student will have a
grade for the course.

Name Dept DoB Id Credit

Id Gpa
Students Courses

Age

Enrolled_In
Semester
Academic
Year

Grade
 Structural Constraints on Relationship

 Structural Constraints, (Cardinality Ratios and Participation Constraints) 

 Cardinality ratios: There are numbers (represented by M and N) written above the lines which connect relationships and

entities.

 Participation Constraints: Participation Constraints tell us that the participation in a relationship can either be total or

partial.

• When each entity in an entity set participates in a relation, it is called Total Participation.

• When all entities in the given entity set do not participate in a relation, it is called Partial Participation. 
 Conti…
The Structural constraints are represented by Min-Max notation.

One-to-one relationship:
E.g.: Relationship Manages between STAFF and BRANCH.

 One branch can only have one manager.

 One employee could manage either one or no branches

1..1 0..1
Manages Branch
Employee
 Cont…
One-To-Many Relationships

E.g.: Relationship Leads between STAFF and PROJECT

 One staff may Lead one or more project(s)

 One project is Lead by one staff

0..1 1..*
Leads Project
Employee
 Cont…
Many-To-Many Relationship

E.g.: Relationship Teaches between INSTRUCTOR and COURSE

The multiplicity of the relationship

 One Instructor Teaches One or more Course(s)

 One Course Thought by Zero or more Instructor(s)

0..* 1..*
Teaches Course
Instructor
 Problem in ER Modeling

 While designing the ER model one could face a problem on the design which is called a connection traps.
 Connection traps are problems arising from misinterpreting certain relationships.
There are two types of connection traps;
1. Fan trap:
2. Chasm Trap:
1. Fan trap:

 Occurs where a model represents a relationship between entity types, but the pathway between certain entity

occurrences is ambiguous.

 May exist where two or more one-to-many (1:M) relationships fan out from an entity.

 The problem could be avoided by restructuring the model so that there would be no 1:M relationships fanning

out from a single entity and all the semantics of the relationship is preserved.
 Conti…

 Example:

1..* Works
EMPLOYEE 1..1
BRANCH 1..1 IsAssigned 1..* CAR
For

 Semantics description of the problem;

 Conti…

 Problem: Which car (Car1 or Car3 or Car5) is used by Employee 6. Emp6 working in Branch 1 (Bra1). Thus from
this ER Model one can not tell which car is used by which staff since a branch can have more than one car and
also a branch is populated by more than one employee. Thus we need to restructure the model to avoid the
connection trap.
 To avoid the Fan Trap problem we can go for restructuring of the E-R Model. This will result in the following E-
R Model.

BRANCH 1..1 Has CAR 1..* Used By EMPLOYEE

1..* 1..*
 Conti…

 Semantics description of the problem;