Introduction to Database Management System (DBMS) - PDF

INTRODUCTION OF DBMS (DATABASE MANAGEMENT SYSTEM) DATABASE MANAGEMENT SYSTEM (DBMS) A DBMS is a software solution designed to efficiently manage, organize, and retrieve data in a structured manner. It serves as a critical component in modern computing, enabling organizations to store, manipulate,...

INTRODUCTION OF DBMS (DATABASE MANAGEMENT SYSTEM) DATABASE MANAGEMENT SYSTEM (DBMS) A DBMS is a software solution designed to efficiently manage, organize, and retrieve data in a structured manner. It serves as a critical component in modern computing, enabling organizations to store, manipulate, and secure their data effectively. From small applications to enterprise systems, DBMS plays a vital role in supporting data-driven decision-making and operational efficiency. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ DATABASE MANAGEMENT SYSTEM (DBMS) A DBMS is a system that allows users to create, modify, and query databases while ensuring data integrity, security, and efficient data access. Unlike traditional file systems, DBMS minimizes data redundancy, prevents inconsistencies, and simplifies data management with features like concurrent access and backup mechanisms. It organizes data into tables, views, schemas, and reports, providing a structured approach to data management. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ A database management system is a computerized solution that helps store information in a manner that is easy to read, edit, delete, and scale, with the primary objective of drawing correlations, powering analysis, and supporting data-driven workflows. It has a backend storage system as well as a front-end user interface. Source: https://www.spiceworks.com/tech/cloud/articles/database-management-systems-dbms/  KEY FEATURES OF DBMS (DATABASE MANAGEMENT SYSTEM) KEY FEATURES OF DBMS  Data Modeling: Tools to create and modify data models, defining the structure and relationships within the database.  Data Storage and Retrieval: Efficient mechanisms for storing data and executing queries to retrieve it quickly.  Concurrency Control: Ensures multiple users can access the database simultaneously without conflicts.  Data Integrity and Security: Enforces rules to maintain accurate and secure data, including access controls and encryption.  Backup and Recovery: Protects data with regular backups and enables recovery in case of system failures. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/  TYPES OF DBMS (DATABASE MANAGEMENT SYSTEM) TYPES OF DBMS Relational Database Management System (RDBMS)  RDBMS organizes data into tables (relations) composed of rows and columns. It uses primary keys to uniquely identify rows and foreign keys to establish relationships between tables. Queries are written in SQL (Structured Query Language), which allows for efficient data manipulation and retrieval.  Examples: MySQL, Oracle, Microsoft SQL Server and Postgre SQL. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ TYPES OF DBMS NoSQL DBMS  NoSQL systems are designed to handle large-scale data and provide high performance for scenarios where relational models might be restrictive. They store data in various non- relational formats, such as key-value pairs, documents, graphs, or columns. These flexible data models enable rapid scaling and are well-suited for unstructured or semi-structured data.  Examples: MongoDB, Cassandra, DynamoDB and Redis. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ TYPES OF DBMS  OODBMS integrates object-oriented programming concepts into the database environment, allowing data to be stored as objects. This approach supports complex data types and relationships, making it ideal for applications requiring advanced data modeling and real-world simulations.  Examples: ObjectDB, db4o. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ DATABASE LANGUAGES DATABASE LANGUAGES Data Definition Language (DDL) DDL is the short name for Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.  CREATE: to create a database and its objects like (table, index, views, store procedure, function, and triggers)  ALTER: alters the structure of the existing database  DROP: delete objects from the database  TRUNCATE: remove all records from a table, including all spaces allocated for the records are removed  COMMENT: add comments to the data dictionary  RENAME: rename an object Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ DATABASE LANGUAGES Data Manipulation Language (DML) DML focuses on manipulating the data stored in the database, enabling users to retrieve, add, update, and delete data.  SELECT: retrieve data from a database  INSERT: insert data into a table  UPDATE: updates existing data within a table  DELETE: Delete all records from a database table  MERGE: UPSERT operation (insert or update)  CALL: call a PL/SQL or Java subprogram  EXPLAIN PLAN: interpretation of the data access path  LOCK TABLE: concurrency Control Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ DATABASE LANGUAGES Data Control Language (DCL) DCL commands manage access permissions, ensuring data security by controlling who can perform certain actions on the database.  GRANT: Provides specific privileges to a user (e.g., SELECT, INSERT).  REVOKE: Removes previously granted permissions from a user. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ DATABASE LANGUAGES Transaction Control Language (TCL) TCL commands oversee transactional data to maintain consistency, reliability, and atomicity.  ROLLBACK: Undoes changes made during a transaction.  COMMIT: Saves all changes made during a transaction.  SAVEPOINT: Sets a point within a transaction to which one can later roll back. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ DATABASE LANGUAGES Data Query Language (DQL) DQL is a subset of DML, specifically focused on data retrieval.  SELECT: The primary DQL command, used to query data from the database without altering its structure or contents. Source: https://www.geeksforgeeks.org/introduction-of-dbms-database-management-system-set-1/ STRUCTURES OF A RELATIONAL DATABASE STRUCTURES OF A RELATIONAL DATABASE Database A database is a logical grouping of data. It contains a set of related table spaces and index spaces. Typically, a database contains all the data that is associated with one application or with a group of related applications.You could have a payroll database or an inventory database, for example. Source: https://www.spiceworks.com/tech/cloud/articles/database-management-systems-dbms/ STRUCTURES OF A RELATIONAL DATABASE Table A table is a logical structure made up of rows and columns. Rows have no fixed order, so if you retrieve data you might need to sort the data. The order of the columns is the order specified when the table was created by the database administrator. At the intersection of every column and row is a specific data item called a value, or, more precisely, an atomic value. A table is named with a high-level qualifier of the owner's user ID followed by the table name, for example TEST.DEPT or PROD.DEPT. Source: https://www.spiceworks.com/tech/cloud/articles/database-management-systems-dbms/ STRUCTURES OF A RELATIONAL DATABASE Source: https://www.spiceworks.com/tech/cloud/articles/database-management-systems-dbms/ STRUCTURES OF A RELATIONAL DATABASE Referring to the table:  Columns–The ordered set of columns are DEPTNO, DEPTNAME, MGRNO, and ADMRDEPT. All the data in a given column must be of the same data type.  Rows–Each row contains data for a single department.  Values–At the intersection of a column and row is a value. For example, PLANNING is the value of the DEPTNAME column in the row for department B01. Source: https://www.spiceworks.com/tech/cloud/articles/database-management-systems-dbms/ STRUCTURES OF A RELATIONAL DATABASE Indexes An index is an ordered set of pointers to rows of a table. Unlike the rows of a table that are not in a specific order, an index must always be maintained in order. An index is used for two purposes:  For performance, to retrieve data values more quickly  For uniqueness. Source: https://www.spiceworks.com/tech/cloud/articles/database-management-systems-dbms/ STRUCTURES OF A RELATIONAL DATABASE By creating an index on an employee's name, you can retrieve data more quickly for that employee than by scanning the entire table. Also, by creating a unique index on an employee number, DB2 will enforce the uniqueness of each value. A unique index is the only way DB2 can enforce uniqueness. Creating an index automatically creates the index space, the data set that contains the index. Source: https://www.spiceworks.com/tech/cloud/articles/database-management-systems-dbms/ STRUCTURES OF A RELATIONAL DATABASE Keys A key is one or more columns that are identified as such in the creation of a table or index, or in the definition of referential integrity. Primary key A table can only have one primary key because it defines the entity. There are two requirements for a primary key: 1. It must have a value, that is, it cannot be null. 2. It must be unique, that is, it must have a unique index defined on it. Source: https://www.spiceworks.com/tech/cloud/articles/database-management-systems-dbms/ STRUCTURES OF A RELATIONAL DATABASE Primary and foreign keys are the most basic components on which relational database theory is based. Primary keys enforce entity integrity by uniquely identifying entity instances. Foreign keys enforce referential integrity by completing an association between two entities. The next step in building the basic data model to:  Identify and define the primary key attributes for each entity  Validate primary keys and relationships  Migrate the primary keys to establish foreign keys Source: https://condor.depaul.edu/gandrus/240IT/accesspages/primary-foreign-keys.htm#:~:text=Primary%20keys%20enforce%20entity%20integrity,key%20attributes%20for%20each%20entity STRUCTURES OF A RELATIONAL DATABASE Validate Keys and Relationships  Basic rules governing the identification and migration of primary keys are:  Every entity in the data model shall have a primary key whose values uniquely identify entity instances.  The primary key attribute cannot be optional (i.e., have null values).  The primary key cannot have repeating values. That is, the attribute may not have more than one value at a time for a given entity instance is prohibited. This is known as the No Repeat Rule.  Entities with compound primary keys cannot be split into multiple entities with simpler primary keys. This is called the Smallest Key Rule.  Two entities may not have identical primary keys with the exception of entities within generalization hierarchies.  The entire primary key must migrate from parent entities to child entities and from supertype, generic entities, to subtypes, category entities. Source: https://condor.depaul.edu/gandrus/240IT/accesspages/primary-foreign-keys.htm#:~:text=Primary%20keys%20enforce%20entity%20integrity,key%20attributes%20for%20each%20entity STRUCTURES OF A RELATIONAL DATABASE A foreign key is an attribute that completes a relationship by identifying the parent entity. Foreign keys provide a method for maintaining integrity in the data (called referential integrity) and for navigating between different instances of an entity. Every relationship in the model must be supported by a foreign key. Source: https://condor.depaul.edu/gandrus/240IT/accesspages/primary-foreign-keys.htm#:~:text=Primary%20keys%20enforce%20entity%20integrity,key%20attributes%20for%20each%20entity STRUCTURES OF A RELATIONAL DATABASE Identifying Foreign Keys: Every dependent and category (subtype) entity in the model must have a foreign key for each relationship in which it participates. Foreign keys are formed in dependent and subtype entities by migrating the entire primary key from the parent or generic entity. If the primary key is composite, it may not be split. Source: https://condor.depaul.edu/gandrus/240IT/accesspages/primary-foreign-keys.htm#:~:text=Primary%20keys%20enforce%20entity%20integrity,key%20attributes%20for%20each%20entity END OF PRESENTATION