UNIT II – Database Design, Architecture &

Model – 6 HRS

1
Notes By: Raju Poudel (Mechi Multiple Campus)
Overview of Database Design Process
• Database systems are designed to manage large bodies of information.
• Database design mainly involves the design of the database schema. The
design of a complete database application environment that meets the
needs of the enterprise being modeled requires attention to a broader set of
issues.
• In this text, we focus initially on the writing of database queries and the
design of database schemas.
• Database Design is a collection of processes that facilitate the designing,
development, implementation and maintenance of database systems.
• It helps produce database systems
1. That meet the requirements of the users
2. Have high performance.

2
Notes By: Raju Poudel (Mechi Multiple Campus)
Overview of Database Design Process

• A high-level data model provides the database designer with a conceptual

framework in which to specify the data requirements of the database users,
and how the database will be structured to fulfill these requirements.
• The initial phase of database design, then, is to characterize fully the data
needs of the prospective database users. The database designer needs to
interact extensively with domain experts and users to carry out this task. The
outcome of this phase is a specification of user requirements.
3
Notes By: Raju Poudel (Mechi Multiple Campus)
Overview of Database Design Process
• Next, the designer chooses a data model, and by applying the concepts of
the chosen data model, translates these requirements into a conceptual
schema of the database.
• The schema developed at this conceptual-design phase provides a
detailed overview of the enterprise. The designer reviews the schema to
confirm that all data requirements are indeed satisfied and are not in conflict
with one another. The designer can also examine the design to remove any
redundant features. The focus at this point is on describing the data and
their relationships, rather than on specifying physical storage details.
• In terms of the relational model, the conceptual-design process involves
decisions on what attributes we want to capture in the database and how to
group these attributes to form the various tables.

4
Notes By: Raju Poudel (Mechi Multiple Campus)
Overview of Database Design Process
• A fully developed conceptual schema indicates the functional requirements of
the enterprise. In a specification of functional requirements, users describe the
kinds of operations (or transactions) that will be performed on the data.
• Example operations include modifying or updating data, searching for and
retrieving specific data, and deleting data. At this stage of conceptual design,
the designer can review the schema to ensure it meets functional requirements.
• The process of moving from an abstract data model to the implementation of the
database proceeds in two final design phases.
• In the logical-design phase, the designer maps the high-level conceptual
schema onto the implementation data model of the database system that will be
used.
• The designer uses the resulting system-specific database schema in the
subsequent physical-design phase, in which the physical features of the
database are specified.
5
Notes By: Raju Poudel (Mechi Multiple Campus)
Database Design for a University Organization
• To illustrate the design process, let us examine how a database for a
university could be designed. The initial specification of user requirements
may be based on interviews with the database users, and on the designer’s
own analysis of the organization.
• The description that arises from this design phase serves as the basis for
specifying the conceptual structure of the database. Here are the major
characteristics of the university.
 The university is organized into departments. Each department is identified
by a unique name (dept name), is located in a particular building, and has a
budget.
 Each department has a list of courses it offers. Each course has associated
with it a course id, title, dept name, and credits.
 Instructors are identified by their unique ID. Each instructor has name,
associated department (dept name), and salary.
6
Notes By: Raju Poudel (Mechi Multiple Campus)
Database Design for a University Organization
 Students are identified by their unique ID. Each student has a name, an associated
major department (dept name), and tot cred (total credit hours the student earned
thus far).
 The university maintains a list of classrooms, specifying the name of the building,
room number, and room capacity.
 The university maintains a list of all classes (sections) taught. Each section is
identified by a course id, sec id, year, and semester, and has associated with it a
semester, year, building, room number, and time slot id (the time slot when the
class meets).
 The department has a list of teaching assignments specifying, for each instructor,
the sections the instructor is teaching.
 The university has a list of all student course registrations, specifying, for each
student, the courses and the associated sections that the student has taken
(registered for).

7
Notes By: Raju Poudel (Mechi Multiple Campus)
View of Data - Data Abstraction
• A database system is a collection of interrelated data and a set of programs
that allow users to access and modify these data.
• A major purpose of a database system is to provide users with an abstract
view of the data. That is, the system hides certain details of how the data are
stored and maintained.
• For the system to be usable, it must retrieve data efficiently. The need for
efficiency has led designers to use complex data structures to represent
data in the database.
• Since many database-system users are not computer trained, developers
hide the complexity from users through several levels of abstraction, to
simplify users’ interactions with the system:

8
Notes By: Raju Poudel (Mechi Multiple Campus)
View of Data - Data Abstraction
 Physical level. The lowest level of abstraction describes
how the data are actually stored. The physical level
describes complex low-level data structures in detail.
 Logical level. The next-higher level of abstraction describes
what data are stored in the database, and what relationships
exist among those data. Database administrators, who must
decide what information to keep in the database, use the
logical level of abstraction.
 View level. The highest level of abstraction describes only
part of the entire database. Even though the logical level
uses simpler structures, complexity remains because of the
variety of information stored in a large database. Many users
of the database system do not need all this information;
instead, they need to access only a part of the database.
The view level of abstraction exists to simplify their
interaction with the system. The system may provide many
views for the same database.
9
Notes By: Raju Poudel (Mechi Multiple Campus)
Data Models
• Data Model a collection of conceptual tools for describing data, data relationships, data
semantics, and consistency constraints. A data model provides a way to describe the design
of a database at the physical, logical, and view levels.
• The data models can be classified into four different categories:
 Relational Model. The relational model uses a collection of tables to represent both data and
the relationships among those data.
 Each table has multiple columns, and each column has a unique name. Tables are also
known as relations. Each table contains records of a particular type. Each record type defines
a fixed number of fields, or attributes.
 The columns of the table correspond to the attributes of the record type. The relational data
model is the most widely used data model, and a vast majority of current database systems
are based on the relational model.
 Entity-Relationship Model. The entity-relationship (E-R) data model uses a collection of
basic objects, called entities, and relationships among these objects. An entity is a “thing” or
“object” in the real world that is distinguishable from other objects. The entity-relationship
model is widely used in database design.
10
Notes By: Raju Poudel (Mechi Multiple Campus)
Data Models
 Object-Based Data Model. Object-oriented programming (especially in Java, C++, or C#) has
become the dominant software-development methodology. This led to the development of an
object-oriented data model that can be seen as extending the E-R model with notions of
encapsulation, methods (functions), and object identity. The object-relational data model
combines features of the object-oriented data model and relational data model.

 Semistructured Data Model. The semistructured data model permits the specification of data
where individual data items of the same type may have different sets of attributes. The
Extensible Markup Language (XML) is widely used to represent semistructured data.

 Historically, the network data model and the hierarchical data model preceded the
relational data model. These models were tied closely to the underlying implementation, and
complicated the task of modeling data. As a result they are used little now, except in old
database code that is still in service in some places.

11
Notes By: Raju Poudel (Mechi Multiple Campus)
Database Languages
 A database system provides a data-definition language to specify the database schema and
a data-manipulation language to express database queries and updates.
Data-Manipulation Language(DML)
 A data-manipulation language (DML) is a language that enables users to access or
manipulate data as organized by the appropriate data model.
 Examples of DML:
• SELECT – is used to retrieve data from the a database.
• 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.
 There are basically two types:
• Procedural DMLs require a user to specify what data are needed and how to get those
data.
• Declarative DMLs (also referred to as nonprocedural DMLs) require a user to
specify what data are needed without specifying how to get those data.
12
Notes By: Raju Poudel (Mechi Multiple Campus)
Database Languages
Data-Definition Language (DDL)
 DDL or Data Definition Language actually consists of the SQL commands that can be used
to define the database schema.
 It simply deals with descriptions of the database schema and is used to create and modify the
structure of database objects in database.

 Examples of DDL commands:

• 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.
• TRUNCATE–is used to remove all records from a table, including all spaces allocated for the
records are removed.
• COMMENT –is used to add comments to the data dictionary.
• RENAME –is used to rename an object existing in the database.
13
Notes By: Raju Poudel (Mechi Multiple Campus)
Database Languages
Data Control Language (DCL)
 DCL includes commands such as GRANT and REVOKE which mainly deals with the rights,
permissions and other controls of the database system.
 Examples of DCL commands:
• GRANT-gives user’s access privileges to database.
• REVOKE-withdraw user’s access privileges given by using the GRANT command.

Transaction Control Language (TCL)

 TCL commands deals with the transaction within the database.
 Examples of TCL commands:
• COMMIT– commits a Transaction.
• ROLLBACK– rollbacks a transaction in case of any error occurs.
• SAVEPOINT– sets a save point within a transaction.
• SET TRANSACTION– specify characteristics for the transaction.
14
Notes By: Raju Poudel (Mechi Multiple Campus)
Query By Example (QBE)
 Query by example is a query language used in relational databases that allows users to search
for information in tables and fields by providing a simple user interface where the user will be able
to input an example of the data that he or she wants to access.
 The principle of QBE is that it is merely an abstraction between the user and the real query that the
database system will receive. In the background, the user's query is transformed into a database
manipulation language form such as SQL, and it is this SQL statement that will be executed in the
background.
 (QBE) is a method of query creation that allows the user to search for documents based on an
example in the form of a selected text string or in the form of a document name or a list of
documents.
 Because the QBE system formulates the actual query, QBE is easier to learn than formal query
languages, such as the standard Structured Query Language (SQL), while still enabling powerful
searches.
 In terms of database management system, QBE can be thought of as a "fill-in-the blanks" method
of query creation. The Microsoft Access Query Design Grid is an example. To conduct a search for
field data matching particular conditions, the user enters criteria into the form, creating search
conditions for as many fields as desired. A query is automatically generated to search the
database for matching data.
15
Notes By: Raju Poudel (Mechi Multiple Campus)
DBMS Structure
 A database system is partitioned into
modules that deal with each of the
responsibilities of the overall system. The
functional components of a database system
can be broadly divided into the storage
manager and the query processor
components.

 The storage manager is important because

databases typically require a large amount
of storage space.

 The query processor is important because

it helps the database system simplify and
facilitate access to data.

16
Notes By: Raju Poudel (Mechi Multiple Campus)
DBMS Structure
Query Processor
 The query processor components include:
• DDL interpreter, which interprets DDL statements and records the definitions in the data
dictionary.
• DML compiler, which translates DML statements in a query language into an evaluation plan
consisting of low-level instructions that the query evaluation engine understands.
 A query can usually be translated into any of a number of alternative evaluation plans that all
give the same result.
 The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation
plan from among the alternatives.
Storage Manager
 A storage manager is a program module that provides the interface between the low level data
stored in the database and the application programs and queries submitted to the system. The
storage manager is responsible for the interaction with the file manager.
 It is responsible for storing, retrieving, and updating data in the database.
17
Notes By: Raju Poudel (Mechi Multiple Campus)
DBMS Structure
Storage Manager
The storage manager components include:
 Authorization and integrity manager, which tests for the satisfaction of integrity constraints
and checks the authority of users to access data.

 Transaction manager, which ensures that the database remains in a consistent (correct)
state despite system failures, and that concurrent transaction executions proceed without
conflicting.

 File manager, which manages the allocation of space on disk storage and the data structures
used to represent information stored on disk.

 Buffer manager, which is responsible for fetching data from disk storage into main memory,
and deciding what data to cache in main memory. The buffer manager is a critical part of the
database system, since it enables the database to handle data sizes that are much larger than
the size of main memory.
18
Notes By: Raju Poudel (Mechi Multiple Campus)
DBMS Structure
Transaction Manager
 A transaction is a collection of operations that performs a single logical function in a
database application.

 Each transaction is a unit of both atomicity and consistency. Thus, we require that
transactions do not violate any database-consistency constraints.

 That is, if the database was consistent when a transaction started, the database must
be consistent when the transaction successfully terminates.

 Transaction - manager ensures that the database remains in a consistent (correct)

state despite system failures (e.g., power failures and operating system crashes) and
transaction failures.

19
Notes By: Raju Poudel (Mechi Multiple Campus)
DBMS Architecture
 DBMS architecture helps in design, development, implementation, and maintenance of a
database. A database stores critical information for a business. Selecting the correct
Database Architecture helps in quick and secure access to this data.

1 Tier Architecture

 The simplest of Database Architecture are 1 tier where the Client, Server, and Database all
reside on the same machine.
 Anytime you install a DB in your system and access it to practise SQL queries it is 1 tier
architecture. But such architecture is rarely used in production.
20
Notes By: Raju Poudel (Mechi Multiple Campus)
DBMS Architecture
2 Tier Architecture
 A two-tier architecture is a database architecture
where:
1. Presentation layer runs on a client (PC, Mobile,
Tablet, etc.)
2. Data is stored on a Server.

 An application interface which is called ODBC

(Open Database Connectivity) an API which
allows the client-side program to call the DBMS.
 Today most of the DBMS offers ODBC drivers
for their DBMS. 2 tier architecture provides
added security to the DBMS as it is not exposed
to the end user directly.

21
Notes By: Raju Poudel (Mechi Multiple Campus)
DBMS Architecture
3 Tier Architecture

3-tier schema is an extension of the 2-tier architecture. 3-tier architecture has following layers:
1.Presentation layer (your PC, Tablet, Mobile, etc.)
2.Application layer (server)
3.Database Server

22
Notes By: Raju Poudel (Mechi Multiple Campus)
DBMS Architecture
3 Tier Architecture
 This DBMS architecture contains an Application layer between the user and the DBMS, which
is responsible for communicating the user's request to the DBMS system and send the
response from the DBMS to the user.
 The application layer(business logic layer) also processes functional logic, constraint, and rules
before passing data to the user or down to the DBMS
 Three tier architecture is the most popular DBMS architecture.

 The goal of Three-tier architecture is:

• To separate the user applications and physical database
• Proposed to support DBMS characteristics
• Program-data independence
• Support of multiple views of the data

 Example of Three-tier Architecture is any large website on the internet.

23
Notes By: Raju Poudel (Mechi Multiple Campus)