Database Management System Important Notes 2023-24

Database Management System Important Notes

Basic Terms of DBMS

DATABASE: A collection of related data that can be easily accessed and managed.

DATA: Known facts that can be recorded and have an implicit meaning.

DBMS: A software package to facilitate the creation and maintenance of the computerized database.

RDBMS: Relational database management refers to the database in a structured format using tables.

SQL: SQL ( Structured Query Language) is a standard language for accessing and manipulating data from Database.

FUNCTION

TAKE INPUT, PROCESS IT, AND RETURN OUTPUT.

SOME SPECIAL FUNCTIONS USED IN SQL :

• NUMERIC FUNCTION
• AGGREGATE FUNCTION
• CHARACTER FUNCTION
• DATE FUNCTION

SQL OPERATORS

SQL Operators are some reserved keywords used in where clause of SQL Statement to perform arithmetic, logical, and comparison operations.

ARITHMETIC:  addition, subtraction, multiplication etc.

COMPARISON: =,<,>,<>,>=,<=,!= etc..

LOGICAL: AND,OR ,ALL ,ANY ,BETWEEN ,EXIST, LIKE ETC…

SET OPERATORS

These operators are used to combine the result obtained from two or more queries into a single result.

Example of set operators: UNION, UNION ALL, INTERSECTION, MINUS

TYPES OF CLAUSES

• FOR UPDATE
• FROM
• GROUP BY
• HAVING
• WINDOW
• ORDER BY
• USING
• WHERE

TYPES OF KEYS :

THE PRIMARY KEY IS A COLUMN OR SET OF COLUMNS IN A TABLE THAT UNIQUELY IDENTIFIES TUPLE(ROWS) IN THAT TABLE.

SUPER KEY IS A SET OF ONE OR MORE COLUMNS TO UNIQUELY IDENTIFY ROWS IN A TABLE.

THE CANDIDATE KEY IS SUPER KEY WITH NO REDUNDANT ATTRIBUTE IS KNOWN AS A CANDIDATE KEY.

COMPOSITE KEY THAT CONSISTS OF MORE THAN ONE ATTRIBUTE TO UNIQUELY IDENTIFY ROWS.

FOREIGN KEYS ARE COLUMNS OF A TABLE THAT POINTS TO THE PRIMARY KEY OF ANOTHER TABLE.

ALTERNATE KEY IS DEFINED AS OUT OF ALL CANDIDATE KEY ONLY ONE IS SELECTED AS CANDIDATE KEY AND REMAINING ARE KNOWN AS ALTERNATE KEY.

INTEGRITY CONSTRAINTS

CONSTRAINTS ENFORCES LIMIT TO A DATA OR TYPES OF DATA THAT CAN BE INSERTED/UPDATED/DELETED FROM A TABLE.

SIMPLY, THE MAIN PURPOSE OF CONSTRAINTS IS TO MAINTAIN DATA INTEGRITY DURING UPDATE/DELETE/INSERT INTO TABLE.

DATABASE ARCHITECTURE

DBMS ARCHITECTURE MANAGE HOW USER ARE CONNECTED TO THE DATABASE TO GET THEIR REQUEST DONE.

TYPES OF DATABASE ARCHITECTURE:

• 1-TIER: MEANS USER AND SERVER DIRECTLY CONNECTED TO THEM

EG: LOCAL APPLICATION MANAGED BY PROGRAMMERS

• 2-TIER: MEANS APPLICATION AT USER SIDE IS USED TO COMMUNICATE WITH DATABASE.

EG: ODBC, JDBC

• 3-TIER: MEANS APPLICATION ON USER SIDE INTERACT WITH APPLICATION SERVER TO COMMUNICATE WITH DATABASE.

EG: LARGE WEB APPLICATION

HIERARCHICAL MODEL

In the hierarchical model, segments pointed to by the logical association are called the child segment and the other segment is called the parent segment.

In a hierarchical model, data is organized into a tree-like structure with each record is having one parent record and many children. The main drawback of this model is that it can have only one to many relationships between nodes.

ILLUSTRATE THIS WITH AN EXAMPLE :

Advantages of Hierarchical Model:

• Conceptual simplicity

• Database security 

• Data independence 

• Database integrity

• Efficiency dealing with a large database

Disadvantages of Hierarchical Data Model:

• Complex implementation

• Difficult to manage

• Lacks structural independence

• Applications programming and use complexity

• Implementation limitations

• Lack of standards

ER MODEL

The entity-relationship model is a model used for the design and representation of relationships between data.

ER model helps to systematically analyze data requirements to produce a well-designed database.

ER model consists of:

• Entity and Entity Set : AN ENTITY IS GENERALLY A REAL-WORLD OBJECT WHICH HAS CHARACTERISTICS AND HOLDS RELATIONSHIPS IN A DBMS.

EXAMPLE: Consider An example of a business management software like employee info, department info, etc…

EMPLOYEES, DEPARTMENTS, etc are entities.

Employee is entity then each and every dataset of all Employee is entity set.

• ATTRIBUTE: IF EMPLOYEE IS ENTITY THEN AGE, NAME,  ID  ARE ATTRIBUTES

• KEYS: IF ID UNIQUELY IDENTIFES EVERY EMPLOYEE THEN IT WILL BE SAID TO BE A KEY.

• RELATIONSHIP : IN TABLE ONE ENTITY RELATED TO ANOTHER ENTITY.

EXAMPLE: OFFICE IS AN ENTITY THAT IS RELATED TO ANOTHER ENTITY EMPLOYEE.

ER-DIAGRAM SYMBOLS

• Rectangles: This Entity Relationship Diagram symbol represents entity types
• Ellipses : Symbol represent attributes
• Diamonds: This symbol represents relationship types
• Lines: It links attributes to entity types and entity types with other relationship types
• Primary key: attributes are underlined
• Double Ellipses: Represent multi-valued attributes

RELATIONSHIP CARDINALITY

One-to-One Cardinality:

• An entity in set A can be associated with at most one entity in set B.
• An entity in set B can be associated with at most one entity in set A.

EXAMPLE:

• One student can enroll in at most one course.
• One course can be enrolled by at most one student.

One-to-Many Cardinality:

• An entity in set A can be associated with any number (zero or more) of entities in set B.
• An entity in set B can be associated with at most one entity in set A.

EXAMPLE:

• One student can enroll in any number (zero or more) of courses.
• One course can be enrolled by at most one student.

Many-to-One Cardinality:

• An entity in set A can be associated with at most one entity in set B.
• An entity in set B can be associated with any number (zero or more) of entities in set A.

EXAMPLE:

• One student can enroll in at most one course.
• One course can be enrolled by any number (zero or more) of students.

Many-to-Many Cardinality:

• An entity in set A can be associated with any number (zero or more) of entities in set B.
• An entity in set B can be associated with any number (zero or more) of entities in set A.

EXAMPLE:

• One student can enroll in any number (zero or more) of courses.
• One course can be enrolled by any number (zero or more) of students.

FUNCTIONAL DEPENDENCY

Functional Dependency (FD) is a relation between one attribute to another attribute in a DBMS

FUNCTIONAL DEPENDENCY IS REPRESENTED AS

X -> Y Where X is determinant and Y is dependent.

A functional dependency, denoted by X ->Y, between two sets of attributes X and Y that are subsets of R, such that any two tuples t1 and t2 in r that have t1[X] = t2[X], they must also have t1[Y] = t2[Y].

Let consider a table consist X and Y values:

t1[X] =t2[X] now put two values from table

eg. put 1 and 2 in the above statement

t1[1] = t2[2]

now both are not equal so we don’t need to check for t1[Y] = t2[Y].

Now again take two values 2 and 2 from the table.

t1[2] = t2[2] -> both are equal

check for 2nd statement t1[Y] = t2[Y]

t1[6]=t1[6] -> from table put Y values correspond to X values.

2 and 2 satisfy both statement so it is functional dpendency

Advantages of Functional Dependency

• Functional Dependency avoids data redundancy. Therefore same data do not repeat at multiple locations in that database
• It helps you to maintain the quality of data in the database
• It helps you to defined meanings and constraints of databases
• It helps you to identify bad designs
• It helps you to find the facts regarding the database design

Rules of Functional Dependencies:

• Reflexive rule: If A is a set of attributes and B is subset of A, then A holds a value of B.
• Augmentation rule: When A -> B holds, and C is attribute set, then AC -> BC also holds.
• Transitivity rule: If A -> B holds and B -> C holds, then A -> C also holds.

Types of Functional Dependencies:

• Multivalued Dependency:Multivalued dependency occurs in the situation where there are multivalued independ attributes in a single table. 
• Trivial Functional Dependency:The Trivial dependency is a set of attributes in which the set of attributes are included in that attribute. example : A -> B is a trivial functional dependency if B is a subset of A.
• Non-Trivial Functional Dependency: if attribute B is not a subset of attribute A, then it is considered as a non-trivial dependency.
• Transitive Dependency: Functional dependency which happens when relationship is indirectly formed by two functional dependencies. Example: Student id -> Name AND Name ->Batch name So, Student id -> Batch name.

Normalization

Normalization is a process of assembling the data in the database which helps you to avoid data redundancy, insertion, update & deletion anomaly.

It is a step-by-step decomposition of complex records into simple records. It is also called Canonical Synthesis.

Normal forms in SQL :

• 1NF (First Normal Form)
• 2NF (Second Normal Form)
• 3NF (Third Normal Form)
• BCNF (Boyce-Codd Normal Form)
• 4NF (Fourth Normal Form)
• 5NF (Fifth Normal Form)
• 6NF (Sixth Normal Form)

Let’s Consider An Example To Understand Normal Forms:

1NF (First Normal Form) Rules:

• Each table cell should contain a single value.
• Each record needs to be unique.

THE ABOVE TABLE CAN BE REPRESENTED IN 1NF AS :

2NF (Second Normal Form) Rules:

• Rule 1- Be in 1NF
• Rule 2- Single Column Primary Key that does not functionally dependant on any subset of candidate key relation

It is clear that we can’t move forward to make our simple database in 2^nd Normalization form unless we partition the table above.

We have divided our 1NF table into two tables. Table 1 contains User information. Table 2 contains their contact no.

We have added a new column ID no which is the primary key(uniquely identifies records) in table 1.

And in Table 2 ID no is a Foreign key( columns points to the primary key of table 1).

3NF (Third Normal Form) Rules:

• Rule 1- Be in 2NF
• Rule 2- Has no transitive functional dependencies(A transitive functional dependecy is when changing a non-key column, might cause any of the other non-key columns to change)

To move our 2NF table into 3NF, we again need to again divide our table.

We have again divided our tables and created a new table which stores city names.

There are no transitive functional dependencies, and hence our table is in 3NF

In Table 3 Cr No is the primary key, and in Table 1 Cr No is foreign pointing to the primary key in Table 3

BCNF (Boyce-Codd Normal Form):

Even when a database is in 3^rd Normal Form, still there would be anomalies resulting if it has more than one Candidate Key.

Sometimes is BCNF is also referred to as 3.5 Normal Form.

4NF (Fourth Normal Form) Rules:

If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in the 4^th Normal Form.

Below Given Some Youtube Links to practice How to find the Highest Normal Form of the given relation:

Link:

How to identify normal form in dbms part1

Identify highest normal form of a relation part 2

Procedures

A procedure is a combination of SQL statements written to perform specified tasks. It helps in code re-usability and saves time and lines of code.

Triggers :

A trigger is a special kind of procedure that executes only when some triggering event such as INSERT, UPDATE, DELETE operations occurs in a table.

Difference between Triggers and Procedures :

Transaction and Concurrency control

A transaction is a logical piece of work of any database, which may be a complete program, a fraction of a program, or a single command (like INSERT or UPDATE) that may involve any number of processes on the database.

In Simple Words, It is an action or sequence of actions passed out by a single user and/or application program that reads or updates the contents of the database.

Concurrency control is the method of managing concurrent operations on the database without getting any obstruction from one another.

NEED OF CONCURRENCY CONTROL:

When multiple users are accessing the database at the same time, and at least one is updating data, there may be the case of interference, which can result in data inconsistencies. So, Concurrency key purpose is in developing a database is to facilitate multiple users to access shared data in parallel (i.e., at the same time).

Lock-Based Protocol

In this type of protocol, any transaction cannot read or write data until it acquires an appropriate lock on it.

There are two types of locks:

1. Shared lock:

It can be shared between the transactions because when the transaction holds a lock, then it can’t update the data on the data item.

2. Exclusive lock:

This lock is exclusive, and in this lock, multiple transactions do not modify the same data simultaneously.

IF YOU WANT TO KNOW MORE ABOUT LOCKS PROTOCOL THEN TAP ON THE BELOW LINK :

LOCKS PROTOCOL

ALSO CHECK SOME IMPORTANT TOPICS OF DBMS

Testing of Serializability

Recoverability of Schedule

Deadlock in DBMS

Timestamp Ordering Protocol

CHECK SOME ONLINE TOOL FOR DBMS QUESTIONS

Database Schedule Parser Online Tool – Check Given Schedule Is serializable or not

Determine All Candidate Keys – Find Candidate Key of Any relation (In some exceptional cases it will not work so, don’t depend on this)

BCNF Check – Check if the given relation is in BCNF or not

Also Read

• Numerical Optimization Techniques and Dynamic Programming Lagrange Multipliers Knapsack
• LINEAR PROGRAMMING AND OPTIMIZATION TECHNIQUES
• Syllogism Coding Decoding Direction Sense Important Tips and Tricks
• DATA STRUCTURE IMPORTANT NOTES