DATABASE MANAGEMENT SYSTEM (8 marks)

Data, Information and Database

Data : Collection of raw facts and figures which do not provide any clear meaning. Data exists in
various forms such as numbers or texts which are generally raw and unprocessed.

Information : The meaningful result obtained after processing the data is known as information.
It is an organized, meaningful and classified data which delivers meaningful values to the receiver
or the user.

Database : A database is an organized collection of related data that is stored and managed for
future use. It allows users to efficiently store, retrieve, update, and delete data.

Database Management System (DBMS)

Database Management System (DBMS) is software that allows us to create, manage, and
manipulate data stored in a structured format, such as tables. With a DBMS, we can easily add,
update, retrieve, and delete data, making it an essential tool for organizing large amounts of
information efficiently. Example: MS-Access, MySQL, Oracle, PostgreSQL, SQLite, Foxpro,
Sybase.

1.2. Fields/Attributes, Records, Table, Objects and Keys

I. Fields/Attributes
● Fields, also known as attributes, represent the columns in a table which define the type of
data that can be stored in each column.
● For example, in a student database, fields can include
student_id, student_name, student_phone,dob.
II. Records: A record is a collection of related fields stored together as one row in a database
table.
III. Table: A table is an object of a database that stores related data in an organized way using
rows and columns. Columns are called fields or attributes, and rows are called records.
Each table stores data about one type of item, like students, teachers, or products.
IV. Objects: Objects are used in database to store or reference data. So, object holds data for
information and display it in the proper format. Main components of DBMS are field,
record and table. Examples of components of objects are:
Keys and its types
Attributes or a set of attributes that help in uniquely identifying a record in a table ensuring
integrity and uniqueness of data.
Types of keys:
Candidate Key: A candidate key is a column (or a combination of columns) that uniquely
identifies a row in a table and does not contain null values. A table can have multiple
candidate keys, but only can be chosen as the primary key. It’s the smallest “super‑key. In
a table of students, both `student_id` and `email` are candidate keys.

Alternate Key: It is a candidate key that is not chosen as the primary key. In a table of
students, if `student_id` is the primary key, then `email` can be an alternate key.

Primary Key: It is a field (or combination of fields) that uniquely identifies each record
in a table. It does not allow NULL values and must contain unique values. Each table can
have only one primary key.In a table of students, `student_id` can be the primary key.

Example:
Student_ID Name Email
101 Anil Rai anil.rai@example.com
102 Maya Shrestha maya.shrestha@example.com

Advantages and Disadvantages of DBMS

Advantages
● Stores data only once, reducing duplication.
● Maintains accurate and consistent data through integrity constraints.
● Protects data from unauthorized access and manipulation.
● Allows multiple users to access and manipulate data simultaneously.
● Provides mechanisms for data backup and restoration in case of system failure.

Disadvantages
● Requires specialized knowledge for design, implementation, and maintenance.
● Can be expensive due to software, hardware, and skilled personnel.
● May be slower than file-based systems
● Can be large and demand a lot of system resources.
● Requires regular updates and maintenance for efficient and secure operation.
 Database Vs Database Management System

Database Database Management System

A database is a collection of organized data that A DBMS is software that allows for the creation,
is stored electronically. management, and manipulation of databases.

The primary purpose of a database is to store A DBMS manages the data within the database,
data in an organized manner. providing functionalities such as data insertion,
update, deletion, and retrieval.

A simple database might be a file that contains Examples of DBMS software include MySQL,
data records, such as a spreadsheet. Oracle, and SQL Server, which provide advanced
tools for managing databases.

The databases do not ensure that the data will be The database management system (DBMS) ensures
available after failure arises. that the data will always be available even after
system failures.

A database alone may have issues with data A DBMS minimizes data redundancy by using
redundancy and duplication. normalization techniques.

1.4. Structured Query Language

SQL is a Structured Query Language used to store, manage, and retrieve data from databases.
Types of DBMS Language
i. DDL (Data Definition Language)
Used to define or change the structure (schema) of the database — like creating, altering, or
deleting tables. Some of the DDL Commands are as follows:
 CREATE: To create table, database, index and view
 RENAME: To rename table and column
 DROP: To delete table
 ALTER: To alter the structure of database
DML (Data Manipulation Language)
They are used to retrieve, insert, update, and delete data within database tables. Some of the DML
Commands are as follows:
 SELECT: To retrieve data from database
 INSERT: To insert or add new records to the database
 UPDATE: To update or modify the data in the database
 DELETE: To delete the unnecessary records in the database

Differences between DDL AND DML

Point
DDL (Data Definition Language) DML (Data Manipulation Language)
No.
Used to define or change the structure of Used to manage and manipulate the
1
database objects actual data in tables
Commands: CREATE, ALTER, DROP, Commands: SELECT, INSERT,
2
TRUNCATE, RENAME UPDATE, DELETE
Works directly on the data (rows) inside
3 Affects the database schema, not the data
tables
Changes are permanent and cannot be rolled Changes can be rolled back if not
4
back committed
WHERE clause is used to specify which
5 WHERE clause is not used
rows to manipulate

DDL Commands Syntax and Example

1. CREATE DATABASE
Syntax : CREATE DATABASE company;

Example : Create the `employee` database:

CREATE DATABASE employee;

2. CREATE TABLE
Syntax : CREATE TABLE table_name(column1 datatype constraint,column2 datatype
constraint, ...);

Example : Create the `employees` table:

CREATE TABLE employees (id INT PRIMARY KEY, name

VARCHAR(100) NOT NULL, age INT, department VARCHAR(100));
 3. ALTER TABLE (Add Column):
Syntax: ALTER TABLE table_name ADD column_name datatype constraint;

Example : Add a column `salary` to the `employees` table:

ALTER TABLE employees ADD salary DECIMAL(10, 2);

4. ALTER TABLE (Drop Column):

Syntax: ALTER TABLE table_name DROP COLUMN column_name;

Example: Drop the column `age` from the `employees` table:

ALTER TABLE employees DROP COLUMN age;

5. DROP TABLE
Syntax: DROP TABLE table_name;

Example : Drop the `employees` table:

DROP TABLE employees

DML Commands Syntax and Example

1. SELECT

Syntax : SELECT column1, column2, ... FROM table_name WHERE condition;

Example: Select `id`, `name`, and `department` from the `employees` table where
`department` is 'HR':

SELECT id, name, department FROM employees WHERE department = 'HR';

2. INSERT
Syntax: INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example: Insert a new employee into the `employees` table:

INSERT INTO employees (id, name, age, department, salary) VALUES (1, 'John Doe', 30,
'HR', 50000.00);
 3. UPDATE
Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE
condition;

Example : Update the `salary` of the employee with `id` 1:

UPDATE employees SET salary = 55000.00 WHERE id = 1;

4. DELETE
Syntax : DELETE FROM table_name WHERE condition;

Example : Delete the employee with `id` 1 from the `employees` table:

DELETE FROM employees WHERE id = 1;

Database models and its types

A database model is the blueprint or logical design of a database system—it shows how data is
organized, managed, stored, and how tables connect to each other. This blueprint guides how
the database is built and used.

Types of database models are as follows:

1. Hierarchical model
2. Network model
3. Relational model

Hierarchical Model
● A way to organize data in a tree-like structure where each record has a single parent and
can have multiple children, establishing a one-to-many relationship.
● Used in databases to store information in a top-down or bottom up approach.
● If a parent node is deleted then the child node is automatically deleted.

Advantages :

● Easy to understand and navigate.

● It is fast to traverse through a tree-like structure, so searching is fast and easy if parent is
known.
Disadvantages:

 The strict parent-child hierarchy makes it difficult to represent many-to-many

relationships.
 If a parent node is deleted, all its child nodes are also deleted, which can lead to
unintended data loss.
 Information might have to be duplicated in multiple places, leading to inefficiencies
and potential errors when updating data.

Network Model
● A Network Database Model organizes data using a graph-like structure, allowing records
to have multiple parent and child connections—supporting not just one-to-many, but also
many-to-many relationships.
● Allows for more complex relationships between records, such as a student being enrolled
in multiple courses and each course having multiple students.
● Used in databases to represent many-to-many relationships.

Advantages

● Data access is faster compared to the hierarchical model as there can be more than one path
to reach a particular node.
● Reduces data redundancy as similar data is not stored in more than one place.
Disadvantages

● Complex to design and to make any changes like updating, deleting, and inserting
● Lacks security as data can be accessed from any parents.

Relational Model
● Organizes data into tables (also known as relations), where each table consists of rows
(records) and columns (attributes).
● Data in the relational model can be easily linked through the use of keys (primary keys and
foreign keys).
● Supports normalization of a database.
● Widely adopted and supported by many database management systems. RDBMS
(Relational Database Management System) is a type of database management system
that stores data in tables with rows and columns adopting the relational model.

Advantages

 Data in the relational model can be easily linked and related through the use of keys
(primary keys and foreign keys), which helps maintain data integrity and avoid duplication.
 Allows for flexibility, enabling users to retrieve and manipulate data efficiently using SQL
.

Disadvantages

 Managing and designing very large databases can become complex.

 Requires more powerful hardware computers and data storage devices.
RDBMS(Relational Database Management System)
A flat file database stores data in a single table without any relationships, like in a text or
spreadsheet file, and is suitable for simple data storage. In contrast, an RDBMS (Relational
Database Management System) stores data in multiple related tables and uses keys to connect
them, allowing better organization, data integrity, and efficient data handling.

Advantages of database over file system

● Databases use normalization to remove duplicate data, saving storage space and improving
access time. This also ensures data consistency.
● Databases allow for setting access permissions so that only authorized users can access
certain data. This prevents identity theft, data leaks, and misuse.
● Databases provide better privacy protections compared to file systems.
● Databases are designed to quickly retrieve data, ensuring fast response times for queries.
● Databases regularly back up data, making it easier to recover in case of data loss or system
failure.
● Databases are more adaptable to changes and different needs compared to file processing
systems.
Entity Attributes and Relationship
Entity
Entity means a real-world object or thing about which we store information in a database. In very
simple words, an entity is anything — like a person, place, or object — that has data we want to
save. For example, if we are making a school database, a student is an entity because we store
student details, and a teacher is also an entity because we store teacher details.

Attributes
Attributes are the details or properties of an entity in a database. In simple words, an attribute tells
us something about the entity. For example, if Student is an entity, then name, roll number, class,
and age are the attributes of the student. So, attributes are the information about the entity that we
store in the database.

Relationship
Relationship shows how two or more entities are connected in a database. In simple words, a
relationship tells us how one entity is linked to another. For example, in a school database, a student
is related to a teacher because the teacher teaches the student. So, a relationship shows the
connection between different entities in the database.

E-R Diagram
● An ERD shows the relationships of entity sets stored in a database, graphically.
● Entity in a database could be a single person, place, or thing about which data can be stored.
● Each entity has a set of properties. The properties of each entity are termed as attributes.
Eg. Student can be an entity and would be described by attributes such as RollNumber,
Semester, Address etc.
● Relationships are meaningful associations between or among entities.
● Relationships are represented as lines connecting the points and shapes.
Normalization
Normalization is the process of organizing data in a database to reduce data redundancy
(repetition) and improve data accuracy. In simple words, normalization means breaking
large tables into smaller ones and linking them properly. For example, instead of storing
student and course details in one big table, we make separate tables for students and
courses, and connect them. So, normalization helps to make the database clean, clear, and
easy to manage.
The types of normalization are:-
1. First Normal Form (1NF): Ensures that each column in a table contains only a
single(atomic )value and that each record is unique.
2. Second Normal Form (2NF): Follows 1NF and ensures that non-key fields depend on the
entire primary key (no partial dependency).

3. Third Normal Form (3NF): Meets 2NF and ensures no field depends on another non-key
field (no transitive dependency).

Importance of Normalization:
● Minimizes duplicate data, saving storage space.
● Ensures data consistency and accuracy by organizing it logically.
● Makes it easier to update data and add new information without inconsistencies.
● Helps in optimizing queries by organizing data efficiently.

Example of Normalization

The above table can be represented in 1NF, 2NF and 3NF in following ways:

1 NF

Ensures that each column in a table contains only a single(atomic )value and that each record is
unique.
2NF

1. Should be on 1NF
2. shouldn’t have any partial dependency.
3. The primary key of the table should compose exactly 1 column.

3 NF

1. Should be on 2NF
2. All non-key columns should directly be dependent on the primary key.
3. There should be no transitive dependencies (i.e., non-key columns should not depend on
other non-key columns).
Q. The following Order table is in unnormalized form (UNF). Normalize the table from
First Normal Form (1NF) up to Third Normal Form (3NF).
Order Table
OrderID CustomerName Items ItemPrices
101 Ram Pen, Pencil 10, 5
102 Sita Notebook 50

The First Normal Form – 1NF

For a table to be in the first normal form, it must meet the following criteria:
 a single cell must not hold more than one value (atomicity)
 there must be a primary key for identification
 no duplicated rows or columns
 each column must have only one value for each row in the table

OrderID CustomerName Items ItemPrice

101 Ram Pen 10
101 Ram Pencil 5
102 Sita Notebook 50

The Second Normal Form – 2NF

 A table is said to be in 2NF if it meets the following criteria:
 it’s already in 1NF
 has no partial dependency. That is, all non-key attributes are fully dependent on a primary
key.
Order Table

OrderID CustomerName
101 Ram
102 Sita

Order_Items Table
OrderID Items ItemPrice
101 Pen 10
101 Pencil 5
102 Notebook 50

The Third Normal Form – 3NF

So, for a table to be in 3NF, it must:
 be in 2NF
 have no transitive partial dependency.

Order Details

OrderID CustomerName
101 Ram
102 Sita

Item Details

ItemID Items
1 Pen
2 Pencil
3 Notebook

Item Prices

OrderID ItemID ItemPrice

101 1 10
101 2 5
102 3 50
Centralized Database VS Distributed Database
Centralized Database:
A centralized database is a type of database that is stored and managed in a single central
location, such as a server or mainframe. All users and applications access the data through a
central system. It ensures high control, consistency, and easy management, but may suffer from
slow access if users are far from the central location.

Distributed Database:
A distributed database is a type of database where the data is stored across multiple computers or
locations but appears to users as a single system. Each site can store part of the database and can
work independently or together. It improves reliability, faster access, and supports sharing across
different locations.
Centralized Database Distributed Database

Database in which data is stored, located as well as Database in which data is stored in multiple
maintained at a single location only. databases and are connected with each other and are
spread across different physical locations.

Generally faster access due to the single server Can be slower due to the need to access data from
handling all requests. multiple locations.

Management and modification of databases is easier Management and modification of databases is very
as entire data is present in the same computer and in difficult as data is distributed in multiple computers
the same location. in different physical locations.

If the central server fails, the entire system is Failure of one server does not necessarily affect
affected. others.

Installation and maintenance cost is low. Installation and maintenance cost is expensive.

High consistency since all data is managed in one Can have issues with data consistency due to
place. replication across nodes.

Suitable for small organizations. Suitable for larger organizations.

Database Security , its importance and security measures

● Data security means protecting information from being accessed, changed, or stolen by
unauthorized people.
● Keeping data secure is important to protect personal, financial and other confidential
information from hackers or from any unauthorized access.
● Data security ensures information stays accurate and unchanged when stored or shared.
● Protects personal and financial information from theft, ensures privacy and confidentiality,
maintains data accuracy and integrity, prevents identity theft, supports compliance with
legal and regulatory requirements, fosters trust in digital services, avoids financial losses
from breaches, and ensures continuity of services and operations.
● It involves using methods like encryption, access controls, strong passwords, multi-factor
authentication, regular update of software and systems to keep data safe and private from
any thefts.
 ● Other data security measures include back up data, restrict access based on roles, monitor
for unusual activities.

Data Integrity and its importance

● Data Integrity refers to the accuracy, consistency, and reliability of data stored in a
database.
● It ensures that the data remains correct, unaltered, and reliable throughout its lifecycle,
from creation to disposal.
● It ensures data accurately represents real-world entities, which is crucial for reliable
decision-making.
● It keeps data uniform across the database, preventing errors and contradictions.
● Also protects data from unauthorized access and corruption, maintaining its integrity.

Types of Data Integrity

Entity integrity

It ensures that each table has a primary key and that the values in this primary key are unique and
not null. It means every row in a table can be uniquely identified, and no two rows can be the same
or have missing identifiers.

Referential integrity

It ensures that relationships between tables remain consistent. This is typically enforced by foreign
keys. It means if one table references another table, the references must be valid. For example, if
a record points to another record in a different table, that other record must exist.

Domain integrity

It ensures that all values in a column are valid according to defined rules, such as data type, format,
and range. It means the values in a column must follow specific rules. For instance, a column for
dates must only have valid date entries, not numbers or text.

Database Administrator
● A Database Administrator (DBA) is a person who is responsible for the management and
maintenance of database systems to ensure their optimal performance, integrity, and
security.
● DBA is responsible for designing, implementing, maintaining, and optimizing database
systems to meet organizational needs.
● They ensure data security by implementing access controls and performing regular backups
and recovery operations.
● DBAs troubleshoot and resolve database issues while providing support to users and
 developers.
● They continuously monitor and tune database performance for optimal efficiency.

Criteria to be a good DBA:

● Proficient in DBMS technologies (e.g., Oracle, SQL Server) and SQL for querying and
managing databases.
● Strong analytical abilities to troubleshoot and resolve complex database issues effectively.
● Knowledgeable in implementing and managing database security, including access
controls and encryption.
● Effective in collaborating with team members and documenting processes clearly.
● Committed to staying updated with the latest developments in database technologies and
best practices

Some Practice Questions

● What are the major uses of database application softwares?
● Explain the benefits of centralized databases.
● Differentiate between hierarchical and network data models.
● Difference between relational and object oriented model.
● What are the characteristics of a good data model?
● What are the functions of RDBMS?
● What are candidate keys in a relational database table? Write its importance in a relational
database.
● Differentiate between primary key and foreign key.
● What are candidate keys in a relational database table? Write its importance in a relational
database.