Data

 Definition: Data refers to raw facts, figures, and symbols that are collected for reference,
analysis, or processing. It can be anything from numbers, text, images, or sounds. For example, a
list of names, transaction amounts, or sensor readings.
 Types:
o Structured Data: Organized in a fixed format, often in tables (e.g., SQL databases).
o Unstructured Data: Not organized in a predefined manner (e.g., text files, videos).
o Semi-structured Data: Contains organizational properties but does not conform to a strict
structure (e.g., XML, JSON).
2. Database
 Definition: A database is an organized collection of related data stored in a structured format,
typically managed by a Database Management System (DBMS). The purpose of a database is to
store, retrieve, and manage data efficiently.
 Examples:
o Relational Databases: Organize data into tables with rows and columns (e.g., MySQL,
PostgreSQL).
o NoSQL Databases: Store data in formats like key-value pairs, documents, or graphs (e.g.,
MongoDB, Cassandra).
3. Database System
 Definition: A database system refers to the combination of a database and the software (DBMS)
used to manage it. It includes all the necessary tools and infrastructure to store, process, and
retrieve data, ensuring data integrity, security, and accessibility.
 Components:
o Database: The stored data.
o DBMS: The software that interacts with users, applications, and the database itself to
capture and analyze data.
o Hardware: Physical devices on which the database and DBMS run (e.g., servers, storage
systems).
o Users: Individuals or applications that interact with the database system.
4. Database Management System (DBMS)
 Definition: A Database Management System (DBMS) is software that provides an interface for
users to interact with databases. It allows for the creation, retrieval, updating, and management
of data in a database.
 Functions:
o Data Definition: Defining the structure of the data (schemas).
o Data Manipulation: Inserting, updating, and deleting data.
o Data Retrieval: Querying the database to get specific data.
o Data Control: Managing user access and ensuring data security and integrity.
 Examples:
o Relational DBMS (RDBMS): MySQL, Oracle, SQL Server.
o NoSQL DBMS: MongoDB, CouchDB.
Key Concepts in Databases: Field, Record, Objects, Keys
1. Field
 Definition: A field is a single piece of data or attribute in a database. It represents a column in a
table where each entry corresponds to a specific type of data, like a name, age, or product ID.
 Example: In a table of employees, "Employee Name" could be a field that stores the names of
all employees.
2. Record
 Definition: A record is a complete set of related fields, representing a single item or entity
within a database table. It corresponds to a row in the table.
 Example: In an "Employees" table, a record might include fields like Employee ID, Name,
Department, and Salary, all related to a single employee.
3. Objects
 Definition: In database terminology, an object often refers to any item within the database that
can be defined and manipulated, such as tables, views, indexes, and stored procedures. In
Object-Oriented Databases, an object is an instance of a class, storing both data and behavior
(methods).
 Example: In a relational database, a "Customer" table is an object. In an Object-Oriented
Database, a "Customer" object might include attributes (e.g., Name, Address) and methods (e.g.,
getFullName()).
4. Keys
 Definition: A key is an attribute (field) or a set of attributes used to identify a record uniquely in
a table and to establish relationships between tables.
4.1. Primary Key
 Definition: A primary key is a unique identifier for a record in a table. It ensures that each
record is unique and can be retrieved or referenced without ambiguity.
 Example: In a "Students" table, the "Student ID" field might be the primary key because it
uniquely identifies each student.

Student ID
Student Name Class Age
(Primary Key)
S001 Ram Sharma 12 17
S002 Sita Thapa 12 18
S003 Hari Bhandari 11 16
S004 Gita Karki 12 17
S005 Nabin Chaudhary 11 16
4.2. Alternate Key
 Definition: An alternate key is any candidate key that is not chosen as the primary key. It is still
unique and can serve as a unique identifier for records.
 Example: In a "Students" table, if "Email" is unique for each student but not chosen as the
primary key, it would be an alternate key.

Student ID
Student Name Email (Alternate Key) Class Age
(Primary Key)

S001 Ram Sharma [Link]@[Link] 12 17

S002 Sita Thapa [Link]@[Link] 12 18

S003 Hari Bhandari [Link]@[Link] 11 16

S004 Gita Karki [Link]@[Link] 12 17

S005 Nabin Chaudhary [Link]@[Link] 11 16

4.3. Candidate Key

 Definition: A candidate key is any field or combination of fields that can uniquely identify a
record in a table. There can be multiple candidate keys in a table, but only one can be chosen as
the primary key.

Student_ID Student_Name Email

S001 Ram Sharma ram@[Link]

S002 Sita Thapa sita@[Link]

S003 Hari Karki hari@[Link]

In this table:

 Student_ID uniquely identifies each student.

 Email also uniquely identifies each student because no two students have the same email
address.

Therefore, both Student_ID and Email are Candidate Keys because both can uniquely identify a
student record.
4.4. Foreign Key
 Definition: A foreign key is a field (or combination of fields) in one table that refers to the
primary key in another table, creating a relationship between the two tables.

Student Table (Parent Table)

Student_ID
(Primary Student_Name Class
Key)
S001 Ram Sharma 12
S002 Sita Thapa 12
S003 Hari Karki 11

Here, Student_ID is the Primary Key because it uniquely identifies each student.

Result Table (Child Table)

Result_ID Student_ID (Foreign Key) GPA

R101 S001 3.80
R102 S002 3.95
R103 S003 3.60

Here, Student_ID in the Result table is a Foreign Key because it refers to the Student_ID in the
Student table.
Relationship Diagram
Student Table Result Table
----------------- -----------------
Student_ID (PK) -------------------------------> Student_ID (FK)
Student_Name GPA
Class Result_ID

Advantages and Disadvantages of Using a DBMS (Database Management System)

Advantages of Using a DBMS
1. Data Integrity and Accuracy
o Description: A DBMS ensures that data is accurate and consistent across the database
through integrity constraints and validation rules.
o Advantage: Reduces data redundancy and inconsistency, ensuring that the information is
reliable.
2. Data Security
o Description: DBMSs provide mechanisms for data protection, including user
authentication, access control, and encryption.
o Advantage: Ensures that sensitive data is protected from unauthorized access or breaches.
 3. Data Sharing and Concurrent Access
o Description: Multiple users and applications can access the database simultaneously
without conflicts.
o Advantage: Facilitates data sharing across an organization while managing concurrency
to avoid data anomalies.
4. Data Backup and Recovery
o Description: DBMSs offer automatic backup and recovery processes to protect against
data loss.
o Advantage: Ensures data can be recovered in case of failures, minimizing downtime and
loss.
5. Data Independence
o Description: The structure of the database can be changed without affecting the
application programs that use the data.
o Advantage: Allows for easier maintenance and upgrades without disrupting business
operations.
Disadvantages of Using a DBMS
1. Cost
o Description: Implementing and maintaining a DBMS can be expensive due to software
licensing, hardware, and training costs.
o Disadvantage: High upfront and ongoing costs can be a barrier, especially for smaller
organizations.
2. Complexity
o Description: DBMSs are complex systems that require specialized knowledge to install,
configure, and manage.
o Disadvantage: The complexity may lead to a steep learning curve and require dedicated
IT staff.
3. Performance Overhead
o Description: The additional features of a DBMS, such as security checks, data integrity
enforcement, and transaction management, can slow down performance.
o Disadvantage: May cause slower response times for high-volume transactions compared
to simpler file-based systems.
4. Security Risks
o Description: While DBMSs provide security features, they can also become a single point
of failure if not properly secured.
o Disadvantage: A breach or failure in the DBMS could compromise the entire database,
leading to significant data loss or exposure.
5. Vendor Dependence
o Description: Organizations may become dependent on a particular DBMS vendor for
updates, support, and proprietary features.
o Disadvantage: Vendor lock-in can limit flexibility and increase costs if the organization
wants to switch to another system.

2.1 Installation Steps on Windows

1) To install XAMPP in windows 7,10,11 first you need to download the XAMPP
installer for windows. To download the XAMPP installer for windows, visit
the URL [Link]
This page shows the latest version of XAMPP for windows. It also shows
the versions of Apache, PHP, MySQL, and other softwares included in this
version of XAMPP.
2) Now, go to the "Download" section in the page. Here, you will see
XAMPP for Windows, Linux, and Mac OS X. We can easily download the
XAMPP installer for Windows.
3) Click on the Download link to download XAMPP as shown below

4) After downloading the installer, double click on the executable(.exe) file

to start the XAMPP installation process. Click Yes, if User Account Control
dialog box appears.
Select your language in the dialog box then click OK.

5) This dialog box below shows that you should avoid installing XAMPP to
C:\Program Files. Click OK.
 Open Command prompt. type the command .

Structured Query Language:

SQL (Structured Query Language) is a standard database language used to

communicate with relational databases. It allows users to create tables, insert
data, retrieve information, update records, and delete data efficiently.

Types of SQL Commands

SQL commands are divided into five types based on their functions.

Type Full Form Purpose Examples of Commands

Data Definition Defines and modifies the CREATE, ALTER, DROP,

DDL
Language structure of a database. TRUNCATE, RENAME

Data Manipulation Inserts, updates, and deletes data

DML INSERT, UPDATE, DELETE
Language in tables.

DQL Data Query Language Retrieves data from the database. SELECT

Data Control Controls user permissions and

DCL GRANT, REVOKE
Language access.

Transaction Control COMMIT, ROLLBACK,

TCL Manages database transactions.
Language SAVEPOINT
DDL (Data Definition Language) and DML (Data Manipulation Language)
1. Data Definition Language (DDL)
 Definition: DDL is a subset of SQL (Structured Query Language) used to define and manage the
structure of a database. It includes commands that create, alter, and delete database objects such
as tables, indexes, and schemas.
 Key Commands:
o CREATE: Creates new database objects such as tables, indexes, views, or entire
databases.

Example:
step1. CREATE DATABASE Employee;
step 2. use Employee;
[Link] TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100),
Department VARCHAR(50));
step 4: show tables;
step 5: describe Employee;

ALTER: Modifies the structure of an existing database object, such as adding or dropping a column in
a table.
 Example:
ALTER TABLE Employees ADD Salary DECIMAL(10, 2);

DROP: Deletes database objects like tables, indexes, or databases.

 Example:
DROP TABLE Employees;

Data Manipulation Language (DML)

 Definition: DML is a subset of SQL used to interact with and manipulate the data within a
database. It includes commands to retrieve, insert, update, and delete data in database tables.

Key Commands:
o SELECT: Retrieves data from one or more tables based on specified criteria.
 Example:
SELECT Name, Department FROM Employees WHERE Salary > 50000;

INSERT: Adds new records (rows) to a table.

 Example:
INSERT INTO Employees (EmployeeID, Name, Department, Salary) VALUES (1, 'John Doe', 'HR',
55000);

UPDATE: Modifies existing records in a table.

 Example:
UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;
DELETE: Removes records from a table based on specified conditions.
 Example:
DELETE FROM Employees WHERE EmployeeID = 1;

Step-by-Step Process

1. Open DOS Command Prompt

 Start your computer in DOS mode or open a command prompt window if you're using a modern
version of Windows.

2. Start the Database Server

 If the database server (MySQL, for example) is not running, you'll need to start it. You can
usually do this by navigating to the directory where the MySQL server is installed and running a
command like

mysqld
This starts the MySQL server and allows you to connect to it.

Connect to the Database

 Once the server is running, you can connect to it using the MySQL client:

mysql -u username –p

 Replace username with your MySQL username. The -p flag prompts you to enter your password.

4. Create a Database

 After connecting to the MySQL client, you can create a new database with the following
command:

CREATE DATABASE database_name;

Select the Database

 To use the database you've created or another existing database, use the USE command:

USE database_name;

6. Create Tables

 Define the structure of your tables using the CREATE TABLE command:
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(100),
Department VARCHAR(50),
Salary DECIMAL(10, 2));

7. Insert Data into Tables

 Insert records into your tables using the INSERT command:

INSERT INTO Employees (EmployeeID, Name, Department, Salary)VALUES (1, 'John Doe',
'HR', 55000);

8. Query Data

 Retrieve data from the tables using the SELECT command:

SELECT * FROM Employees WHERE Salary > 50000;

9. Update Data

 Modify existing records using the UPDATE command:

UPDATE Employees SET Salary = 60000 WHERE EmployeeID = 1;

10. Delete Data

 Remove records from a table using the DELETE command:

DELETE FROM Employees WHERE EmployeeID = 1;

11. Exit the Database

 To exit the MySQL client, type:

EXIT;

12. Stop the Database Server

 If necessary, stop the database server by closing the command prompt window or by running a
specific shutdown command, depending on your DBMS:

mysqladmin -u username -p shutdown

Database Model:
A database model is a logical structure that defines how data is organized, stored, and related in a
database. It provides rules for creating, managing, and accessing data efficiently.

Example Using Student Information

Suppose a school stores the following student records.

Students Table
Student_ID Student_Name Class Section
S001 Aayush 11 A
S002 Sita 11 B
S003 Rohan 12 A

Subjects Table

Subject_ID Subject_Name
C101 Computer Science
M101 Mathematics
E101 English

Student_Subject Table:
Student_ID Subject_ID
S001 C101
S001 M101
S002 E101
S003 C101

Database Model: Network Model, Hierarchical Model, Relational database Model

Hierarchical Database Model

Hierarchical database model is one of the oldest type database models., In this model data is
represented in the form of records. Each record has multiple fields/attributes. All records are arranged
in database as tree like structure. The relationship between the records is called parent/ child
relationship in which any child record relates to only a single parent type record. In the figure below,
there are parent records at the top level with high privileges and child records at bottom Level.
 SCHOOL
(Gyan Jyoti
Model School)

Class 11
Class 11 Science
Management

Aarav Sita Rohan Ram Priya Aayush

Another Example:

SCHOOL
(Gyan Jyoti
Model School)

Computer Management
Department Department

Mr. Sitaram Ms. Basudev

Regmi Khanal

Aarav Sita Rohan Priya Ram Anisha

Advantages of Hierarchical Database Model

 It is the easiest model.

 It has one or more attributes.
 The searching is fast and easy, if parent is known.
 It supports one-to-one and one-to-many relationship.
Disadvantages of Hierarchical Database Model

 It is old fashioned, outdated database model.

 It does not support many-to-many relationship.
 The dependency on parent node is not beneficial always.
 It increases redundancy because same data is to be repeated in different places.

Network Database Model

The network database model replaced hierarchical database model due to some limitations on the
model. Suppose if an employee relates to two departments, then the hierarchical database model cannot
able to arrange records in proper place. So network database model was emerged to arranged non-
hierarchical database. The structure of database is more like graph rather than tree structure. A network
database model consists of collection of records which are inter-related to each other with the help of
relationship. Each record has multiple fields and each field has only one data value. In this model, each
record in the database can have multiple parents that is the relationships among data elements can have
many to many relationships.

Fig : Network Database Model

In the above diagram, there are two parent tables (students and teachers) and each of them has
child tables (subjects and class respectively). However, each of these child tables is interconnected to
one another, which establishes the relationship in the database.
 This figure shows that Order is related to Customer, Manager, and Salesman
simultaneously. Since one record is connected to multiple other records, it demonstrates a
Network Database Model, which supports many-to-many relationships and is more
flexible than the Hierarchical Database Model.
Advantages of Network Database Model

 It accepts many-to-many relationship, so it is more flexible.

 The searching is faster because of multidirectional pointers.
 The network model is simple and easy to design.
 It reduces redundancy because data shouldn't be repeated if same data is needed.

Disadvantages of Network Data Model

 It is difficult to handle the relationship in complex programs.

 There is less security because of sharing data.
 It increases the processing overhead due to the complex relationship

Relational Database Model

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

We can notice from below table (Student and Marks), here The StudentID in the Students table is the
Primary Key, while the StudentID in the Marks table is the Foreign Key. This creates a relationship
between the two tables.
Table :Marks
Table :Student MarkID StudentID Subject Marks
StudentID (PK) Name (FK)
1 101 Computer 85
101 Hari
2 102 Computer 92
102 Sita
3 103 Computer 78
103 Ram
Advantages of Relational Database Model

 The breaking of complex database table into simple database table becomes possible.
 Database processing is faster than other model.
 There is very less redundancy (reputation of data).
 The integrity rules can easily be implemented.

Disadvantages of Relational Database Model

 It is more complex than other models.

 There are too many rules because of complex relationships.
 It needs more powerful computers and data storage devices.

Concept of Normalization: 1NF, 2NF, 3NF

Normalization is a systematic process of organizing data in a database to minimize redundancy and

dependency. The goal of normalization is to ensure that data is stored efficiently, avoiding duplication
and ensuring data integrity.

1. First Normal Form (1NF):

 Definition: A table is in 1NF if it only contains atomic (indivisible) values, and each entry in a
column contains a single value. Essentially, there should be no repeating groups or arrays in any
row.
 Rules:
o Each column must contain only atomic values.
o Each column must contain values of a single type.
o Each column must have a unique name.
o The order in which data is stored does not matter.

 Problem:
The Subjects column contains multiple values in a single cell, so the table is not in 1NF.

StudentID Student Name Subjects

101 Ram Computer, English
102 Sita Mathematics, Science

Step 1: Original Table (Not in 1NF)

CREATE TABLE Student (StudentID INT PRIMARY KEY, StudentName VARCHAR(50), Subjects VARCHAR(100));

Insert Data
INSERT INTO Student VALUES(101, 'Ram', 'Computer, English'),(102, 'Sita', 'Mathematics, Science');
Problem: The Subjects column contains multiple values in a single cell, which violates First Normal
Form (1NF).

Convert into First Normal Form (1NF)

Create a new table where each subject is stored in a separate row.
CREATE TABLE Student_1NF (StudentID INT,StudentName VARCHAR(50), Subject VARCHAR(50), PRIMARY KEY
(StudentID, Subject));

Insert Data
INSERT INTO Student_1NF VALUES (101, 'Ram', 'Computer'),(101, 'Ram', 'English'),(102, 'Sita', 'Mathematics'),
(102, 'Sita', 'Science');

View the Table

SELECT * FROM Student_1NF;

Converted into 1NF

StudentID Student Name Subject
101 Ram Computer
101 Ram English
102 Sita Mathematics
102 Sita Science

Query to Find All Subjects of Student 101

SELECT Subject FROM Student_1NF WHERE StudentID = 101;

Query to Find Students Who Study English

SELECT StudentID, StudentName FROM Student_1NF WHERE Subject = 'English';

Explanation:

 Each cell now contains only one value.

 There are no repeating groups.
 The table satisfies the rules of First Normal Form (1NF).

Characteristics of 1NF

 Each field contains only a single value.

 No multiple or repeating values in a column.
 Each row is unique.
 Data is organized in a simple tabular format.

Advantages

 Eliminates repeating groups.

  Makes data easier to search and update.
 Improves data consistency.
 Provides the foundation for higher normal forms (2NF and 3NF).

2. Second Normal Form (2NF):

 Definition: A table is in 2NF if it is in 1NF and all non-primary key attributes are fully
functionally dependent on the primary key. This means that there should be no partial
dependency; every non-key attribute must depend on the entire primary key, not just part of it.
 Rules:
o The table must be in 1NF.
o There should be no partial dependency; all non-key attributes must be fully dependent on
the primary key.

Step 1: Create the Original Table (Not in 2NF)

CREATE TABLE StudentMarks(StudentID INT,SubjectID VARCHAR(10),StudentName
VARCHAR(50), SubjectName VARCHAR(50),Marks INT,PRIMARY KEY (StudentID, SubjectID));
Step 2: Insert the Data
INSERT INTO StudentMarks(StudentID, SubjectID, StudentName, SubjectName,
Marks)VALUES(101, 'C101', 'Ram', 'Computer', 85),(101, 'M101', 'Ram', 'Mathematics', 78),(102,
'C101', 'Sita', 'Computer', 90);
Display the Table
SELECT * FROM StudentMarks;

Example (Not in 2NF)

StudentID SubjectID StudentName SubjectName Marks

101 C101 Ram Computer 85
101 M101 Ram Mathematics 78
102 C101 Sita Computer 90

Primary Key:

(StudentID, SubjectID) (Composite Key)

Problem:

 StudentName depends only on StudentID.

 SubjectName depends only on SubjectID.
 They do not depend on the whole composite key.

Therefore, the table is not in 2NF because it has partial dependency.

Fetch Marks of StudentID = 101 and SubjectID = 'C101'
SELECT Marks FROM StudentMarks WHERE StudentID = 101 AND SubjectID = 'C101';

Convert into Second Normal Form (2NF)

Student Table
CREATE TABLE Student (StudentID INT PRIMARY KEY, StudentName VARCHAR(50));
INSERT INTO Student VALUES (101,'Ram'),(102,'Sita');

Subject Table
CREATE TABLE Subject (SubjectID VARCHAR(10) PRIMARY KEY, SubjectName VARCHAR(50));
INSERT INTO Subject VALUES ('C101','Computer'), ('M101','Mathematics');

Marks Table
CREATE TABLE Marks ( StudentID INT, SubjectID VARCHAR(10), Marks INT, PRIMARY KEY
(StudentID, SubjectID), FOREIGN KEY (StudentID) REFERENCES Student(StudentID), FOREIGN
KEY (SubjectID) REFERENCES Subject(SubjectID));
INSERT INTO Marks VALUES(101,'C101',85),(101,'M101',78),(102,'C101',90);

Converted into 2NF

Students Table Subject Table

StudentID (PK) StudentName SubjectID (PK) SubjectName

101 Ram C101 Computer
102 Sita M101 Mathematics

Student_Marks Table

StudentID (FK) SubjectID (FK) Marks

101 C101 85
101 M101 78
102 C101 90

Retrieve Complete Information (After 2NF)

SELECT
[Link], [Link], [Link], [Link], [Link] FROM Student s JOIN
Marks m ON [Link] = [Link] JOIN Subject sub ON [Link] = [Link];

Characteristics of 2NF

 The table must already be in 1NF.

 Removes partial dependency.
 Every non-key attribute depends on the entire primary key.
  Data redundancy is reduced.

Advantages

 Eliminates unnecessary duplicate data.

 Improves data consistency.
 Reduces update, insertion, and deletion anomalies.
 Makes the database easier to maintain.

3. Third Normal Form (3NF):

 Definition: A table is in 3NF if it is in 2NF and all the attributes are functionally dependent only
on the primary key. There should be no transitive dependency, meaning no non-key attribute
should depend on another non-key attribute.
 Rules:
o The table must be in 2NF.
o There should be no transitive dependency.

Create the Original Table (Not in 3NF)

CREATE TABLE Student (StudentID INT PRIMARY KEY,StudentName VARCHAR(50), DepartmentID
VARCHAR(10),DepartmentName VARCHAR(50));

Insert Data
INSERT INTO Student(StudentID, StudentName, DepartmentID, DepartmentName)
VALUES(101, 'Ram', 'D01', 'Computer Science'),(102, 'Sita', 'D02', 'Management'),(103, 'Hari', 'D01', 'Computer
Science');

Display the Table

SELECT * FROM Student;

Example (Not in 3NF)

Student Table

StudentID StudentName DepartmentID DepartmentName

(PK)
101 Ram D01 Computer Science
102 Sita D02 Management
103 Hari D01 Computer Science

Problem
 StudentID → DepartmentID
  DepartmentID → DepartmentName

Here, DepartmentName depends on DepartmentID, not directly on StudentID.

This is called transitive dependency, so the table is not in 3NF.

Convert into 3NF

Student Table
CREATE TABLE Student ( StudentID INT PRIMARY KEY, StudentName VARCHAR(50),
DepartmentID VARCHAR(10), FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID));

Insert Data
INSERT INTO Student VALUES(101,'Ram','D01'),(102,'Sita','D02'),(103,'Hari','D01');

Department Table
CREATE TABLE Department ( DepartmentID VARCHAR(10) PRIMARY KEY,DepartmentName VARCHAR(50));

Insert Data
INSERT INTO Department VALUES('D01','Computer Science'),('D02','Management');

Retrieve Complete Information

SELECT [Link], [Link], [Link], [Link] FROM Student s
JOIN Department d ON [Link] = [Link];

Converted into 3NF

Students Table Departments Table

StudentID StudentName DepartmentID DepartmentID DepartmentName

(PK) (FK) (PK)
101 Ram D01 D01 Computer Science
102 Sita D02
D02 Management
103 Hari D01
Now:
Query to Find the Department of StudentID = 101
SELECT [Link],[Link],[Link] FROM Students JOIN Department d ON
[Link] = [Link] WHERE [Link] = 101;
 StudentName depends only on StudentID.
 DepartmentName depends only on DepartmentID.
 There is no transitive dependency.
Therefore, the database is in Third Normal Form (3NF).
Characteristics of 3NF

 The table must already be in 2NF.

 Removes transitive dependency.
 Every non-key attribute depends only on the primary key.
 Reduces data redundancy and improves data integrity.

Advantages

 Eliminates transitive dependency.

 Reduces duplicate data.
 Prevents update, insertion, and deletion anomalies.
 Makes the database easier to maintain.
 Improves consistency and accuracy.

Centralized Vs. Distributed Database:-

Centralized Database:

A centralized database is basically a type of

database that is stored, located as well as
maintained at a single location only. This type of
database is modified and managed from that
location itself. This location is thus mainly any
database system or a centralized computer system.
The centralized location is accessed via an internet
connection (LAN, WAN, etc). This centralized
database is mainly used by institutions or
organizations.

Advantages:

 Since all data is stored at a single location only thus it is easier to access and coordinate data.
 The centralized database has very minimal data redundancy since all data is stored in a single
place.
 It is cheaper in comparison to all other databases available.

Disadvantages:

 The data traffic in the case of a centralized database is more.

 If any kind of system failure occurs in the centralized system then the entire data will be
destroyed.
2. Distributed Database:

A distributed database is basically a type of

database which consists of multiple databases
that are connected with each other and are
spread across different physical locations. The
data that is stored in various physical
locations can thus be managed independently
of other physical locations. The
communication between databases at different
physical locations is thus done by a computer
network.

Advantages:

 This database can be easily expanded as data is already spread across different physical
locations.
 The distributed database can easily be accessed from different networks.
 This database is more secure in comparison to a centralized database.

Disadvantages:

 This database is very costly and is difficult to maintain because of its complexity.
 In this database, it is difficult to provide a uniform view to users since it is spread across
different physical locations.

Data Security:

Data security involves protecting digital information from unauthorized access, corruption, or theft
throughout its lifecycle. It encompasses various practices, technologies, and standards designed to
ensure the confidentiality, integrity, and availability of data. Key aspects of data security include:

1. Encryption: Converting data into a code to prevent unauthorized access. Both data at rest
(stored data) and data in transit (data being transmitted) can be encrypted.
2. Access Control: Restricting who can view or use data. This can be managed through
authentication methods like passwords, biometric scans, or multi-factor authentication, and
authorization measures like role-based access controls.
3. Data Masking: Obscuring specific data within a database to protect it from unauthorized users,
often used in non-production environments.
4. Backup and Recovery: Regularly backing up data and ensuring that it can be recovered in case
of loss, corruption, or disaster.
 5. Firewalls and Intrusion Detection Systems (IDS): Using software or hardware-based systems
to monitor and control incoming and outgoing network traffic, and detect and respond to
potential threats.

Database Administrator (DBA):

 A Database Administrator is a professional responsible for managing, backing up, and ensuring
the overall performance, security, and integrity of a database system. DBAs are crucial for
maintaining the availability of data, optimizing database performance, and implementing
necessary updates or patches.

Key Responsibilities of a DBA:

 Database Installation and Configuration: Setting up and configuring database management

systems (DBMS) like Oracle, MySQL, SQL Server, etc.
 Performance Monitoring and Tuning: Ensuring the database is running efficiently by
monitoring performance, optimizing queries, and adjusting configurations.
 Backup and Recovery: Implementing strategies to back up data and recover it in case of
failures or corruption.
 Security Management: Ensuring that only authorized users have access to the database and
implementing encryption or other security measures.
 Data Migration: Moving data between different systems or environments as needed.
 Troubleshooting: Diagnosing and resolving database-related issues.
 Updating and Patching: Applying updates to the DBMS software to fix bugs or add new
features.

What is Relational Database Management System?

A Relational Database Management System (RDBMS) is a type of database management system

(DBMS) that stores data in a structured format using rows and columns. The RDBMS model organizes
data into tables (also known as relations) that are linked to each other through keys, making it possible
to perform complex queries and data manipulation operations efficiently. RDBMSs are the most
widely used database systems for managing structured data, and they support SQL (Structured Query
Language) as the standard language for querying and managing the data.

Key Features of RDBMS

1. Data Structure
o Tables: Data is organized into tables, where each table is a collection of related data
entries. Each row in a table represents a record, and each column represents an attribute of
the record.
o Schema: The schema defines the structure of the database, including the tables, columns,
and the relationships between tables.
2. Keys
 o Primary Key: A unique identifier for each record in a table. It ensures that no two rows
have the same primary key value.
o Foreign Key: A field in a table that creates a link between two tables. It is a reference to
the primary key in another table, establishing a relationship between the two tables.
o Composite Key: A primary key that consists of two or more columns to uniquely identify
a record in the table.
3. Relationships
o One-to-One: A single record in one table is related to a single record in another table.
o One-to-Many: A single record in one table is related to multiple records in another table.
o Many-to-Many: Multiple records in one table are related to multiple records in another
table. This relationship often requires a function table.
4. Data Integrity
o Entity Integrity: Ensures that each record within a table is unique, typically enforced
through the primary key.
o Referential Integrity: Ensures that foreign keys correctly reference the primary keys in
related tables, maintaining consistent relationships between tables.
o Domain Integrity: Ensures that all data in a column follows the defined format, data type,
and constraints.
5. Normalization
o Normalization: The process of organizing data in a database to reduce redundancy and
improve data integrity. This involves dividing large tables into smaller ones and defining
relationships between them.
6. SQL (Structured Query Language)
o Querying: SQL is used to retrieve data from the database. Examples include SELECT,
JOIN, GROUP BY, and ORDER BY clauses.
o Data Manipulation Command: SQL commands such as INSERT, UPDATE, and
DELETE are used to modify data.
o Data Definition Language: SQL allows defining and altering the structure of the
database using commands like CREATE TABLE, ALTER TABLE, and DROP TABLE.
o Transaction Control: SQL includes commands like BEGIN, COMMIT, and
ROLLBACK to manage transactions and ensure data integrity.