1.) Introduction to DBMS Tutorial – A database is a collection of related data organized in a way that can be easily accessed. A database management system (DBMS) is a system software-defined for creating and managing databases. The DBMS provides users and programmers a systematic way to create, update, manage and retrieve data. Let’s move ahead to the comprehensive concepts in this DBMS Tutorial.
2.) DBMS Architecture/3 Level Architecture – DBMS architecture helps design, develop, implement and maintain a database. There are mainly three types of DBMS architecture which are mentioned below –
a.) 1-Tier Architecture
b.) 2-Tier Architecture
c.) 3-Tier Architecture
a.) 1-Tier Architecture – In this architecture, the client, server and database all reside on the same machine. In our systems, mostly we have 1-Tier architecture. This architecture is rarely used in production.
b.) 2-Tier Architecture – In this architecture, the presentation layer (where the data is present) runs on a client (PC, Mobile, Tablet, etc.) whereas the data is stored on a server.
c.) 3-Tier Architecture – It consists of a presentation layer (PC, Mobile), application layer (server) and database server. This DBMS architecture contains an application layer between the user and the DBMS, which is responsible for communicating the user’s request to the DBMS and send the response from the DBMS to the user.
3.) Entity-Relational (E-R) Model – An ER model describes the structure of a database with the help of a diagram, which is called ER diagram. It is a design or blueprint of a database that can later be implemented as a database.
An entity may be an object with physical existence, for example, a person, car, house or employee. Or it may be an object with a conceptual existence, for example, a job, a company or a university.
An entity type is defined as a collection of entities that have the same attributes. An entity set is the collection of all entities of a particular entity type in the database at any point in time. The ER model is represented with the help of the below shapes –
Rectangle – Represents entity sets
Ellipses – Attributes
Diamonds – Relationship Set
Lines – They link the attributes with the entity sets and the entity sets with the relationship sets
Double Ellipses – Multivalued attributes
Dashed Ellipses – Derived attributes
Double Rectangles – Weak entity sets
Double Lines – They represent the total participation of an entity in a relationship set.
Main components of an ER Model –
a.) Entity
b.) Attributes
c.) Relationship
a.) Entity – There are 2 types of entities
i.) Strong Entity
ii) Weak Entity
i.) Strong Entity – An entity that has a primary key is known as a strong entity.
ii.) Weak Entity – The entity which does not have sufficient attributes to form a primary key is called a weak entity.
b.) Attributes – Attributes are the descriptive properties which are owned by each entity of an entity set. There are six types of attributes that are explained below –
i) Simple attribute – It cannot be divided further.
ii.) Composite attribute – It is composed of many other simple attributes.
iii.) Single valued attribute – It can take only one value for a given entity from an entity set.
iv.) Multivalued attribute – A multi valued attribute can take more than one value for a given entity from an entity set.
v.) Derived attributes – Those attributes which can be derived from other attributes are called derived attributes.
vi.) Key attribute – The attribute which can identify an entity uniquely in an entity set.
c.) Relationship – There are 4 types of relationships –
i.) One to one relationships
ii.) One to many relationships
iii.) Many to one relationships
iv.) Many to many relationships
i.) One to one relationships (1:1) – An entity in set A can be associated with at most one entity in set B.
ii.) One to many relationships (1:N) – An entity in set A can be associated with any number (0 or more) of entities in set B.
iii.) Many to one relationships (N:1) – An entity in set B can be associated with any number (0 or more) of entities in set A.
iv.) Many to many relationships (N:N) – An entity in set A can be associated with any number (0 or more) of entities in set B and an entity in set B can be associated with any number (0 or more) of entities in set A.
4.) Relational Model and Relational Algebra– A Relational Model represents the database as a collection of relations. It stores data in the form of relations (tables). Each table is a group of columns and rows, where the column represents an attribute of an entity and the row represents records.
Relational Algebra is a procedural query language that takes a relation as an input and generates a relation as an output. Relational algebra operations are performed repeatedly on a relation. The output of these operations is a new relationship that might be formed from one or more input relations.
Basic relational algebras are as following –
a.) Selection operation – The selection operation selects those rows or tuples from the relation that satisfy the selection condition.
b.) Projection operation – The projection option displays the columns of a relation or table based on the specified attributes.
c.) Union operation – R union S is the set of all tuples belonging to either R or S or both relations. In this method, duplicate entries are automatically removed.
d.) Intersection operation – R intersection S is the set of all the tuples belonging to both R and S relations. It finds the records which are in both relations. In this method, the duplicates are automatically removed.
e.) Rename operation – It is used to give another name to a relation.
f.) Difference operation – R minus S is the set of all the tuples belonging to the set R and not to S. In this method, the duplicates are automatically removed.
g.) Cartesian product operation – It is used to join two tables without any condition. Each row of one table is joined with every row of another table by performing Cartesian product operation.
h.) Join operations – It is a Cartesian product followed by selection criteria. It also allows joining various related tuples from different relations. There are mainly two types of join operations –
i.) Outer Join
ii.) Inner Join
i.) Outer Join – In this join, along with the tuples which satisfy the matching criteria, those tuples are included which do not match the criteria. There are 3 types of Outer join –
A.) Left outer join
B.) Right outer join
C.) Full outer join
ii.) Inner Join – In this join, only those tuples which satisfy the matching criteria are included, while the rest are excluded. There are three types of Inner join –
A.) Theta join
B.) Equi join
C.) Natural join
5.) Normalization – Normalization is a process of making the database consistent by reducing redundancies and ensuring the integrity of data through loss-less decomposition. There are mainly four types of normal forms available for a database –
a.) First NF (1NF)
b.) Second NF (2NF)
c.) Third NF (3NF)
d.) Boyce-Codd NF (BCNF)
a.) First NF – If each cell of the table contains only an atomic value or if the attribute of every tuple is either single-valued or null value.
b.) Second NF – For second NF, below two conditions are necessary –
i.) Relation already exists in 1NF.
ii.) No partial dependency exists in the relation.
c.) Third NF – For third NF, below two conditions are necessary –
i.) Relation already exists in 2NF.
ii.) No transitive dependency exists for a non-prime attribute.
d.) Boyce-Codd NF – For Boyce-Codd NF, below two conditions are necessary –
i.) Relation already exists in 3NF.
ii.) For each non-trivial functional dependency A -> B, A is a super key of the relation.
In general, 3NF is considered the adequate form for normal relational database design.
6.) Query Language – SQL overview – SQL is a programming language for a relational database. SQL is equipped with Data Manipulation Language (DML). In this language, inserting, updating and deleting data from a database is allowed. Below are the options which can be used in SQL language –
a.) SELECT/FROM/WHERE
b.) INSERT/INTO/VALUES
c.) UPDATE/SET/WHERE
d.) DELETE/FROM/WHERE
This was an overall preview of DBMS. In this DBMS tutorial, we have covered the introduction to DBMS, DBMS architecture, the basic points of Entity-Relationship model of databases, the relational model and relational algebra, the types of algebraic operations available for databases, the concept of normalization and all the types of normal forms, and an overview of SQL query language.