Untitled

Introduction to DBMS

• A DBMS (Database Management System) is a software designed to efficiently manage, organize, and retrieve data
• It serves as a critical component in modern computing
• A DBMS enables organizations to store, manipulate, and secure their data effectively
• DBMS supports data-driven decision-making and operational efficiency across applications
• Users can create, modify, and query databases using a DBMS
• A DBMS ensures data integrity, security, and efficient data access
• Unlike traditional file systems, a DBMS minimizes data redundancy and prevents inconsistencies
• Features like concurrent access and backup mechanisms are included
• Data is organized into tables, views, schemas, and reports
• A DBMS is a computerized solution that stores information that can be easily read, edited, deleted, and scaled
• The primary objective is to draw correlations, power analysis, and support data-driven workflows
• It includes a backend storage system and a front-end user interface

Key features of DBMS

• Data modeling tools are present to create and modify data models and to define the structure and relationships within a database
• Efficient mechanisms are present for data storage and to retrieve data quickly
• Concurrency control ensures multiple users can access a database simultaneously without conflicts
• Data integrity and security enforce rules to maintain accurate and secure data, including access controls and encryption
• Included backup and recovery protect data with backups and enable recovery from system failures

Types of DBMS

• Relational Database Management System (RDBMS) organizes data into tables (relations), composed of both rows and columns
• Primary keys uniquely identify rows within a table in a RDBMS
• Foreign keys establish relationships between tables in a RDBMS
• Queries are written in SQL (Structured Query Language) for efficient data manipulation and retrieval
• MySQL, Oracle, Microsoft SQL Server, and PostgreSQL are examples of RDBMS
• NoSQL systems are designed to handle large-scale data for scenarios where relational models might be restrictive
• Data is stored in non-relational formats such as key-value pairs, documents, graphs, or columns
• Flexible data models enable rapid scaling and are well-suited for unstructured or semi-structured data
• MongoDB, Cassandra, DynamoDB, and Redis are examples of NoSQL DBMS
• Object-Oriented Database Management System (OODBMS) integrates object-oriented programming concepts into a database environment
• Data can be stored as objects within a OODBMS
• OODBMS supports complex data types and relationships, ideal for advanced data modeling and real-world simulations
• ObjectDB and db4o are examples of a OODBMS

Database Languages

• Data Definition Language (DDL) deals with database schemas and descriptions of how data should reside
• CREATE creates a database and objects(tables, index, views, store procedure, function, and triggers)
• ALTER alters the structure of an existing database
• DROP deletes objects from the database
• TRUNCATE removes all records from a table, including all allocated spaces
• COMMENT adds comments to the data ditionary
• RENAME renames an object
• Data Manipulation Language (DML) focuses on manipulating the data, so users can retrieve, add, update, and delete data
• SELECT retrieves data from a database
• INSERT inserts data in to a table
• UPDATE updates existing data within a table
• DELETE deletes all records from a database table
• MERGE is a UPSERT operation (insert or update)
• CALL is to call a PL/SQL or Java subprogram
• EXPLAIN PLAN: interpretation of the data access path
• LOCK TABLE is for concurrency control
• Data Control Language (DCL) commands manage access permissions, and ensures data security by controlling those who perform certain actions
• GRANT provides specific privileges to a user (e.g., SELECT, INSERT).
• REVOKE removes previously granted permissions from a user.
• Transaction Control Language (TCL) commands oversee transactional 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, and can roll back to it later
• Data Query Language (DQL) is a subset of DML, specifically focused on data retrieval
• SELECT is a primary DQL command, used to query data from a database without altering its context

Structures of a Relational Database

• A database is a logical grouping of data, including a set of related table spaces and index spaces
• A database contains all the data associated wth one application or with a group of related applications
• Databases can be a payroll database or an inventory database as an example
• A table is a logical structure made up of rows and columns
• Rows have no fixed so you might have to organize the data
• Column order when the table was created by the database administrator
• Intersection of every column and row is a value/atomic value
• A table is named with a high-level qualifier of the owner's user ID, TEST.DEPT or PROD.DEPT for exmample
• Columns like, DEPTNO, DEPTNAME, MGRNO, and ADMRDEPT. will be the same data type
• Each row contains data for a single department in a database

Keys

• A key is one or more columns in the creation of a table or index
• Primary Key is the definition of referential integrity
• A table can only have one primary key
• it defines entity, there are two requirements for it
• Must have a value
• Must be distinct
• Must not be nul
• must have a unqiue index defined on it
• 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
• Identify and define the primary key attributes for each entity
• Every entity in the data model shall has a unique primary key
• The primary key cannot be optional and or repeat values
• Entities with compound primary keys cannot be split into multiple entities with simpler primary keys, called the smallest key rule
• 2 entities might not have indetical primary keys and entire primary key must migrate from parent, child supertype, generic subtype, and category ents wto them
• Foreign keys create relationships by identifying the parent entity, it maintains data integrity by enforcing every relationship on instances of the entity
• Identifying Foreign Keys in both dependent and subtype entities by migrating parent / generic entities primary key