Data Base Management System
What is a Database?
A database is an organized collection of related data that can be
easily accessed, managed, and updated.
Example:
A student database contains tables like:
• Student (ID, Name, DOB, Email)
• Courses (Course_ID, Course_Name)
• Enrollments (Student_ID, Course_ID)
2
What is DBMS
Database Management System (DBMS) is a software application that allows users
to create, manage, retrieve, and manipulate data stored in a database. It acts as
an interface between the database and its end-users or applications, enabling
efficient and organized access to information
3
Example Databases
University
1. Data: departments, students, exams, rooms, ...
2. Usage: creating exam plans, enter exam results, create statistics, build
timetables, …
3. Web-based access common
Bank
1. Data: clients, accounts, credits, funds,…
2. Applications: accounting, transfers (> 50,000 per day), risk management
3. Web-based access for some parts of the application
Airline
1. Data: flights, passengers, employees, airplanes,…
2. Applications: reservation, booking, creating flight schedules, querying
flight schedules
3. Web-based access for some parts of the application
4
Example Databases (contd.)
Genetics
1. Data: DNA-sequences (> 1 Mio. bases for simple bacteria), proteins
2. Applications: search similar sequences, predict protein structure, keep track of
experiments…
Online bookstore
1. Data: all what is sold at the bookstore, customer information
2. Applications: keyword search, booking, maintaining shopping basket, checkout, …
Global Change Research
1. Data: topographic maps, satellite data (NASA Earth Observation System: 1 Terabyte per
day)
2. Applications: Analysis and prediction of climate and environmental changes (e.g.,
greenhouse effect, waldsterben), visualization of data
Stock market, libraries, etc.
5
File System vs Database Management
Database Management
Aspect File System
System (DBMS)
A way of storing and A software to manage
Meaning
organizing files on a disk structured data systematically
Stores raw files (text, images, Stores data in tables with rows
Storage Format
documents) and columns
Managed by DBMS software
Management Managed by Operating System
(e.g., MySQL, Oracle)
6
Types of Databases
1. Relational Database (RDBMS)
2. NoSQL Database
3. Cloud Database
7
Relational Database (RDBMS)
A relational database is a collection of
data points with pre-defined
relationships between them. It is a
smaller subset of DBMS which was
designed by E.F Codd in the 1970s.
The major DBMSs like SQL, My-SQL,
and ORACLE are all based on the
principles of relational DBMS
8
Benefits of relational databases
Flexibility
Easily add, update, or delete tables and
relationships without affecting the overall
structure.
9
Benefits of relational databases
ACID Compliance
This ensures that a
transaction is treated as a
Transactions bring
single, indivisible unit of the database from
work. Either all one valid state to
operations within a another, upholding
transaction are completed all predefined rules
successfully, or none of and constraints
them are.
Committed
Concurrent transactions transactions are
execute independently permanently saved
without interfering, and survive system
preventing data
failures
inconsistencies.
10
Benefits of relational databases
Collaboration
Multiple users can access and work on the database simultaneously, with data locking mechanisms.
11
Benefits of relational databases
Built-in Security
Role-based access restricts data visibility and actions based on user permissions.
12
Benefits of relational databases
Database Normalization
Reduces data duplication and enhances data integrity using structured design techniques.
13
Benefits of relational databases
Ease of Use
Users can run complex queries with SQL, even with minimal technical knowledge.
14
Examples of RDBM Databased
15
NoSQL Database Management
The term NoSQL, short for “not only SQL,”
refers to non-relational databases that store
data in a non-tabular format, rather than in
rule-based. NoSQL databases use a flexible
schema model that supports a wide variety of
unstructured data such as documents, key-
value, wide columns, and graphs
16
Advantages of NoSQL Database
Advantage Description
Supports different types of data (structured, semi-structured, unstructured) with
Flexible Data Model
flexible schemas that evolve with business needs.
Helps developers build applications quickly by storing data in native format without
Agile Development
strict schemas; supports rapid iteration and less transformation.
Easily scales horizontally (adding more servers) to handle increased data and user
Scalability
traffic, often with zero downtime and autoscaling in cloud systems.
Designed for big data, real-time analytics, and IoT — handles large and complex
Massive Data Storage
datasets efficiently.
Built for distributed systems with no single point of failure; supports replication to
High Availability
ensure continuous uptime.
Optimized for quick access to data with minimal use of joins; returns results faster
Faster Queries
compared to relational systems.
17
NoSQL Database
18
Cloud Database
A cloud database is a type of database
management system hosted and accessed
via a cloud computing platform instead of
on-premises servers.
19
Cloud Database Companies
20
Instructions:
✓Identify various types of databases other than SQL, NoSQL, and Cloud
databases.
✓Identify companies that provide multiple types of databases, and list
the different database products they offer.
21
Data Model
A data model is a blueprint or abstract representation that defines how
data is organized, stored, and accessed within a system
22
Data Model Levels
23
Conceptual Model
A conceptual data model (CDM) is the
most abstract level of data modeling.
It focuses on representing the high-
level business concepts, entities, and
their relationships within an
organization, independent of any
specific database technology or
implementation details.
24
Logical Model
A logical data model establishes the structure of data elements and
the relationships among them.
25
Physical Model
A physical data model represents how data is structured and stored
in a specific database management system (DBMS)
26
Schema
A database schema serves as the
blueprint or logical design of a
database, outlining how data is
structured, organized, and related
within it. It defines the framework for
storing and managing data, but does
not contain the actual data itself.
27
Database Schema Designs
✓Flat Model
✓Hierarchical Model
✓Network Model
✓Relational Model
✓Star Schema
✓Snowflake Schema
28
Database Schema Designs
Flat Model
A flat model schema is a 2-D array in which
every column contains the same type of
data/information and the elements with
rows are related to each other. It is just like
a table or a spreadsheet. This schema is
better for small applications that do not
contain complex data.
29
Database Schema Designs
Hierarchical Model
Data is arranged using parent-child
relationships and a tree-like
structure in the Hierarchical
Database Model. Because each record
consists of several children and one
parent, it can be used to illustrate
one-to-many relationships in
diagrams such as organizational
charts.
30
Database Schema Designs
Network Model
The network model is similar to the
hierarchical model in that it
represents data using nodes
(entities) and edges (relationships).
However, unlike the hierarchical
model, which enforces a strict
parent-child relationship, the
network model allows for more
flexible many-to-many relationships.
31
Database Schema Designs
Relational Model
The relational model is mainly used
for relational databases, where the
data is stored as relations of the
table. This relational model schema is
better for object-oriented
programming.
32
Database Schema Designs
Star Schema
Star schema is better for
storing and analyzing large
amounts of data. It has a fact
table at its center & multiple
dimension tables connected to
it just like a star
33
Database Schema Designs
Snowflake Schema
Just like star schema, the
snowflake schema also has a
fact table at its center and
multiple dimension tables
connected to it, but the main
difference in both models is
that in snowflake schema –
dimension tables are further
normalized into multiple
related tables. 34
Entity Relationship Method (ER)
A conceptual data modelling approach used to understand and simplify data
relationships.
Derived from the Semantic School of knowledge modelling (i.e. Symbols &
Notations are Used for articulation of Association and Depiction of
behaviour and relationships).
• The concepts are used by linguists to represent the meaning of sentences and words.
• The concepts are used by artificial intelligence researchers to represent and model
knowledge.
35
3 Main Components of E-R Model
1. Entities
2. Attributes
2. Relationships
For More Details Refer below link
ER Diagram Text Book URL
36
Normalization
Normalization is a process in database design used to organize data into structured tables to:
➢ Reduce redundancy (duplicate data).
➢ Improve data integrity (accuracy & consistency).
➢ Make the database more efficient for queries and updates.
It is done by applying a series of rules (called Normal Forms – 1NF, 2NF, 3NF, … up to 5NF). Each
higher normal form removes a specific type of problem (like repeating data, partial dependency,
transitive dependency, etc.).
37
Employee Data Base
Employee_id Name Jobs (Job_code: Job) State_code Home_state
E001 Alice {J01: Chef, J02: Waiter} 26 Michigan
E002 Bob {J02: Waiter, J03: Bartender} 56 Wyoming
E003 Alice {J01: Chef} 56 Wyoming
Source: [Link] 38
1NF (First Normal Form) → Remove Repeating Groups
➢ A Single Cell Must Not Hold More Than One
Value (Atomicity)
➢ There Must Be A Primary Key For Identification
➢ No Duplicated Rows Or Columns
➢ Each Column Must Have Only One Value For
Each Row In The Table
State_co
Employee_id Name Job_code Job Home_state
de
E001 Alice J01 Chef 26 Michigan
E001 Alice J02 Waiter 26 Michigan
E002 Bob J02 Waiter 56 Wyoming
E002 Bob J03 Bartender 56 Wyoming
E003 Alice J01 Chef 56 Wyoming
39
2NF (Second Normal Form) → Remove Partial Dependency
A table is said to be in 2NF if it meets the following criteria:
it’s already in 1NF
has no partial dependency. That is, all non-key attributes are fully dependent on a primary key.
Employee_id Job_code
E001 J01
E001 J02
E002 J02
E002 J03
E003 J01
Employee_id Name State_code Home_state Job_code Job
E001 Alice 26 Michigan J01 Chef
E002 Bob 56 Wyoming J02 Waiter
E003 Alice 56 Wyoming J03 Bartender 40
3NF (Third Normal Form) → Remove Transitive Dependency
When a table is in 2NF, it eliminates repeating groups and redundancy, but it does not eliminate
transitive partial dependency. This means a non-prime attribute (an attribute that is not part of the
candidate’s key) is dependent on another non-prime attribute. This is what the third normal form
(3NF) eliminates.
Employee_id Job_code Job_code Job Employee_id Name State_code
E001 J01 J01 Chef E001 Alice 26
E001 J02 J02 Waiter E002 Bob 56
E002 J02 J03 Bartender E003 Alice 56
E002 J03
E003 J01
State_code Home_state
26 Michigan
56 Wyoming 41
Task
Student_ Student Dept_buildin Instructor_nam
Course_info Dept_name Instructor_email
id _name g e
{C101: Database,
S01 John ComputerSci CS Block Dr. Smith smith@[Link]
C102: AI}
{C101: Database,
S02 Alice ComputerSci CS Block Dr. Smith smith@[Link]
C103: ML}
{C104: Marketing,
S03 Bob Business B Block Dr. Jones jones@[Link]
C105: Finance}
42