Database Management System (DBMS) is a software used to manage data from a
database.
A database is a structured collection of data that is stored in an electronic device.
The data can be text, video, image or any other format.
A relational database stores data in the form of tables and a NoSQL database in the
form of key-value pairs.
SQL (Structured Query Language) is a standard language for accessing and
manipulating data in a relational database.
A DBMS is a software that allows to create, update and retrieval of data in an
organized way. It also provides security to the database.
Examples of relational DBMS are MySQL, Oracle, Microsoft SQL Server, Postgre
SQL and Snowflake.
Examples of NoSQL DBMS are MongoDB, Cassandra, DynamoDB and Redis.
1. Database Systems
Data vs. Information
Data: Raw, unprocessed facts (e.g., numbers, characters, symbols). Example: "John",
"5000"
Information: Processed, organized data that has meaning and is useful for decision-
making. Example: "John's salary is $5000."
Introducing the Database
A database is an organized collection of data, generally stored and accessed
electronically from a computer system.
It allows efficient data storage, retrieval, and manipulation.
2. File System vs. Database System
File System
Traditional way of storing data using files in a file structure (e.g., .txt, .csv).
Each application has its own private files, leading to data redundancy.
Problems with File Systems
1. Data Redundancy and Inconsistency: Same data stored in multiple files.
2. Difficulty in Accessing Data: Custom programs needed.
3. Data Isolation: Scattered in different files, difficult to combine.
4. Integrity Problems: No central enforcement of rules.
5. Security Problems: No standardized mechanism for controlling access.
6. Concurrent Access Issues: File systems lack concurrency control.
3. Database Systems
Use Database Management Systems (DBMS) like MySQL, Oracle, SQL Server.
Provide:
o Data integrity
o Reduced redundancy
o Enhanced security
o Support for concurrent access
o Query languages (like SQL)
o Data abstraction
4. Data Models
Definition
A data model is a collection of concepts that describe the structure of a database, including data
types, relationships, and constraints.
Importance of Data Models
Define how data is connected and stored
Ensure consistency and clarity in database design
Provide a blueprint for database creation
Basic Building Blocks
1. Entities: Objects or things (e.g., Student, Book)
2. Attributes: Characteristics of entities (e.g., Name, Age)
3. Relationships: Associations between entities (e.g., Student borrows Book)
4. Constraints: Rules (e.g., Student ID must be unique)
Business Rules
Derived from organizational policies and requirements
Translate real-world policies into database constraints and logic
Example: "A customer can place many orders, but an order is placed by one customer."
5. Evolution of Data Models
1. Hierarchical Model: Tree-like structure; 1-to-many relationships
2. Network Model: Graph structure; many-to-many relationships
3. Relational Model: Based on tables (relations); widely used today
4. Object-Oriented Model: Incorporates object principles (encapsulation, inheritance)
5. NoSQL Models: For unstructured or semi-structured data (document, key-value, graph)
6. Degrees of Data Abstraction
1. Physical Level: Lowest level; describes how data is stored
2. Logical Level: Describes what data is stored and relationships (schema)
3. View Level: Highest level; how users interact with data (user views)
Summary Table
Concept Description
Data vs Information Raw facts vs meaningful context
File System Traditional, isolated data storage
Problems of File Systems Redundancy, access difficulty, security
Database Systems Organized data management via DBMS
Data Model Blueprint for database design
Building Blocks Entities, Attributes, Relationships, Constraints
Business Rules Organizational policies reflected in data
Model Evolution Hierarchical → Relational → NoSQL
Data Abstraction Physical, Logical, View levels
UNIT2
Concept Description
Relational Model Logical organization of data in tables
Keys Ensure uniqueness, relationships
Integrity Rules Maintain data validity
Set Operators Perform operations on sets of data
Data Dictionary/System Catalog Metadata repository
Concept Description
Relationships Link tables via keys
Redundancy & Indexing Avoid duplicate data; boost query performance
Codd’s Rules 13 principles of a true RDBMS
ER Model & Diagram Visual and conceptual database design tool
UNIT 3
Normalization 1NF → 5NF, reduces redundancy and anomalies
DDL CREATE, ALTER, DROP, TRUNCATE
DML INSERT, UPDATE, DELETE
SELECT Query data with conditions, grouping, sorting
Joins Combine data from related tables
Views/Indexes Improve usability and performance
UNIT 4
Topic Key Concepts
Set Operators UNION, UNION ALL, INTERSECT, MINUS
Joins CROSS, NATURAL, USING, ON, OUTER
Subqueries Nested, correlated, used in WHERE, HAVING, FROM
ANY/ALL Compare with multiple values
SQL Functions Date, Numeric, String, Conversion
UNIT 5
PL/SQL Summary Table
Category Details
History Created by Oracle to extend SQL with procedural features
Block Structure DECLARE → BEGIN → EXCEPTION → END;
Comments -- single-line, /* multi-line */
Data Types NUMBER, VARCHAR2, BOOLEAN, DATE, %TYPE, %ROWTYPE
Operators +, -, *, /, MOD, **
Control Structures IF...THEN, CASE, LOOP, WHILE, FOR
SQL in PL/SQL SELECT INTO, INSERT, UPDATE, DELETE
Transaction Control COMMIT, ROLLBACK, SAVEPOINT
Cursors Implicit (auto), Explicit (manual), Cursor FOR loop
Cursor Attributes %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT
Cursor FOR UPDATE Locks selected rows for update
Exceptions Predefined, User-defined, Caught using WHEN
Cursor Variables REF CURSOR for dynamic queries
