Chapter : 1 DBMS Handwritten Notes

SYLLABUS:

1.1 Introduction to data, database, Database system, DBMS

1.2 Field, Record, Objects, Primary Key, Alternate key, Candidate key

1.3 Advantages of using DBMS

1.4 DDL (Data Definition Language) and DML (Data Manipulation Language)

1.5 Database Model: Network Model, Hierarchical Model, Relational database model

1.6 Concept of Normalization: 1NF, 2NF, 3NF

1.7 Centralized Vs. Distributed Database

1.8 Database Security

Advantages of a DBMS over a flat-file system:

1. Data integrity: A database system enforces data constraints and relationships, ensuring that the data is accurate and consistent.
2. Data independence: A database system allows for changes to be made to the structure of the data without affecting the applications that use the data.
3. Concurrent access: A database system allows multiple users to access the same data simultaneously, without interfering with each other.
4. Data security: A database system provides various security mechanisms to control access to the data and prevent unauthorized access.
5. Data sharing: A database system allows for data to be easily shared among different applications and users.
6. Data Backup and Recovery: Database system provides various options for data backup and recovery, which makes it easy to recover data in case of any failure.

More Notes will be continued ...

2) Second Normal Form (2NF):

A relation or table is said to be in second normal form (2NF) if it is already in First Normal Form (1NF) and its all non-key attributes are fully functionally dependent on the Primary Key.

The purpose of 2NF is to eliminate partial key dependencies.

To convert the table in 1NF into 2NF, the non-key attributes which are not fully functionally dependent on the Primary Key are decomposed into separate table.

For example:

Original Table (Before 2NF):

StudentID	Class	StudentName	Subject	Marks
1	10	Ram	English	80
1	10	Ram	Math	75
2	10	Hazi	English	85
2	10	Hazi	Math	70

Primary Key: (StudentID, Subject)

Here this table is  not in 2NF  because StudentName and Class depend only on StudentID.

 

After 2NF Decomposition:

We split the table into 3 smaller tables as follows:

Table 1: Students

StudentID	StudentName	Class
1	Ram	10
2	Hazi	10

Primary Key: StudentID

 

Table 2: Subjects

SubjectID	SubjectName
101	English
102	Math

Primary Key: SubjectID

Table 3: Marks

StudentID	SubjectID	Marks
1	101	80
1	102	75
2	101	85
2	102	70

Primary Key: (StudentID, SubjectID)

3) Third Normal Form (3NF):

A relation or table is said to be in third normal form (3NF) if it is already in Second Normal Form (2NF) and it has no transitive(indirect) dependencies (non-key attributes don't depend on other non-key attributes)

In simpler terms, in 3NF, every non-key column must depend only on the primary key, not on any other columns in the table.

The purpose of 3NF is to eliminate transitive key dependencies.

For Example:

Original Table (Before 3NF):

StudentID	StudentName	Subject	SubjectCode
1	Ramesh	Mathematics	M101
2	Sita	Science	S102
3	Gopal	Mathematics	M101

Primary Key: StudentID
Here this table is not in 3NF because there's a transitive dependency: SubjectCode → Subject→ StudentID

After 3NF Decomposition:

We split the table into 2 smaller tables as follows:

Table1: Student

StudentID	StudentName	Subject
1	Ramesh	Mathematics
2	Sita	Science
3	Gopal	Mathematics

 

Table2: Subject

Subject	SubjectCode
Mathematics	M101
Science	S102

Now all transitive dependencies are removed, and each table is in 3NF. The non-key columns in each table depend only on the primary key of that table.

1.7 Centralized Vs. Distributed Database

1. Centralized Database

A centralized database is a database system where all data is stored, managed, and processed in a single location (e.g., one server or data center). All users and applications access this central repository for data operations. Examples: Traditional SQL databases like MySQL (single-server), Oracle (non-distributed setup), or a company’s internal SQL server.

Advantages:

a)       Simpler management: Single point of administration

b)       Consistency: Easier to maintain data integrity

c)       Lower infrastructure costs: No need for complex network systems between nodes

d)       Simpler backup/recovery: Single system to manage

Disadvantages:

a)       Single point of failure: Entire system fails if server goes down

b)       Scalability limitations: Limited by single server capacity

c)       Performance bottlenecks: All requests go to one server

d)       Geographic limitations: Higher latency for remote users

 

2. Distributed Database:

A distributed database is a database system where data is stored across multiple physical locations, either on different servers, data centers, or even geographically dispersed regions. These nodes work together to appear as a single database to users. Examples: Google Spanner, Cassandra, MongoDB (sharded), Amazon DynamoDB, CockroachDB.

Advantages:

a)       High availability: Failure of one node doesn't take down system

b)       Scalability: Can add more nodes to handle increased load

c)       Faster local access: Data can be located near users

d)       Fault tolerance: Redundancy protects against data loss

Disadvantages:

a)       Complexity: More difficult to design and maintain

b)       Consistency challenges: Requires coordination between nodes

c)       Higher infrastructure costs: More hardware and network requirements

d)       Transaction management: Distributed transactions are complex

 

A comparison table showing the differences between Centralized and Distributed Databases based on key factors:

Basis of Comparison	Centralized Database	Distributed Database
Location of Data	Stored in a single location/server	Stored across multiple locations/servers
System Architecture	Single system	Multiple interconnected systems (nodes)
Availability	Low – failure of central server causes system downtime	High – failure of one node doesn't bring down the entire system
Scalability	Limited to the capacity of a single machine	Easily scalable by adding more nodes
Data Access Speed	Slower for remote users due to central location	Faster local access as data can be placed closer to users
Consistency	Easier to maintain strong consistency	Harder to maintain due to data replication and synchronization
Complexity	Simple design and maintenance	Complex architecture requiring synchronization and coordination
Cost	Lower infrastructure cost	Higher due to multiple servers and network requirements
Performance	Performance bottleneck under high load	Better performance due to load distribution
Backup & Recovery	Easier – only one system to manage	More complex – needs coordinated backup across multiple locations
Examples	MySQL (single server), Oracle (non-clustered), MS SQL Server	Google Spanner, MongoDB (sharded), Cassandra, Amazon DynamoDB

 

1.8 Database Security

Data Security refers to the protective measures and protocols implemented to safeguard digital data from unauthorized access, corruption, theft, or damage throughout its lifecycle. It includes technologies, policies, and procedures designed to ensure the confidentiality, integrity, and availability (CIA triad) of data, whether at rest (stored), in transit (being transmitted), or in use (being processed).

Key Aspects of Data Security:

1. Confidentiality: Ensuring only authorized users can access data (e.g., encryption, access controls).
2. Integrity: Protecting data from unauthorized modification (e.g., checksums, digital signatures).
3. Availability: Ensuring data is accessible when needed (e.g., backups, disaster recovery).

Common Data Security Measures:

1. Encryption: Scrambling data to make it unreadable without a decryption key.
2. Access Controls: Authentication (passwords, biometrics) and authorization (role-based permissions).
3. Firewalls & Intrusion Detection Systems (IDS): Blocking or monitoring malicious network traffic.
4. Data Masking & Anonymization: Hiding sensitive data in non-production environments.
5. Regular Audits & Monitoring: Tracking access and detecting anomalies.
6. Backups & Recovery Plans: Protecting against data loss from breaches or disasters.

Importance of Database Security in DBMS (Database Management System):

Database security plays a critical role in any DBMS because it protects valuable data assets from unauthorized access, misuse, corruption, or loss. Below are the key points that describe the importance of database security:

1.       Protects Sensitive Data: Secures confidential information like personal, financial, and health records from unauthorized access.

2.       Ensures Legal Compliance: Helps meet regulations (e.g., GDPR, HIPAA), avoiding legal penalties.

3.       Prevents Data Breaches: Reduces the risk of exposing critical or private data to outsiders.

4.       Maintains Data Integrity: Prevents unauthorized changes, ensuring data remains accurate and reliable.

5.       Ensures Data Availability: Provides continuous access to authorized users, even during failures or attacks.

6.       Controls Access: Restricts data access and actions based on user roles, preventing misuse or accidental damage.

7.       Supports Business Continuity: Enables ongoing operations during cyberattacks or system failures through secure backups.

8.       Enables Monitoring & Accountability: Tracks user activity via logs and audits to detect misuse and promote transparency.

Some Out of Syllabus but important:

DBA (Database Administrator):

A Database Administrator (DBA) is a specialized IT professional responsible for managing, maintaining, securing, and optimizing database systems to ensure efficient and reliable access to data.

Key Characteristics of a DBA:

1. Technical Expertise:
   Strong understanding of database systems like Oracle, SQL Server, MySQL, PostgreSQL, etc.
2. Problem Solving Skills:
   Able to quickly diagnose and resolve database issues like performance bottlenecks or crashes.
3. Attention to Detail:
   Precision in tasks like backup schedules, security permissions, and schema changes.
4. Security Awareness:
   Implements access controls, encryption, and auditing to safeguard data.
5. Adaptability:
   Keeps up with evolving technologies and upgrades in DBMS platforms.
6. Communication Skills:
   Coordinates with developers, system admins, and stakeholders to meet data requirements.

Top 6 Roles and Responsibilities of a DBA:

1. Database Installation and Configuration
   Setting up the DBMS software and configuring it for optimal performance.
2. Backup and Recovery:
   Scheduling regular backups and planning for disaster recovery to prevent data loss.
3. Performance Monitoring and Tuning:
   Analysing query performance and optimizing indexes, queries, and server resources.
4. Security Management:
   Creating user accounts, assigning roles, and enforcing access control policies.
5. Database Design and Schema Management:
   Assisting in logical and physical database design based on application needs.
6. Data Integrity and Availability:
   Ensuring data is accurate, consistent, and available to authorized users when needed.

DCL (Data Control Language):

Used to control access and permissions on database objects.

Common DCL Commands:

a)       GRANT – gives user access rights

b)       REVOKE – removes access rights

TCL (Transaction Control Language):

Used to manage transactions in a database.

Common TCL Commands:

a)       COMMIT: saves changes made during the transaction

b)       ROLLBACK: undoes changes if there’s an error

c)       SAVEPOINT: sets a point within a transaction to rollback to

 

IMPORTANT EXTRA QUESTIONS:

Q1) Write SQL DDL command to execute the following task with reference to the schema given below: student_info(regno as integer, name as character(25), class integer,gender character(1), address character(25)).           

Ans:

The SQL Data Definition Language (DDL) command to create the student_info table with the specified schema is as follows:

CREATE TABLE student_info (
    regno INT PRIMARY KEY,
    name VARCHAR(25),
    class INT,
    gender VARCHAR(1),
    address VARCHAR(25)
);

Output:

Student_info

regno	name	class	gender	address
empty

Explanation of above command:

1. Creates a new table named student_info

2. Defines five columns with the specified data types:

• regno as INTEGER

• name as CHARACTER(25) (fixed-length string of 25 characters)

• class as INTEGER

• gender as CHARACTER(1) (single character)

• address as CHARACTER(25) (fixed-length string of 25 characters)

Q2) {2081 GIE Set A Q.No. 10 OR}
Write the SQL DDL statement to create an employee table with the mentioned schema with following attributes.

Field	Data type	Constraints
Employee ID	INT	PRIMARY KEY
Name	CHAR(30)
Address	CHAR(20)
Gender	CHAR(2)
Post	CHAR(15)

 

Ans:

The SQL DDL (Data Definition Language) statement to create the employee table based on the given schema is as follows:

CREATE TABLE employee (

    EmployeeID INT PRIMARY KEY,

    Name VARCHAR(30),

    Address VARCHAR(20),

    Gender VARCHAR(2),

    Post VARCHAR(15)

);

Output:

Employee

EmployeeID	Name	Address	Gender	Post
empty

Explanation of above command:

Ø  EmployeeID is of type INT and is set as the Primary Key.

Ø  Name, Address, Gender, and Post use the CHAR datatype with specified lengths as per the question.

Ø  No additional constraints are given, so only the primary key is defined.

Q3) Consider the table and answer the following:

StudentID	StudentName	CourseID	CourseName	InstructorName
1	Ram	C101	Math	Mr. Sharma
2	Sita	C102	Physics	Ms. Joshi
1	Ram	C102	Physics	Ms. Joshi

 

a. Is the above table in 1NF? Justify your answer.

Ans:

Yes, the table is in First Normal Form (1NF) because all fields contain atomic (single) values and there are no repeating groups.

 

b. If the table is not in 1NF, convert it into 1NF.

Ans:

Not applicable: because the table is already in 1NF.

 

c. Is the 1NF table in 2NF? If not, convert it to 2NF and explain.

Ans:

The table is not in 2NF because:

Ø  The primary key is (StudentID, CourseID).

Ø  StudentName, CourseName, and InstructorName depend only on part of the primary key (partial dependency).

Converting above 1NF table into multiple 2NF Tables:

1. Student Table:

StudentID	StudentName
1	Ram
2	Sita

 

2. Course Table:

CourseID	CourseName	InstructorName
C101	Math	Mr. Sharma
C102	Physics	Ms. Joshi

 

3. Enrollment Table:

StudentID	CourseID
1	C101
2	C102
1	C102

 

d. Is the 2NF table in 3NF? If not, convert it to 3NF and explain.

 Ans:

Yes, the 2NF tables are also in Third Normal Form (3NF) because:

Ø  All non-key attributes depend only on the primary key.

Ø  There are no transitive (indirect) dependencies.

Heritage Publication Book's Exercise:

Old Questions from Asmita Set: