Table of Contents
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:
| X | Y |
| 1 | 4 |
| 2 | 6 |
| 3 | 8 |
| 2 | 6 |
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:
| NAME | CITY | CONTACT NO |
| HARRY | LONDON | 88-8800789 ; 88-9089653 |
| ROBERT | NEW YORK | 00-987654 |
| JERRY | USA | 92-993256 ; 23-457473 |
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 :
| NAME | CITY | CONTACT NO |
| HARRY | LONDON | 88-8800789 |
| HARRY | LONDON | 88-9089653 |
| ROBERT | NEW YORK | 00-987654 |
| JERRY | USA | 92-993256 |
| JERRY | USA | 23-457473 |
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 2nd Normalization form unless we partition the table above.
| ID NO. | NAME | CITY |
| 1 | HARRY | LONDON |
| 2 | ROBERT | NEW YORK |
| 3 | JERRY | USA |
| ID NO. | CONTACT NO. |
| 1 | 88-8800789 |
| 1 | 88-9089653 |
| 2 | 00-987654 |
| 3 | 92-993256 |
| 3 | 23-457473 |
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.
| ID NO. | NAME | Cr. No. |
| 1 | HARRY | 1 |
| 2 | ROBERT | 2 |
| 3 | JERRY | 3 |
| ID NO. | CONTACT NO. |
| 1 | 88-8800789 |
| 1 | 88-9089653 |
| 2 | 00-987654 |
| 3 | 92-993256 |
| 3 | 23-457473 |
| Cr. No. | CITY |
| 1 | LONDON |
| 2 | NEW YORK |
| 3 | USA |
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 3rd 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 4th 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 :
| Triggers | Procedures |
| A Trigger is implicitly invoked whenever any event such as INSERT, DELETE, UPDATE occurs in a TABLE. | A Procedure is explicitly called by the user/application using statements or commands such as exec, EXECUTE, or simply procedure_name |
| Only nesting of triggers can be achieved in a table. We cannot define/call a trigger inside another trigger. | We can define/call procedures inside another procedure. |
| In a database, the syntax to define a trigger: CREATE TRIGGER TRIGGER_NAME | In a database, the syntax to define a procedure: CREATE PROCEDURE PROCEDURE_NAME |
| Triggers are used to maintain referential integrity by keeping a record of activities performed on the table. | Procedures are used to perform tasks defined or specified by the users. |
| We cannot return values in a trigger. Also, as an input, we cannot pass values as a parameter. | We can return 0 to n values. However, we can pass values as parameters. |
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 :
ALSO CHECK SOME IMPORTANT TOPICS OF DBMS
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
Leave a comment