Database Design

Unit 2

Muhammad Qasim
 Quiz 1
Thursday 13-Oct-2022
Unit 1&2
(Introduction + DB Design)

Assignment 1
In Tomorrows Class

2
Data Models
 A Data Model is
◦ A collection of concepts that can be used to describe the
structure of a database.
 By structure it means data-types, relationships and
constraints that should hold on the data.

◦ It provides the means to achieve data abstraction by hiding

unneeded details of data storage.
 Data abstraction is suppression of details of data
organization and storage, highlighting of the essential
features for an improved understanding of data

◦ Most data models include a set of basic operations for

specifying retrieval and updates on the database and dynamic
operation e.g. Calculate GPA, Update Inventory.
Categories of Data Models
Data models can be categorized according to the types of concepts
they use to describe the database structure.

 Conceptual data Models

 Physical data Models
 Representational data Models
Data Models
1. Conceptual Data Models (High level): provide concepts close to the
way users perceive data. These uses concepts such as entities,
attributes and relationships.
1. An entity represents a real world object or concept (employee,
project).
2. An attribute represents some property of interest that further
describes an entity (employee name).
3. A relationship among two entities represents an interaction
among them.
Data Models
2. Physical Data Models: Provide concepts that may be understood by
end user and how data organized within the computer. It hides some
details but can be directly implemented. These describe how data is
stored as file in the computer by representing information such as
record formats, record ordering and access path.
Data Models
3. Representational Data Models: Provide concepts that describe the
details of how data is stored in the computer. It represent data by
using record structures.

 Flat Data Model

 Hierarchical Model
 Network Model 
 Relational Model
 Entity-Relationship Model
 Object-Oriented Data Model
 Object-Relational Data Model
Data Model
Flat Data Model: The flat Data Model is a model which kept all the data
in the same plane. It is a first and foremost introduced traditional data
model, which is not much scientific.

In this model, the database is indicated as a single table in the form of

tuples (rows) and fields (columns). This DBMS data model is slow and
inefficient process.
Data Models
Hierarchical Model:
 The hierarchical model is that data model which indicates the data of

the database in the tree-like structure. This is the first database

management system model.
 This data model stores the data as records and connects them

through links. Each hierarchical data model has a single parent entity
for each record. A parent can have more than one child node, and all
the siblings are sorted in a specific order.
 In the 60s and 70s, this data model was mainly used by the

Information Management System (IMS) of IBM.

Data Models
Network Model: The data in network model are represented by
collection of records and relationships among data are represented by
links, which can be viewed as pointers. The records in the database are
organized as collection of arbitrary groups. The main advantage of this
model is its representation of relationships between entities is
implemented using pointers which allows representation of arbitrary
relationship.
 Before the invention of the relational model, this was the most

popular database model.

 This model is mainly used to denote the many-to-many relationship

between the given data.

Data Models
 Relational Data Model: The relational data model is a data model
indicating the structure of the database in the 2-D (2-dimensional)
tables. In this model, all the data or information given by the user is
stored in the table rows according to the specific column.
 Edgar Frank Codd (E.F. Codd) was a great scientist who invented the
relational model for the databases in 1970.
 In the relational model, tables are called relations, and rows and
columns are called tuples and attributes.
 The following example consists of a relation (table) whose name
is Employee. This Employee table contains five attributes (columns)
and six tuples (rows).
 Five attributes of this table are Employee_ID, Employee_Name,
employee_Age, Employee_Salary, and Dept_ID.
Data Models
Entity Relationship Data Model:
 Entity-Relationship Model is that model which helps in designing the

structure of the database conceptually or logically. It is a high-level

data model, which is developed by Peter Chen in the year of 1976.
Any developer can easily understand the database system by the ER
diagram. ER diagram is a tool which consists of various symbols for
representing the ER model.
 It is a best data model for designing a database. After designing the

database, we can easily convert the designed ER diagram into the

tables of the relational model.
 ER model of DBMS consists of the following three components:

◦ 1. Entity
◦ 2. Attribute
◦ 3. Relationship
Data Model
 In the following example, the Employee and Department are the two entities.
The attributes of the Employee entity are Emp_ID, Emp_Name,
Emp_Salary, and Emp_Age. And, the attributes of the Department entity
are Dept_ID and Dept_name. These two entities are connected by a
relationship, whose name is Works_In.
Data Model
Advantages of Entity-Relationship Data Model
Some benefits or advantages of the Entity-Relationship data model are
as follows:
 If any user knows the relationship between the entities, then he/she

can easily and fastly develop and maintain the ER model. So, this
model is very easy to set up and develop.
 This data model can be easily converted by the developers into other

data models without any problem.

 This tool is so effective for communication between the designers.
Data Model
Disadvantages of Entity-Relationship Data Model
Some benefits or disadvantages of the Entity-Relationship data model
are as follows:
 As compared to other data models, it denotes fewer relationships.

 In this data model, there are more chances that some details of data

might be lost or hidden.

Data Models
Object-Oriented Data Model
 The object-Oriented data model is that model representing the data

and relationship as the object. This model stores the videos, audios,
and graphical files which can't be stored in the relationship model.  
 This model is based on the three main components whose names

are object class, object identity, and object structure. This data
model is mainly designed for storing and retrieving the objects
which are created by the programs of object-oriented languages. It
also provides the facilities of object-oriented to the database users.
Database Schema
A database schema in a database system is structure described in a
formal language which is supported by database management system
(DBMS) and refers to organization of data as a blueprint of how a
database is constructed (divided into database tables in case of
Relational Databases). The formal definition of database schema is a
set of formulas (sentences) called integrity constraints imposed on a
database. These integrity constraints ensure compatibility between
parts of schema. It Includes descriptions of database structure, data
types and constraints of database.
Database Schema
 A database schema divided broadly into two categories:
 Physical Database Schema: The schema pertains to actual storage of data and
its form of storage like files, indices etc. It defines how data will be stored in
a secondary storage.
 Logical Database Schema: The schema defines all logical constraints that
need to be applied on stored data. It defines views, tables and integrity
constraints.
Database State / Instance
 Database itself is called Database State/Extension/Instance:

◦ It is the data in a database at a particular moment

◦ Every time we insert or delete a record we change the state
of a database
◦ Schema change is called Schema Evolution
Example: University Database
DB Schema

Schema Constructs

A DB State/Instance/Extension

21
Schema Vs Instance
 Important to distinguish between DB schema and state:
◦ When we define a new database, specify its database schema
only to the DBMs
 The corresponding database state is the empty state
◦ Initial state of database when database is first Populated or
loaded with initial data
 Every time when a basic operation is applied we get another database
state
 At any point in time database has a current state
◦ DBMS ensures that every state of database is Valid State
 i.e. satisfies structure and constraints specified in schema
 Schema only change when requirement change called schema
evolution, E.g. we may decide another data item needed to be stored
for each record
Three Schema Architecture of DBMS
 The three schema architecture describes how the data is
represented or viewed by the user in the database. This
architecture is also known as three-level architecture and is
sometimes called ANSI/ SPARC architecture.
 This architecture contains three layers or levels of the
database management system:
◦ External level
◦ Conceptual level
◦ Internal level
Three Schema Architecture of DBMS
Three Schema Architecture of DBMS
1. External or View level: This is the highest level of database
abstraction. External or view level describes the actual view of
data that is relevant to the particular user. This level also
provides different views of the same database for a specific
user or a group of users. An external view provides a powerful
and flexible security mechanism by hiding the parts of the
database from a particular user.
Three Schema Architecture of DBMS
2. Conceptual or Logical level: The conceptual level describes
the structure of the whole database. This level acts as a middle
layer between the physical storage and user view. It explains
what data to be stored in the database, what relationship exists
among those data, and what the datatypes are. There is only
one conceptual schema per database.
Database administrator and the programmers work at this level.
This level does not provide any access or storage details but
concentrates on the relational model of the database. The
conceptual schema also includes features that specify the
checks to retain integrity and consistency.
Three Schema Architecture of DBMS
3. Internal or Physical level: This is the lowest level of database
abstraction. It describes how the data is actually stored in the
database and provides methods to access data from the
database. It allows viewing the physical representation of the
database on the computer system. The interface between the
conceptual schema and the internal schema identifies how an
element in the conceptual schema is stored and how it may be
accessed.
Three Schema Architecture of DBMS
Note: If there is any change in the internal or physical schema,
it needs to be addressed to the interface between the
conceptual and internal schema. But there is no need to change
in the interface of a conceptual and external schema. It means
that the changes in physical storage devices such as hard disks,
and the files organized on storage devices, are transparent to
application programs and users.
Advantages of Three-schema
Architecture
Following are the advantages of three schema architecture:
 This architecture makes the database abstract. It is used to

hide the details of how data is physically stored in a computer

system, which makes it easier to use for a user.
 This architecture allows each user to access the same

database with a different customized view of data.

 This architecture enables a database admin to change the

storage structure of the database without affecting the user

currently on the system.
 Three Schema: Example
Employee Data Conceptual Model
Workers
External
First Name: Fatima
Level/View Name: F. Abdullah
Last Name: Abdullah
Age: 34 Y, 10 d
DOB: 12th Sept. 1980
Department: Sales

Ext/conceptual mapping External Layer

Representational Model

Name DOB Dependent Dep. Id

Conceptual
Level/View Fatima Abdullah 12-08-1980 5 D019

Ahmad Ali 10-03-1990 0 D012

Conceptual/int mapping
Internal Layer

Internal/Physical Model
Internal
Level/View RH Fatima Abdullah 12081980 5 D019 RH Ahmad Ali…

OS Layer

100011001111000011100001110000111000011100101010…
Database Languages
1. Data Definition Language (DDL):
◦ Used to define schemas.

2. Data Manipulation Language (DML):

◦ Used to retrieve, insert, delete, and modify data.

3. Structured Query Language (SQL):

◦ A comprehensive language that combines both DDL and DML.
SQL Commands in each Language
These SQL commands are mainly categorized into four categories as:
 DDL – Data Definition Language

◦ CREATE – is used to create the database or its objects (like table, index,
function, views, store procedure and triggers).
◦ DROP – is used to delete objects from the database.
◦ ALTER-is used to alter the structure of the database.
 DQL – Data Query Language
◦ SELECT – is used to retrieve data from the a database.
 DML – Data Manipulation Language
◦ INSERT – is used to insert data into a table.
◦ UPDATE – is used to update existing data within a table.
◦ DELETE – is used to delete records from a database table.
 DCL – Data Control Language
◦ GRANT-gives user’s access privileges to database.
◦ REVOKE-withdraw user’s access privileges given by using the GRANT command.
DBMS Component Modules
 A DBMS is a complex software system
 The figure showed in next slide is divide into two halves.
 The top half refers to the various users of the database system
 The lower half shows the internals of the DBMS responsible for
storage of data and processing of transactions
Typical DBMS Component Modules

Users of the
database system

Internals of the
DBMS
Centralized DBMSs Architecture
◦ All DBMS functionality, application program execution, and
user interface processing carried out on one machine
Client/Server Architectures
 The client/server architecture was developed to deal with
computer environment in which a large number of PCs,
workstation, file server…

 A client in this framework is typically a user machine that

provides user interface capabilities and local processing
 A server is a system containing both hardware and software that
can provide services to the client machines.

 Two types of Client/Server architectures

◦ Logical two-tier client server architecture
◦ Three-tier Architecture
Two-tier Architecture
 This is called two-tire architectures because the software
components are distributed over two systems: client and server

 The emergence of the Web changed the roles of client and

server, leading to the three-tier architecture
Three-tier architecture
 The presentation layer displays information to the user.

 The business logic layer handles intermediate rules and constrains

before data is passed up to the user or down to the DBMS

 If the bottom layer is split into two layers (a web server and a
database server), then it is a 4-tire architecture (possible to the n-
tier)
 The intermediate layer or middle layer is sometimes called the
application server or Web server
 Three-tier Architecture Can Enhance Security:
1. Database server only accessible via middle tier
2. Clients cannot directly access database server
Three-tier Architecture