DBMS

NOTES

1
 DBMS (Database Management System)
DBMS stands for "Database Management System." It's like a special software that
helps organize and manage large amounts of data. It is a software tool that helps
manage databases and store, organize, retrieve, and manipulate data efficiently.
A DBMS keeps your data safe and secure, making sure only authorized people can
access it. It also ensures that the data is accurate and consistent, so you don't end up
with conflicting or wrong information.
With a DBMS, many people can work with the data at the same time without getting
in each other's way. It allows for collaboration and prevents data from being
duplicated unnecessarily.
DBMS is a powerful tool that simplifies handling and organizing data, making it
useful in various fields like businesses, schools, and many other places where data
needs to be managed efficiently.
Advantages of DBMS:-
1. Data Organization: DBMS organizes data in a structured manner, making it
easier to store and retrieve information. This structured approach ensures data
integrity and reduces data redundancy.

2. Data Security: DBMS provides built-in security features to protect sensitive

information from unauthorized access. It allows access control through user
authentication and authorization.

3. Data Integrity: DBMS ensures data consistency and accuracy by enforcing

constraints and rules on the data, preventing invalid or conflicting entries.

4. Data Sharing: Multiple users can access the same data simultaneously in a
controlled manner. This enables collaboration and reduces data duplication.

5. Backup and Recovery: DBMS offers mechanisms for regular data backups and
easy data recovery in case of system failures or data loss.

Disadvantages of DBMS:-
1. Cost: Implementing a DBMS can be expensive, including licensing fees,
hardware requirements, and training costs for users.
2
2. Complexity: Managing a DBMS can be complex, requiring skilled personnel to
set up, maintain, troubleshoot the system.
and

3. Performance Overhead: Depending on the complexity of the system and the

amount of data, a DBMS can introduce performance overhead, slowing down
certain operations.

4. Single Point of Failure: If the DBMS fails, it can affect the entire system and
lead to downtime, potentially disrupting business operations.

5. Data Migration and Compatibility: Moving data between different DBMS

systems or versions can be challenging and may require data conversion, leading
to potential compatibility issues.

Logical and Physical Data Independence

 Logical Data Independence:-

1. It's about the ability to change the logical structure or organization of the
database without affecting the applications or programs using it.

2. You can modify the way data is organized, add new tables, or change
relationships between tables without breaking the existing software that relies on
the database.

3. Logical data independence makes it easier to adapt the database to new

requirements or changes in business rules without requiring extensive
modifications to the applications.

4. Application developers can focus on their tasks without worrying about the
underlying database changes, as long as the logical view remains consistent.

 Physical Data Independence:-

1. It refers to the ability to alter the physical storage or access methods of the
database without impacting the way data is represented in the applications.

3
2. You can change the storage hardware, database server, or storage optimizations
without needing to rewrite the application code.

3. Physical data independence shields the applications from the low-level technical
details of how data is stored and managed.

4. It enables system administrators to optimize the database performance or migrate

to different hardware seamlessly without affecting the functioning of
applications.
Both are crucial aspects of a well-designed database management system, as they
enable flexibility, scalability, and maintainability of the overall system.

Three Schema / Level Architecture

The three-schema architecture, also known as the ANSI/SPARC architecture. It is
a conceptual framework for database design and management. It was proposed by
the American National Standards Institute (ANSI) and the Standards Planning and
Requirements Committee (SPARC) to provide a clear separation between different
aspects of a database system.

1. External Level (also known as User Level):-

 This is the topmost layer that interacts directly with the end-users or applications.

 It defines how individual users or different software applications see and interact
with the database.

 Each user or application can have its own customized view of the data, showing
only the relevant information needed for their specific tasks.

4
 It provides a simplified and user-friendly interface, shielding users from the
complexities of the underlying database structure.

2. Conceptual Level (also known as Logical Level):-

 This is the middle layer that defines the overall logical structure and organization
of the entire database.

 It represents the entire database as a single unified entity, irrespective of how the
data is physically stored.

 The conceptual level ensures data consistency and integrity by defining

relationships, constraints, and rules that apply to the entire database.

 It acts as an intermediary between the external level and the internal level,
translating user requests into database operations.

3. Internal Level (also known as Physical Level):-

 This is the bottommost layer that deals with the physical storage and retrieval of
data on the actual hardware.

 It defines how the data is physically stored on disk, including data structures,
indexes, and access methods.

 The internal level focuses on optimizing data storage and retrieval for efficient
performance.

 Changes in the internal level, such as storage optimizations or hardware

upgrades, should not affect the conceptual and external levels.

Keys in DBMS
There are various types of keys that help establish relationships and ensure data
integrity within the database.
1. Primary Key:-
 The primary key is a unique identifier for each record (or row) in a table.

5
 It ensures that each row in the table has a distinct identity and can be uniquely
identified.
 In a table, there can be only one primary key, and it cannot contain duplicate or
null values.
Example Consider a table named "Students" with columns "Student_ID,"
"Name," and "Age." If we use the "Student_ID" column as the primary key, it will
uniquely identify each student.
Student_ID Name Age
101 John 20
102 Emily 22
103 Michael 21
2. Foreign Key:-
 A foreign key is a field in one table that refers to the primary key in another table.
 It establishes a relationship between two tables, allowing data from one table to
reference data in another.
 The foreign key ensures referential integrity, meaning that the value in the
foreign key column must match an existing value in the referenced table's
primary key.

Example Consider two tables, "Students" and "Courses." The "Courses" table
has a primary key "Course_ID," and the "Students" table has a foreign key
"Course_ID" to reference the courses taken by each student.
Students Table:-
Student_ID Name Age Course_ID
101 John 20 1
102 Emily 22 2
103 Michael 21 1

Courses Table:-

Course_ID Course_Name
1 Math
2 Science

6
3. Unique Key:-
 A unique key is similar to a primary key but allows for one or more columns to
have unique values within a table.
 Unlike the primary key, a table can have multiple unique keys.
 It ensures that the specified columns have unique values but allows for null
values.
Example In the "Students" table, we might want to ensure that the "Student_ID"
and "Name" columns have unique values. In this case, both "Student_ID" and
"Name" can be unique keys.
Student_ID Name Age
101 John 20
102 Emily 22
103 Michael 21
4. Candidate Key:-
 A candidate key is a column or set of columns that can uniquely identify each
record in a table, just like a primary key.
 A table can have multiple candidate keys, but only one is chosen as the primary
key.
Example In the "Students" table, both "Student_ID" and "Email" might uniquely
identify each student. Therefore, both "Student_ID" and "Email" are candidate keys.
However, we can choose only one of them as the primary key.
Student_ID Name Age Email
101 John 20 abcd123@gmail.com
102 Emily 22 efgh456@gmail.com
103 Michael 21 ijkl789@gmail.com
5. Super Key:-
 A super key is a set of one or more attributes that can uniquely identify records
in a table. It can include the primary key, candidate keys, or other attributes.
 Unlike a primary key or candidate key, a super key may have additional attributes
that are not strictly necessary for uniqueness.
Example In the "Students" table, a super key could be the combination of
"Student_ID" and "Phone_Number." While "Student_ID" alone is sufficient for

7
uniqueness, adding "Phone_Number" creates a super key that still uniquely
identifies each student.
Student_ID Name Age Phone_Number
101 John 20 012345679
102 Emily 22 9876543210
103 Michael 21 1122443355
6. Composite Key:-
A composite key is a primary key or candidate key that consists of two or more
attributes (columns) working together to uniquely identify each record.
Example In the "Students" table, a composite key could be the combination of
"First_Name" and "Last_Name." Individually, "First_Name" and "Last_Name"
may not be unique, but together, they form a composite key that ensures uniqueness
for each student.
Student_ID First_Name Last_Name Age Email
101 John Miller 20 abcd123@gmail.com
102 Emily Heart 22 efgh456@gmail.com
103 James Bond 21 ijkl789@gmail.com
RDBMS (Rational Database Management System)
RDBMS stands for "Relational Database Management System." In simple
language, an RDBMS is a type of software that helps manage large amounts of data
in a structured and organized manner using a system of tables and relationships.
1. Tables: In an RDBMS, data is organized into tables, which are like spreadsheets
with rows and columns. Each table represents a specific type of data, such as
customers, products, or employees.

2. Rows and Columns: Each row in a table represents a single record or data entry,
while each column represents a specific attribute or characteristic of that data,
such as name, age, or address.

3. Keys: RDBMS uses keys to uniquely identify each record in a table. The primary
key ensures that no two records have the same identification, and foreign keys
are used to establish relationships between tables.

8
4. Relationships: RDBMS allows you to create relationships between tables. For
example, you can link customers to their orders or employees to their
departments using primary and foreign keys.

5. SQL: RDBMS uses a language called SQL (Structured Query Language) to

interact with the data. With SQL, you can perform various operations like
inserting, updating, deleting, and retrieving data from the database.

6. Data Integrity: RDBMS ensures data integrity by enforcing rules and

constraints on the data. It prevents invalid or inconsistent data from being entered
into the database.

7. Data Security: RDBMS provides security features to control access to the data.
It allows you to define user roles and permissions, ensuring that only authorized
users can view or modify specific data.

8. Scalability: RDBMS can handle large amounts of data and can scale to support
growing data needs. It offers efficient data storage and retrieval mechanisms to
maintain performance as the database size increases.

9. Data Independence: RDBMS provides data independence, meaning you can

modify the database structure without affecting the applications that use the data.
This allows for easier maintenance and updates.

It is widely used in various applications, from managing inventory and customer

information to running complex financial systems and online applications.

Relational Model
The relational model is a way of organizing data in a database that follows a tabular
structure, where data is stored in tables with rows and columns. It was introduced
by Edgar F. Codd in the 1970s and has become the foundation for most modern
Database Management Systems (DBMS).
In the relational model:-
1. Tables (Relations): Data is represented using tables, also called "relations."
Each table is given a unique name and consists of rows (tuples) and columns

9
 (attributes). Each row in the table represents a specific data entry, and each
column represents a specific attribute or characteristic of the data.

2. Primary Key: Each table has a column designated as the primary key. The
primary key uniquely identifies each row in the table, ensuring no two rows have
the same identifier.

3. Foreign Key: Tables can be related to each other using foreign keys. A foreign
key is a column in one table that refers to the primary key of another table,
creating a relationship between the two tables.

4. Normalization: The relational model encourages normalization, which is the

process of organizing data to minimize redundancy and dependency. This
ensures data consistency and reduces the chances of data anomalies.

5. SQL (Structured Query Language): To interact with the data stored in the
relational database, you use SQL, which is a special language designed for
querying and manipulating relational databases.

6. Data Integrity and Constraints: The relational model allows you to enforce
various constraints on the data, such as ensuring unique values, restricting certain
data entries, and defining relationships between tables. This helps maintain data
integrity and prevents inconsistencies.

7. Data Independence: The relational model provides a high level of data

independence, meaning you can modify the database's structure without
affecting the applications using the data. This allows for easier maintenance and
system evolution.
Normalization
 Normalization is the process of organizing the data in the database.
 Normalization is used to minimize the redundancy from a relation or set of
relations. It is also used to eliminate undesirable characteristics like Insertion,
Update, and Deletion Anomalies.
 Normalization divides the larger table into smaller and links them using
relationships. The normal form is used to reduce redundancy from the database
table.
10
Advantages:-
1. Normalization helps to minimize data redundancy.
2. Greater overall database organization.
3. Data consistency within the database.
4. Much more flexible database design.
5. Enforces the concept of relational integrity.
Disadvantages:-
1. You cannot start building the database before knowing what the user needs.
2. The performance degrades when normalizing the relations to higher normal
forms, i.e., 4NF, 5NF.
3. It is very time-consuming and difficult to normalize relations of a higher degree.
4. Careless decomposition may lead to a bad database design, leading to serious
problems.

 First Normal Form (1NF):-

A relation will be 1NF if it contains an atomic value.
It states that an attribute of a table cannot hold multiple values. It must hold only
single-valued attribute.
First normal form disallows the multi-valued attribute, composite attribute, and their
combinations.
Example Relation EMPLOYEE is not in 1NF because of multi-valued attribute
EMP_PHONE.

EMPLOYEE table:

Emp_ID Emp_Name Emp_Phone Emp_State

14 John 7272826385, 9064738238 UP
20 Harry 8574783832 Bihar
Table not in 1NF
Emp_ID Emp_Name Emp_Phone Emp_State
14 John 7272826385 UP
14 John 9064738238 UP
20 Harry 8574783832 Bihar
Table in 1NF

11
 Second Normal Form (2NF):-
In the 2NF, relational must be in 1NF.
In the second normal form, all non-key attributes are fully functional dependent on
the primary key
Example Let's assume, a school can store the data of teachers and the subjects
they teach. In a school, a teacher can teach more than one subject.

TEACHER table:

TEACHER_ID SUBJECT TEACHER_AGE

25 Chemistry 30
25 Biology 30
47 English 35
83 Math 38
83 Computer 38
Non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a
proper subset of a candidate key. That's why it violates the rule for 2NF.

TEACHER_DETAIL table:

TEACHER_ID TEACHER_AGE
25 30
47 35
83 38
TEACHER_SUBJECT table:
TEACHER_ID SUBJECT
25 Chemistry
25 Biology
47 English
83 Math
83 Computer
 Third Normal Form (3NF):-
A relation will be in 3NF if it is in 2NF and not contain any transitive partial
dependency.
3NF is used to reduce the data duplication. It is also used to achieve the data
integrity.

12
If there is no transitive dependency for non-prime attributes, then the relation must
be in third normal form.
A relation is in third normal form if it holds atleast one of the following conditions
for every non-trivial function dependency X → Y.
X is a super key.
Y is a prime attribute, i.e., each element of Y is part of some candidate key.
Example:-
EMPLOYEE_DETAIL table:
EMP_ID EMP_NAME EMP_ZIP EMP_STATE EMP_CITY
222 Harry 201010 UP Noida
333 Stephan 02228 US Boston
444 Lan 60007 US Chicago
555 Katharine 06389 UK Norwich
666 John 462007 MP Bhopal
Super key in the table above:-
{EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}....so
on
Candidate key: {EMP_ID}
N on-prime a tt ributes : In the given table, all attributes except EMP_ID are non-prime.
Here, EMP_STATE & EMP_CITY dependent on EMP_ZIP and EMP_ZIP
dependent on EMP_ID. The non-prime attributes (EMP_STATE, EMP_CITY)
transitively dependent on super key(EMP_ID). It violates the rule of third normal
form.
That's why we need to move the EMP_CITY and EMP_STATE to the new
<EMPLOYEE_ZIP> table, with EMP_ZIP as a Primary key.
EMPLOYEE table:
EMP_ID EMP_NAME EMP_ZIP
222 Harry 201010
333 Stephan 02228
444 Lan 60007
555 Katharine 06389
666 John 462007
EMPLOYEE_ZIP table:
13
 EMP_ZIP EMP_STATE EMP_CITY
201010 UP Noida
02228 US Boston
60007 US Chicago
06389 UK Norwich
462007 MP Bhopal
 Boyce Codd normal form (BCNF):-
BCNF is the advance version of 3NF. It is stricter than 3NF.
A table is in BCNF if every functional dependency X → Y, X is the super key of
the table.
For BCNF, the table should be in 3NF, and for every FD, LHS is super key.
Example Let's assume there is a company where employees work in more than
one department.
EMPLOYEE table:
EMP_ID EMP_COUNTRY EMP_DEPT DEPT_TYPE EMP_DEPT_NO
264 India Designing D394 283
264 India Testing D394 300
364 UK Stores D283 232
364 UK Developing D283 549
In the above table Functional dependencies are as follows:
EMP_ID → EMP_COUNTRY
EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate key: {EMP-ID, EMP-DEPT}

The table is not in BCNF because neither EMP_DEPT nor EMP_ID alone are keys.
To convert the given table into BCNF, we decompose it into three tables:
EMP_COUNTRY table:
EMP_ID EMP_COUNTRY
264 India
364 UK
EMP_DEPT table:

14
 EMP_DEPT DEPT_TYPE EMP_DEPT_NO
Designing D394 283
Testing D394 300
Stores D283 232
Developing D283 549
EMP_DEPT_MAPPING table:
EMP_ID EMP_DEPT
D394 283
D394 300
D283 232
D283 549
Functional dependencies:-
EMP_ID → EMP_COUNTRY
EMP_DEPT → {DEPT_TYPE, EMP_DEPT_NO}
Candidate keys:-
For the first table: EMP_ID
For the second table: EMP_DEPT
For the third table: {EMP_ID, EMP_DEPT}
Now, this is in BCNF because left side part of both the functional dependencies is
a key.

 Fourth normal form (4NF):-

A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-
valued dependency.
For a dependency A → B, if for a single value of A, multiple values of B exists,
then the relation will be a multi-valued dependency.
Example:-
STUDENT table:

15
 STU_ID COURSE HOBBY
21 Computer Dancing
21 Math Singing
34 Chemistry Dancing
74 Biology Cricket
59 Physics Hockey
The given STUDENT table is in 3NF, but the COURSE and HOBBY are two
independent entity. Hence, there is no relationship between COURSE and HOBBY.
In the STUDENT relation, a student with STU_ID, 21 contains two
courses, Computer and Math and two hobbies, Dancing and Singing. So there is a
Multi-valued dependency on STU_ID, which leads to unnecessary repetition of
data.
So to make the above table into 4NF, we can decompose it into two tables:
STUDENT_COURSE table:
STU_ID COURSE
21 Computer
21 Math
34 Chemistry
74 Biology
59 Physics
STUDENT_HOBBY table:
STU_ID HOBBY
21 Dancing
21 Singing
34 Dancing
74 Cricket
59 Hockey
 Fifth normal form (5NF):-
A relation is in 5NF if it is in 4NF and not contains any join dependency and joining
should be lossless.
5NF is satisfied when all the tables are broken into as many tables as possible in
order to avoid redundancy.
5NF is also known as Project-join normal form (PJ/NF).
Example:-
16
 SUBJECT LECTURER SEMESTER
Computer Anshika Semester 1
Computer John Semester 1
Math John Semester 1
Math Akash Semester 2
Chemistry Praveen Semester 1
In the above table, John takes both Computer and Math class for Semester 1 but he
doesn't take Math class for Semester 2. In this case, combination of all these fields
required to identify a valid data.
Suppose we add a new Semester as Semester 3 but do not know about the subject
and who will be taking that subject so we leave Lecturer and Subject as NULL. But
all three columns together acts as a primary key, so we can't leave other two columns
blank.
So to make the above table into 5NF, we can decompose it into three relations P1,
P2 & P3:
P1
SEMESTER SUBJECT
Semester 1 Computer
Semester 1 Math
Semester 1 Chemistry
Semester 2 Math
P2
SUBJECT LECTURER
Computer Anshika
Computer John
Math John
Math Akash
Chemistry Praveen
P3
SEMESTER LECTURER
Semester 1 Anshika
Semester 1 John
Semester 1 John
Semester 2 Akash
Semester 1 Praveen

17
 Cardinality of a Relationship
Cardinality is crucial in database design as it helps define the relationships between
different entities in a database. It describes how many records in one table are
related to how many records in another table.
Cardinality is expressed using notations like "one-to-one" (1:1), "one-to-many"
(1:N), and "many-to-many" (N:N).
 One-to-One (1:1) Cardinality:-
In a one-to-one relationship, each record in one table is related to only one record
in the other table, and vice versa.
Example Consider two tables, "Person" and "Passport." Each person can have
only one passport, and each passport is issued to only one person. This is a one-to-
one relationship.

 One-to-Many (1:N) Cardinality:-

In a one-to-many relationship, each record in one table can be related to multiple
records in the other table, but each record in the other table is related to only one
record in the first table.
Example Consider two tables, "Department" and "Employee." Each department
can have multiple employees, but each employee belongs to only one department.
This is a one-to-many relationship.

 Many-to-Many (N:N) Cardinality:-

In a many-to-many relationship, each record in one table can be related to multiple
records in the other table, and vice versa.
Example Consider two tables, "Student" and "Course." Each student can enroll
in multiple courses, and each course can have multiple students enrolled. This is a
many-to-many relationship.
To implement a many-to-many relationship in a relational database, an intermediate
table, often called a junction or associative table, is used to link the two main tables.

Functional Dependency

18
Functional dependency is a concept in database management that describes the
relationship between attributes (columns) in a table. It indicates how one attribute's
value uniquely determines another attribute's value.
Functional dependency is denoted as X -> Y, where X determines Y, meaning for
every unique value of X, there is a unique value of Y associated with it.

Functional dependency and its types with examples:-

Consider a table "Employees" with attributes:-
Employee_ID, Employee_Name, and Department_ID.

1. Full Functional Dependency:-

A full functional dependency occurs when an attribute (Y) is functionally dependent
on the entire primary key (X) and not on any subset of the primary key.
Example In the "Employees" table, if Employee_ID is the primary key, and each
Employee_ID is associated with a unique Employee_Name, then Employee_Name
is fully functionally dependent on Employee_ID.

2. Partial Functional Dependency:-

A partial functional dependency occurs when an attribute (Y) is functionally
dependent on only a part (proper subset) of the primary key (X).
Example In the "Employees" table, if Department_ID is part of the primary key,
and each Department_ID is associated with multiple Employee_IDs and
Employee_Names, then Employee_Name is partially functionally dependent on
Department_ID .

3. Transitive Functional Dependency:-

A transitive functional dependency occurs when an attribute (Y) is functionally
dependent on another attribute (Z), which is not part of the primary key, but
indirectly related through the primary key (X).
Example In the "Employees" table, if Department_ID determines
Department_Name, then Employee_Name is transitively functionally dependent on
Department_ID through Department_Name.

19
4. Multivalued Dependency:-
A multivalued dependency occurs when an attribute (Y) is functionally dependent
on an attribute (X), and X can have multiple values for each Y.
Example Consider a table "Student_Courses" with attributes: Student_ID and
Course_Name. If each student can be enrolled in multiple courses and each course
can have multiple students, then both Student_ID and Course_Name are
multivalued dependent on each other.

Functional dependencies help in database design and normalization, ensuring data

integrity and reducing data redundancies. They are essential for creating well-
structured databases that follow standard rules and prevent data anomalies.

Generalization
It is the process of combining several entities with similar attributes and behaviors
into a more abstract entity. It allows us to represent common characteristics shared
by different entities as a single higher-level entity.
Example:-
Consider a database for a company that sells different types of vehicles, such as
cars, motorcycles, and bicycles. Instead of having separate tables for each vehicle
type, we can use generalization to create a higher-level entity called "Vehicle" that
contains common attributes like "Model," "Manufacturer," and "Price" shared by
all types of vehicles.
The specific attributes unique to each type, like "Number of Doors" for cars or
"Number of Wheels" for bicycles, can be represented in separate specialized tables.

Specialization
It is the opposite of generalization. It involves breaking down an abstract entity into
more specific sub-entities with distinct attributes and behaviors.
Example:-
Continuing with the vehicle example, we can use specialization to create specialized
entities like "Car," "Motorcycle," and "Bicycle," each representing a specific type
of vehicle. These specialized entities inherit the common attributes from the

20
"Vehicle" entity and add their own unique attributes, like "Number of Doors" for
cars or "Type of Handlebars" for bicycles.

Aggregation
It is a concept used to represent a relationship between a whole entity and its parts.
It allows us to treat a group of related entities as a single entity.
Example:-
Suppose we have a database for a school, where "Class" is an entity that contains
information about a specific class, and "Student" is another entity representing
individual students. We can use aggregation to create an entity called "Classroom,"
which aggregates all the students belonging to a particular class. The "Classroom"
entity doesn't have its own separate existence but represents a group of students
associated with a specific class.

SQL (Structured Query Language)

SQL (Structured Query Language) is a powerful and popular programming
language used for managing and manipulating relational databases. It provides a
standardized way to interact with databases, making it easy for users to retrieve,
insert, update, and delete data.
Types of SQL Commands:-
SQL commands can be broadly categorized into four main types:-
1. Data Manipulation Language (DML) Commands:-
DML commands are used to manipulate data in the database, such as inserting,
updating, and deleting records in tables.
Examples:-
1. INSERT INTO: Used to add new records (rows) into a table.
2. UPDATE: Used to modify existing records in a table.
3. DELETE FROM: Used to remove records from a table.

2. Data Definition Language (DDL) Commands:-

21
DDL commands are used to define and manage the structure of the database and its
objects, like tables, indexes, and constraints.
Examples:-
 CREATE TABLE: Used to create a new table with specified columns and data
types.
 ALTER TABLE: Used to modify the structure of an existing table, such as
adding or dropping columns.
 DROP TABLE: Used to delete an entire table and its data from the database.

3. Data Query Language (DQL) Commands:-

DQL commands are used to retrieve data from the database. They allow you to
query and retrieve specific information from one or more tables.
Examples:-
 SELECT: Used to retrieve data from one or more tables based on specified
conditions.
 FROM: Specifies the tables from which data will be retrieved.
 WHERE: Allows you to set conditions to filter the data.

4. Data Control Language (DCL) Commands:-

DCL commands are used to control access and permissions on the database objects.
They manage security and permissions for users.
Examples:-
 GRANT: Used to provide specific privileges or permissions to users or user
groups.
 REVOKE: Used to remove previously granted permissions from users.

MariaDB
MariaDB is an open-source relational database management system (RDBMS) and
a popular alternative to MySQL. It was developed by the original creators of
MySQL and is designed to be a drop-in replacement for MySQL, meaning it is
compatible with MySQL applications and tools.
Key Features:-

22
 1. Open Source: MariaDB is open-source software, which means its source code
is freely available to the public. Anyone can view, modify, and distribute the
software as per the terms of the General Public License (GPL).

2. Compatibility with MySQL: MariaDB is designed to be fully compatible with

MySQL, making it easy for users to switch from MySQL to MariaDB without
significant changes to their applications.

3. High Performance: MariaDB is optimized for high performance and offers

various storage engines that allow you to choose the most suitable one based on
your specific requirements.

4. Data Security: MariaDB provides robust data security features, including user
authentication, role-based access control, and data encryption, ensuring that only
authorized users can access sensitive data.

5. Replication and High Availability: MariaDB supports master-slave

replication, allowing you to create copies (replicas) of the database for increased
availability and fault tolerance.

6. Clustering and Sharding: MariaDB supports clustering and sharding, enabling

you to distribute data across multiple nodes to improve scalability and
performance.

7. Dynamic Columns and JSON Support: MariaDB offers dynamic columns and
native JSON support, allowing you to store and query JSON data directly in the
database.

8. Multi-Version Concurrency Control (MVCC): MariaDB uses MVCC to

manage concurrent data access, ensuring that multiple users can read and write
data simultaneously without interfering with each other.

9. Stored Procedures and Functions: MariaDB supports stored procedures and

user-defined functions, allowing you to encapsulate complex logic in the
database for better performance and code reusability.

10. Community and Support: MariaDB has a large and active community,
providing support, documentation, and continuous improvements to the
software.

23
 Datatypes used in MariaDB
In MariaDB, data types define the type of data that can be stored in a column of a
table. Each column in a table is associated with a specific data type, which
determines what kind of data can be stored in that column.
Common data types:-
1. INTEGER (or INT): Used to store whole numbers, both positive and negative,
without any decimal places. For example, 10, -20, 1000, etc.

2. FLOAT: Used to store floating-point numbers with decimal places. It can

represent numbers with fractional parts. For example, 3.14, -0.5, 2.75, etc.

3. VARCHAR: Used to store variable-length character strings (text). The length

of the VARCHAR can vary up to a specified maximum length. For example,
'Hello', 'John', 'Database', etc.

4. CHAR: Similar to VARCHAR but used for fixed-length character strings. It

always stores a specific number of characters, padding with spaces if the data is
shorter. For example, 'Cat', 'Dog', 'Car', etc.

5. DATE: Used to store dates (year, month, and day) in the format 'YYYY-MM-
DD'. For example, '2023-08-03'.

6. TIME: Used to store time values in the format 'HH:MM:SS'. For example,
'12:30:45'.

7. DATETIME: Used to store both date and time values in the format 'YYYY-
MM-DD HH:MM:SS'. For example, '2023-08-03 12:30:45'.

8. BOOLEAN: Used to store boolean values, which can be either true (1) or false
(0).

9. BLOB: Used to store binary data, such as images, audio, or video files.

10. ENUM: Used to create a list of allowed values for a column. The column can
only store one of the predefined values.

24
 SQL Commands
1. Create:-
The "CREATE" command is used to create a new database or a new table within an
existing database. When creating a table, you need to specify the table's name and
define its columns along with their data types.
Example:-
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100)
);

2. Insert:-
The "INSERT" command is used to add new data (rows) into an existing table. You
provide the values for each column to insert a new record.
Example:-
INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
VALUES (1, 'John', 'Doe', 'john@example.com');

3. Update:-
The "UPDATE" command is used to modify existing data in a table. You specify
the column(s) to update and the new values.
Example:-
UPDATE Customers
SET Email = 'newemail@example.com'
WHERE CustomerID = 1;

4. Delete:-

25
The "DELETE" command is used to remove one or more rows from a table based
on certain conditions.
Example:-
DELETE FROM Customers
WHERE CustomerID = 1;

5. Drop:-
The "DROP" command is used to remove an existing database or table from the
database system permanently. This action cannot be undone, so be careful when
using it.
Example:-
DROP TABLE Customers;

6. Alter:-
The "ALTER" command is used to modify an existing table's structure, such as
adding or removing columns, changing data types, or altering constraints.
Example (adding a new column to the table):-
ALTER TABLE Customers
ADD PhoneNumber VARCHAR(15);

SQL Statements
1. SELECT Statement:-
The "SELECT" statement is used to retrieve data from a database table. It allows
you to specify which columns you want to see and filter the rows based on certain
conditions using the "WHERE" clause.
Example:-
SELECT FirstName, LastName, Age
FROM Customers
WHERE Age >= 18;

26
This query selects the first name, last name, and age of customers from the
"Customers" table where their age is greater than or equal to 18.

2. LIKE Clause:-
The "LIKE" clause is used with the "SELECT" statement to perform pattern
matching on text data. It allows you to search for values that match a specified
pattern using wildcard characters.
Example:-
SELECT ProductName
FROM Products
WHERE ProductName LIKE 'Apple%';
This query selects the product names from the "Products" table where the product
name starts with 'Apple'.

3. GROUP BY Clause:-
The "GROUP BY" clause is used with the "SELECT" statement to group rows
based on the values of one or more columns. It is often used with aggregate
functions like SUM, AVG, COUNT, etc., to perform calculations on the grouped
data.
Example:-
SELECT Category, AVG(Price) AS AvgPrice
FROM Products
GROUP BY Category;
This query groups the products based on their category and calculates the average
price for each category.

4. ORDER BY Clause:-
The "ORDER BY" clause is used with the "SELECT" statement to sort the result
set in ascending or descending order based on one or more columns.
Example:-
SELECT FirstName, LastName, Age
27
FROM Customers
ORDER BY Age DESC;
This query retrieves the first name, last name, and age of customers from the
"Customers" table and sorts them in descending order based on the age.

5. JOIN:-
A "join" is an operation used to combine data from two or more tables based on
related columns. Joins are crucial for querying data from multiple tables and
retrieving meaningful information.
There are different types of joins, each serving a specific purpose.
Consider two tables:-
"Employees" and "Departments," where the "Employees" table has columns
"Employee_ID," "Employee_Name," and "Department_ID," and the
"Departments" table has columns "Department_ID" and "Department_Name."

1. Inner Join:-
An inner join returns only the rows that have matching values in both tables based
on the specified condition (the join predicate).
Example To get a list of employees along with their corresponding department
names, we can perform an inner join on the "Employee_ID" column in the
"Employees" table and the "Department_ID" column in the "Departments" table.

SELECT Employees.Employee_Name, Departments.Department_Name

FROM Employees
INNER JOIN Departments ON Employees.Department_ID =
Departments.Department_ID;

2. Left Join (or Left Outer Join):-

A left join returns all the rows from the left table and the matching rows from the
right table. If there's no match in the right table, NULL values are shown for the
right table's columns.

28
Example To get a list of all employees along with their corresponding department
names (if available), we can perform a left join on the "Employee_ID" column in
the "Employees" table and the "Department_ID" column in the "Departments"
table.

SELECT Employees.Employee_Name, Departments.Department_Name

FROM Employees
LEFT JOIN Departments ON Employees.Department_ID =
Departments.Department_ID;

3. Right Join (or Right Outer Join):-

A right join returns all the rows from the right table and the matching rows from the
left table. If there's no match in the left table, NULL values are shown for the left
table's columns.
Example To get a list of all departments along with the names of employees
working in each department (if available), we can perform a right join on the
"Department_ID" column in the "Departments" table and the "Department_ID"
column in the "Employees" table.

SELECT Employees.Employee_Name, Departments.Department_Name

FROM Employees
RIGHT JOIN Departments ON Employees.Department_ID =
Departments.Department_ID;

4. Full Join (or Full Outer Join):-

A full join returns all the rows from both tables, including matching rows from both
tables. If there's no match in one table, NULL values are shown for the columns of
the other table.
Example To get a list of all employees and all departments, along with their
corresponding matches (if available), we can perform a full join on the
"Employee_ID" column in the "Employees" table and the "Department_ID" column
in the "Departments" table.

SELECT Employees.Employee_Name, Departments.Department_Name

29
FROM Employees
FULL JOIN Departments ON Employees.Department_ID =
Departments.Department_ID;

6. UNION: -
The "UNION" operator combines the result sets of two or more SELECT statements
into a single result set. It removes duplicates from the combined result.
Example:-
SELECT FirstName
FROM Customers UNION SELECT FirstName
FROM Employees;
This statement will retrieve the first names of customers and employees and
combine them into a single result set, removing any duplicate names.

Lossless & Lossy Decompositions

Lossless and lossy decomposition are two methods used to break down a large piece
of data into smaller parts. Both methods are commonly used in various fields, such
as data compression and database management.
 Lossless Decomposition:-
It is a method in which data is divided into smaller pieces, but the original
information can be completely reconstructed from the smaller parts.
In other words, no information is lost during the process, and the data integrity is
preserved.
Example:-
Imagine you have a document, and you want to split it into smaller sections to send
over the internet. If you use lossless decomposition, you ensure that when the
receiver gets all the smaller sections and puts them together, they will have the exact
same document you started with. No words or details will be missing.

 Lossy Decomposition:-
It is a method where data is divided into smaller parts, but some information is
intentionally discarded to reduce the overall size.
30
In this process, some level of quality loss occurs, and the original data cannot be
perfectly reconstructed.
Example:-
An example of lossy decomposition is image compression. When you take a high-
quality image and compress it to reduce its file size, you are using lossy
decomposition. During compression, some image details are removed to save space,
which results in a decrease in image quality. If you later decompress the image, it
won't be identical to the original because some information was lost in the
compression process.

Three Conditions for Decomposition (Jan 2023)

When decomposing a relation (table) into sub-relations (smaller tables), we want to
ensure that the process is lossless, meaning that we can reconstruct the original
relation from the decomposed sub-relations without losing any information.
To achieve this, there are three conditions that must be met:-
1. Lossless Join Decomposition Condition:-
This condition ensures that when we join the sub-relations back together, we get the
original relation exactly as it was before the decomposition.

2. Lossless Dependency Preservation Decomposition Condition:-

This condition ensures that all functional dependencies present in the original
relation are preserved in at least one of the sub-relations.

3. Lossless Common Attribute Decomposition Condition:-

This condition ensures that there is at least one common attribute (common column)
between the sub-relations to maintain the connection and allow for lossless
reconstruction.

Conditions with example:

Consider a relation "StudentCourse" with the following attributes:-

StudentID (Primary Key)

StudentName
31
CourseID (Primary Key)
CourseName

Functional dependencies:-
1. StudentID -> StudentName
2. CourseID -> CourseName

Now, let's decompose this relation into two sub-relations:-

Sub-Relation 1: "StudentInfo"
- StudentID (Primary Key)
- StudentName

Sub-Relation 2: "CourseInfo"
- CourseID (Primary Key)
- CourseName

Now, let's check the three conditions:-

1. Lossless Join Decomposition Condition:

When we join "StudentInfo" and "CourseInfo" using the common attribute
"StudentID" (which is a primary key in "StudentInfo" and also present in
"StudentCourse"), we can reconstruct the original relation "StudentCourse." The
same applies when joining using "CourseID" as the common attribute.

2. Lossless Dependency Preservation Decomposition Condition:

Both functional dependencies from the original relation are preserved in the sub-
relations:
- In "StudentInfo": StudentID -> StudentName
- In "CourseInfo": CourseID -> CourseName

3. Lossless Common Attribute Decomposition Condition:

Both sub-relations have a common attribute, which is either StudentID or CourseID,
depending on which one is chosen as the primary key.
32
Since all three conditions are met, the decomposition is lossless. We can split the
"StudentCourse" relation into "StudentInfo" and "CourseInfo" sub-relations, and
later, by joining them back on the common attribute, we can obtain the original
"StudentCourse" relation without losing any information.

Acid Properties
the ACID properties are a set of four essential characteristics that ensure the
reliability and consistency of transactions. A transaction is a sequence of operations
that are executed as a single unit of work.
The ACID properties stand for:-
1. Atomicity:-
It ensures that a transaction is treated as a single indivisible unit of work. Either all
the operations within the transaction are completed successfully, or none of them
are. If any part of the transaction fails, the entire transaction is rolled back, and the
database returns to its original state before the transaction started.
Example:-
Suppose a transaction involves transferring money from one bank account to
another. Atomicity ensures that if the withdrawal from the sender's account
succeeds, but the deposit into the receiver's account fails, the whole transaction is
rolled back, and the withdrawal is undone.

2. Consistency:-
Consistency ensures that a transaction takes the database from one valid state to
another. It means that the data must satisfy all the defined integrity constraints after
the successful execution of a transaction. If a transaction violates any integrity
constraints, it is rolled back to maintain data consistency.
Example:-
If a database has a constraint that all ages must be greater than zero, and a
transaction tries to insert a record with an age of -5, the transaction will be rolled
back to keep the database in a consistent state.

3. Isolation:-

33
Isolation ensures that each transaction is executed in isolation from other
transactions. It means that the operations of one transaction are not visible to other
transactions until the transaction is committed. This prevents interference between
concurrent transactions and maintains data integrity.
Example:-
If two transactions simultaneously try to modify the same record in the database,
isolation ensures that one transaction's changes are not visible to the other until both
transactions are completed.

4. Durability:-
Durability ensures that once a transaction is committed, its changes are permanent
and survive any subsequent failures, such as power outages or crashes. The changes
made by a committed transaction are stored in a non-volatile memory, like a hard
disk, to ensure they are retained even if the system fails.
Example:-
After a successful transaction that updates a customer's address, the new address
will be permanently stored in the database, and even if the system crashes later, the
address change will still be there when the system restarts.

NoSQL
NoSQL, short for "Not Only SQL," is a type of database management system that
differs from traditional relational databases (SQL databases). It is designed to
handle large volumes of unstructured or semi-structured data, providing a more
flexible and scalable approach to data storage and retrieval.
Advantages:-
1. Scalability: NoSQL databases are built to scale horizontally, meaning they can
handle increased amounts of data by adding more servers to the system. This
makes them well-suited for applications with growing data needs.

2. Flexibility: NoSQL databases can store and handle various types of data,
including documents, key-value pairs, column-family data, and graphs. They are
not bound by a fixed schema, making them adaptable to changes in data
requirements.

34
3. High Performance: NoSQL databases can achieve faster read and write
operations compared to traditional SQL databases, especially for certain use
cases like retrieving large sets of unstructured data.

4. Schema Evolution: NoSQL databases allow developers to modify the data

structure on-the-fly without the need for a strict schema. This makes it easier to
adapt to evolving application needs.

5. Distributed Architecture: Many NoSQL databases are designed to operate on

distributed systems, providing fault-tolerance and data redundancy.

Disadvantages:-
1. Limited Querying Capability: NoSQL databases often lack the powerful
querying capabilities found in SQL databases, making it more challenging to
perform complex data analysis.

2. Lack of Standardization: Unlike SQL databases, which follow a standard query

language (SQL), NoSQL databases use different query languages and APIs,
leading to a lack of standardization across the industry.

3. Data Integrity Constraints: NoSQL databases may not enforce strong data
integrity constraints, which could lead to data inconsistencies if not handled
carefully.

4. Learning Curve: Since NoSQL databases differ significantly from traditional

relational databases, there may be a learning curve for developers and database
administrators who are more familiar with SQL-based systems.

5. Less Mature Ecosystem: NoSQL databases, especially the newer ones, may
have a less mature ecosystem compared to well-established SQL databases,
leading to fewer available tools, documentation, and community support.

Differences btw Relational & NoSQL databases

 Relational Databases:-
1. Structure: Relational databases store data in a structured manner using tables,
where each table has rows and columns. Each row represents a record, and each
column represents an attribute of that record.
35
2. Schema: Relational databases have a fixed schema, which means you need to
define the structure of tables and data types in advance. All rows in a table must
conform to this schema.

3. Query Language: Relational databases use SQL (Structured Query Language)

for querying and managing data. SQL provides powerful and standardized
operations for data manipulation.

4. Joins: Relational databases support complex joins, allowing you to retrieve data
from multiple tables based on specified relationships.

5. Transactions: Relational databases follow the ACID properties (Atomicity,

Consistency, Isolation, Durability), ensuring data integrity and consistency in
complex operations.

 NoSQL Databases:-
1. Structure: NoSQL databases store data in a more flexible and dynamic way.
They can use various data models, such as documents, key-value pairs, column
families, or graphs, to handle different types of data.

2. Schema: NoSQL databases often have a dynamic or schema-less approach,

allowing you to change the data structure on-the-fly without the need to define a
rigid schema.

3. Query Language: NoSQL databases use different query languages or APIs

based on the data model they support. Some may not have a standardized query
language like SQL.

4. Joins: NoSQL databases may not support complex joins like relational
databases. Instead, they often encourage denormalization and embedding data
for more efficient data retrieval.

5. Transactions: NoSQL databases may not fully adhere to ACID properties.

While some NoSQL databases provide certain aspects of ACID (e.g., atomicity
and durability), others may prioritize features like scalability over strict
consistency.
Ss

36