DBMS Tutorial – Database Management System notes

DBMS stands for Database Management System. We can break it like this DBMS = Database + Management System. Database is a collection of data and Management System is a set of programs to store and retrieve those data. Based on this we can define DBMS like this: DBMS is a collection of inter-related data and set of programs to store & access those data in an easy and effective manner. Here are the DBMS notes to help you learn database systems in a Systematic manner. Happy Learning!!

DBMS Tutorial Index

DBMS Introduction:

• Introduction to DBMS
• Types of DBMS
• DBMS Applications
• Advantages of DBMS over file processing system
• DBMS vs RDBMS
• DBMS Architecture
• Three level DBMS Architecture
• View of Data
• Data Abstraction
• Instances and Schemas
• DBMS languages

Data Models:

• Data Models in DBMS
• E-R Model in DBMS
• ER Design issues
• ER To table conversion
• Recursive relationship in ER diagram
• DBMS Generalization
• DBMS Specialization
• DBMS Aggregation
• Relational Model in DBMS
• Hierarchical data Model in DBMS
• Network Model in DBMS

Relational Database:

• RDBMS Concepts
• Relational Algebra
• Relational Calculus
• View vs table
• Keys in DBMS
  • Primary key
  • Super key
  • Candidate key
  • Alternate key
  • Composite key
  • Foreign key
• Constraints in DBMS
  • Domain constraints
  • Mapping constraints
• Cardinality in DBMS
• Functional dependencies in DBMS
  • Trivial functional dependency
  • non-trivial functional dependency
  • Multivalued dependency
  • Transitive dependency
• Normalization in dbms – This covers all the normal forms: First Normal Form(1NF), Second Normal Form(2NF), Third Normal Form(3NF), Boyce–Codd Normal Form(BCNF)
• Denormalization in DBMS
• Denormalization vs Normalization
• Decomposition in DBMS

Transaction Management:

• Transaction Management in DBMS
• ACID Properties
• Transaction States
• DBMS Schedules
• Serializability
• DBMS Conflict Serializability
• DBMS View Serializability
• Recoverability of Schedule
• Failure classification
• Log based recovery
• DBMS checkpoint
• Deadlock
• Starvation in DBMS

Concurrency Control:

• Concurrency Control
• Lock based protocol
• Timestamp based protocol
• Validation based protocol

File Organization:

• File Organization in DBMS
• Sequential File Organization
• Heap File Organization
• Hash File Organization
• DBMS ISAM
• B+ File Organization
• Cluster File Organization
• Data replication in DBMS
• Indexing in DBMS

SQL Introduction:

• SQL Introduction
• Characteristics of SQL
• Advantages of SQL
• SQL commands
• SQL operators
• SQL CREATE TABLE statement
• SQL DROP TABLE statement
• SQL SELECT statement
• SQL INSERT statement

What is a Database?

A database is collection of interrelated data, stored in such a way so that a user can read, insert, update and delete the data efficiently.

Database systems are basically developed for large amount of data. When dealing with huge amount of data, there are two things that require optimization: Storage of data and retrieval of data.

Storage: According to the principles of database systems, the data is stored in such a way that it acquires lot less space as the redundant data (duplicate data) has been removed before storage.

Fast Retrieval of data: Along with storing the data in an optimized and systematic manner, it is also important that we retrieve the data quickly when needed. Database systems ensure that the data is retrieved as quickly as possible.

Database Management System (DBMS)

DBMS is a software that manages the data for efficient storage and fast retrievals. MySQL, IBM Db2, Oracle, PostgreSQL etc. are all DBMS softwares that manages the data.

DBMS is used in various applications such as telecom, banking, sales, airlines, education, online shopping etc.

DBMS also secures the data from unauthorised access as well as corrupt data insertions. It allows multiple users to access data simultaneously while maintaining the data consistency and data integrity.

DBMS allows following operations to the authorized users of the database:

Data Definition: Creation of table, table schema creation, removal of table definition etc. comes under data definition. It is basically a layout of the table and their relation with the other tables in the database.

Data Modification: DBMS allows users to insert, update and delete the data from the tables. These tables contains rows and columns, where row represents a record of data while column represents attributes of the records.

Data Retrieval: DBMS allows users to fetch data from the database.

User administration: DBMS also allows user management such as organizing users in different groups with different access levels. Granting users access to certain tables in database, revoking access from certain users etc.

Characteristics of DBMS

• Stores the data in such a way so that the relation between data is still maintained in the database.
• Allows fast retrieval.
• It can handle multiple accessing the database at the same time.
• It maintains data integrity by following ACID properties of the database.
• It provides data security by managing user access.
• DBMS allows automatic backup of database to handle accidental corruption or deletion of data.
• It allows scaling of database as per the need.
• It allows data rollback and redone in case of a data operation failure.

Advantages of DBMS

• Handles Database redundancy: The major disadvantage of file based system of storing the data is data redundancy, same data is stored in multiple files. DBMS handles data redundancy to manage the storage space efficiently.
• Data sharing: DBMS allows data sharing so that data can be shared between multiple users of the same organization efficiently.
• Data Maintenance: DBMS performs regular data checks and automatic backup.
• Performance: Provides better performance for operations such as read, insert, update and deletion of data.
• Backup: It maintains backup of the database so that in case of a failure, database can be recovered to the previous state using the backup.
• Multiple users: It allows multiple users to access the data at the same time.

Disadvantages of DBMS

• Hardware and Software Cost: Although DBMS has several advantages over file system of data management, however all this comes with a cost. DBMS needs a dedicated hardware and software system to manage the database.
• Need large Storage: DBMS is usually used in the large organisations that require large amount of data stored in the devices.
• Complexity: Database management system is complex and not easy to implement.
• Requires learning: In order to manage database, user require learning the concepts of DBMS which require additional time and resources that a organization has to bear.