1.

Explain Basic architecture of DBMS with its neat block

diagram.
A Database Management System (DBMS) is software that manages databases. It provides an
interface between the users and the database, ensuring data is organized, stored, and retrieved
efficiently. The basic architecture of a DBMS includes several key components, typically organized
into layers. Here's an explanation with a block diagram:
Components of DBMS Architecture:
1. User Interface:
• The topmost layer, where users interact with the DBMS. This could be through a
GUI or command-line interface.
• It takes inputs from users, such as queries (written in SQL), and sends them to the
DBMS for processing.
2. Query Processor:
• Parser: Converts the user's query into a format that the system can process.
• Query Optimizer: Optimizes the query by determining the most efficient way to
execute it, often considering available indices, joins, and execution paths.
• Execution Engine: Executes the query after it is optimized.
3. Database Manager:
• Authorization and Integrity Manager: Ensures users have the necessary
permissions and that data integrity constraints are maintained.
• Transaction Manager: Ensures transactions are executed completely and correctly,
providing properties such as ACID (Atomicity, Consistency, Isolation, Durability).
• Buffer Manager: Manages the database’s interaction with the system’s memory,
ensuring data is efficiently stored and retrieved from the main memory.
• File Manager: Manages the physical storage of data on disk and handles
reading/writing operations.
4. Storage Manager:
• Responsible for storing, retrieving, and updating data in the database.
• It works closely with the file manager and buffer manager to handle data on both
disk and in memory.
5. Data Dictionary/Metadata:
• Stores data about data (metadata), such as the schema of the database, table
structures, constraints, and user privileges.
• The data dictionary is vital for the DBMS to manage and enforce the rules around
data organization.
6. Database:
• The actual database where data is stored in tables, indexes, and other structures like
B-trees, hash tables, etc.
• The DBMS interacts with the database to retrieve or modify the stored data as per
user requests.
2. Write short notes on (i) Purpose of Database systems(5) (ii) Views of data (4) (iii)
Applications of database systems(4)

(i) Purpose of Database Systems

1. Data Storage and Management:
• Database systems are designed to store vast amounts of data in a structured and
efficient manner. They help in organizing data into tables, rows, and columns, which
can be easily retrieved and managed.
2. Data Security and Integrity:
• DBMS ensures that only authorized users have access to data. Security mechanisms
include authentication, authorization, and encryption. Integrity constraints help
maintain the accuracy and consistency of data.
3. Concurrency Control:
• In multi-user environments, DBMS manages concurrent access to the data. It ensures
that simultaneous operations on the same data do not result in data inconsistency.
4. Transaction Management:
• DBMS handles transactions by ensuring they are executed fully and correctly,
adhering to the ACID properties (Atomicity, Consistency, Isolation, Durability).
5. Data Backup and Recovery:
• DBMS provides mechanisms for backing up data to prevent data loss in case of
failures and supports data recovery methods to restore the system to a previous state.

(ii) Views of Data

1. Physical View:
• The lowest level of abstraction, focusing on how data is stored in the database (e.g.,
the data structures and storage media like hard drives). This view deals with the
internal storage details and algorithms used to retrieve data efficiently.
2. Logical View:
• The middle level of abstraction, which deals with what data is stored and how it is
organized. This is where database schemas, tables, and relationships are defined. The
logical view abstracts away the physical storage details, providing a model that is
easier to understand.
3. External View:
• The highest level of abstraction, which presents data to the end-users in a way that is
meaningful to them. Each user or application may have a different external view,
showing only relevant portions of the database, often via a customized interface.
4. Schema-Based View:
 • This view deals with the database schema, which defines the logical structure of the
data, including tables, relationships, and constraints. It is often part of the logical
view but focuses on the design and organization of the database.

(iii) Applications of Database Systems

1. Banking and Finance:
• Databases are widely used in banking for managing customer accounts, transactions,
loans, and credit systems. They support secure and accurate financial operations and
reporting.
2. E-commerce:
• Online shopping platforms rely heavily on databases to manage product catalogs,
customer data, transactions, and inventory management. Databases help track orders,
process payments, and maintain customer relationships.
3. Healthcare:
• In healthcare, databases store patient records, medical histories, treatment plans, and
medication data. They support healthcare providers in decision-making, ensuring
accurate and accessible patient information.
4. Telecommunications:
• Telecom companies use databases to manage call records, billing systems, network
data, and customer information. They help in tracking usage, generating bills, and
optimizing network performance.
Each of these components demonstrates the versatility and importance of database systems across
various sectors.
4o
Illustrate with an example, Extended E-R Diagram and its notations

An Extended Entity-Relationship (EER) diagram is an enhancement of the basic ER model to

include more advanced data modeling concepts such as specialization, generalization, aggregation,
and categories. It extends the ER model to represent the more complex relationships between
entities, making it suitable for more detailed data models.

Key Components and Notations of an EER Diagram

1. Entities: Represented by rectangles, these are the main objects in the system, such as
Student, Course, Teacher, etc.

2. Attributes: Represented by ovals connected to entities or relationships. They represent

properties of entities or relationships.
• Simple Attribute: An atomic, indivisible value (e.g., Name or ID).
• Composite Attribute: Composed of multiple simple attributes (e.g., Full Name
could be split into First Name and Last Name).
• Derived Attribute: Calculated or derived from other attributes (e.g., Age derived
from Date of Birth).
• Multi-Valued Attribute: Attributes that can have multiple values (e.g., Phone
Numbers).
3. Relationships: Represented by diamonds, relationships define how entities are related to
each other (e.g., Enrolled connects Student and Course).

4. Specialization: Represented by a triangle with lines pointing toward specialized entities.

Specialization is the process of defining subtypes of a generalized entity.
• Example: Vehicle can be specialized into Car, Truck, Motorcycle.
5. Generalization: The reverse of specialization, where different entities are generalized into a
common superclass.
• Example: Car, Truck, Motorcycle can be generalized into Vehicle.
6. Aggregation: Represented by a diamond inside a rectangle, aggregation is a higher-level
abstraction of relationships, where a relationship between entities is treated as an entity
itself.
• Example: An Employee works on a Project, and Department oversees the
Project. Here, the relationship Works_On can be aggregated into a higher-level
relationship with Department.
7. Category/Union Types: Represented by a circle or a U symbol, it defines a superclass that
can be shared by multiple unrelated classes.

Example of an Extended E-R Diagram:

Let’s consider a university database with entities such as Person, Student, Teacher,
Course, and relationships such as Teaches, Enrolled, etc. We will include generalization,
specialization, and aggregation.
Scenario:
• Person can be generalized into Student and Teacher.
• A Student enrolls in Courses, and a Teacher teaches Courses.
• A Department oversees the Courses.
• We’ll also aggregate the Teaches relationship into a higher-level abstraction.

Explanation of the Example:

1. Generalization/Specialization:
• Person is a generalized entity, which is specialized into two subtypes: Student
and Teacher. This is shown by the triangle symbol pointing to the subtypes.
2. Entities and Attributes:
• The entities are represented as rectangles (e.g., Person, Student, Teacher,
Course, Department). Each entity contains its attributes (e.g., Person_ID,
Major, Salary).
3. Relationships:
• Enrolled is a relationship between Student and Course, and Teaches is a
relationship between Teacher and Course.
4. Aggregation:
 • The relationship Teaches is aggregated into a higher-level relationship
Oversees, which connects Department to the Teaches relationship. This
indicates that a department oversees the course being taught.
This example illustrates how EER diagrams can model complex relationships with advanced
features like generalization, specialization, and aggregation, allowing for a more detailed and
structured representation of data.
1. DDL (Data Definition Language)
DDL is used to define the structure of the database schema. It includes commands like CREATE,
ALTER, DROP, and TRUNCATE.

1. DDL (Data Definition Language)

DDL is used to define the structure of the database schema. It includes commands like CREATE,
ALTER, DROP, and TRUNCATE.

a) Creating the Student Table

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
DOB DATE,
Branch VARCHAR(50),
DOJ DATE
);

b) Creating the Course Table

CREATE TABLE Course (

CourseID INT PRIMARY KEY,
CourseName VARCHAR(100),
FacultyID INT,
FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID)
);

c) Creating the Faculty Table

Copy code
CREATE TABLE Faculty (
FacultyID INT PRIMARY KEY,
FacultyName VARCHAR(100)
);

d) Creating the Marks Table

CREATE TABLE Marks (

StudentID INT,
CourseID INT,
Marks INT,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
2. DML (Data Manipulation Language)
DML is used to manipulate data within the schema. Common DML commands include INSERT,
UPDATE, DELETE, and SELECT.

a) Inserting Records
• Inserting into the Student table:

INSERT INTO Student (StudentID, Name, DOB, Branch, DOJ)

VALUES (1, 'John Doe', '2000-05-10', 'Computer Science', '2020-08-01');

• Inserting into the Course table:

INSERT INTO Course (CourseID, CourseName, FacultyID)

VALUES (101, 'Artificial Intelligence', 10);

• Inserting into the Faculty table:

INSERT INTO Faculty (FacultyID, FacultyName)

VALUES (10, 'Dr. Smith');

• Inserting into the Marks table:

INSERT INTO Marks (StudentID, CourseID, Marks)

VALUES (1, 101, 95);

b) Updating Records
• Updating a student's branch:

UPDATE Student
SET Branch = 'Information Technology'
WHERE StudentID = 1;

c) Deleting Records
• Deleting a student:

DELETE FROM Student

WHERE StudentID = 1;

d) Selecting Data
• Selecting all details of students:

SELECT *
FROM Student;

• Selecting student marks for a specific course:

SELECT s.Name, c.CourseName, m.Marks
FROM Student s
JOIN Marks m ON s.StudentID = m.StudentID
JOIN Course c ON m.CourseID = c.CourseID
WHERE c.CourseName = 'Artificial Intelligence';

3. DCL (Data Control Language)

DCL is used to control access to the database. The most common DCL commands are GRANT and
REVOKE.

a) Granting Permissions
• Granting SELECT permission to a user:

GRANT SELECT ON Student TO 'username';

• Granting INSERT and UPDATE permissions:

GRANT INSERT, UPDATE ON Course TO 'username';

b) Revoking Permissions
• Revoking UPDATE permission from a user:

REVOKE UPDATE ON Course FROM 'username';

Explanation:
1. DDL commands focus on defining and managing the structure of the database, such as
creating tables, altering table schemas, and removing tables.
2. DML commands handle data manipulation within the tables, including inserting, updating,
deleting, and selecting records.
3. DCL commands manage access rights and privileges to ensure only authorized users can
manipulate or access certain data.
This structure helps organize your student, course, faculty, and marks data while ensuring data
integrity and security through proper privilege management.
Develop queries and show how a SQL relation can be defined using various
constraints and types of keys? Also, Explain different data definition
Language statements in detail.

SQL Relations with Constraints and Keys

In SQL, relations (tables) can be defined with various constraints and types of keys to ensure data
integrity and enforce rules on the data. Let's define these concepts and demonstrate how they are
implemented with queries.

1. Primary Key (PK)

• Uniquely identifies each row in a table.
• No duplicate values and cannot be NULL.

2. Foreign Key (FK)

• A column or set of columns that establishes a link between data in two tables.
• Ensures referential integrity between the parent and child tables.

3. Unique Key
• Ensures all values in a column are unique.
• Allows NULL, but only one per column.

4. Not Null
• Ensures that a column cannot have NULL values.

5. Check Constraint
• Limits the values that can be placed in a column based on a specific condition.

6. Default
• Sets a default value for a column when no value is provided.

Example: Creating a SQL Relation with Constraints

sql
Copy code
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- Primary Key
FirstName VARCHAR(50) NOT NULL, -- Not Null Constraint
LastName VARCHAR(50) NOT NULL, -- Not Null Constraint
DepartmentID INT, -- Foreign Key Constraint will be
added below
Email VARCHAR(100) UNIQUE, -- Unique Constraint
DateOfBirth DATE CHECK (DateOfBirth < GETDATE()), -- Check Constraint
Salary DECIMAL(10, 2) DEFAULT 50000.00 -- Default Constraint
);

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,
 DepartmentName VARCHAR(100) NOT NULL
);

-- Adding a Foreign Key Constraint

ALTER TABLE Employees
ADD CONSTRAINT FK_Employees_Departments
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

In the above example:

• Primary Key: EmployeeID uniquely identifies each employee.
• Foreign Key: DepartmentID links the Employees table to the Departments table.
• Unique: Email ensures no two employees share the same email.
• Not Null: FirstName and LastName cannot be NULL.
• Check: Ensures that the DateOfBirth is always in the past.
• Default: Sets a default salary of 50000.00 for employees who don’t have a salary
specified.

Data Definition Language (DDL) Statements

DDL statements are used to define or alter the structure of database objects like tables, indexes, and
views. Key DDL commands include:
1. CREATE
• Used to create a new table, index, or database object.
• Example:

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
Address VARCHAR(255)
);

2. ALTER
• Used to modify an existing database object, such as adding, modifying, or dropping
columns or constraints.
• Example:

ALTER TABLE Customers

ADD Email VARCHAR(100);

3. DROP
• Used to delete tables, views, or other objects from the database.
• Example:

DROP TABLE Customers;

4. TRUNCATE
• Removes all rows from a table but does not delete the table itself.
 • Faster than DELETE because it bypasses logging and triggers.
• Example:

TRUNCATE TABLE Customers;

5. RENAME
• Renames a table or column.
• Example:

ALTER TABLE Customers

RENAME TO Clients;

6. COMMENT
• Adds a comment to a table or column for documentation purposes.
• Example:

COMMENT ON COLUMN Employees.Salary IS 'Employee Salary in USD';

Explanation of Different DDL Statements:

• CREATE: Defines a new table, view, index, etc. The CREATE statement is fundamental for
initializing database objects.
• ALTER: Allows modifications to the structure of a database object after it has been created.
It’s used to change the schema without dropping and recreating the object.
• DROP: Permanently deletes the database object. Once dropped, all data within that table or
object is permanently lost.
• TRUNCATE: Removes all records from a table without removing the table definition itself.
Unlike DELETE, it cannot be rolled back.
• RENAME: Renames the table or column, providing flexibility when refactoring a database
schema.
• COMMENT: Useful for documentation purposes, helping developers understand the
purpose of certain columns or tables.
These DDL statements allow you to define, modify, and manage the database schema in a flexible
manner while maintaining data integrity through constraints and keys.
Perform the following clauses of SQL with example queries.
i. FROM clause
ii. GROUP BY clause
iii. HAVING clause
iv. ORDER BY clause.