DBMS – Unit 1 Detailed Notes
Introduction to DBMS
A Database Management System (DBMS) is basically the OG system that helps us store, organize, and
manage data in a structured way. Before DBMS came around, people used file systems—like those classic
registers and files in old offices—super messy, super confusing. DBMS came in like a hero and said, “Chill,
I’ll handle your data.”
Why Database?
A database is needed because:
• Life is chaotic, and data is even more chaotic without structure.
• Organizations store tons of information—students’ records, banking details, product info, employee
data, etc.
• If stored randomly, data becomes confusing, time-consuming, and prone to errors.
So databases bring order, accuracy, and speed to the table.
Characteristics of Data in a Database
1. Shared Data:
Multiple users can access the same data simultaneously. Example: Your college’s attendance portal.
2. Persistent Storage:
Data stays safe even after the program is closed. Like your mark sheets stored in the university
server.
3. Non-volatile:
Power cut won’t delete the stored data (thank God).
4. Structured Format:
Stored using tables, rows, and columns.
5. Redundancy Control:
Same data won’t be stored repeatedly (unlike Indian parents repeating the same dialogues every
day).
6. Integrity:
Data remains accurate, valid, and reliable.
7. Consistency:
All updates maintain data correctness. No contradictory values.
8. Security:
Only authorized users get access.
(Your exam record won't be editable by your friend—sad.)
DBMS – Meaning
DBMS is software that allows users to store, retrieve, and manage data efficiently.
Examples: MySQL, Oracle, PostgreSQL, SQL Server, MongoDB.
It provides:
• A systematic way to organize data
• Tools to insert, delete, update, and retrieve data
• Strong security and backup features
• High performance for complex queries
Advantages of DBMS
1. Reduces Data Redundancy:
Stores data only once, preventing duplication.
2. Improves Data Consistency:
Same data across all applications.
3. Enhanced Security:
Logins, permissions, encryption… sab kuch.
4. Data Sharing:
Multiple users can access data concurrently.
5. Backup and Recovery:
Automatic backups help recover data after system crashes.
6. Better Data Integration:
All data stays centralized, easy to use for analytics.
7. Enforces Data Integrity:
Ensures only correct data goes into the database.
8. Improved Decision-Making:
Accurate reports = smarter decisions.
9. Querying Capability:
SQL lets users ask questions from the database and get instant answers.
10. Scalability:
Easy to handle huge data without issues.
Database Architecture and Modeling
Database architecture decides how data is structured inside a DBMS. Think of it as the “blueprint” of the
entire system.
Types of Database Models
There are three major levels defined by the ANSI-SPARC architecture:
1. Conceptual Model (High-level design)
• Focuses on the overall structure of the entire database.
• Describes entities, attributes, and relationships.
• Independent of hardware or software.
• Example: ER diagrams.
Think of it like making the layout of a house before building anything.
2. Logical Model (DBMS-specific design)
• Represents how data is logically stored in the database.
• Table structures, primary keys, foreign keys, constraints, relationships.
• Independent of physical storage.
This is like preparing the actual floor plan with room sizes, locations, etc.
3. Physical Model (Low-level design)
• How data is stored on the disk physically.
• Focuses on indexing, partitioning, file structures.
• Very close to hardware.
This is like deciding how the cement, bricks, wiring, plumbing will be placed.
Role of DBA (Database Administrator)
A DBA is basically the guardian of the database.
Like a watchman + engineer + doctor for the database.
Main Responsibilities:
1. Installation & Configuration:
Setting up DBMS software.
2. Backup & Recovery:
Ensures data is never lost.
(DBA kabhi system crash se nahi darta.)
3. Security Management:
Grants and revokes user permissions.
4. Performance Tuning:
Optimizes query execution, indexing, memory management.
5. Database Design:
Helps create schemas, tables, and structures.
6. Storage Management:
Allocates storage space.
7. Monitoring:
Keeps an eye on database health.
8. Data Migration:
Moves data safely during upgrades.
Database Design
Database design ensures data is structured properly before storing.
Steps in Database Design:
1. Requirement Analysis:
Understand what the user needs.
2. Conceptual Design:
Identify entities, attributes, relationships (ER modeling).
3. Logical Design:
Create table structures, keys, constraints.
4. Normalization:
Remove redundancy and improve consistency.
5. Physical Design:
Define indexes, partitioning, storage formats.
6. Implementation:
Create the database using SQL.
Entity Relationship (ER) Model: [Link]
UNIT- 2 The Relational Data Model
I. Fundamental Concepts of the Relational Model
This section covers the basic building blocks of the relational model, which was first proposed by E.F. Codd.
1. Relations (Tables)
A relation is the main construct in the relational model. It's a two-dimensional table used to store data
about a specific entity (e.g., Students, Courses).
• Tuple: A row in the table. It represents a single record or data item (e.g., one specific student).
• Attribute: A column in the table. It represents a property or characteristic of the entity (e.g.,
StudentID, FirstName).
• Domain: The set of all possible legal values for an attribute. For example, the domain for a Grade
attribute might be the set {'A', 'B', 'C', 'D', 'F'}.
• Schema: The design or "blueprint" of the relation, written as:
RelationName(Attribute1, Attribute2, ...)
Example: Student(StudentID, LastName, Major)
• Instance: The actual data in the relation at any given point in time (i.e., the set of all tuples).
Key Properties of a Relation:
1. Atomic Values: Each cell (intersection of a row and column) must contain a single, indivisible value.
This is the foundation of First Normal Form (1NF).
2. Unique Tuples: No two rows in a relation can be completely identical. This is guaranteed by defining
a Primary Key.
3. Attribute Order is Insignificant: The order of columns doesn't matter. Attributes are referenced by
their name, not their position.
4. Tuple Order is Insignificant: The order of rows doesn't matter. A relation is a set of tuples.
2. Null Values
A Null value represents "missing" or "unknown" information. It is not the same as zero (0) or an empty
string ("").
• Meaning: NULL can mean:
o Value Unknown: The value exists but is not currently known (e.g., a PhoneNumber is NULL
because we haven't collected it yet).
o Value Inapplicable: The value does not apply to this specific tuple (e.g., a SpouseName
attribute might be NULL for an unmarried person).
• Issues with NULL:
o Logic: NULL complicates logical comparisons. A check like Salary > 50000 is neither true nor
false if Salary is NULL; it is unknown. This requires three-valued logic (True, False, Unknown).
o Aggregates: Functions like COUNT(), SUM(), and AVG() must be defined to either include or
(more commonly) ignore NULL values, which can be confusing.
o Constraints: NULL is problematic in primary keys (see Entity Integrity).
3. Keys
Keys are one or more attributes used to uniquely identify tuples (rows) in a table. They are crucial for
creating relationships and ensuring data integrity.
• Superkey: Any set of one or more attributes that, taken together, can uniquely identify a tuple in a
relation.
o Example: In a Student table with (StudentID, SSN, Email, Name), {StudentID}, {SSN}, {Email},
{StudentID, Name}, and {SSN, Name} are all superkeys.
• Candidate Key: A minimal superkey. This is a superkey from which no attribute can be removed
without it losing its uniqueness.
o Example: From the list above, {StudentID}, {SSN}, and {Email} are candidate keys. {StudentID,
Name} is not a candidate key because Name is redundant ( {StudentID} is already unique on
its own).
• Primary Key (PK): The one candidate key chosen by the database designer to be the main, official
identifier for tuples in the relation.
o It cannot contain NULL values.
o It must be unique.
o Example: The designer would likely choose {StudentID} as the primary key.
• Alternate Key: Any candidate key that was not chosen to be the primary key.
o Example: {SSN} and {Email} would be alternate keys. They are still unique, but not the
primary identifier.
• Foreign Key (FK): An attribute (or set of attributes) in one relation that references the primary key
of another relation (or the same relation). This is the "glue" that links tables together.
o Example: In a CourseEnrollment table, (StudentID, CourseID), the StudentID attribute is a
foreign key that references the StudentID primary key in the Student table.
4. Integrity Constraints
These are rules that the Database Management System (DBMS) must enforce to maintain the accuracy and
consistency of data.
• Entity Integrity:
Rule: No attribute of a primary key can ever be NULL.
o Reason: The primary key is the unique identifier for a row. If it were NULL, we would have a
row that we cannot identify, which defeats its purpose.
• Relational Integrity (Referential Integrity):
Rule: If a relation R1 has a foreign key FK that references the primary key PK of relation R2, then every
value of FK in R1 must either:
1. Be equal to an existing PK value in R2, OR
2. Be NULL. (This is allowed only if the foreign key column is not part of its own table's primary
key and is not set to NOT NULL).
o Reason: This rule prevents "orphan" records. It ensures that you cannot have an Enrollment
record for a StudentID that doesn't exist in the Student table.
• Domain Integrity:
o Rule: All values in an attribute must be from its defined domain.
o Reason: Ensures data is valid. Enforced through data types (e.g., INT, DATE), CHECK
constraints (e.g., Age > 18), and NOT NULL constraints.
• Key Integrity:
o Rule: All candidate keys (including the primary key) must be unique within their relation.
o Reason: This is the definition of a key. It prevents duplicate records.
II. The Normalization Process
Normalization is a formal, step-by-step process of decomposing (splitting) relations into smaller, well-
structured relations.
Goals:
• Minimize Data Redundancy: Avoid storing the same piece of information multiple times.
• Avoid Anomalies: Prevent problems that can occur when inserting, updating, or deleting data.
o Insertion Anomaly: Cannot add a new record because part of the key is missing (e.g., cannot
add a new Course until a Student enrolls in it).
o Deletion Anomaly: Deleting one piece of data accidentally deletes other unrelated data
(e.g., deleting the last student enrolled in a course also deletes the course's existence).
o Update Anomaly: Updating a single piece of data requires changing it in multiple rows (e.g.,
a StudentName is stored in every Enrollment record, and if the name changes, all rows must
be updated).
1. Functional Dependencies (FDs)
This is the single most important concept in normalization.
• Definition: An FD, denoted $X \to Y$, exists in a relation if two tuples that have the same value for
attribute set X must also have the same value for attribute set Y.
• Meaning: We say "X functionally determines Y" or "Y is functionally dependent on X."
• Simple Example:
o In a Student table: {StudentID} \to {StudentName}
o This means if you find two rows with the same StudentID, they must have the same
StudentName. Knowing the StudentID uniquely determines the name.
2. Normal Forms (The Steps)
This is a hierarchy. To be in nNF, a relation must first be in (n-1)NF.
First Normal Form (1NF)
• Rule: A relation is in 1NF if and only if all its attributes are atomic.
• Meaning: No "repeating groups" or multi-valued attributes. You cannot have a PhoneNumber
column that contains a list of numbers ("555-1234, 555-9876").
• Solution: To fix a non-1NF table, you "flatten" it by creating a separate row for each value or, more
correctly, create a new table.
o Example:
▪ Non-1NF: Student(StudentID, Name, PhoneNumbers)
▪ 1NF Fix: Create a new table: StudentPhone(StudentID_FK, PhoneNumber)
Second Normal Form (2NF)
• Prerequisite: Must be in 1NF.
• Rule: A 1NF relation is in 2NF if and only if no non-prime attribute is partially dependent on any
candidate key.
• Key Terms:
o Prime Attribute: An attribute that is part of any candidate key.
o Non-Prime Attribute: An attribute that is not part of any candidate key.
o Partial Dependency: A non-prime attribute is dependent on only a part of a composite
(multi-column) candidate key.
• Who it applies to: This rule only matters for tables with composite primary keys.
• Example:
o Enrollment(StudentID, CourseID, StudentName, Grade)
o PK: {StudentID, CourseID}
o FDs:
▪ {StudentID, CourseID} \to {Grade} (Full dependency - good)
▪ {StudentID} \to {StudentName} (Partial dependency - bad!)
o Problem: StudentName is a non-prime attribute, and it's dependent on only StudentID,
which is just part of the primary key. This causes redundancy (the student's name is
repeated for every course they take).
o Solution: Decompose into two tables:
1. Student(StudentID, StudentName)
2. Enrollment(StudentID, CourseID, Grade)
Third Normal Form (3NF)
• Prerequisite: Must be in 2NF.
• Rule: A 2NF relation is in 3NF if and only if no non-prime attribute is transitively dependent on any
candidate key.
• Key Term:
o Transitive Dependency: A dependency where $A \to B$ and $B \to C$, which implies $A \to
C$ (but B does not determine A). Here, $A$ is the key, and $C$ is transitively dependent on
$A$ through $B$.
• Example:
o Student(StudentID, Name, Major, MajorBuilding)
o PK: {StudentID}
o FDs:
▪ {StudentID} \to {Name}
▪ {StudentID} \to {Major}
▪ {Major} \to {MajorBuilding} (Transitive dependency - bad!)
o Problem: MajorBuilding (a non-prime attribute) is dependent on Major (another non-prime
attribute), which in turn is dependent on the key StudentID. This causes redundancy (the
MajorBuilding is repeated for every student with that major).
o Solution: Decompose into two tables:
1. Student(StudentID, Name, Major_FK)
2. Major(Major, MajorBuilding)
Boyce-Codd Normal Form (BCNF)
• Prerequisite: Must be in 3NF.
• Rule: A 3NF relation is in BCNF if and only if for every non-trivial functional dependency $X \to Y$,
$X$ must be a superkey.
• Meaning: This is a stricter version of 3NF. It says the only determinants allowed in a table are
superkeys. 3NF allows $X \to Y$ even if $X$ is not a superkey, as long as $Y$ is a prime attribute.
BCNF forbids this.
• When 3NF is not BCNF: This happens in rare cases where a table has multiple overlapping
composite candidate keys.
Higher Normal Forms
• Fourth Normal Form (4NF): Deals with multi-valued dependencies (MVDs). An MVD $X \to\to Y$
means that $X$ determines a set of $Y$ values, independent of any other attributes. 4NF says you
can't have non-trivial MVDs where $X$ is not a superkey.
• Fifth Normal Form (5NF): Deals with join dependencies (JDs). A table is in 5NF if it cannot be
decomposed into smaller tables without losing information, and then be perfectly reconstructed
(lossless join).
• Domain/Key Normal Form (DKNF): The "ultimate" normal form. It's a theoretical ideal.
o Rule: A relation is in DKNF if all constraints on the data are logical consequences of domain
constraints and key constraints.
o Meaning: If a table is in DKNF, it is guaranteed to be free of all modification anomalies. It is
very difficult to achieve in practice.
III. Transforming a Conceptual Model to a Relational Model
This section describes the "recipe" for converting a conceptual design, like an Entity-Relationship (ER)
Diagram, into a set of relational tables (a logical schema).
Here are the standard mapping rules:
1. Transforming Entity Sets (Object Sets)
• Strong Entity: For each strong entity set E, create a new relation (table) T.
o The attributes of E become the columns of T.
o The key attribute of E becomes the primary key of T.
• Weak Entity: For each weak entity set W (which depends on an owner entity O):
o Create a new table T.
o Include all attributes of W as columns in T.
o Add the primary key of the owner entity O as a foreign key column in T.
o The primary key of T is the combination of the owner's primary key (the new FK) and the
weak entity's partial key.
2. Transforming Attributes
• Simple Attributes: Mapped directly to a column.
• Composite Attributes: Only the simple, component attributes are added as columns.
o Example: An Address attribute composed of (Street, City, Zip) becomes three columns:
Street, City, and Zip.
• Multi-valued Attributes: Cannot be stored in one cell (violates 1NF).
o Solution: Create a new table T to hold the multi-valued attribute.
o This new table T must contain:
1. The primary key of the original entity (as a foreign key).
2. A column for the multi-valued attribute itself.
o The primary key of this new table T is the combination of both columns.
3. Transforming Relationships
This is the most critical part. The mapping depends on the relationship's cardinality.
One-to-One (1:1)
• Example: Employee (1) --- Manages --- (1) Department
• Method (Foreign Key Approach): Choose one of the tables (e.g., Employee) and add the primary
key of the other table (e.g., DepartmentID) as a foreign key. To avoid NULLs, it's best to add it to the
table that has total participation (if one exists).
• Method (Merge Approach): If both entities have total participation, you can merge them into a
single table. This is rare.
One-to-Many (1:N)
• Example: Professor (1) --- Teaches --- (N) Course
• Method (The Standard): This is the classic relational link.
1. Identify the table on the "Many" side (e.g., Course).
2. Add the primary key of the "One" side (e.g., ProfessorID) to the "Many" side's table as a
foreign key.
• Result:
o Professor(ProfessorID, ProfName, ...)
o Course(CourseID, Title, ..., ProfessorID_FK)
Many-to-Many (M:N)
• Example: Student (M) --- EnrollsIn --- (N) Course
• Method (Junction/Associative Table): You must create a new relation (table) to represent the
relationship itself.
1. Create a new table (e.g., Enrollment).
2. Add the primary key from the first entity (e.g., StudentID) as a foreign key.
3. Add the primary key from the second entity (e.g., CourseID) as a foreign key.
4. The primary key of this new junction table is the combination of both foreign keys:
{StudentID, CourseID}.
5. Any attributes of the relationship itself (e.g., Grade) also go into this new table.
• Result:
o Student(StudentID, Name, ...)
o Course(CourseID, Title, ...)
o Enrollment(StudentID_FK, CourseID_FK, Grade)
4. Transforming Specialization and Generalization
• Example: A Vehicle (superclass) can be specialized as Car, Truck, or Motorcycle (subclasses).
• Method 1 (One Table for All): Create one table (Vehicle) and include all attributes from all
subclasses. Use a VehicleType column. Attributes for other types will be NULL (e.g., TrunkSize will be
NULL for a Motorcycle).
• Method 2 (Table per Subclass): Create tables only for the subclasses (Car, Truck). Each table also
includes the attributes of the superclass (Vehicle). This is good if the subclasses have few shared
attributes.
• Method 3 (Table for Superclass, Tables for Subclasses): This is the most common.
1. Create a table for the superclass (Vehicle) with its shared attributes (VehicleID, Make,
Model).
2. Create separate tables for each subclass (Car, Truck) with only their specific attributes.
3. The primary key of each subclass table is also a foreign key that references the superclass
table's primary key (e.g., [Link] is both a PK and an FK referencing [Link]).
5. Transforming Aggregated Object Sets
• Aggregation is used when a relationship itself needs to participate in another relationship.
• Example: A Student EnrollsIn a Course, and this entire enrollment is then MonitoredBy a Proctor.
• Method:
1. First, map the M:N relationship Student-EnrollsIn-Course to its own table (Enrollment) as
described above.
2. Now, treat this Enrollment table as a regular entity.
3. To map the MonitoredBy relationship, add the primary key of Proctor (ProctorID) as a
foreign key into the Enrollment table.
6. Transforming Recursive Relationships
• This is a relationship where an entity is related to itself.
• Example: An Employee (1) Supervises (N) other Employees.
• Method: This is just a 1:N relationship, but it's mapped within the same table.
1. You have the Employee table with EmployeeID as the primary key.
2. Add a new column to the Employee table, such as SupervisorID.
3. This SupervisorID column is a foreign key that references... the EmployeeID column in the
very same table.
• Result: Employee(EmployeeID, Name, ..., SupervisorID_FK)
UNIT- 3 Relational Algebra and Relational Calculus
Relational Algebra and Relational Calculus are two preliminarily important topics in the study of DBMS.
Both are standardized query languages used for querying databases but the two have different approaches
and operations. Relational Algebra is imperative as it deals mainly with how the data is to be obtained
whereas Relational Calculus is imperative-free as it is concerned with which data is to be obtained but not
how. Knowledge of the differences between these two is critical to database professional as it impacts the
way they design databases and optimize their queries.
What is Relational Algebra?
Relational Algebra is a procedural language. In Relational Algebra, The order is specified in which the
operations have to be performed. In Relational Algebra, frameworks are created to implement the
queries. The basic operations included in relational algebra are:
1. Select (σ)
2. Project (Π)
3. Union (U)
4. Set Difference (-)
5. Cartesian product (X)
6. Rename (ρ)
Advantages of Relational Algebra
• Simplicity and Precision: On the same note, Relational Algebra offers a simple and efficient
methodology for how questions can be framed especially for a layman who has a crystal-clear
understanding of the entire method.
• Optimization Potential: It makes optimization possible due to the procedural aspect, and shows
how one can find the best way to execute the statement.
• Foundational for SQL: Most of the SQL commands can be traced to the Relational Algebra
operations as will be seen from the following sections.
Disadvantages of Relational Algebra
• Complex for Complex Queries: Relational algebra is more or less effective when the query is highly
complex because with increasing complexity, Relational Algebra analysis in full becomes a
cumbersome exercise.
• Less Abstract: It is also procedural and thus it forces users to detail out the procedure of data
access, which may not be easily understood by end users who are not very conversant with
databases.
What is Relational Calculus?
Relational Calculus is the formal query language. It is also known as Declarative language. In Relational
Calculus, the order is not specified in which the operation has to be performed. Relational Calculus means
what result we have to obtain.
Relational Calculus has two variations:
1. Tuple Relational Calculus (TRC)
2. Domain Relational Calculus (DRC)
Relational Calculus is denoted as:
{ t | P(t) }
Where,
t: the set of tuples
p: is the condition which is true for the given set of tuples.
Advantages of Relational Calculus
• Higher Abstraction: Relational Calculus enables a user to tell what information is needed but not
how it needs to be retrieved which makes it easier to express yourself.
• User-Friendly for Non-Experts: This form is easier for the users who may not know the steps that
have to be taken when formulating queries.
Disadvantages of Relational Calculus
• Less Optimizable: It can be said that this is a disadvantage of the ALG, as being non-procedural, it
does not produce definite recommendations to be followed in optimization of a query, which is
likely to result in inferior query processing.
• Potentially Ambiguous: The abstract nature can at time make it difficult to get the sense of
interpretation on the query side and therefore predict results.
Difference Between Relational Algebra and Relational Calculus
Basis of
Comparison Relational Algebra Relational Calculus
Relational Calculus is a Declarative (non-
Language Type It is a Procedural language.
procedural) language.
Relational Algebra means how to Relational Calculus means what result we
Procedure
obtain the result. have to obtain.
In Relational Algebra, the order is
In Relational Calculus, the order is not
Order specified in which the operations
specified.
have to be performed.
Relational Algebra is independent Relation Calculus can be domain-dependent
Domain
of the domain. because of domain relational calculus.
Basis of
Comparison Relational Algebra Relational Calculus
Relational Calculus is not nearer to
Programming Relational Algebra is nearer to a
programming language but to natural
language programming language.
language.
The SQL includes only some SQL is based to a greater extent on the tuple
Inclusion in SQL
features from the relational algebra. relational calculus.
Relational Algebra is one of the
languages in which queries can be For a database language to be relationally
Relationally
expressed but the queries should complete, the query written in it must be
completeness
also be expressed in relational expressible in relational calculus.
calculus to be relationally complete.
The evaluation of the query relies The order of operations does not matter in
Query
on the order specification in which relational calculus for the evaluation of
Evaluation
the operations must be performed. queries.
For accessing the database,
relational algebra provides a For accessing the database, relational
solution in terms of what is calculus provides a solution in terms as
Database access
required and how to get that simple as what is required and lets the
information by following a step-by- system find the solution for that.
step description.
The completeness of a language is measured
in the manner that it is least as powerful as
The expressiveness of any given
calculus. That implies relation defined using
Expressiveness language is judged using relational
some expression of the calculus is also
algebra operations as a standard.
definable by some other expression, the
language is in question.