(Grade XII) Chapter 1- Database Management Software

 Data: Data are the collection of raw facts and figure which are unorganized, uninterrupted, and isolated, which doesn’t give any particular sense. They are randomly gathered. So that they can be processed to obtain the desired result.

Ram, 17, Pokhara, 18

 

Information: Information are the processed form of data. Information gives a meaning full result after data are interpreted.

Ram 17 year old and lives in Pokhara 18.

 

Database: 

·         The systematic and scientific collection of data that can access quickly whenever required is called database.

·         It is the organized way of collecting and keeping data and information.

·         E.g. Dictionary, telephone directory, marks ledger, attendance register.

 

DBMS: 

·         DBMS stands for Database Management System which is the collection of software that is used to manage database i.e. storing, manipulating and retrieving data systematically and scientifically.

·         It is a software used to store, process and access data and information whenever required.

·         Eg, MS-Access, MY SQL, Oracle, dBase, Foxpro

 

Advantages of DBMS

·         Prevent data redundancy(duplication of data)

·         Faster operation of data

·         Data security and privacy

·         Helps in decision making

·         Recovery and backup

 

Disadvantages of DBMS

·         Increases cost

·         Database failure

·         Frequent update

·         Highly complex

·         Huge size

Differentiate between database and DBMS with examples.

Database	DBMS
It is collection of related data.	It is a software to maintain database.
It is not secured.	It is highly secured.
Data sharing is impossible.	Data can be easily share in a network
Every database doesn't have DBMS.	Every DBMS has database.
E.g, Dictionary, telephone directory	E.g, MY SQL, Oracle.

 

 

Database Model

There are different forms of Database Management system. Each characterized by the way where data are defined and structured. This arrangement of data in several structure are known as data base model. Different types of database model. 

 

 

Centralized database VS Distributed database

 

Centralized database

It is a simple type which works on client server basis. In this type clients or user are directly connected to the centrally totally located server. This server hosts the data of its client or user and helps them to store and retrieve data as requirement. This type of system is used in small scale industries which don’t have to deal with large volume of data and user. Centralized database runs on single computer which may have single or multiple users. Since database is centralized, security is not a crucial part here. The maintenance of database is easier because of data are centrally stored. This type of system denotes allow unauthorized person to access data.

 

Advantages

·         Suitable for small scale industries.

·         Operation and maintenance is easier.

·         Since it prevent unauthorized person being accessed to database, it minimizes risk factor.

 

Disadvantages

·         Data are not secured in this type of system.

·         Not suitable for large scale industries.

·         Failure of centrally located serves will collapse whole network.

 

Distributed database

This type of database system is complex in structure, instead of storing and retrieving data from centrally located server, it uses several numbers of database and server randomly located at different place. It is the collection of multiple logically interrelated databases which are distributed in many geographical location. Since server are located at different locations user can experience a good speed of bandwidth. Similarly, backup and recovery process is lot more easier there, which makes data more secured. This type of system is used by large organization who has to deal with large volume of data and user all around the world. Since it is distributed in nature there may arises security issue and are costly to maintain and operate. Simply, distributed database system are the collection of several number of centralized database system in different locations.

 

Advantages

·         Backup and recovery of data is easier.

·         It can handle large volume of data and user all over the word.

·         User can experience high speed bandwidth.

 

Disadvantages

·         Very expensive to operate and maintain.

·         Data security may be real issue.

 

Differences between centralized and distributed database system

Basis of Comparison	Centralized database	Distributed database
Definition	It is a database that is stored, located as well as maintained at a single location only.	It is a database that consists of multiple databases which are connected with each other and are spread across different physical locations.
Access time	The data access time in the case of multiple users is more in a centralized database.	The data access time in the case of multiple users is less in a distributed database.
Management of data	The management, modification, and backup of this database are easier as the entire data is present at the same location.	The management, modification, and backup of this database are very difficult as it is spread across different physical locations.
View	This database provides a uniform and complete view to the user.	Since it is spread across different locations thus it is difficult to provide a uniform view to the user.
Data Consistency	This database has more data consistency in comparison to distributed database.	This database may have some data replications thus data consistency is less.
Failure	The users cannot access the database in case of database failure occurs.	In a distributed database, if one database fails users have access to other databases.

 

Structure query Language (SQL)

SQL stands for Structured Query Language. It is an international standard data base query language for accessing and managing data in the database. SQL was introduced and developed by IBM in early 1970’s. It was able to control relational database. SQL is not a complete programming language. It is only used for communicating with database. SQL has statement for data definition (DDL), data manipulation (DML) and data control (DCL). A query is a request to a DBMS for the retrieval, modification, insertion and deletion of the data from database.

SQL is made of three sub languages: DDL, DML and DCL

 

1) DDL (Data Definition Language): DDL is used by the database designer and programmers to specify the content and the structure of table. It is used to define the physical characteristics of record. It includes commands that manipulate the structure of object such as tables: For eg, to create table

Syntax:

CREATE TABLE table_name (field_name1 data_type1  field_name2 data_type2 ………);

CREATE TABLE Student (SN Number Fname text);

 

2) DML (Data Manipulation Language): DML is related with manipulation of records such as retrieval, sorting, display and deletion of records or data. It helps user to use query and display report of the table. It provide technique for processing the database. It includes commands like insert, delete, select, and update to manipulate the information stored in the database.

Syntax:

INSERT INTO table_name VALUES (list of values);

INSERT INTO student VALUES (1 RAM);

 

DDL (Data Definition Language)	DML (Data Manipulation Language)
DDL is used to define, modify, or delete the structure of database objects such as tables and schemas.	DML is used to insert, update, delete, or retrieve data stored in database tables.
DDL commands make permanent changes to the database structure and are automatically committed.	DML commands do not permanently change data unless they are committed.
DDL affects the schema or structure of the database rather than the actual data.	DML affects the actual records or data present in the database tables.
Examples of DDL commands include CREATE, ALTER, DROP	Examples of DML commands include INSERT, UPDATE, DELETE, and SELECT.

Entity Relationship Database model (ER Model)

The entity relationship database model (ER diagram) is based on the perception of a real world that contains a collection of basic object called entities and relationship among these objects. The ER diagram is an overall logical structure of database that can be expressed graphically. It was developed to facilitates database design. The major objectives of ER diagram are to show relationship among different entities. It has following components.

 

1) Entity: The distinguishable object of this real world is known as entities. It has a set of properties which uniquely identifies an entity. For eg, if student is an entity then his/her name may be property. It is denoted by rectangle.

 

2) Attributes: Attributes are the properties possessed by an entity. They are represented by ellipse or oval sign. For eg, if student is an entity then its attribute can be registration number, name, roll no, class, address, etc.

 

3) Link: The flow of information is indicated by the link in ER diagram. It is simply denoted by a line. It is a connection of entity, attributes and relationships.

 

4) Relationships: A relationship is a association among several entities. It is represented by diamond. For eg, if teachers and students are two entities the association can be derived as teacher teachers students. It shows meaningful dependencies between several entities. There are 3 types of relationships. One to one. One to many, many to many.

 

DBA (Database Administrator)

DBA is the most responsible person in an organization with sound knowledge of DBMS. He/she is the overall administrator of the program. He/she has the maximum amount of privileges for accessing database and defining the role of the employee which use the system. The main goal of DBA is to keep the database server up to date, secure and provide information to the user on demand.

Qualities of good DBA

·         He/she should have sound and complete knowledge about DBMS and its operation.

·         He/she should be familiar with several DBMS packages such as MS Access, MY SQL, Oracle etc

·         He/she should have depth knowledge about the OS in which database server is running.

·         He/she should have good understanding of network architecture.

·         He/she should hove good database designing skill.

Responsibilities

1.      DBA has responsibility to install, monitor, and upgrade database server.

2.      He/she should has responsibility to maintain database security by creating backup for recovery.

3.      He/she has responsibility to conduct training on the uses of database.

4.      DBA defines user privilege, relationships and manages form, reports in database.

 

Normalization (v.imp)

Normalization is technique of organizing data in the database. It is a systematic approach of decomposing tables to eliminate data redundancy and inconsistency.

 Advantages

·       It reduces data redundancy and inconsistency.

·       It helps to optimize memory space.

·       Less risk of mistakes.

 Disadvantages

·       It creates many tables, making database structure complex.

·       It can slow data retrieval.

·       It is difficult to understand and manage, especially for the beginners.

 Un- normalized table

Roll No.	Name	Subject	Teacher	Department
1	Ram	Account, Nepali	Hari, Gita	Account,Nepali
2	Sita	Nepali	Gita	Nepali

 1NF

Roll No.	Name	Subject	Teacher	Department
1	Ram	Account	Hari	Account
1	Ram	Nepali	Gita	Nepali
2	Sita	Nepali	Gita	Nepali

 

 

 

·       1NF means each field should contain only one value, not multiple values together.

·       In above example, subjects and teachers are split into separate rows so there is only one subject and one teacher per row.

·       This removes repeating groups and makes the table easier to read and search.

2NF

Student

Roll No.	Name
1	Ram
2	Sita

Subject

Subject	Teacher	Department
Account	Hari	Account
Nepali	Gita	Nepali

Enrollment

Roll No.	Subject
1	Account
1	Nepali
2	Nepali

·       2NF means the table is already in 1NF and all non-key data depends on the whole primary key.

·       In above example, student details are moved to a Student table and subject details to a Subject table, instead of repeating them.

·       This reduces duplication of data like student names and teacher information.

3NF

Student

Roll No.	Name
1	Ram
2	Sita

Teacher

Teacher	Department
Hari	Account
Gita	Nepali

Subject

Subject	Teacher
Account	Hari
Nepali	Gita

Enrollment

Roll No.	Subject
1	Account
1	Nepali
2	Nepali

·       3NF means the table is in 2NF and there are no indirect dependencies.

·       In above example, teacher and department information is separated so the department depends only on the teacher, not on the subject.

·       This avoids repeating department data and keeps each fact stored in only one place.

 

1NF (First Normal Form)	2NF (Second Normal Form)	3NF (Third Normal Form)
1NF removes multiple values from a single field and ensures each cell contains only one value.	2NF removes partial dependency by making sure non-key attributes depend on the full primary key.	3NF removes transitive dependency so non-key attributes depend only on the primary key.
In 1NF, data may still be repeated even though values are atomic.	In 2NF, repeated data is reduced by separating related data into different tables.	In 3NF, data repetition is further reduced by separating indirectly related data.
1NF focuses on organizing data into rows and columns properly.	2NF focuses on the relationship between primary keys and other attributes.	3NF focuses on keeping each fact in only one place to avoid inconsistency.

 

My SQL Examples

Example 1

a. Create a database named employee.

CREATE DATABASE employee;

b. Create a table employee with the given fields.

CREATE TABLE employee (

    employee_id INT,

    employee_name VARCHAR(100),

    employee_address VARCHAR(100),

    employee_salary FLOAT

);

c. Insert any 6 records.

INSERT INTO employee VALUES

(1, 'Amit Sharma', 'Kathmandu', 30000),

(2, 'Bikash Rai', 'Pokhara', 20000),

(3, 'Anita Gurung', 'Lalitpur', 25000),

(4, 'Ramesh Y', 'Kathmandu', 20000),

(5, 'Sita Ary', 'Bhaktapur', 18000),

(6, 'Ajay', 'Kathmandu', 30000);

d. Display all the records of all fields.

SELECT * FROM employee;

e. Display employee_id and employee_name.

SELECT employee_id, employee_name

FROM employee;

f. Display employees whose name starts with 'A'.

SELECT * FROM employee

WHERE employee_name LIKE 'A%';

g. Display employees whose name ends with 'Y'.

SELECT * FROM employee

WHERE employee_name LIKE '%Y';

h. Display employees whose salary is 20000.

SELECT * FROM employee

WHERE employee_salary = 20000;

i. Display employees whose salary is 30000 and address is 'Kathmandu'.

SELECT * FROM employee

WHERE employee_salary = 30000

AND employee_address = 'Kathmandu';

j. Update the employee name with any other name who has id 1.

UPDATE employee

SET employee_name = 'Rajesh Sharma'

WHERE employee_id = 1;

k. Delete the employee whose id is 3.

DELETE FROM employee

WHERE employee_id = 3;

l. Display records sorted by employee_name.

SELECT * FROM employee

ORDER BY employee_name ASC;

m. Add employee_department field.

ALTER TABLE employee

ADD employee_department VARCHAR(100);

---

Example 2

a. Create a database named staff.

CREATE DATABASE staff;

b. Create a table staff.

CREATE TABLE staff (

    staff_id INT,

    staff_name VARCHAR(100),

    staff_department VARCHAR(100),

    staff_bonus FLOAT

);

c. Insert any 5 records.

INSERT INTO staff VALUES

(1, 'Suman', 'IT', 2000),

(2, 'Rita', 'HR', 1500),

(3, 'Santosh', 'Finance', 2500),

(4, 'Sagar', 'IT', 2000),

(5, 'Mina', 'Library', 1800);

d. Display all records.

SELECT * FROM staff;

e. Display staff_id and staff_department.

SELECT staff_id, staff_department

FROM staff;

f. Display staff whose name starts with 'S'.

SELECT * FROM staff

WHERE staff_name LIKE 'S%';

g. Display staff whose department ends with 'Y'.

SELECT * FROM staff

WHERE staff_department LIKE '%Y';

h. Display staff whose bonus is 2000.

SELECT * FROM staff

WHERE staff_bonus = 2000;

i. Display staff whose bonus is 2000 and department is 'IT'.

SELECT * FROM staff

WHERE staff_bonus = 2000

AND staff_department = 'IT';

j. Update the staff name whose id is 4.

UPDATE staff

SET staff_name = 'Rajan'

WHERE staff_id = 4;

k. Delete the staff whose id is 5.

DELETE FROM staff

WHERE staff_id = 5;

l. Display records sorted by staff_name.

SELECT * FROM staff

ORDER BY staff_name ASC;

m. Add staff_position field.

ALTER TABLE staff

ADD staff_position VARCHAR(100);

---

Example 3

a. Create a database named library.

CREATE DATABASE library;

b. Create a table book.

CREATE TABLE book (

    book_id INT,

    book_title VARCHAR(100),

    book_author VARCHAR(100),

    book_price FLOAT

);

c. Insert any 5 records.

INSERT INTO book VALUES

(1, 'The Alchemist', 'Paulo Coelho', 500),

(2, 'Thinking Fast', 'Ram Sharma', 350),

(3, 'Tomorrow', 'Hari Kiran', 450),

(4, 'Python Basics', 'Suman', 250),

(5, 'Technology', 'Ram Sharma', 600);

d. Display all records.

SELECT * FROM book;

e. Display book_id and book_title.

SELECT book_id, book_title

FROM book;

f. Display books whose title starts with 'T'.

SELECT * FROM book

WHERE book_title LIKE 'T%';

g. Display books whose author name ends with 'N'.

SELECT * FROM book

WHERE book_author LIKE '%N';

h. Display books whose price is 500.

SELECT * FROM book

WHERE book_price = 500;

i. Display books whose price is greater than 300 and author is 'Ram Sharma'.

SELECT * FROM book

WHERE book_price > 300

AND book_author = 'Ram Sharma';

j. Update the book title whose id is 2.

UPDATE book

SET book_title = 'Database Concepts'

WHERE book_id = 2;

k. Delete the book whose id is 4.

DELETE FROM book

WHERE book_id = 4;

l. Display records sorted by book_title.

SELECT * FROM book

ORDER BY book_title ASC;

m. Add book_genre field.

ALTER TABLE book

ADD book_genre VARCHAR(100);

---

Example 4

a. Create a database named hospital.

CREATE DATABASE hospital;

b. Create a table patient.

CREATE TABLE patient (

    patient_id INT,

    patient_name VARCHAR(100),

    patient_disease VARCHAR(100),

    patient_age INT

);

c. Insert any 5 records.

INSERT INTO patient VALUES

(1, 'Sita', 'Diabetes', 45),

(2, 'Ram', 'Asthma', 30),

(3, 'Shyam', 'Malaria', 28),

(4, 'Sarita', 'Diabetes', 50),

(5, 'Hari', 'Influenza', 35);

d. Display all records.

SELECT * FROM patient;

e. Display patient_id and patient_name.

SELECT patient_id, patient_name

FROM patient;

f. Display patients whose name starts with 'S'.

SELECT * FROM patient

WHERE patient_name LIKE 'S%';

g. Display patients whose disease ends with 'A'.

SELECT * FROM patient

WHERE patient_disease LIKE '%A';

h. Display patients whose age is 30.

SELECT * FROM patient

WHERE patient_age = 30;

i. Display patients whose age is greater than 40 and disease is 'Diabetes'.

SELECT * FROM patient

WHERE patient_age > 40

AND patient_disease = 'Diabetes';

j. Update the patient name whose id is 3.

UPDATE patient

SET patient_name = 'Rohan'

WHERE patient_id = 3;

k. Delete the patient whose id is 2.

DELETE FROM patient

WHERE patient_id = 2;

l. Display records sorted by patient_name.

SELECT * FROM patient

ORDER BY patient_name ASC;

m. Add patient_address field.

ALTER TABLE patient

ADD patient_address VARCHAR(100);

 

 

Book's Activity Page number-37

 

1.      1. Multiple Choice Questions.

 

i. Which of the following is an example of DDL?

d. ALTER

 

 ii. Which SQL query is used to display the records whose name starts with A from the table named 'Employees'?

b. `SELECT  FROM Employees WHERE name LIKE 'A%';`

 

iii. Which of the following attributes can be further divided into other attributes?

c. Composite

 

iv. Which SQL query is used to delete the record of Employees whose Age is greater than 58?

a. `DELETE FROM Employees WHERE Age > 58;`

 

v. Which of the following database model organizes the data in the table in the form of row and columns?

b. Relational

 

vi. Which SQL query is used to modify the existing records in the database?

c. Update

 

 vii. Which database model organizes data more like a graph and can have more than one parent node?

c. Network

 

viii. The remaining attributes from the candidate key which is not selected as primary key becomes key.

b. Alternate

 

 ix. Which of the following is not an example of DBMS?

b. MS Excel

 

 x. Each column in a table is known as ...... which gives the smallest unit of information.

a. Attribute

 

 xi. In which normal form of database, atomicity is introduced?

a. First

 

 xii. Which of the following technique is not implemented to protect a database?

a. Rollbackup

 

 xiii. Which of the following statements are used in DDL?

a. Create, alter and drop

xiv. Which is the correct query to update the record from table named Student with fields Id, Roll, Name?

b. `UPDATE Student SET Roll = 150 WHERE id = 1;`

 

xv. A field that is used to uniquely define a particular record in a table is called

a. Primary key

 

xvi. Which normal form is used to remove the partial dependency?

b. Second

 

2. Short Answer Questions

 

i. Define a distributed database with its merits and demerits.

Distributed Database is a type of database that is stored across multiple physical locations but appears as a single database to users.

 

Merits:

·         Better reliability and availability.

·         Faster access due to local data access.

·         Easy expansion of the system.

Demerits:

·         Complex to manage and maintain.

·         Difficult to ensure data consistency.

·         High setup and communication cost.

ii. Differentiate between distributed database and centralized database.

Distributed Database	Centralized Database
Data is stored at multiple locations.	Data is stored at a single central location.
More reliable and fault-tolerant.	Failure leads to total system shutdown.
Faster access for local users.	Slower access from remote areas.
Complex to manage.	Easy to manage and maintain.

 

iii. What is database security? Explain different ways for database security.

Database security refers to protecting the database from unauthorized access, misuse, or corruption.

Ways to ensure security:

·         Authentication: Only authorized users can access.

·         Authorization: Set permission levels for users.

·         Encryption: Encode data to prevent unauthorized reading.

·         Backup and Recovery: Restore data in case of loss.

·         Firewall and Antivirus: Prevent malicious attacks.

iv. What is normalization? Write its advantages and disadvantages.

Normalization is a process in Database Management Systems (DBMS) used to organize data efficiently by eliminating redundancy (repetitive data) and ensuring data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them to minimize data anomalies (insertion, update, deletion).

There are several normal forms (NF), each with specific rules:

1.      1NF (First Normal Form): Eliminates repeating groups by ensuring atomic (indivisible) values.

2.      2NF (Second Normal Form): Removes partial dependencies; table must be in 1NF and all non-key attributes fully depend on the primary key.

3.      3NF (Third Normal Form): Eliminates transitive dependencies; non-key attributes must depend only on the primary key.

Purpose of Normalization:

·         Reduce data redundancy

·         Improve data consistency

·         Simplify database maintenance

·         Enhance query performance

 

Advantages:

·         Eliminates data redundancy.

·         Maintains data consistency.

·         Makes database efficient.

Disadvantages:

·         Complex queries due to multiple tables.

·         Slower data retrieval for complex joins.

 

v. Explain the different types of keys in DBMS.

Primary Key: Uniquely identifies each record.
Candidate Key: Any field that could be a primary key.
Foreign Key: Refers to primary key in another table.
Composite Key: A combination of fields that uniquely identify a record.
Alternate Key: Candidate key not chosen as primary.

vi. Differentiate between manual database and electronic database.

Manual Database	Electronic Database
Stored on paper.	Stored in computer system.
Slower access and update.	Fast and efficient.
More prone to errors.	Accurate and reliable.
Difficult to maintain.	Easy to manage and search.

 

vii. Which type of database system is preferred by financial institutions like banks? Give reasons.

Distributed Database is preferred by financial institutions like banks.

1.      High Availability: Data is replicated across multiple sites, ensuring the system remains operational even if one site fails.

2.      Faster Access for Branch Offices: Each branch can access data locally, reducing latency and improving response time.

3.      Better Disaster Recovery: Data stored in multiple locations allows quick recovery in case of hardware failure or natural disasters.

4.      Load Distribution: Workload is shared across servers, improving overall system performance and reliability.

5.      Scalability: Banks can easily add new branches and scale their operations without overhauling the entire database system.

Thus, distributed databases offer the reliability, speed, and resilience that banks require for secure and continuous operations.

viii. Why do most organizations prefer relational model? Justify.

Relational Model is preferred because:

·         It is easy to use and understand.

·         Ensures data consistency through normalization.

·         Supports powerful query language (SQL).

·         Allows relationships between tables (foreign keys).

·         Scalable and supports multiple users.

ix. Demonstrate basic DML statements with examples.

DML (Data Manipulation Language):

INSERT
INSERT INTO Students VALUES

(1, 'Sita'),

(2, 'Gita'),

(3, 'Rita');

 

UPDATE
UPDATE Students SET Name = 'Gita' WHERE ID = 1;

 

DELETE
DELETE FROM Students WHERE ID = 1;

SELECT
SELECT  FROM Students;

 

 

x. Explain the advantages and disadvantages of DBMS.

Advantages:

·         Reduces data redundancy.

·         Ensures data integrity.

·         Allows multiple users to access data.

·         Better security and backup.

Disadvantages:

·         Expensive setup and maintenance.

·         Requires trained personnel.

·         System failure affects entire data access.

 

3. Long Answer Questions

i. Explain different types of database models with merits and demerits.

Database models define how data is stored, organized, and accessed in a database system. The major types of database models are:

1. Hierarchical Model:

Structure: Data is organized in a tree-like structure with parent-child relationships.

Merit:

Fast data access due to a clear hierarchy.

Simple and easy to navigate for one-to-many relationships.

Demerit:

·         Difficult to manage complex relationships (many-to-many).

·         Rigid structure makes changes hard.

2. Network Model:

Structure: Uses a graph structure where records can have multiple parent and child records.

Merit:

More flexible than the hierarchical model.

Supports many-to-many relationships efficiently.

Demerit:

Complex to design and understand.

Requires advanced programming knowledge.

3. Relational Model:

Structure: Data is organized in tables (relations) with rows and columns.

Merit:

Easy to use and understand.

Supports SQL for powerful querying and data manipulation.

Demerit:

May be slower for very large and complex queries.

Performance may decrease with too many joins.

4. Object-Oriented Model:

Structure: Data is represented as objects, similar to object-oriented programming.

Merit:

Efficient for handling complex data types like images, audio, and videos.

Supports inheritance, encapsulation, and polymorphism.

Demerit:

Less widely adopted and supported.

Can have slower performance compared to relational databases.

ii. Explain different types of normalization with examples.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The most common normal forms are:

 Rule: Eliminate repeating groups; each column should have atomic (indivisible) values.

 Example:

  Before 1NF:

StudentID	Name	Subjects
1	Raj	Math, Science
2	Priya	English, History

After 1NF:

StudentID	Name	Subjects
1	Raj	Math
1	Raj	Science
2	Priya	English
2	Priya	History

 

2.      Second Normal Form (2NF):

Rule: Table must be in 1NF, and all non-key attributes must be fully dependent on the entire primary key (no partial dependency).

 Example:

  Before 2NF (composite key):

StudentID	Subject	StudentName
1	Math	Raj
1	Science	Raj

Here, StudentName depends only on StudentID, not on the full key (StudentID + Subject).

After 2NF:

Student Table:

StudentID	StudentName
1	Raj

Enrollment Table:

StudentID	Subject
1	Math
1	Science

 

3. Third Normal Form (3NF):

Rule: Table must be in 2NF, and all non-key attributes must depend only on the primary key (no transitive dependency).

 Example:

  Before 3NF:

StudentID	StudentName	Department	DeptLocation
1	Raj	CS	Building A

DeptLocation depends on Department, not directly on StudentID (transitive dependency).

 

  After 3NF:

  Student Table:

StudentID	StudentName	Department
1	Raj	CS

 

  Department Table:

Department	DeptLocation
CS	Building A

Conclusion:

Normalization improves data consistency, reduces redundancy, and organizes data for efficient access. Each normal form builds upon the previous one, ensuring better database design.

Example:
 If a table has StudentID, Name, Department, HOD_Name
 → Move HOD_Name to a separate Department table (3NF).

iii. Explain different types of attributes with examples.

1. Simple Attribute:

A simple attribute cannot be divided further.

It holds atomic (indivisible) values.

Example:

Age, Salary, FirstName, Gender

Each of these holds a single value for an entity.

2. Composite Attribute:

A composite attribute can be divided into smaller sub-parts, each representing more basic attributes.

These are useful for capturing detailed information in a structured way.

Example:

FullName → can be split into FirstName, MiddleName, and LastName

Address → can be divided into Street, City, State, and ZIP Code

 

3. Derived Attribute:

A derived attribute is not stored directly in the database but is calculated from other attributes.

It saves storage and keeps data updated automatically.

Example:

Age can be derived from DateOfBirth

TotalPrice can be derived from Quantity × UnitPrice

4. Multi-Valued Attribute:

An attribute that can hold multiple values for a single entity.

These require separate tables or special handling to maintain normalization.

Example:

PhoneNumbers for an employee (an employee can have more than one phone number)

LanguagesKnown for a person

5. Single-Valued Attribute:

This type of attribute holds only one value for a given entity.

It is the opposite of a multi-valued attribute.

Example:

EmployeeID, DateOfJoining, PAN Number

4. Activity 

1.Write the SQL statement to perform the following task.

 

a. Create a table named Students with following fields and insert the following five records.

Roll number	Name	Class	Address	Percentage

CREATE TABLE Students (

    Roll_number INT,

    Name VARCHAR(50),

    Class INT,

    Address VARCHAR(100),

    Percentage FLOAT

);

 

INSERT INTO Students  VALUES

(1, 'Ram', 10, 'Kathmandu', 72.5),

(2, 'Shyam', 11, 'Lalitpur', 80.0),

(3, 'Sita', 12, 'Bhaktapur', 85.0),

(4, 'Gita', 11, 'Kathmandu', 90.5),

(5, 'Navya', 12, 'Pokhara', 78.0);

 

b Add the field named Rank in the above table.

 ALTER TABLE Students ADD Rank INT;

 

c.Display Name, Class and Address of the students from the table Students.

 SELECT Name, Class, Address FROM Students;

 

 d. Display all the records of students whose Address is "Bhaktapur"

 SELECT  * FROM Students WHERE Address = 'Bhaktapur';

 

 e. Display Roll number, Name and Percentage from the table Students.

SELECT Roll_number, Name, Percentage FROM Students;

  

f. Increase the percentage of all the students by 5.

 UPDATE Students SET Percentage = Percentage + 5;

 

 g. Update the Name and Class of Student into Navya and 12 having roll number 8.

UPDATE Students SET Name = 'Navya', Class = 12 WHERE Roll_number = 8;

 

h. Delete the record of student named Ram.

DELETE FROM Students WHERE Name = 'Ram';

 

 i. Display all the records in ascending order by Name.

 SELECT *  FROM Students ORDER BY Name ASC;

 

j. Display all the records whose name starts with N.

 SELECT * FROM Students WHERE Name LIKE 'N%';

 

k Display the records of Students whose Class is 12 and lives in Kathmandu.

 SELECT * FROM Students WHERE Class = 12 AND Address = 'Kathmandu';

 

l. Display the record of Students whose Percentage lies between 75 and 90

SELECT *  FROM Students WHERE Percentage BETWEEN 75 AND 90;

 

2. Write the SQL statement to perform the following task.

a. Create a table named Employee with following fields and insert the following fin records.

Emp id	Name	Post	Salary
1	Shyam	Manager	76000
2	Hari	Officer	50000
3	Ramesh	DBA	80000
4	Gita	Manager	70000
5	Swonika	Officer	90000

CREATE TABLE Employee (

    Emp_id INT,

    Name VARCHAR(50),

    Post VARCHAR(50),

    Salary FLOAT

);

 Insert records

INSERT INTO Employee (Emp_id, Name, Post, Salary) VALUES

(1, 'Shyam', 'Manager', 76000),

(2, 'Hari', 'Officer', 50000),

(3, 'Ramesh', 'DBA', 80000),

(4, 'Gita', 'Manager', 70000),

(5, 'Swonika', 'Officer', 90000);

 

 b. Display all the records of employees whose post is Manager.

SELECT *  FROM Employee WHERE Post = 'Manager';

 

C. Display Name and Post of employees whose Salary is greater than 70000.

SELECT  * Name, Post FROM Employee WHERE Salary > 70000;

 

d. Display all the record of employee whose Emp Id is 5.

SELECT  * FROM Employee WHERE Emp_id = 5;

 

e. Increase the Salary of employees whose post is Manager by 10%.

UPDATE Employee SET Salary = Salary * 1.10 WHERE Post = 'Manager';

 

f. Display Name and Post of employees..

SELECT Name, Post FROM Employee;

 

g. Delete the record of employee whose post is DBA

DELETE FROM Employee WHERE Post = 'DBA';

 

h. Display all the record of employees whose post is Officer.

SELECT *  FROM Employee WHERE Post = 'Officer';

 

i. Display the record of Employees whose name ends with 'n'.

SELECT *  FROM Employee WHERE Name LIKE '%n';

 

j. Display all the record of Employees whose Post is either Manager or Officer

SELECT  * FROM Employee WHERE Post IN ('Manager', 'Officer');

 

k. Display the record in descending order by Salary.

SELECT *  FROM Employee ORDER BY Salary DESC;

 

1. Create a table NEB with attributes Emp_Id, Name and Address

CREATE TABLE NEB (

    Emp_Id INT,

    Name VARCHAR(50),

    Address VARCHAR(100)

);