UNIT-I

1. Introduction to Database System

A database system is a collection of interrelated data and a set of programs (DBMS) that allow users to
store, retrieve, update, and manage data efficiently.

 Database → Organized collection of data

 DBMS (Database Management System) → Software that manages the database

Examples of DBMS

 MySQL
 Oracle
 SQL Server
 PostgreSQL
 MongoDB

Instead of storing data in separate files, a database system stores data centrally and systematically.

2. Characteristics: Database System vs File System

Feature File System Database System
Data storage Stored in separate files Stored in a centralized database
Data redundancy High (duplicate data) Low (controlled redundancy)
Data consistency Poor High
Data security Limited Strong security mechanisms
Data sharing Difficult Easy
Data independence Not supported Supported
Backup & recovery Manual Automatic

Database systems are more efficient, secure, and reliable than file systems.

3. Database Users
1. Database Administrator (DBA)

 Controls the database

 Manages security, backup, and recovery
 Defines schema

2. Database Designers

 Design the structure of the database

 Decide tables, relationships, constraints

3. Application Programmers
  Write programs to interact with the database
 Use SQL or programming languages

4. End Users

 Use the database for queries and updates

Types:
 Naive users – use predefined applications (bank customers)
 Casual users – occasional queries
 Sophisticated users – complex queries (analysts)

[Link] of Database Systems

1. Reduced Data Redundancy

In a file system, the same data is stored in multiple files, causing duplication.
A database system stores data centrally, so the same data is stored only once and shared by multiple users.

2. Improved Data Consistency

When data is duplicated, updating one copy but not others causes inconsistency.
In a database system, since data is stored at a single location, any update is reflected everywhere.

3. Data Sharing

A database allows multiple users to access the same data simultaneously.

Example:
In a bank, tellers, managers, and customers can access the same database at the same time.

4. Data Security

Database systems provide strong security mechanisms like:

 User authentication (login)

 Authorization (access control)
 Encryption

Different users get different access rights.

Example:
A student can view marks but cannot modify them.

5. Data Integrity

Data integrity ensures that data is accurate, valid, and reliable.

DBMS enforces integrity constraints such as:

 Primary key
  Foreign key
 Domain constraints

Example:
A student ID cannot be null or duplicated.

6. Backup and Recovery

Database systems provide automatic backup and recovery mechanisms.

If a system failure occurs due to:

 Power failure
 Hardware crash
 Software error

The DBMS can restore the database to a correct state.

7. Data Independence

Changes made at one level of the database do not affect other levels.

Example:
Changing the physical storage does not affect application programs.

8. Efficient Data Access

DBMS uses:

 Indexing
 Query optimization
 Advanced search techniques

This allows fast retrieval of data, even from large databases.

9. Concurrency Control

Multiple users can access the database at the same time without causing conflicts.

DBMS ensures:

 No data corruption
 Correct transaction execution

Example:
Two users updating the same record are handled safely.

10. Centralized Control

All data is managed by a central system under the control of the DBA.
Benefit:

 Uniform data standards

 Better control and monitoring

11. Reduced Application Development Time

DBMS provides built-in features like:

 Query languages (SQL)

 Forms and reports
 Data validation

5. Database Applications:
Definition

A database application is a software system that uses a database to store, retrieve, and manage data
efficiently.
These applications interact with the database using a DBMS.

Major Areas of Database Applications

1. Banking Systems

Databases are used to manage:

 Customer details
 Account information
 Transactions
 Loans and credit cards

Example:
ATM systems, online banking, mobile banking apps.

Why DBMS is needed:

 High security
 Accuracy
 Real-time updates

2. Airline and Railway Reservation Systems

Databases store:

 Passenger details
 Ticket bookings
 Seat availability
 Payment records
Features:

 Multiple users booking simultaneously

 Fast search and update

3. University and Education Systems

Used to manage:

 Student records
 Courses and subjects
 Attendance
 Examination results

Example:
Student Management Systems, Learning Management Systems (LMS).

4. Hospital and Healthcare Systems

Databases maintain:

 Patient records
 Doctor schedules
 Medical history
 Billing information

Benefits:

 Quick access to patient data

 Improved medical decision-making

5. E-Commerce Applications

Databases are used for:

 Product catalogs
 Customer details
 Orders and payments
 Inventory management

Example:
Amazon, Flipkart, eBay.

6. Telecommunication Systems

Databases store:

 Customer information
 Call records
 Billing details
 Network usage
Benefit:

 Accurate billing
 Efficient customer service

7. Government Applications

Used to manage:

 Citizen records
 Tax information
 Voter databases
 Passport and ID systems

Example:
Aadhaar, income tax systems.

8. Social Media Platforms

Databases handle:

 User profiles
 Posts and comments
 Messages
 Likes and followers

Example:
Facebook, Instagram, Twitter.

9. Library Management Systems

Databases store:

 Book details
 Issued and returned books
 Member information
 Fine calculations

10. Manufacturing and Inventory Systems

Databases are used for:

 Stock management
 Supplier details
 Production schedules
 Sales records

6. Data Models :
A data model defines how data is stored, organized, and manipulated in a database.
1. Hierarchical Model

 Data organized in tree structure

 One-to-many relationship
 Example: File system

2. Network Model

 Graph structure
 Many-to-many relationships

3. Relational Model

 Data stored in tables (relations)

 Uses rows and columns
 Most widely used model (SQL)

4. Entity-Relationship (ER) Model

 Uses entities, attributes, and relationships

 Used for database design

5. Object-Oriented Model

 Data stored as objects

 Supports inheritance and encapsulation

7. Schema, Instance, and Data Independence

Schema

 Structure of the database

 Defined once
 Does not change frequently
Example: Table structure

Instance

 Actual data stored at a particular time

 Changes frequently
Example: Records in tables

8. Data Independence
Data independence means changing one level of the database without affecting other levels.

Types of Data Independence

1. Physical Data Independence

 o Changes in physical storage
o Does not affect logical structure
2. Logical Data Independence
o Changes in logical schema
o Does not affect application programs

9. Three-Tier Schema Architecture

This architecture provides data independence.

1. External Level

 User views
 Different views for different users

2. Conceptual Level

 Logical structure of entire database

 Tables, relationships, constraints

3. Internal Level

 Physical storage of data

 Files, indexes, storage devices

Separation of levels ensures security, abstraction, and data independence.

10. Database System Structure and Environment
A database system includes:

 Hardware – servers, storage devices

 Software – DBMS, OS, application programs
 Data – stored in the database
 Users – DBA, programmers, end users
 Procedures – rules and instructions

11. Centralized Database Architecture

 Entire database stored at one location
 Multiple users access it

Advantages

 Easy control
 Better security

Disadvantages

 Single point of failure

 Limited scalability
 12. Client-Server Database Architecture
 Database stored on server
 Clients send requests

Two-Tier Architecture

 Client ↔ Server

Three-Tier Architecture

 Client ↔ Application Server ↔ Database Server

Advantages

 Better performance
 Scalability
 Security

ER MODEL:
The Entity-Relationship Model (ER Model) is a conceptual model for designing a database. This model represents
the logical structure of a database, including entities, their attributes, and relationships between them.
 Entity: An object that is stored as data. E.g: Student, Course, or Company.
 Attribute: Properties that describe an entity. E.g: StudentID, CourseName, or EmployeeEmail.
 Relationship: A connection between entities. E.g: Student enrolls in a Course.
 Symbols Used in ER Model
ER Model is used to model the logical view of the system from a data perspective which consists of these symbols:

Entity
An Entity represents a real-world object, concept or thing about which data is stored in a database. It act as a
building block of a database. Tables in relational database represent these entities.
Example of entities:
 Real-World Objects: Person, Car, Employee etc.
 Concepts: Course, Event, Reservation etc.
 Things: Product, Document, Device etc.
The entity type defines the structure of an entity, while individual instances of that type represent specific entities.

Entity Set
An entity refers to an individual object of an entity type, and the collection of all entities of a particular type is called
an entity set. For example, E1 is an entity that belongs to the entity type "Student," and the group of all students
forms the entity set.
In the ER diagram below, the entity type is represented as:
Types of entities:

Types of Attributes
1. Simple Attribute

 Cannot be divided into smaller parts

 Stores a single value

Example:
Age, Gender

2. Composite Attribute

 Can be divided into sub-parts

Example:
Address → Street, City, PIN

3. Single-Valued Attribute

 Has only one value for each entity

Example:
Date of Birth

4. Multi-Valued Attribute

 Can have more than one value

Example:
Phone Numbers, Skills

Symbol: Double oval

5. Derived Attribute

 Value is calculated from another attribute

Example:
Age (derived from Date of Birth)

Symbol: Dashed oval

6. Key Attribute

 Uniquely identifies an entity

Example:
Student_ID

Symbol: Underlined

Relationship Type and Relationship Set

A Relationship Type represents the association between entity types. For example, ‘Enrolled in’ is a relationship
type that exists between entity type Student and Course. In ER diagram, the relationship type is represented by a
diamond and connecting the entities with lines
Degree of a Relationship
Definition

The degree of a relationship is the number of entity sets participating in a relationship.

Types of Relationship Degree

1. Unary Relationship (Degree = 1)

 Relationship involves only one entity

 Also called recursive relationship

Example:
Employee manages Employee

2. Binary Relationship (Degree = 2)

 Relationship involves two entities

 Most common type

Example:
Student enrolls in Course

3. Ternary Relationship (Degree = 3)

 Relationship involves three entities

Example:
Supplier supplies Product to Store

4. N-ary Relationship (Degree = N)

 Relationship involves more than three entities

Example:
Multiple entities involved together
Cardinality
Definition

Cardinality specifies how many instances of one entity can be associated with instances of another entity in
a relationship.

It answers the question: “How many?”

Types of Cardinality
1. One-to-One (1:1)

 One entity is related to only one entity

Example:
One person has one passport

2. One-to-Many (1:N)

 One entity is related to many entities

Example:
One teacher teaches many students

3. Many-to-One (N:1)

 Many entities are related to one entity

Example:
Many students belong to one department

4. Many-to-Many (M:N)

 Many entities are related to many entities

Example:
Students enroll in many courses, and courses have many students
Constraints in DBMS
Definition

A constraint is a rule applied on database data to ensure accuracy, consistency, and integrity of the data.

Constraints restrict invalid data from being stored in the database.

Why Constraints Are Needed

 To maintain data integrity
 To avoid duplicate and invalid values
 To enforce business rules
 To keep data reliable and meaningful

Types of Constraints in DBMS

1. Domain Constraint

 Restricts values to a specific range or type

 Ensures correct data format

Example:
Age must be between 18 and 60
Salary must be positive
2. NOT NULL Constraint

 Ensures a column cannot have NULL values

Example:
Student name cannot be empty.

name VARCHAR(50) NOT NULL

3. UNIQUE Constraint

 Ensures all values in a column are different

 Prevents duplicate entries

Example:
Email ID must be unique.

email VARCHAR(100) UNIQUE

4. PRIMARY KEY Constraint

 Uniquely identifies each record in a table

 Cannot be NULL
 Must be unique

Example:
Student_ID

PRIMARY KEY (student_id)

5. FOREIGN KEY Constraint

 Establishes a relationship between two tables

 Ensures referential integrity

Example:
Department_ID in Employee table refers to Department table.

FOREIGN KEY (dept_id) REFERENCES Department(dept_id)

6. CHECK Constraint

 Ensures values satisfy a specific condition

Example:
Marks should be between 0 and 100.

CHECK (marks >= 0 AND marks <= 100)

7. DEFAULT Constraint
  Assigns a default value if no value is provided

Example:
Status = 'Active'

status VARCHAR(10) DEFAULT 'Active'

Constraint Purpose
Domain Restricts range/type of values
NOT NULL Prevents NULL values
UNIQUE Prevents duplicate values
PRIMARY KEY Uniquely identifies records
FOREIGN KEY Maintains relationship
CHECK Validates conditions
DEFAULT Assigns default value

Superclass
Definition

A superclass is a general entity that contains common attributes shared by its subclasses.

Example

Employee (Employee_ID, Name, Salary)

ER Representation

 Shown as a normal entity (rectangle)

 Acts as the parent entity

Subclass
Definition

A subclass is a specialized entity derived from a superclass.

It has:

 All attributes of the superclass

 Its own specific attributes

Example

 Teacher (Subject)
 Clerk (Department)

Inheritance
Definition

Inheritance means a subclass automatically gets (inherits) all attributes and relationships of the superclass.

Example

Teacher inherits:

 Employee_ID
 Name
 Salary

No need to repeat common attributes.

Specialization (Top-Down)
Definition

Specialization is the process of dividing a superclass into multiple subclasses based on some characteristics.

Example

Employee → Teacher, Engineer

Why used?

 To show more detailed data

 To represent real-world differences

ER Explanation

 One superclass at top

 Multiple subclasses below

Generalization (Bottom-Up)
Definition

Generalization is the process of combining multiple similar entities into one superclass.

Example

Teacher + Engineer → Employee

Why used?

 To reduce redundancy
 To simplify the database design
ER Diagram Explanation
Employee
(ID, Name, Salary)
|
----------------
| |
Teacher Clerk
(Subject) (Department)

 Employee → Superclass
 Teacher, Clerk → Subclasses
 Attributes of Employee are inherited