Notes:

Chapter 1: DBMS (Database Management System)

1. Define database, DBMS and also write the advantages of electronic database (DBMS)
over paper database.
Answer : Database is the collection of related data and information about any object, thing,
organization, entity etc. In the database, data and information are organized and managed in
such way that the contents of database can be easily accessed, retrieved and updated.
Example : Phone book, Mark ledger, Payroll, Flight schedule etc.
DBMS : It stands for database Management System. It is software which is used to create a
database to manage, organize the related data and information of any entity systematically in
good manner.
e.g. MySql, MS ACCESS, ORACLE,SQL Server, etc.
1. Data can be shared: Data stored in a database can be shared among different users
simultaneously. DBMS enables concurrent access to data by multiple users and applications.
2. Reduce data redundancy: In paper based database, there is high chances of repetition of
data, this is known as data redundancy. DBMS provides a centralized storage of data, and it
reduces the duplication of data from database.
3. Data Security: DBMS provides robust security mechanisms to protect sensitive data from
unauthorized access, ensuring data confidentiality, integrity, and availability. It allows for
user authentication, authorization, and encryption of data to safeguard against security
threats.
4. Data Integrity: DBMS ensures data integrity by enforcing limitations and rules on data
manipulation operations, such as insertion, deletion, and modification. This prevents invalid
or inconsistent data from being stored in the database.
5. Data availability: In a traditional file processing system, data is scattered in many files and
due to non-uniformity in file design, the same data may have different identification. It
makes it difficult to obtain the relevant data. DBMS helps to overcome this problem.
6. Data Backup and Recovery: DBMS provides mechanisms for data backup and recovery to
protect against data loss due to system failures, disasters, or human errors. This ensures
data availability and reliability in case of unexpected events.
2. Define Table Field, Record, Objects, Primary key, Alternate Key, and Candidate Key giving
examples.
1) Table: A table is the collection of data and information organized in rows and columns.
2) Field: Field is a single piece of information or single data items of any entity. Field is located
in column of the table.
3) Record: Record is the collection of related fields of any entity which is located in row of the
table.
4) Object: Object is the elements of database which is used to design, enter, store, view and
analyze etc. Objects of database are table, query, form and report.

5) Primary Key: Primary key is the special field in the table that uniquely identifies each record
of the table. Any two uses of Primary key are: It is the unique identifier for each row of a table.
• To reduce data redundancy(duplication)
• To set the relationship between tables of database.
6) Candidate Key: A candidate key is set of two of more fields (attributes) in a table that can
uniquely identify any database record.
7) Alternate Key: Alternate keys are those candidate keys which are not the Primary key.
There can be only one Primary key for a table. Therefore, all the remaining Candidate keys are
known as Alternate or Secondary keys.
8) Foreign Key: A foreign key is a field in one table that refers to the primary key in another
table. A foreign key is a key used to link two tables together.
3) Define and discuss ACID properties of database Management System.
1. Atomicity
Atomicity means that a transaction must be treated as a single, indivisible unit.
Either all steps in the transaction are completed or none of them are.
Example:
While transferring money from Account A to Account B:
Deduct from A
Add to B
If any step fails, the whole transaction is cancelled.
2. Consistency
Consistency ensures that a transaction must take the database from one valid state to another valid state.
No rules or constraints should be violated.
Example:
If a bank account cannot have a negative balance, the database must ensure this rule is never broken after a
transaction.
3. Isolation
Isolation ensures that transactions occur independently of each other.
No transaction should interfere with another while they are running.
Example:
If two users book the last seat at the same time, isolation ensures only one gets it, avoiding conflicts.

4. Durability
Durability means once a transaction is completed, the result is permanent.
Even if the system crashes, the data stays saved.
Example:
After booking a ticket, the confirmation remains stored even if the system fails immediately afterward.

4) What is SQL? Define and discuss DDL and DML with examples.
Ans: SQL (Structured Query Language) is a standard programming language used to store, retrieve,
manage, and manipulate data in a relational database.
SQL is used in database systems like MySQL, Oracle, PostgreSQL, MS SQL Server, etc.

1. DDL: DDL stands for Data Definition Language which is used to define, create, modify, and delete the
structure of database objects (like tables, views, indexes).
Common DDL Commands:
CREATE – create database objects
ALTER – modify existing objects
DROP – delete objects
TRUNCATE – remove all records but keep table structure
Creating a table
CREATE TABLE Students (RollNo INT, Name VARCHAR (50), Age INT);
Adding a new column
ALTER TABLE Students ADD Address VARCHAR (100);
Deleting the table
DROP TABLE Students;

2. DML: DML stands for Data Manipulating Language which is Data manipulating language is used for data
manipulations such as insertion, deletion, retrieval and modification of data records in database. It provides
a user to query a database to perform required operations. It is integrated with programming languages to
implement sophisticated database functions.
Some SQL DML commands or statements are :
 SELECT,INSERT INTO, UPDATE, DELETE
1) SELECT statement: It is used to retrieve data from the database
Example 1 :
SELECT * FROM student
FROM student It retrieves all the data records from student
WHERE marks >65; table whose marks is more than 65.
Example 2:
SELECT * FROM student
FROM student It retrieves all the data records from student table in
ORDER BY name ASC; alphabetical order by name.

2) INSERT INTO : It is used to insert data records to the table

Example 3: INSERT INTO student (‘roll’,’name’,’class’) VALUES (‘5’,’Ashish Thapa’,’XII’);
3. UPDATE : It is used to update existing data records within table

Example 4
UPDATE student
SET marks = 75 It updates the marks of student by 75 whose roll no is 12
WHERE rollno = 12;
4. DELETE: It is used to delete data records from the existing table.
Example1:

DELETE FROM student

It deletes those data records of student from student table whose
WHERE rollno = 102;
roll no is 102

5. What is database model? List and explain the types of database models
Ans: A database model is the method of organizing data and represents the logical relationship among data
elements in the database. Database model is the method used in database management system to organize
the database of the organization. There are three models of database.
 Hierarchical Model
 Network Model
 Relational Model
1) Hierarchical database model:
 In this model, data elements are arranged in top- down structure(tree structure) that is one after
another. The topmost entities or top files are called roots and bottom entities are called leaves.
 In this model, each data record of one level is related to multiple levels in the next lower level.
 There is parent-child relationship among the data elements. A parent data element is one that has one
or more subordinate data elements.
 The data elements below of parent data elements are its child data elements.
Example : Organization chart, file systems.
College

Teacher Department

Professor Lecturer Computer Biology Chemistry Physics

Science
Advantages of Hierarchical database model :
1. It is secure because nobody can see and modify child data without consulting to its parent data.
2. It is easy to search data records if the parent is known.
3. It is efficient in handling one to many relationship.
4. It provides faster access of data because of the predefined data paths.
Disadvantages of Hierarchical database model:
1) It is outdated database model. So it is difficult to implement because it does not provide many to many
relationship.
2) When the parent entity is deleted, all the entities related to it in lower level can be deleted automatically.
3) Data redundancy can be increased because same data is to be written in
different places.
2) Network database model:
• A network database model is an extension of the hierarchical database structure.
• In this model, data elements of database are organized in the form of parent-child relationships.
• In this model, a child data element can have more than one parent element or no parent at all.
• In this model, DBMS allows to retrieve required information any data element instead of starting from
root data element.

College

English Nepali Physics Account Math

Advantages of Network database model

• It offers flexibility and data accessibility to retrieve and modify the data records because it accept many to
many relationship.
• It can handle the one to one and many to many relationship So it provides easy access of data from the
database.
• It promotes database integrity.
• Data searching is faster because of multidirectional pointers
Disadvantages of network database model
• It is complex design of database model.
• It provides less security because any entity's data record can be easily retrieved.
• Network database model is expensive and time consuming for large database operation.
3) Relational database Model:-
In this database model, data records are arranged in the form of table which are easy for a user to understand. Each
table is linked with other table in the database with common property (field) called primary key. Each table record is
linked with other table records.

Student _info table Library table

Advantages of Relational database model
i) Referential integrity rule can be implemented between linked tables so the change in one table's field that
effects related field in other table.
ii) There is a less chances of data redundancies
iii) Normalization of database is possible.
Disadvantages of database model
i. It is complex design of database model.
ii. Because of many rules that makes the database non user friendly.
iii. It needs larger programs to handle the relationship.

6) What is normalization in database? List and explain the types of normalization.

Answer: Normalization is the process of organizing data in the database into tables to reduce the data
redundancies. It is the process of breaking down a table into large number of small tables to preserve the loss
of data and removes unnecessary data. The main objectives of the normalization are to reduce the data
redundancies and inconsistent dependency between various fields. There are three types of Normalization of
database:
Types of Normalization:
1) 1 NF
2) 2 NF
3) 3 NF

1) 1st Normalization Form:- The purpose of 1NF is to reduce repeating group of attributes (fields) of any entity.
If any data records are repeated again and again then such fields are removed and separate table is made.
Example: for following set of data Here is Not normalized table

Roll no Name Class Subject 1 Marks subject 2 Marks 2 Subject 3 Marks 3

1 Ram XI English 50 Economics 45 Accounts 75
1 Sita XII English 65 Economics 65 Accounts 80
2 Gita XII English 68 Economics 60 Accounts 85

Here, subject1, subject2, subjects3 and marks fields are repeated and again.

1st Normal Form table

Rollno Name Class Subject Marks
1 Ram XI English 50
1 Ram XI Economics 45
1 Ram XI Accounts 75
2 Gita XII English 68
2 Gita XII Economics 60
2 Gita XII Accounts 85
2) 2NF (2nd Normal Form): In the 2nd Normal Form of table, the partial key dependency is removed or
reduced and separate tables are formed. In first Normalization form, primary keys are Roll No and Class. So
name and subjects are depend on roll no and class. These key dependencies are removed, and separate tables
are made.

3) 3NF (Third Normal Form) In 3rd normal form of table, relationship is established between
the tables and every non key attribute is functionally depend only on the primary key.

7) What is centralized database? Write its advantages and disadvantages.

Ans: This type of database system is stored in the central location i.e. central server. The centralized database
system work on the basis of client-server network. It is accessed by different users using terminals. Centralized
database is centralized so it is controlled by higher authority person and database is highly protected so there
is less chances of data modification and unauthorized use. The maintenance of database can be done easily
because database is centrally located. It is suitable for small organization
Advantages:
 It has very nominal chances of data redundancy since all data is stored
at a single place.
 All data are stored at a single location only, so it is easier to access
and coordinate data.
 It is suitable for a small organization with limited data and users.
 It has a low cost of implementation and operation.
Disadvantages:
 The data traffic in centralized database is more. So it makes slow
down the operations.
 If any kind of system failure occurs at centralized server then entire data will be destroyed.
 The performance and service will decrease when the number of users increases.
8) What is distributed database system? Write advantages and disadvantages of distributed database system.
Advantages of Distributed database
• It is easy to expand as data is already spread across
different locations.
• It is more secure in comparison to centralized database
• It is easy to access from different networks.
• It is suitable for a big organization with a large amount of
data, database users and computers
Disadvantages of Distributed database
• It is costly and difficult to maintain because of its
complexity.
• It is difficult to provide a uniform view to user being in
different physical locations.
• It has a high risk of data theft, hacking etc.

8) Difference between Centralized database system and distributed database system

9) What is data security? Write security measures .
1. User Authentication and Authorization : Ensuring that only authorized users can access the database by
using methods like passwords, biometrics, and multi-factor authentication (MFA). Assigning permissions to
users based on their roles within the organization.
2. Encryption: Data stored in database must be encrypted. So encrypted data stored in the database is
protected from unauthorized access. It prevents data interception while transferring between clients and
server computer.
3. Database Auditing and Monitoring: Maintaining logs of database activities to track access and changes to
data. Database activities can be monitored in real time and detect suspicious behavior using different tools.
4. Firewalls and Network Security: Setting up firewalls to control and filter traffic to and from the database
server in network.
5. Patch Management: Regular Updates: Applying security patches and regular updates to the database
management system (DBMS) to fix vulnerabilities (Weakness) and also regular scanning is to be done for
vulnerabilities and addressing them promptly.
6. Regular Backup and Recovery: Ensuring that data is regularly backed up and can be restored in case of data
loss or corruption.
7. Physical Security: Ensuring that the physical locations where database servers are housed have robust
security measures, such as access controls, surveillance, and environmental controls.
8. Strong Password Policies: Enforcing the use of strong, complex passwords. It is required periodic changes
to passwords to reduce the risk of compromised credentials.

10) What is DBA? Write the role of DBA.

Ans : Database administrator is the post of high responsibility. He/she is the person responsible for design and
maintained the database. The main goal of the DBA is to keep the database server up to date, secure and
provides information to users on demand.
Roles or task of DBA
1) Installing the database server:- The DBA is responsible to install new database server in which the
database is to be stored. The DBA upgrades the existing database server as well.
2) Monitor the database server:- DBA looks after the database server to check whether the database server is
running with good performance or not. Whether there is error or not. If the errors are found, the DBA keeps
maintenance and repairs.
3) Managing data activities:- The DBA makes sure of the data integrity, security and data privacy.
4) Back up and recovery duties:- The DBA makes rules and standard and schedule for database backup and
recovery procedure to protect the database from being erased and lost accidentally.
5) Managing the database user and security:- The DBA makes authorize user to operate or access the
database by providing the secret password.
6) Working with designers and developers:- The DBA works with designers and developers to support in
database design.
11) What is Data integrity? List the types of data integrity.
Ans : Data integrity refers to the accuracy, consistency, and reliability of data throughout its lifecycle. It ensures that
data remains unchanged from its source and maintains its quality over time. Data integrity is crucial for organizations to
make informed decisions, maintain compliance, and build trust with stakeholders.
There are several types of data integrity:
1. Entity Integrity: Ensures that each row or record in a database table is uniquely identified, usually
through the use of primary keys. This prevents duplicate or null values in key fields.
2. Domain Integrity: Defines the acceptable range of values for a given data field or attribute. It ensures
that data entered into a field meets specified criteria, such as data type, format, or range.
3. Referential Integrity: Maintains the consistency and relationships between related tables in a
database. It ensures that foreign key values in one table match primary key values in another table,
preventing orphaned records.
4. User-Defined Integrity: Allows organizations to define custom rules or constraints to enforce specific
business requirements beyond the standard database constraints. This may include complex validation
logic or business rules.
5. File Integrity: Ensures that files remain unchanged and uncorrupted during storage, transfer, or
processing. Techniques such as checksums or digital signatures are often used to verify file integrity.
6. Backup Integrity: Ensures the reliability and completeness of data backups. Regular validation of
backups and implementing redundancy measures help maintain backup integrity, ensuring data can be
restored in case of loss or corruption.