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.

Hierarchical database model

It is one of the oldest type of database model. In this model data are represented in the forms of record, each record has multiple field or attributes. All records are arranged in database as tree like structure. The relationship between the records is called parent child relationship in which one child record relates to only a single parent i.e child posses property only property of a single parent. Here child are restricted to use the property of a parent to whom it doesn't belong.

Advantages

1. It is the simplest and the easiest model.

2. It supports one to one or one to many relationship.

3. Searching is easier and faster if parent is known.

Disadvantages

1. It is an old fashion and outdated database model.

2. It doesn’t support many to one relationship.

3. It doesn’t reduce data redundancy because some data are written over different places.

Network database model

This network model replace hierarchical model due to some limitation on the model. Suppose an employee relates to two different departments then hierarchical model cannot be able to arrange record in proper place. So, network database model was emerged to arrange non-hierarchical database. The structure of database is more like graph rather than tree structure. A network model consists of collection of record which is interrelated to each other with the help of relationship. Each record has multiple fields and each field has only one data value. In this type of model a parent may have multiple children, as well as child can have multiple parents.

Advantages

1. It accepts many to many relationships. So, it is more flexible.

2. It reduces data redundancy.

3. This network mode is simple and easy to design.

4. Searching is faster due to use of multi-directional pointer.

Disadvantages

1. Needs long program to handle the relationship.

2. Lack of structural independence.

3. Less security

Relational database model

In relational database model, the data are organized into tables which contain multiple row and columns. These tables are called relations. A row in a table represents a relationship among a set of values. Since, a table is collection of such relationship. It is generally referred to the mathematical term relations from whom the relational database model derives its name. It is also known as RDBMS.

Note: The database system which stores and display data in tabular format of rows and column like spreadsheet is known as RDBMS. It is the most practical DBMS those days. For example, MS-Access, MY SQL, Oracle etc.

Advantages

· There is less data redundancy.

· Breaking of complex database into simple is very much easier.

· Database processing is faster than other model.

Disadvantage

· Establishing more relationships complex.

· It requires powerful computer and data storage device.

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

Centralized	Distributed
Simple type	Complex type
Located on particular location	Many geographical location
Only one server	Multiple server in many location
Suitable for small scale industries	Suitable for large scale industries
Maintenance is easy	Maintenance is difficult
Security is high	Security is low
Low speed	High Speed
Cheap	Expensive
Failure of server affect whole network	Doesn’t affect whole network
High chance of data loss	Less chance of data loss

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);

3) DCL (Data Control Language): All provides additional feature for security of table and database. It includes commands for controlling data and access to the database. Some of the example of this command is grant, commit, etc.

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)

The process of breaking down or decomposing as complex relation into simple relation. It reduces redundancy (unnecessary repetition of data) using principle of non-loss decomposition in which table are reduce to smaller tables without loss of information.

Normalization is the database design process in which complex database table is broken down into simple separate tables. It makes data model more flexible and easier to maintain.

Unnormalized database

Emp_code	January
Emp_name	February
Address	March
Contact no.	April
Date of birth	May
Department	June
Designation	July
Basic_salary	Daily_allowance
Travel_Allowance	Gross_salary
Tax	Provident _fund

Normalized database

Employee	Salary	Month
Emp_code	Basic_Salary	January
Emp_name	Travel_allowance	February
Address	Daily_allowance	March
Contact no.	Gross_salary	April
Date of birth	Provident_funt	May
Department	Tax	June
Designation		July

Advantages of normalization

1. It reduces data redundancy (duplication of data)

2. It improves faster sorting and indexing.

3. It simplifies the structure of the database table.

4. It improves the performance of a system.

5. It avoids loss of information.

Normal Forms

Let us consider a following unnormalized table.

Name	Roll	Class	Sub_name	Sub_marks	Sub_name	Sub_marks
Ram	1	11	Computer	95	Account	78
Sita	1	12	Computer	98	Account	80
Hari	2	11	Computer	80	Account	82
Shyam	2	12	Computer	92	Account	83

A) 1NF ( First Normal form)

1. 1NF sets the very basic rules for on organized database.

2. It eliminates duplicate columns from the same table.

3. It creates separate tables from each group of related data and identify each row with a unique column called primary key.

The objective of 1NF is to divide the base datas into logical units called tables.

In above table, we can see that column of subject nome and marks are repeated which are eliminated in 1NF.

Name	Roll	Class	Sub_name	Sub_marks
Ram	1	11	Computer	95
Ram	1	11	Account	78
Sita	1	12	Computer	98
Sita	1	12	Account	80
Hari	2	11	Computer	80
Hari	2	11	Account	82
Shyam	2	12	Computer	92
Shyam	2	13	Account	83

B) 2NF (Second Normal form)

· It further addresses the concept of remaining duplicate data.

· It should be in first normal form.

· It removes the date that applies to multiple row of a table and place them in separate tables.

The objective of second NF is to take data i.e. partly dependent upon the primary key and enter the data into another table.

In above table name depends upon roll no and class, subject name only depends upon class, subject marks depends upon name and subject_name. Hence, above table can be decomposed as:

Name	Roll	Class
Ram	1	11
Sita	1	12
Hari	2	11
Shyam	2	12

Subject	Class
Computer	11
Account	11
Computer	12
Account	12

Name	Sub_name	Sub_marks
Ram	Computer	95
Ram	Account	78
Sita	Computer	98
Sita	Account	80
Hari	Computer	80
Hari	Account	82
Shyam	Computer	92
Shyam	Account	83

3NF (Third Normal Form)

· It should be in second normal form.

· It removes the column that are not dependent on primary key using 3NF above table can be decomposed as:

Sub_id	Subject
C1	Computer
A1	Account

Class_id	Class
XI	11
XII	12

Std_id	Class	Roll	Class_id
1	Ram	1	XI
2	Sita	1	XII
3	Hari	2	XI
4	Shyam	2	XII

Std_id	Sub_id	Marks
1	C1	95
1	A1	78
2	C1	98
2	A1	80
3	C1	80
3	A1	82
4	C1	92
4	A1	83

My SQL Examples

example-1

(a)Create a database named ‘student’

Ans: CREATE DATABASE student;

(b) Get inside the database.

Ans: USE student;

Employee_id—int primary key not null auto increment

Employee_name--varchar(100)

Ans: CREATE TABLE employee (

employee_id int primary key not null auto_increment,

employee_name varchar(100)

);

(d) Delete the table.

Ans: DROP TABLE employee;

(e) Know whether that deleted table exists or not.

Ans: SHOW TABLES;

(f) Delete the database which you have created(student).

Ans: DROP DATABASE student;

(g) Know whether that deleted database exists or not.

Ans: SHOW DATABASES;

example-2

a. Create a database named ‘student’.

Ans: CREATE DATABASE student;

b. Create a table ‘student’ with following fields.

student_id--------------integer not null primary key auto increment

student_name---------varchar(100)

student_address-------varchar(100)

student_grade----------integer(int)

ANS; create table student (

student_id int not null primary key auto_increment,

student_name varchar(100),

student_address varchar(100),

student_grade int );

c. Insert any 6 records using ‘insert’ and ‘values’ command.

ANS: INSERT INTO STUDENT (student_name,student_address,student_grade) VALUES

('RAM SHRESTHA','KATHMANDU',12),

('RAJ SHARMA','BANEPA',12),

('SHYAM ADHIKARI','PATAN',12),

('ANJANA SHA','BOUDHA',11),

('SUBAM KARKI','DURBAR MARGA',12),

('ALINA LAMA ','BANASTHALI',10);

d. Display all the records of all fields.(Use select *)

ANS: SELECT * FROM STUDENT;

e. Display all the records of fields student_id and student_name.

Ans: select student_id,student_name from student;

f. Display records of students whose name starts with letter ‘a’.

Ans: select * from student where student_name like 'a%';

g. Display records of students whose name ends at letter ‘y’.

Ans: select * from student where student_name like '%y';

h. Display all the records of students whose grade is 12. Use ‘where’ command.

Ans: select * from student where student_grade=12;

i. Display all the records of students whose grade is 12 and who are from address “Kathmandu”. Use ‘and’ operator.

Ans: select * from student where student_grade=12 and student_address='Kathmandu';

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

Ans: update student set student_name='Unnat Sapkota' where student_id=1;

k. Delete the record of student whose id is 3.

Ans: delete from student where student_id=3;

l. Display the records of students in sorted order using field ‘student_name’. Use ‘order by field name asc/desc’.

Ans: for ascending : select * from student order by student_name asc; for descending : select * from student order by student_name desc;

m. Alter the table with following fields. Add one more field student_section---varchar(100). [Use alter and add command]

Ans: alter table student add student_section varchar(100); Change the size of field student_address--varchar(200) [Use alter and modify command] Ans: alter table student modify student_address varchar(200);

example-3 a. Create a database named ‘employees’.

Ans: create database employees;

b. Create a table ‘employee’ with following fields.

emp_id--------------integer not null primary key auto increment

emp_name---------varchar(100)

emp_position-------varchar(100)

emp_salary----------float

Ans: create table employee (

emp_id int not null primary key auto_increment,

emp_name varchar(100),

emp_position varchar(100),

emp_salary float );

c. Insert any 6 records using ‘insert’ and ‘values’ command.

Ans: insert into employee (emp_name,emp_position,emp_salary) values

('amit ray','officer',43000),

('anjana sha','office assistant',30000),

('rajnis bhandari','security guard',25000),

('shubha karki','teacher',32000),

('kris sharma','cleaner',45000),

('sonam chaudary','plumber',15000);

d. Display all the records of all fields.(Use select *)

Ans: select * from employee;

e. Display all the records of fields’ emp_id and emp_name.

Ans: select emp_id,emp_name from employee;

f. Display records of employees whose name starts with letter ‘ab’.

Ans: select * from employee where emp_name like 'ab%';

g. Display records of employees whose name ends at letter ‘y’ and starts from ‘b’. Ans: select * from employee where emp_name like 'b%y';

h. Display all the records of employees whose position is ‘engineer’.

Ans: select * from employee where emp_position='engineer';

i. Display all the records of employees whose salary is in range 30000-45000.

Ans: select * from employee where emp_salary>=30000 and emp_salary<=45000; j. Update the employees name with any other name who has id 1.

Ans: update employee set emp_name='asia' where emp_id=1 ;

k. Delete the record of employees whose id is 3.

Ans: delete from employee where emp_id=3;

l. Display the records of employees in sorted order. Use ‘order by field name asc/desc’.

Ans: for ascending: select * from employee order by emp_name asc; for descending: select * from employee order by emp_name desc;

m. Alter the table with following fields. Add one more field employees_address ---varchar(100). [insert after name field]

Ans: ALTER TABLE employee ADD COLUMN employees_address VARCHAR(100) AFTER emp_name; Change the size of field emp _position--varchar(200) [Use alter and modify command] Ans: alter table employee -> modify emp_position varchar(200);

n.Fin the maximum and minimum salary in database.

Ans: select max(emp_salary) from employee; for minimum salary: select min(emp_salary) from employee;

n.Find the average salary distribution in database.

Ans: select avg(emp_salary) from employee;

[Note: Use both shell interface and graphical interface for these operations]

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)

);

Search This Blog

Computer Science

Chapter 1. Database Management Software

Book's Activity Page number-37

4. Activity

Popular posts from this blog

Computer