MODIBBO ADAMA UNIVERSITY, YOLA

FACULTY OF COMPUTING
DEPARTMENT OF COMPUTER SCIENCE

LECTURE NOTE
(Lecture 2)

COURSE TITLE: DATABASE DESIGN AND MANAGEMENT

COURSE CODE: CSC315

Course Lecturer:
Mr. I. H. Ibrahim

2026
ARCHITECTURE OF DATABASE MANAGEMENT SYSTEM (DBMS)
The architecture is a framework for describing database concepts and specifying the design and
structure of database system. Most users of a database system today are not present at the site of the
database system, but connect to it through a network. The architecture of a DBMS can be seen as either
two-tier (two level) or three-tier (three level) architecture. A three level architecture is the most widely
used architecture to design a DBMS. It separates its tiers or levels from each other based on the
complexity of the users and how they use the data present in the database.

The Three Level Architecture

The three-level architecture was suggested by the American National Standards Institute/Standards
Planning and Requirements Committee (ANSI/SPARC).Here database is divided into three levels
external level, conceptual level and internal level as shown below:

The Three Levels or Views are Discussed Below:

 Internal level: Internal level describes the actual physical storage of data or the way in which
the data is actually stored in memory. This level is not relational because data is stored
according to various coding schemes instead of tabular form (in tables). This is the low level
representation of entire database. The internal view is described by means of an internal
schema. The internal level is concerned with the following aspects:
 Storage space allocation
 Access paths
 Data compression and encryption techniques
 Record placement etc.
The internal level provides coverage to the data structures and file organizations used to store
data on storage devices.

1
  Conceptual level: The conceptual level is also known as logical level which describes the
overall logical structure of whole database for a community of users. This level is relational
because data visible at this level will be relational tables and operators will be relational
operators. This level represents entire contents of the database in an abstract form in
comparison with physical level. Here conceptual schema is defined which hides the actual
physical storage and concentrate on relational model of database.
 External level: The external level is concerned with individual users. This level describes the
actual view of data seen by individual users. The external schema is defined by the DBA for
every user. The remaining part of database is hidden from that user. This means user can only
access data of its own interest. In other words, user can access only that part of database for
which he is authorized by DBA. This level is also relational or very close to it.

Data Independence
Data independence is defined as the characteristics of a database system to change the schema at one
level without having to change the schema at the next higher level. It can also be defined as the
immunity of the application programs to change in the physical representation and access techniques
of the database. The above definition says that the application programs do not depend on any
particular physical representation or access technique of the database. The DBMS achieved the data
independence by the use of three-level architecture. The data independence is of two types:
1. Physical data independence: is the ability to change the physical schema (how data is stored)
without affecting the conceptual schema or application programs. Physical data independence
deals with changes to how data is stored (e.g., storage devices). It indicates that the physical
storage structures or devices used for storing the data could be changed without changing the
conceptual view or any of the external views. Modifications at the physical level are
occasionally necessary in order to improve performance.
2. Logical data independence: is the ability to change the conceptual schema (structure of the
database) without affecting external schemas (user views) or application programs. Logical
data independence deals with changes to the structure of the data (e.g., table design, column
modifications). Modifications at the conceptual level are necessary whenever the logical
structure of the database is altered. Logical data independence is more difficult to achieve than
physical data independence since application programs are heavily dependent on the logical
structure of the data they access.

DATA MODELS
A data model is a collection of concepts that can be used to describe the structure of the database
including data types, relationships and the constraints that apply on the data. In other words, Data
models define how the logical structure of a database is modelled. It defines how data is connected to
each other and how they are processed and stored inside the system. Data Modelling is the first step in
the process of database design. A data model helps in understanding the meaning of the data and
ensures that, we understand:
 What data is contained in the database (e.g. entities: students, lecturers, courses, subjects etc.)
 The relationships between data items (e.g. Students are supervised by Lecturers; Lecturers
teach Courses)
 The constraints on data (e.g. Student Number has exactly 12 length of character and digits; a
course has 2 or 3 unit of credits only)

2
A data model supports communication between the users and database designers. The major use of
data model is to understand the meaning of the data and to facilitate communication about the user
requirements.

Characteristics of Data Models

A data model must possess the following characteristics so that the best possible data representation
can be obtained.
 Diagrammatic representation of the data model.
 Simplicity in designing i.e., Data and their relationships can be expressed and distinguished
easily.
 Application independent, so that different applications can share it.
 Data representation must be without duplication.
 Consistency and structure validation must be maintained.
Relationships and Constraints
Relationships: Data models use three types of relationships
1. One-to-many (1:M or 1..*) relationship e.g. A painter paints many different paintings, but each
one of them is painted by only one painter.
2. Many-to-many (M: N or *..*) relationship e.g. Many employees may learn many job skills and
many job skills can be learned by many employees
3. One-to-one (1:1or 1..1) relationship e.g. A student can only have one student ID and one
student ID can only belong to one student.
Constraints: Is a restriction placed on the data. Constraints are important because they help to ensure
data integrity and it's normally expressed in the form of rules. For example: An employee's salary must
have values that are between 70000 and 700000.

Different Types of Data Models are:

1. Hierarchical model
2. Network model
3. Relational model
4. Entity relationship model
5. Object oriented model
Hierarchical model: The hierarchical data model organizes data in a tree structure. In this model,
each entity (real-world object, either active or not, that can be easily identifiable) has only one parent
but can have several children. Only one entity at the top of the hierarchy is called as Root. The structure
is based on the rule that one parent can have many children but children are allowed only one parent.
Linkages are only possible vertically but not horizontally or diagonally, i.e. there is no relation between
different trees at the same level unless they share the same parent. The following are examples of
Hierarchical model:

3
 (1)

(2)

Advantages
 High speed of access to large datasets.
 Data security: Hierarchical model was the first database model that offered the data security
that is provided and enforced by the DBMS.
 Efficiency: The hierarchical database model is very efficient when the database contains a large
number of transactions using data whose relationships are fixed.
 The model allows easy addition and deletion of new information. Data at the top of the
Hierarchy is very fast to access. It is very easy to work with the model because it works well
with linear type data storage such as tapes.

Disadvantages
 Implementation complexity
 Database management problems
 Lack of structural independence.
 This model cannot be used for more sophisticated relationships. It requires data to be
repetitively stored in many different entities. The database can be very slow when searching
for information on the lower entities.
 Searching for data requires the DBMS to run through the entire model from top to bottom until
the required information is found, making queries very slow. It can only model one to many
relationships; many to many relationships are not supported.

Network data model: As a result of limitations in the hierarchical model, designers developed the
Network Model. The ability of this model to handle many to many (N : N) relations between its records
is the main distinguishing feature from the hierarchical model. Thus, this model permits a child record
4
to have more than one parent. In this model, directed graphs are used instead of tree structure in which
a node can have more than one parent. This model was basically designed to handle non-hierarchical
relationships. The relationships between specific records of 1 : 1 (one to one), 1 : N (one to many) or
N : N (many to many) are explicitly defined in database definition of this model. The Network Model
was standardized as the CODASYL DBTG (Conference of Data System Languages, Database Task
Group) model. Below is an example of Network model.

Advantages
 Conceptual simplicity
 Capability to handle more relationship types
 Data independence

Disadvantages
 Detailed structural knowledge is required
 Lack of structural independence

Relational model: The relational model was first introduced by Dr. Edgar Frank, an Oxford-trained
Mathematician, while working in IBM Research Centre in 1970’s. The Relational Model is considered
one of the most popular developments in the database technology because it can be used for
representing most of the real world objects and the relationships between them. In the relational model
the data is represented in the form of tables which is used interchangeably with the word Relation.
Each table consists of rows also known as tuples (A tuple represents a collection of information about
an item, e.g., student record) and column also known as attributes. (An attribute represents the
characteristics of an item, e.g., Student’s Name and Phone No.). There are relationships existing
between different tables. This model doesn’t require any information that specifies how the data should
be stored physically. The organization of data into relational tables is known as the logical view of the
database. Software such as Oracle, Microsoft SQL Server, Sybase, are based on the relational model.
The major advantages of Relational Model are that it is structurally independent, improved conceptual
simplicity, specific query capability and powerful DBMS. The major disadvantages of relational model
are substantial hardware and software cost. The following is an example of Relational model

5
Characteristics of Relational Model
 The relational model eliminated all parent child relationships and instead represented all data
in the database as simple row/column tables of data values.
 A relation is similar to a table with rows/columns of data values.
 Each table is an independent entity and there is no physical relationship between tables.
 Most data management systems based on the relational model have a built-in support for query
languages like American National Standards Institute Structured Query Language (ANSI
SQL). These queries are simple English constructs that allow specific data manipulation from
a table.

Entity-Relationship (E-R) model: The Entity-Relationship (E-R) model allows us to describe the
data involved in real-world enterprise in terms of objects (entities) and their relationships, and is widely
used to develop an initial database design. E-R also allows the representation of the various constraints
as well as their relationships. The relationship between entity sets is represented by a name. E-R
relationship is of 1 : 1, 1 : N, N : 1 or N : N type which tells the mapping from one entity set to another.
E-R model is shown diagrammatically using entity-relationship (E-R) diagrams which represents the
elements of the conceptual model that show the meanings and relationships between those elements
independent of any particular DBMS. Below is an example of E-R model.

The various features of E-R model are:

 E-R Model can be easily converted into relations (tables).
 E-R Model is used for purpose of good database design by database developer.

6
  It is helpful as a problem decomposition tool as it shows entities and the relationship between
those entities.
 It is an iterative process.
 It is very simple and easy to understand by various types of users.
Object-oriented model: The object-oriented model is based on a collection of objects. An object
contains values stored in instance variables within the object. An object also contains bodies of code
that operate in the object, these bodies of code are called methods. When different objects of same type
are grouped together they form a class. This model is used basically for multimedia applications as
well as data with complex relationships. The object model is represented graphically with object
diagrams containing object classes. Classes are arranged into hierarchies sharing common structure
and behaviour and are associated with other classes.

Advantages of Object-Oriented Model

The various advantages of object-oriented data model are as follows:
 Capability to handle various data types: The object-oriented databases has the capability to
store various types of data such as text, video pictures, voices etc.
 Improved data access: Object oriented models represent relationships explicitly. This improves
the data access.
 Improved productivity: Object-oriented models provide various features such as inheritance,
polymorphism and dynamic binding that allow the users to compose objects. These features
increase the productivity of the database developer.
 Integrated application development system: Object-oriented model is capable of combining
object-oriented programming with database technology which provides an integrated
application development system.

Disadvantages of Object-Oriented Model

The various disadvantages of object-oriented data models are as follows:
 Not suitable for all applications: Object-oriented data models are used where there is a need to
manage complex relationships among data objects. They are generally suited for applications
such as e-commerce, engineering and science etc. and not for all applications.
 Difficult to maintain: The definition of object is required to be changed periodically and
migration of existing databases to confirm to the new object definition. It creates problems
when changing object definitions and migrating databases.

Which Data Models to Use

So far we have discussed a large number of data models. Data models are essential as they provide
access techniques and data structure for defining a DBMS. In other words, a data model describe the
logical Organization of data along with operations that manipulate the data. We have large number of
data models, the one which is best for the Organization depends upon the following factors:
 Is the database too small or too big.
 What are the costs involved.
 The volume of daily transactions that will be done.
 The estimated number of queries that will be made from the database by the organization to
enquire about the data.

7
  The data requirements of the organization using it.
From the available types of data models, the relational data model is most commonly used model by
most of the organizations because of the following reasons:
 It increases the productivity of application programmers in designing the database. Whenever
changes are made to the database there is no need of changing the application programs because
of separation of logical level from conceptual level.
 It is useful for representing most of the real world objects and relationships between them.
 It provides very powerful search, selection and maintenance of data.
 It hides the physical level details from the end users so end users are not bothered by physical
storage.
 It provides data integrity and security so that data is not accessed by unauthorized users and
data is always accurate.
 It provides specific query capability. Some of the common DBMS using Relational model are
MS-Access, Informix, Oracle etc.
The hierarchical data model is used in those organizations which use databases consisting of large
number of one to many relationships. Because of the restriction to one to many relationships,
complexity of tree structure diagrams, lack of declarative querying facilities the hierarchical model
lost its importance. The network data model is used in those organizations which use databases
consisting of large number of many to many relationships, but due to its complex nature it is also not
preferred. Most of the DBMS use object oriented data modelling techniques which are used by large
number of organizations. For example, Latest versions of oracle are object relational hybrids because
they support both relational and Object Oriented features.

Types of Database Systems

The database systems can be classified into two major categories, namely:
1. According to the number of users
2. According to database site locations
According to the number of users: According to the number of users, the database systems can be
further subdivided into two categories, namely; Single-user database systems and Multiuser database
systems.
1. Single-user database systems: In a single user database system, the database reside on a PC
on the hard disk. All the applications run on the same PC and directly access the database. In
single user database systems, the application is the DBMS. A single user accesses the
applications and the business rules are enforced in the applications running on PC. The example
is Microsoft access.
2. Multiuser database systems: In a multiuser database system, many PC’s are connected
through a Local Area Network (LAN) and a file server stores a copy of the database files.
Applications run on each PC that is connected to the LAN and access the same set of files on
the file server. The application is the DBMS and each user runs a copy of the same application
and accesses the same files. The applications must handle the concurrency control and the
business rules are enforced in the application. The example is MySQL, Microsoft SQL server
and Oracle files on a file server.

8
According to database site locations: According to database site locations, database systems can be
further subdivided into two major categories namely:
 Centralized database systems
 Distributed database systems
Centralized database systems: The centralised database system consists of a single processor
together with its associated data storage devices and other peripherals. Database files resides on a
personal computer or on a mainframe computer. The applications are run on the same PC or mainframe
computer. Multiple users access the applications through simple terminals that have no processing
power of their own. The user interface is text-mode screens and the business rules are enforced in the
applications running on the mainframe or PC. The example of centralized database system is Cobol
application programs running on IBM 390.

Distributed database systems: A distributed database system is a database system, in which, the data
is spread across a variety of different databases. These are managed by a variety of DBMS’s that are
running on various types of machines having different operating systems. These machines are widely
spread and are connected through the communication networks. Each machine can have is own data
and applications, and can access data stored on other machines. Thus, each machine acts as a server as
well as client. Thus, distributed database system is a combination of logically interrelated databases
distributed over a computer network and the distributed database management system (DDBMS).

9
BUSINESS RULES
It’s a brief and precise description of a policy, procedure, or principle within a specific organization.
It helps to create and enforce actions within that organizations environment. They must be rendered in
writing and updated to reflect any change in the organizations operational environment, and it must be
easy to understand. Business rules are essential to database design for several reasons:
 They help standardize the company's view of data
 They can be a communication tool between users and designers
 They allow the designer to understand the nature, role, and scope of the data
 They allow the designer to develop appropriate relationship rules and constraints.
 It helps create an accurate data model.
 For example: A customer may generate many invoices. A student can only register a maximum
of 5 courses per semester.

10