Unit 1: Introduction

Introduction to Database Management System (DBMS)

Overview of Database System

A Database System is an organized collection of related data along with software (DBMS) that allows users to store, retrieve, update, and manage data efficiently.

Key Objectives of a Database System

• Reduce data redundancy
• Maintain data consistency
• Provide data security
• Allow multi-user access
• Ensure data integrity
• Provide backup and recovery

Examples

• Student Management System
• Banking System
• Hospital Management System
• E-commerce Applications

Database System vs File System

Basis	File System	Database System
Data Storage	Data stored in separate files	Data stored in a centralized database
Redundancy	High data redundancy	Low redundancy
Data Consistency	Difficult to maintain	Maintained automatically
Data Security	Limited security	Strong security mechanisms
Data Sharing	Difficult	Easy and controlled
Backup & Recovery	Manual	Automatic
Query Processing	Not efficient	Efficient (SQL support)
Data Integrity	Hard to enforce	Enforced using constraints

Conclusion: A Database System is more reliable, secure, and scalable than a File System.

Database System Concept and Architecture

A DBMS acts as an interface between users and the database, controlling data access and ensuring proper data management.

Three-Level Architecture (ANSI/SPARC Architecture)

Level	Description
External Level	User view of data (different users have different views)
Conceptual Level	Logical structure of entire database
Internal Level	Physical storage of data

Advantages

• Data abstraction
• Data independence
• Security control
• Simplified database design

Data Model, Schema, and Instances

Data Model

A data model defines how data is stored, organized, and manipulated.

Types of Data Models

• Hierarchical Model
• Network Model
• Relational Model
• Object-Oriented Model

Schema

• A schema is the logical structure of the database.
• It is defined once and rarely changes.

Example: STUDENT (RollNo, Name, Course, Marks)

Instance

• An instance is the actual data stored in the database at a specific time.
• Changes frequently.

Example: (101, Rahul, MCA, 85)

Basis	Schema	Instance
Meaning	Database design	Actual data
Changes	Rare	Frequent
Nature	Static	Dynamic

Data Independence and Database Languages & Interfaces

Data Independence

It is the ability to change schema at one level without affecting other levels.

Types of Data Independence

Type	Description
Logical Data Independence	Changes in conceptual schema do not affect external schema
Physical Data Independence	Changes in internal schema do not affect conceptual schema

Importance:

• Reduces maintenance cost
• Improves flexibility

Database Languages

DBMS uses different languages to interact with data.

Data Definition Language (DDL)

DDL is used to define and modify database structures.

DDL Commands

• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME

Example

CREATE TABLE Student (
    RollNo INT,
    Name VARCHAR(50),
    Course VARCHAR(20)
);

Key Feature: DDL commands automatically commit changes.

Data Manipulation Language (DML)

DML is used to insert, update, delete, and retrieve data.

Types of DML

• Procedural DML – User specifies how to get data
• Non-Procedural DML – User specifies what data is needed (SQL)

DML Commands

• INSERT
• UPDATE
• DELETE
• SELECT

Example SELECT * FROM Student;

Overall Database Structure

Main Components of Database System

1. Database - Stores actual data
2. DBMS Software - Manages data and controls access
3. Hardware - Disk, servers, memory
4. Users - DBA, Application Programmers, End Users
5. Database Access Languages - SQL, APIs

User Types in DBMS

User Type	Role
DBA	Manages database and security
Application Programmer	Develops applications
End User	Uses database via applications

Summary for MCA Exams

• DBMS provides efficient, secure, and structured data management
• Database is superior to file systems
• Three-level architecture ensures data abstraction
• Schema defines structure; instance defines data
• Data independence improves flexibility
• DDL defines structure; DML manipulates data

Data Modeling Using the Entity–Relationship (ER) Model

ER Model Concepts

The Entity–Relationship (ER) Model is a high-level conceptual data model used to represent real-world objects and their relationships in a database.

Main Components of ER Model

1. Entity
2. Attribute
3. Relationship

Entity

An entity is a real-world object that can be uniquely identified.

Examples: Student, Employee, Course, Department

Types of Entities

Type	Description
Strong Entity	Has its own primary key
Weak Entity	Depends on a strong entity for identification

Attributes

Attributes describe properties of an entity.

Types of Attributes

Attribute Type	Description	Example
Simple	Atomic, indivisible	Age
Composite	Made of sub-parts	Name (First, Last)
Single-valued	One value	Roll No
Multi-valued	Multiple values	Phone No
Derived	Calculated from other attributes	Age from DOB

Relationship

A relationship shows how entities are connected.

Example: Student enrolls in Course

Notation for ER Diagram

Symbol	Meaning
Rectangle	Entity
Double Rectangle	Weak Entity
Oval	Attribute
Double Oval	Multi-valued Attribute
Dashed Oval	Derived Attribute
Diamond	Relationship
Double Diamond	Identifying Relationship
Underlined Attribute	Primary Key

Mapping Constraints

Mapping constraints define how many entities can participate in a relationship.

Types of Mapping Constraints

Cardinality Ratio

Type	Meaning
One-to-One (1:1)	One entity ↔ one entity
One-to-Many (1:N)	One entity ↔ many entities
Many-to-One (N:1)	Many entities ↔ one entity
Many-to-Many (M:N)	Many entities ↔ many entities

Participation Constraints

Type	Description
Total Participation	Entity must participate
Partial Participation	Participation is optional

Keys in ER Model

A key uniquely identifies an entity.

Types of Keys

Key Type	Description
Super Key	Any set of attributes that uniquely identifies an entity
Candidate Key	Minimal super key
Primary Key	Selected candidate key
Alternate Key	Candidate key not chosen as primary
Foreign Key	Attribute referencing primary key of another table

Key Concepts Explained

Super Key

• May contain extra attributes
• Example: (RollNo, Name)

Candidate Key

• Minimal super key
• Example: RollNo

Primary Key

• Chosen candidate key
• Must be unique and NOT NULL

Generalization

Generalization is a top-down approach where a higher-level entity is formed from lower-level entities.

Example

• Student
• Employee → Person

Characteristics

• Uses IS-A relationship
• Reduces redundancy
• Improves clarity

Aggregation

Aggregation is used when a relationship itself needs to participate in another relationship.

Example

• Employee works on Project
• Manager manages (Employee works on Project)

Here, works on is aggregated.

Purpose

• Represents complex relationships
• Improves modeling accuracy

Reduction of ER Diagrams to Tables

This process converts ER model into Relational Model.

Rules for Conversion

Strong Entity → Table

• Entity name → Table name
• Attributes → Columns
• Primary key → Primary key

Weak Entity → Table

• Include primary key of owner entity
• Partial key + owner key → Composite primary key

Relationships

Relationship Type	Table Formation
1:1	Foreign key in either table
1:N	Foreign key on N-side
M:N	Separate relation with both primary keys

Extended ER (EER) Model

The Extended ER Model adds more concepts to the basic ER model.

Additional Concepts

1. Specialization
2. Generalization
3. Aggregation
4. Categories (Union types)

Specialization

• Bottom-up approach
• Divides entity into sub-entities

Example: Account → Savings Account, Current Account

Difference Between Generalization and Specialization

Basis	Generalization	Specialization
Approach	Bottom-up	Top-down
Focus	Common features	Distinct features

Relationship of Higher Degree

A relationship of degree > 2 is called a higher-degree relationship.

Types

• Ternary (degree 3)
• N-ary (degree n)

Ternary Relationship Example

Supplier supplies Part to Project

• Entities: Supplier, Part, Project
• Relationship: Supplies

Why Not Binary?

• Binary relationships may lose important constraints
• Ternary gives accurate modeling

Exam-Oriented Key Points (MCA)

• ER Model is a conceptual design tool
• Mapping constraints define cardinality & participation
• Super key → Candidate key → Primary key
• Generalization and aggregation are advanced ER concepts
• ER to table conversion is frequently asked
• Higher-degree relationships preserve semantics