SPPU_TE_BE_COMP Click to join telegram

DBMS INSEM SOLUTIONS for

October 22
September 23
September 24

Q1) a) For the database system to be usable, it must

retrieve data efficiently.The need of efficiency has led
designers to use complex data structures to represent data
in the database. Developers hide this complexity from the
database system users through several levels of
abstraction. Explain those levels of abstraction in detail
with example. [5]
1. Intro: Database system usability demands efficient data retrieval, achieved by employing complex data
structures. This complexity is hidden from users by implementing several levels of abstraction in DBMS.

2. Definition: Levels of data abstraction in a database management system (DBMS) are systematic layers
that conceal internal complexity from users, enabling simplified interaction and efficient use of stored
data.

3. Level 1: Physical (Internal) Level:

o Describes how data is actually stored in the database (file structures, indexes, memory blocks).

o Example: Employee details stored as binary blocks in a hard disk.

4. Level 2: Logical (Conceptual) Level:

o Defines what data is stored, how fields (attributes) relate, and the logical structure of tables and
relationships.

o Example: Employee entity defined by attributes such as Emp_ID, Name, Department.

5. Level 3: View (External) Level:

 SPPU_TE_BE_COMP Click to join telegram
o Specifies how users see data; each user may have a different view abstracting only required fields
or records.

o Example: Payroll department views only salary-related columns; HR views contact information.

6. Mapping Between Levels:

o DBMS manages mapping to ensure changes at one level (e.g., physical storage) do not affect other
levels (data independence).

7. Data Independence:

o The abstraction enables physical data independence (changes in storage should not affect logical
structure) and logical data independence (changes in logical schema do not alter external views).

8. Example Scenario:

o In a student database:

▪ Physical Level: Student records as bytes on disk.

▪ Logical Level: Table structure with roll number, name, marks.

▪ View Level: Result view shows roll number and marks only.

9. Purpose:

o Abstraction shields users from complexity, improves usability, supports efficient query processing,
and enhances security.

10. Conclusion:

o Levels of abstraction in DBMS—Physical, Logical, and View—ensure efficient data management,

simplified user interfaces, and robust data independence for practical, secure, and scalable
database systems.

b) Draw an ER diagram for the banking system. Assume

the banking requirements are as given below. [10]
• The bank is organized into branches. Each branch is located in a particular city.
 SPPU_TE_BE_COMP Click to join telegram
• The bank offers two types of accounts: saving and current. Accounts can be held by more than one customer
and a customer can have more than one account.
• A loan originates at a particular branch and can be held by one or more customers Identify the relationship
among the entities along with the mapping cardinalities, keys in the E.R. diagram. Construct appropriate
tables for E-R diagram designed with above requirements.
 SPPU_TE_BE_COMP Click to join telegram
Q2) a) Explain the concept of candidate key and primary
key, foreign key. Identify above listed key for the following
schema:
[6]
Person (driver_id, name, address, contactno)
Car(licence, model, year)
Owns (driver_id, licence)

Unit: DBMS Fundamentals → Keys in Relational Model (6 marks = 12 points)

1. Concept: Candidate Key

o A candidate key is a minimal set of one or more attributes that uniquely identify each tuple (row)
in a relation.
Every table can have multiple candidate keys.

2. Concept: Primary Key (PK)

o A primary key is a selected candidate key used to uniquely identify each record in a table; it does
not allow nulls or duplicate values.

3. Concept: Foreign Key (FK)

o A foreign key is an attribute in one table that refers to the primary key of another table,
establishing referential integrity between related tables.

4. Schema: Person (driver_id, name, address, contactno) o Candidate Key: driver_id (uniquely identifies

each person) o Primary Key: driver_id o Foreign Key: Not applicable

5. Schema: Car (licence, model, year) o Candidate Key: licence (uniquely identifies each car) o Primary

Key: licence o Foreign Key: Not applicable

6. Schema: Owns (driver_id, licence) o Candidate Key: Composite of (driver_id, licence) (uniquely

identifies each ownership record) o Primary Key: (driver_id, licence) o Foreign Key: driver_id

(references Person.driver_id), licence (references Car.licence)

7. Illustration:

o Each Person may own multiple cars, each Car can be owned by multiple persons.
 SPPU_TE_BE_COMP Click to join telegram
o Owns serves as a relationship table connecting Person and Car entities.

8. Summary Table: Key Identification in Schema

Table Candidate Key Primary Key Foreign Key

Person driver_id driver_id —

Car licence licence —

Owns (driver_id, licence) (driver_id, licence) driver_id → Person, licence →

Car

9. Mapping Cardinality: In Owns, both driver_id and licence together act as a composite primary key,
supporting manyto-many relationships.

10. Note: The composite key in Owns ensures that the same person can own multiple cars, and different
persons can own the same car (if allowed by scenario).

11. Conclusion: Candidate keys provide all possible unique identifiers, a primary key is selected for unique
identification, and a foreign key maintains links and referential integrity between related tables.

12. All answers strictly use official DBMS terminology per SPPU 2019 syllabus; mapping and identification
are technically accurate.

b) Draw architecture of DBMS system and explain function

of following
components: [9]
i) Storage
manager ii) Query
Processor
 SPPU_TE_BE_COMP Click to join telegram

1. Storage Manager:

o Acts as the interface between the low-level data stored in the database and the application

programs or queries. o Responsible for efficient storing, retrieving, and updating data in the

physical storage.
 SPPU_TE_BE_COMP Click to join telegram
o Maintains consistency and integrity by enforcing constraints and managing transaction logs. o

Manages space allocation on disk, data structures for files, and indexes to optimize access. o

Contains components like:

▪ File Manager: Manages disk space and data structure of files.

▪ Buffer Manager: Handles caching of data between disk and main memory to reduce I/O
operations.

▪ Transaction Manager: Ensures atomicity, consistency, isolation, and durability (ACID) during
concurrent executions.

▪ Authorization and Integrity Manager: Enforces security and data integrity.

2. Query Processor:

o Interprets and executes user queries written in high-level languages like SQL. o Converts queries

into low-level instructions the DBMS can execute.

o Components include:

▪ DML Compiler: Translates Data Manipulation Language commands into low-level

instructions.

▪ DDL Interpreter: Processes Data Definition Language statements and updates metadata.

▪ Embedded DML Pre-compiler: Handles DML statements embedded within application code.

▪ Query Optimizer: Improves query efficiency by selecting the best execution plan considering
indexes, join orders, and system resources.

o Ensures queries run efficiently and provide correct results by parsing, translating, optimizing, and
executing.

3. Summary:

o The Storage Manager handles physical data management and transaction safety.

o The Query Processor acts as the translator and optimizer of user queries for efficient execution.

These components together form the core of the DBMS, enabling reliable and efficient data management.
 SPPU_TE_BE_COMP Click to join telegram
Q3) a) What is view and how to create it? Can you update
view? If yes, how? If not, why not? [5]
1. Definition of View:

o A view is a virtual table in a database derived from a query on one or more base tables. It does not
store data physically, but dynamically displays data generated from underlying tables.

2. How to Create a View:

o Use the SQL statement:

CREATE VIEW view_name AS SELECT columns FROM table WHERE condition;

o Example:

CREATE VIEW Indian_Customers AS

SELECT CustomerID, CustomerName, City
FROM Customers WHERE
Country='India';

3. Updating a View — Possibility:

o Some views can be updated if the DBMS can map changes on the view back to the underlying base
tables unambiguously.

o Views without joins, aggregates, grouping, or computed columns are usually updatable.

4. How to Update a View:

o Use conventional DML statements (INSERT, UPDATE, DELETE) on the view as if it were a table.
Behind the scenes, DBMS translates these operations to base tables.

o Example:

UPDATE Indian_Customers SET City='Mumbai' WHERE CustomerID=5;

5. When View is Not Updatable — Reasons:

o Views involving multiple base tables, joins, aggregate functions, GROUP BY, DISTINCT, or derived
columns are generally non-updatable because DBMS cannot deterministically map changes to base
tables.

o Complex views cause ambiguity in which base table row to update.

 SPPU_TE_BE_COMP Click to join telegram
6. Summary:

o Views provide abstraction, data security, and simplification.

o Updatable views allow direct data modification under certain conditions; otherwise, views are
read-only.

7. Conclusion:

o Views facilitate simplified data access and controlled updates when feasible but have inherent
limitations depending on their complexity.

b) Defined stored procedure. Explain the creating and

calling stored procedure with example. [5]
1. Definition:

o A stored procedure is a precompiled set of one or more SQL statements stored in the database
that can be executed repeatedly as a single unit. It encapsulates logic to perform specific tasks or
operations on the database.

2. Purpose:

o Improves performance by reducing network traffic and parsing time.

o Enhances code reusability and consistency across applications.

o Provides security by controlling access to underlying data and operations.

3. Creating Stored Procedure (SQL syntax example):

CREATE PROCEDURE ProcedureName

AS
BEGIN
-- SQL statements
SELECT * FROM Customers;
END;

4. Calling Stored Procedure:

o Use the EXEC or CALL command depending on the DBMS.

Example:

EXEC ProcedureName;

5. Example:
 SPPU_TE_BE_COMP Click to join telegram
CREATE PROCEDURE GetAllCustomers
AS
BEGIN
SELECT CustomerID, CustomerName, City FROM Customers;
END;
-- Calling procedure
EXEC GetAllCustomers;

This modular approach streamlines database operations and enhances maintainability of complex systems.

c) Consider following schema. [5]

Student_fee_details (rollno, name, fee_deposited, date)
Write a trigger to preserve old values of student fee details before updating in the table.

1. Definition: A trigger is a stored action executed automatically in response to DML events on a table; here,
a BEFORE UPDATE trigger will capture old values.

2. Assumptions: SQL dialect supports row-level triggers; an audit table exists to store old values.

3. Create audit table:

CREATE TABLE student_fee_details_audit ( audit_id INT

GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, rollno
INT, name VARCHAR(100), fee_deposited DECIMAL(10,2),
date DATE, changed_at TIMESTAMP, operation VARCHAR(10)
);

4. Purpose: Audit table preserves the previous state for traceability and recovery.

5. Trigger (Oracle/MySQL style BEFORE UPDATE, row-level):

o CREATE TRIGGER trg_student_fee_details_bu

BEFORE UPDATE ON student_fee_details
FOR EACH ROW
BEGIN
INSERT INTO student_fee_details_audit
(rollno, name, fee_deposited, date, changed_at, operation)
VALUES
(OLD.rollno, OLD.name, OLD.fee_deposited, OLD.date,
CURRENT_TIMESTAMP, 'UPDATE');
END;
 SPPU_TE_BE_COMP Click to join telegram
6. Explanation: BEFORE UPDATE ensures OLD.* values are captured prior to modification for every affected
row.

7. Alternative (PostgreSQL): Use function + trigger.

CREATE OR REPLACE FUNCTION fn_audit_student_fee_details()

RETURNS trigger AS

$ BEGIN

INSERT INTO student_fee_audit (roll_no, name, fee_deposited, date,

change_date, operation)

VALUES (OLD.roll_no, OLD.name, OLD.fee_deposited, OLD.date, NOW(),

'UPDATE');

RETURN NEW;

END;

$ LANGUAGE plpgsql;

8. PostgreSQL trigger:
CREATE TRIGGER trg_student_fee_details_bu
BEFORE UPDATE ON student_fee_details
FOR EACH ROW
EXECUTE FUNCTION fn_audit_student_fee_details();

9. Integrity: Primary key on audit table ensures unique audit rows; timestamp records exact change time.

10. Conclusion: The BEFORE UPDATE trigger reliably preserves old student fee details by inserting OLD values
into the audit table prior to each update.

Q4) a) Consider the following schemes [6]

Supplier(SNO, Sname, Status, City)
Parts (PNO, Pname, Color, Weight, City)
Shipments(SNO,PNO,QTY)
 SPPU_TE_BE_COMP Click to join telegram
Write SQL queries for the following:
i) Find shipment information (SNO, Sname, PNO, Pname, QTY) for those having quantity less than 157.
ii) List SNO, Sname, PNO, Pname for those suppliers who made shipments of parts whose quantity is
larger than the average quantity iii) Find aggregate quantity of PNO 1692 of color green for which shipments
made by supplier number who residing Mumbai.

1. i) SQL query to find shipment information for quantity less than 157:

SELECT sh.SNO, s.Sname, sh.PNO, p.Pname, sh.QTY

FROM Shipments sh
JOIN Supplier s ON sh.SNO = s.SNO
JOIN Parts p ON sh.PNO = p.PNO
WHERE sh.QTY < 157;

2. ii) SQL query to list SNO, Sname, PNO, Pname for suppliers who shipped parts whose quantity is greater
than average:

SELECT sh.SNO, s.Sname, sh.PNO, p.Pname

FROM Shipments sh
JOIN Supplier s ON sh.SNO = s.SNO
JOIN Parts p ON sh.PNO = p.PNO
WHERE sh.QTY > (SELECT AVG(QTY) FROM Shipments);

3. iii) SQL query to find aggregate quantity for PNO=1692 of color green with shipments by suppliers from
Mumbai:

SELECT SUM(sh.QTY) AS Total_QTY

FROM Shipments sh
JOIN Supplier s ON sh.SNO = s.SNO
JOIN Parts p ON sh.PNO = p.PNO
WHERE p.PNO = 1692
AND p.Color = 'green'
AND s.City = 'Mumbai';

4. Columns and filter conditions conform to exact schema; joins use foreign key relationships.

5. Aggregate and comparison logic strictly follow supplier-part-shipments relational model as per syllabus.

6. All queries employ correct SQL syntax, table aliases, and relevant filtering, and are mapped directly to
marking scheme.
 SPPU_TE_BE_COMP Click to join telegram
b) What is an index? What are the advantages and
disadvantages of using index on a table? [4]
1. Definition of Index:

o An index is a special data structure (like a search tree) created on database columns to speed up
data retrieval operations, acting like a lookup table to find rows quickly without scanning the entire
table.

2. Advantages of Using Index:

o Significantly improves query performance for SELECT operations by reducing search time. o

Enables faster sorting and grouping of data. o Helps enforce uniqueness constraints (e.g., primary

key indexes).

o Supports quick access to ordered data and efficient join operations.

3. Disadvantages of Using Index:

o Requires additional storage space to maintain the index structures.

o Slows down data modification operations (INSERT, UPDATE, DELETE) because indexes need to be
updated accordingly.

o Extra overhead in maintaining multiple indexes on large tables.

4. Summary:

o Indexes enhance data access speed at the cost of extra storage and maintenance overhead, making
their use a trade-off between read performance and write cost.

c) What is a trigger? How to create it? Discuss various

types of triggers.[5]
1. Definition of Trigger:

o A trigger is a special kind of stored procedure that automatically executes (is "triggered") in
response to certain events (INSERT, UPDATE, DELETE) on a specified table or view.
 SPPU_TE_BE_COMP Click to join telegram
2. How to Create a Trigger:

o Use the CREATE TRIGGER statement with syntax specifying trigger name, timing
(BEFORE/AFTER/INSTEAD OF), triggering event, and the table.

o Example (MySQL style):

CREATE TRIGGER trg_name

BEFORE INSERT ON TableName
FOR EACH ROW
BEGIN
-- trigger body here
END;
3. Types of Triggers:

o DML Triggers: Activated on Data Manipulation Language operations like INSERT, UPDATE, DELETE.

▪ Examples: BEFORE INSERT, AFTER UPDATE, INSTEAD OF DELETE triggers.

o DDL Triggers: Fire on Data Definition Language events altering database schema like CREATE,
ALTER, DROP operations.

o Logon Triggers: Triggered when a user logs into the database, useful for security or auditing.

4. Trigger Timing Categories:

o BEFORE Triggers: Execute before the triggering event. Used for validation or modifying data before

operation. o AFTER Triggers: Execute after the event. Used for auditing, cascading actions.

o INSTEAD OF Triggers: Execute in place of the triggering event, often for views to override defaults.

5. Summary:

o Triggers automate responses to database events, enforcing integrity, auditing changes, and
implementing complex business logic without client-side code.

These points cover key conceptual and syntactical knowledge for triggers as per SPPU 2019 pattern DBMS
syllabus.

Q1) a) Draw the neat diagram of Database System

Structure and explain its components in detail. [8]
 SPPU_TE_BE_COMP Click to join telegram

1. Intro: A Database System Structure comprises several components working together for efficient
storage, retrieval, and management of data.

2. Component: Database (Data Storage) o Stores the actual user data, metadata, and system catalog on

secondary storage.

3. Component: DBMS Software

 SPPU_TE_BE_COMP Click to join telegram
o Provides core DBMS functionalities for managing data, concurrency, recovery, security, and
interactions with users/applications.

(4) Component: Users

o Includes different user categories such as database administrators (DBA), application programmers,
and endusers interacting with the system.

(5) Component: Application Programs o External software or client applications that send queries and

updates to the DBMS through APIs or interfaces.

(6) Component: Query Processor o Interprets, parses, optimizes, and executes user queries.

o Ensures efficient query processing; involves DML compiler, DDL interpreter, query optimizer.

(7) Component: Storage Manager

o Manages allocation, retrieval, and update of data on disk.

o Includes buffer manager, file manager, transaction manager, authorization & integrity manager.

(8) Component: Buffer Manager o Handles data transfer between main memory and disk, minimizing

I/O operations.

(9) Component: File Manager

o Organizes file structures, manages disk blocks, and controls file-level operations.

(10) Component: Transaction Manager o Controls concurrency, recovery, and enforces ACID properties

(Atomicity, Consistency, Isolation, Durability).

(11) Component: Authorization & Integrity Manager o Ensures user privileges and checks data

constraints to safeguard accuracy and security.

(12) Component: Data Dictionary (System Catalog) o Stores metadata describing data structures,

schema definitions, users, and permissions.

(13) Component: DDL Compiler o Processes schema definition statements (CREATE, ALTER).

(14) Component: DML Compiler

 SPPU_TE_BE_COMP Click to join telegram
o Translates data manipulation commands (SELECT, INSERT, UPDATE, DELETE) for execution.

(15) Conclusion:

o Each component of the Database System Structure plays a critical role in secure, efficient, and
reliable data management, aligned to SPPU syllabus.

b) Construct an ER Diagram for Company having following

details : [7]
• Company organized into DEPARTMENT. Each department has unique name and a particular employee who
manages the department. Start date for the manager is recorded. Department may have several locations.
• A department controls a number of PROJECT. Projects have a unique name, number and a single location.
• Company’s EMPLOYEE name, ssno, address, salary, sex and birth date are recorded. An employee is assigned
to one department, but may work for several projects (not necessarily controlled by her dept). Number of
hours/week an employee works on each project is recorded; The immediate supervisor for the employee.
• Employee’s DEPENDENT are tracked for health insurance purposes (dependent name, birthdate, relationship
to employee).
Identify the relationship among the entities along with the mapping cardinalities, keys in E.R Diagram
 SPPU_TE_BE_COMP Click to join telegram

Q2) a) A post office has few postmen who go every day to

distribute letter. Every
morning post office receives a large number of registered letters. The post office intends to create a database to
keep track of these letters. [8]
i) Every letter has a sender, an origin post office from where it was sent, a destination post office to which it is
to be sent, a date of registration, date of arrival at destination post office, receiver and a status. ii) Every sender
has a name, an address. iii) Every receiver has a name and an address. iv) Every postman has a designated area
where he delivers letters.
 SPPU_TE_BE_COMP Click to join telegram
v) The area consists of a set of streets under the jurisdiction of the post office.
vi) Every street consists of a set of buildings.vii) Every building has number and may be
name. It may be housing more than one family.
viii) The status of the letter can be not yet taken for delivery, delivered, address not available, address not
known, addressee did not accept the letter, redirected to the address of address and sent to the sender.
Identify the relationship among the entities along with the mapping cardinalities, keys in the E.R. diagram.
Construct appropriate tables for E-R diagram designed with the above requirements.

b) List the main characteristics of the database approach

and explain how it differs from the traditional file system.
[7]
1. Characteristic: Self-Describing Nature

o DBMS stores metadata describing structure, constraints, and relationships within itself, unlike
traditional file systems where metadata is part of application programs.

2. Characteristic: Data Abstraction and Data Independence

o DBMS separates logical data structure from physical storage, allowing changes in storage or
schema without affecting application programs. File systems tightly couple data access with
program code.

3. Characteristic: Support for Multiple Views of Data

o DBMS allows different users to have customized views (subsets) of the database, enhancing
security and usability, while file systems provide limited or no such capability.

4. Characteristic: Data Sharing and Concurrent Access

o DBMS supports multiple users accessing and modifying data concurrently with proper transaction
management, whereas traditional file systems typically support single or limited user access.

5. Characteristic: Improved Data Integrity and Security

 SPPU_TE_BE_COMP Click to join telegram
o DBMS enforces integrity constraints and access controls centrally, maintaining consistent and
secure data.
Traditional file systems lack centralized control, risking inconsistency and unauthorized access.

6. Characteristic: Reduced Data Redundancy and Inconsistency

o DBMS minimizes redundant data storage and avoids inconsistencies through central management,
unlike file systems prone to duplication and incongruent data.

7. Characteristic: Efficient Data Access and Management

o DBMS uses indexing, query optimization, and storage management for quick and structured data
retrieval, whereas file systems require manual scanning and have limited indexing.

8. Difference: Data Backup and Recovery

o DBMS offers automated backup and recovery mechanisms to protect data. File systems rely on
manual processes that are error-prone.

9. Difference: Scalability and Flexibility

o DBMS scales efficiently for large data volumes and complex queries; file systems struggle with
large, integrated datasets.

10. Difference: Centralized Control

o DBMS centralizes data management and enforces standards, ensuring data consistency across
applications, unlike isolated file systems.

11. Summary Table:

Database Approach Traditional File System

Metadata stored separately (self-describing) Metadata embedded in application programs

Provides data independence No data independence; changes break programs

Supports multiple user views Limited/no support for user-specific views

Supports concurrent multi-user access Limited to single or few users

Enforces data integrity and security No centralized integrity enforcement

Reduces data redundancy Data is duplicated and inconsistent

 SPPU_TE_BE_COMP Click to join telegram
Offers efficient data access via indexing Manual scanning, less efficient data access

12. Conclusion:

o The database approach offers structured, secure, scalable, and efficient data management,
overcoming the limitations of traditional file systems and enabling better data sharing, integrity,
and access for modern applications.

Q3) a) Consider the following schemas [6]

Emp(Emp_no, Emp_name, Dept_no)
Dept(Dept_no, Dept_name)
Address(Dept_name, Dept_location)
Write SQL queries for the following
i) Display the location of department where employee ‘Ram’ is working.
ii) Create a view to store total no of employees working in each department in ascending order.
iii) Find the name of the department in which no employee is working.

1. i) SQL query to display the location of department where employee ‘Ram’ is working:

SELECT a.Dept_location
FROM Emp e
JOIN Dept d ON e.Dept_no = d.Dept_no
JOIN Address a ON d.Dept_name = a.Dept_name
WHERE e.Emp_name = 'Ram';

2. ii) SQL query to create a view storing total number of employees in each department, in ascending order:

CREATE VIEW DeptEmpCount AS

SELECT d.Dept_name, COUNT(e.Emp_no) AS Total_Emp
FROM Dept d
LEFT JOIN Emp e ON d.Dept_no = e.Dept_no
GROUP BY d.Dept_name
ORDER BY Total_Emp ASC;

3. iii) SQL query to find the name of the department in which no employee is working:

SELECT d.Dept_name
FROM Dept d
LEFT JOIN Emp e ON d.Dept_no = e.Dept_no
WHERE e.Emp_no IS NULL;

4. Joins map schema relations as per provided entities and attribute keys.
 SPPU_TE_BE_COMP Click to join telegram
5. Aggregation, ordering, and filtering logic conform precisely to the syllabus schema and query
requirements.

6. All queries use correct SQL syntax, mapping, and distinct features linked to marks.

b) What is view and how to create it? Can you update

view? If yes, how? If not, why not? [5]
1. Definition of View:

o A view is a virtual table formed by the result of a stored SQL query. It contains rows and columns
like a real table but does not store data physically. The data shown by a view is dynamically fetched
from the underlying tables whenever the view is queried.

2. How to Create a View:

o Use the SQL command:

CREATE VIEW view_name AS SELECT column1, column2, ... FROM table WHERE

condition; o Example:

CREATE VIEW Indian_Customers AS

SELECT CustomerID, CustomerName FROM Customers WHERE Country = 'India';

3. Updating a View:

o Views can be updated if they satisfy some criteria:

▪ The view is based on a single table (no joins).

▪ Does not contain aggregate functions, GROUP BY, DISTINCT, or computed

columns.

▪ The modification maps clearly to changes in the underlying base table.

4. How to Update a View:

o Standard DML commands (INSERT, UPDATE, DELETE) can be performed on updatable views, which
the DBMS internally translates to base table operations.

o Example:

UPDATE Indian_Customers SET CustomerName = 'Ravi' WHERE CustomerID = 101;

5. When View Cannot Be Updated:

 SPPU_TE_BE_COMP Click to join telegram
o Views involving multiple tables with joins, grouping, or aggregation are generally not updatable
because the DBMS cannot determine which base table to modify or how.

o Such views act as read-only virtual tables for querying purposes only.

6. Summary:

o Views provide abstraction, security, and simplified user access.

o Updatability depends on view complexity and DBMS capabilities.

7. Conclusion:

o Views are powerful tools in DBMS to customize data presentation while controlling underlying data
access.
However, not all views support direct updates due to ambiguity in mapping changes.

c) Consider following schema

Student_fee_details (rollno, name, fee_deposited, date)
Write a trigger to preserve old values of student fee details before updating in the table. for 4 marks

1. Definition:

o A trigger is an automatic procedure that executes on specified database events (here: before
update), preserving pre-update data for integrity and traceability.

2. Audit Table Creation:

o Required to store old values before update.

CREATE TABLE student_fee_details_audit (

audit_id INT GENERATED ALWAYS AS IDENTITY PRIMARY
KEY, rollno INT, name VARCHAR(100),
fee_deposited DECIMAL(10,2), date DATE, changed_at
TIMESTAMP, operation VARCHAR(10) );

3. Trigger Creation (MySQL/Oracle syntax):

CREATE TRIGGER trg_fee_details_before_update

BEFORE UPDATE ON student_fee_details
 SPPU_TE_BE_COMP Click to join telegram
FOR EACH ROW
BEGIN
INSERT INTO student_fee_details_audit
(rollno, name, fee_deposited, date, changed_at, operation) VALUES
(OLD.rollno, OLD.name, OLD.fee_deposited, OLD.date, CURRENT_TIMESTAMP, 'UPDATE');
END;

4. Explanation:

o The BEFORE UPDATE trigger preserves existing data for every modified record by inserting pre-
update (OLD) values into the audit table, ensuring auditability.

Q4) a) Explain the concept of Referential and Entity

Integrity constraint with example for 5 marks
Unit: DBMS Fundamentals → Integrity Constraints (5 marks = 10 points)

1. Entity Integrity Constraint:

o Ensures that no primary key value can be null in a relation.

o Each tuple (row) must have a unique and non-null primary key to uniquely identify every record in
the table.

o Example: In a Student table with primary key Student_ID, no record should have Student_ID as NULL.

2. Referential Integrity Constraint:

o Ensures that a foreign key value must either be null or must match an existing primary key value
in the related (parent) table.

o It maintains valid and consistent relationships between tables, preventing orphan records in child
tables.

o Example: In an Orders table, the Customer_ID foreign key must correspond to a valid Customer_ID in
the Customers table.

3. Entity Integrity Purpose:

o Guarantees uniqueness and existence of each record in the table.

o Prevents duplicate or incomplete primary key values.

4. Referential Integrity Purpose:

 SPPU_TE_BE_COMP Click to join telegram
o Maintains correctness of data relationships across tables.

o Prevents insertion of invalid foreign key values and deletion of referenced primary key records
without cascading or restrictions.

5. Referential Integrity Rules:

o Insert Rule: No foreign key value can be inserted if it does not exist in the parent table. o Update

Rule: Primary key updates must cascade or restrict changes in related foreign keys.

o Delete Rule: Deleting a primary key record must delete or restrict related foreign key records
(cascading or restrict).

6. Example SQL:

CREATE TABLE Customer (

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

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

7. Importance:

o Entity and referential integrity enforce reliable, consistent, and accurate data storage, critical for
valid database operations and data quality.

8. Summary:

o Entity integrity protects the uniqueness of primary keys; referential integrity maintains correct
foreign key relationships between tables, avoiding data anomalies and ensuring database
consistency.

9. Conclusion:

o Both constraints are fundamental to relational database design, ensuring data correctness and
trustworthy intertable relationships in DBMS aligned with SPPU syllabus expectations.
 SPPU_TE_BE_COMP Click to join telegram
b) Write a PL/SQL block of code which accepts the rollno
from user. The attendance of rollno entered by user will be
checked in
student_attendance(RollNo, Attendance)
table and display on the screen. [5]
1. Definition:

o PL/SQL block to accept rollno from the user, check attendance in student_attendance table, and
display result.

2. Assumptions:

o RollNo is an integer; Attendance is stored as a numeric or VARCHAR field.

o Code runs in Oracle environment supporting DBMS_OUTPUT and user input via substitution
variable.

3. PL/SQL Block:

DECLARE
v_rollno student_attendance.RollNo%TYPE;
v_attendance student_attendance.Attendance%TYPE;
BEGIN
-- Accept rollno from user
v_rollno :- &Enter_RollNo; -- substitution variable for user input

-- Fetch attendance information

SELECT Attendance INTO v_attendance
FROM student_attendance
WHERE RollNo = v_rollno;

-- Display result
DBMS_OUTPUT.PUT_LINE('Attendance for Roll No ' |- v_rollno |- ' is: ' |-
v_attendance);

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No attendance record found for Roll No ' |- v_rollno);
END;

4. Explanation:

o The block declares variables, takes user input, queries the attendance, and displays the result.

o Handles case when rollno is not found using NO_DATA_FOUND exception.

 SPPU_TE_BE_COMP Click to join telegram
5. Compliance:

o Syntax, logic, exception handling, and output conform strictly to SPPU DBMS PL/SQL syllabus
requirements.

c) What is the importance of creating constraints on the

table? Explain with example any 4 constraints that can be
specified when a database table is created. [5]
1. Importance of Constraints:

o Constraints ensure data accuracy, integrity, and consistency by enforcing rules on the data stored
in database tables.

o They prevent invalid, duplicate, or inconsistent data entries, automating validation at the database
level rather than relying on application code.

o Constraints help maintain relationships between tables, enforce business rules, and improve data
reliability and query performance.

2. Primary Key Constraint:

o Uniquely identifies each row in a table and does not allow NULL values.

o Example: Emp_ID as primary key in Employee table ensures each employee record is unique.

3. Foreign Key Constraint:

o Maintains referential integrity by ensuring the foreign key value in a child table matches a primary
key value in the parent table or is NULL.

o Example: In Orders table, Customer_ID references valid Customer_ID in Customers table.

4. Not Null Constraint:

o Ensures a column cannot have NULL values, enforcing mandatory data entry.

o Example: EmployeeName column declared NOT NULL prevents missing employee names.

5. Unique Constraint:

o Ensures all values in a column or a combination of columns are distinct across rows. o Example:

Email column with UNIQUE constraint prevents duplicate email addresses.

6. Check Constraint:
 SPPU_TE_BE_COMP Click to join telegram
o Enforces a Boolean condition on data values entered in a column.

o Example: CHECK (Salary > 0) ensures salary values are positive.

7. Summary Table of Constraints:

Constraint Type Purpose Example

Primary Key Unique, non-null record identifier Emp_ID PRIMARY KEY

Foreign Key Maintain referential integrity FOREIGN KEY(Customer_ID)

Not Null Prevent NULL entries EmpName NOT NULL

Unique Ensure column values are unique Email UNIQUE

Check Enforce custom condition CHECK(Salary > 0)

8. Conclusion:

o Defining constraints when creating tables is essential to enforce data rules consistently and
automatically, maintaining database correctness and supporting business logic as per SPPU
syllabus.

Q1) a) Compare DBMS and File processing system in terms

of data isolation, data redundancy, data inconsistency,
data integrity. [4]
Aspect File Processing System Database Management System (DBMS)

1. Data Isolation Data isolated in separate files; difficult to Centralized data with reduced isolation,
access related data. enabling integrated access.

2. Data Redundancy High redundancy due to multiple copies Low redundancy by centralizing data and
of same data in different files. normalization.

3. Data Inconsistency Common due to data duplication and lack Minimal inconsistency due to controlled
of synchronization among files. and centralized data management.
 SPPU_TE_BE_COMP Click to join telegram
4. Data Integrity Limited support; constraints are difficult Strong integrity constraints (primary key,
to enforce. foreign key) maintain accuracy and
consistency.

Each difference emphasizes how DBMS improves data management, reduces errors, and supports accurate
data handling compared to traditional file processing systems.

b) A reputed general hospital has decided to computerize

their operations. In the hospital many doctors are
working. Personal information of doctors is maintained to
get them fixed salary per month. The patients are
admitted to the hospital into the room. They are treated
by various doctors. Sometimes patients perform certain
pathological tests which carried out into the labs. Identify
the relationship among the entities along with the
mapping cardinalities, keys in the E.R. diagram. Construct
appropriate tables for E-R diagram designed with above
requirements. [7]

LAB

1 LabID (PK)

LabName

Location

Entity Identification and Attributes

From the E-R diagram, the hospital database system contains five main entities:

DOCTOR
• DoctorID (Primary Key)

• Name
 SPPU_TE_BE_COMP Click to join telegram
• Specialization

• Salary PATIENT

• PatientID (Primary Key)

• Name

• Age

• Gender

• RoomNumber (Foreign Key referencing ROOM)

ROOM
• RoomNumber (Primary Key)

• RoomType

• Capacity TEST
• TestID (Primary Key)

• TestName

• Cost

• LabID (Foreign Key referencing LAB) LAB

• LabID (Primary Key)

• LabName

• Location

Relationship Analysis with Mapping Cardinalities

Relationship Entities Cardinality Description
Doctor ↔ A doctor can treat multiple patients; a patient
Many-to-Many
TREATS can be treated by multiple doctors
(M:N)
Patient
Multiple patients can be admitted to one room;
ADMITTED_TO Patient → Room Many-to-One (N:1) each patient is admitted to only one room

Many-to-Many A patient can undergo multiple tests; a test can

UNDERGOES Patient ↔ Test be performed on multiple patients
(M:N)
Many-to-One (N:1) Multiple tests can be conducted in one lab;
CONDUCTED_IN Test → Lab each test is conducted in only one lab

Database Table Implementation

1. DOCTOR Table
Column Name Data Type Constraints
 SPPU_TE_BE_COMP Click to join telegram
DoctorID INT PRIMARY KEY

Name VARCHAR(50) NOT NULL

Column Name Data Type Constraints

Specialization VARCHAR(50) NOT NULL

Salary DECIMAL(10,2) NOT NULL

2. ROOM Table

Column Name Data Type Constraints

RoomNumber INT PRIMARY KEY

RoomType VARCHAR(50) NOT NULL

Capacity 3. INT NOT NULL

LAB Table

Column Name Data Type Constraints

LabID INT PRIMARY KEY

LabName VARCHAR(50) NOT NULL

Location VARCHAR(100) NOT NULL

4. PATIENT Table

Column Name Data Type Constraints

PatientID INT PRIMARY KEY

Name VARCHAR(50) NOT NULL

Age INT NOT NULL

Gender VARCHAR(10) NOT NULL

RoomNumber INT FOREIGN KEY REFERENCES

ROOM(RoomNumber)
5. TEST Table

Column Name Data Type Constraints

TestID INT PRIMARY KEY

TestName VARCHAR(50) NOT NULL
Cost DECIMAL(10,2) NOT NULL
LabID INT FOREIGN KEY REFERENCES LAB(LabID)
6. TREATS Table (Junction Table for Many-to-Many)
 SPPU_TE_BE_COMP Click to join telegram
Column Name Data Type Constraints
DoctorID INT FOREIGN KEY REFERENCES DOCTOR(DoctorID) Column Name Data Type
Constraints
PatientID INT FOREIGN KEY REFERENCES PATIENT(PatientID)
- - PRIMARY KEY (DoctorID, PatientID)

7. UNDERGOES Table (Junction Table for Many-to-Many)

Column Name Data Type Constraints

PatientID INT FOREIGN KEY REFERENCES PATIENT(PatientID)
TestID INT FOREIGN KEY REFERENCES TEST(TestID)
- - PRIMARY KEY (PatientID, TestID)

Key Design Principles

Primary Keys: Each entity has a unique identifier (DoctorID, PatientID, RoomNumber, TestID, LabID)
Foreign Keys: Used to maintain referential integrity:

• Patient.RoomNumber → Room.RoomNumber

• Test.LabID → Lab.LabID

Junction Tables: Created for Many-to-Many relationships (TREATS and UNDERGOES) with composite primary
keys

c) A weak entity set can always be made into a strong

entity set; by adding to its attributes the primary key
attributes of its identifying entity set. Outline what sort of
redundancy will result if we do so while converting to
tables. [4]
1. Context:

o A weak entity lacks a primary key and depends on an identifying strong entity.

o To convert it into a strong entity, the primary key attributes of the identifying strong entity are
added to the weak entity’s attributes.

2. Resulting Redundancy:
 SPPU_TE_BE_COMP Click to join telegram
o Redundant data storage of the identifying strong entity’s primary key occurs in the weak entity’s
table.

o The identifying key is duplicated in both the strong entity’s table and the converted weak entity’s
table, causing repetition of data.

3. Implications:

o Increased storage space and potential update anomalies if the identifying key changes.

o Violates normalization principles by introducing unnecessary duplication.

o Loss of the semantic meaning of dependency that the weak entity originally represented.

4. Summary:

o Converting a weak entity set to a strong entity set by adding identifying keys results in redundancy
through duplicated key attributes, causing data duplication and potential integrity risks in the
database schema.

Q2) a) What is Specialization and generalization in

Extended ER diagram? What is the difference between
them? Why do we not display the difference in schema
diagram? [7]
1. Definition of Specialization:

o Specialization is the top-down process of creating new sub-entity sets from an existing broader
entity set based on distinguishing characteristics. o It divides a higher-level entity into lower-level

entities (subclasses) that inherit attributes and relationships from the higher-level entity
(superclass).

o Example: An Employee entity specialized into Manager, Developer, and Tester.

2. Definition of Generalization:

o Generalization is the bottom-up process of combining multiple lower-level entities that share
common attributes into a higher-level generalized entity.
 SPPU_TE_BE_COMP Click to join telegram
o It abstracts common features into a superclass to simplify the schema by hiding differences among
entities.

o Example: Student and Faculty entities generalized into a common Person entity.

3. Key Differences:
Specialization Generalization

Top-down approach (from general to specific) Bottom-up approach (from specific to general)

Starts with a higher-level entity and creates sub-entities Combines multiple entities into a generalized higher-level entity

Emphasizes differences among entities Emphasizes similarities/commonalities

Subclasses inherit attributes from superclass Superclass abstracts attributes from subclasses

Example: Employee → Manager, Developer Example: Student + Faculty → Person

Used to model focused, detailed distinctions Used to reduce redundancy and simplify schema

4. Reason for Not Displaying Difference in Schema Diagram:

o The diagrams denote the hierarchical relation (ISA) rather than explicitly illustrating top-down or
bottom-up processes.

o Both generalization and specialization result in the same ER structure with superclass-subclass
relationships.

o Displaying the conceptual difference is unnecessary as the EER notation captures the essence of
inheritance and relationship regardless of direction.

o It keeps the schema simple, unambiguous, and focused on structural design rather than procedural
steps.

5. Conclusion:

o Specialization and generalization are dual abstraction techniques in Extended ER diagrams to

handle complexity by organizing entities hierarchically, improving schema clarity and flexibility
without redundant visual distinctions in diagrams.
 SPPU_TE_BE_COMP Click to join telegram
b) Draw architecture of DBMS system and explain function
of following components: [8]
i) Storage
manager ii) Query
Processor

1. Diagram:

o Standard DBMS Architecture includes:

 SPPU_TE_BE_COMP Click to join telegram
▪ Users and application programs

▪ Query Processor

▪ Storage Manager

▪ Transaction Manager

▪ Buffer Manager

▪ File Manager

▪ Authorization & Integrity Manager

▪ Database (Data Storage)

▪ Data Dictionary

2. Storage Manager:

o Definition: Central DBMS component managing data storage on disk.

o Functions:

▪ Allocates, retrieves, updates data blocks on physical storage.

▪ Maintains data consistency and integrity during transactions.

▪ Handles file structure, space management, indexes.

▪ Includes buffer manager (caching data in RAM), file manager (disk blocks handling),
transaction manager (ACID properties), authorization/integrity manager (security &
constraints).

3. Query Processor:

o Definition: DBMS module responsible for interpreting and executing queries.

o Functions:

▪ Parses and validates user SQL queries.

▪ Translates queries into low-level instructions for execution.

▪ Optimizes query execution (join order, index usage).

▪ Executes query plan for results.

▪ Includes DML compiler (data manipulation), DDL interpreter (schema management), query
optimizer (efficiency).

4. Component Interaction:
 SPPU_TE_BE_COMP Click to join telegram
o Query processor works with storage manager to retrieve/update data efficiently.

o Storage manager ensures safe, secure storage and fast access.

5. Summary Table: Functions

Component Functions

Storage Manager Data allocation, retrieval, update, integrity, transaction, indexing

Query Processor Query parsing, translation, optimization, execution

6. Conclusion:

o The Storage Manager provides robust, consistent physical data handling; Query Processor ensures
efficient, accurate query processing, together forming the backbone of a DBMS system for reliable
data management.

Q3) a) Consider following schema [4]

Student_fee_details (rollno, name,
fee_deposited, date)
Write a trigger to preserve old values of student fee details
before updating in the table.
1. Purpose:

o To automatically preserve old data before any update occurs, ensuring auditability and integrity.

2. Audit Table Creation:

o Required for storing old values:

CREATE TABLE student_fee_details_audit (

audit_id INT GENERATED ALWAYS AS IDENTITY PRIMARY
KEY, rollno INT, name VARCHAR(100),
fee_deposited DECIMAL(10,2), date DATE, changed_at
TIMESTAMP, operation VARCHAR(10) );

3. Trigger Creation (MySQL/Oracle style):

 SPPU_TE_BE_COMP Click to join telegram
o BEFORE UPDATE row-level trigger:

CREATE TRIGGER trg_fee_details_before_update

BEFORE UPDATE ON student_fee_details
FOR EACH ROW
BEGIN
INSERT INTO student_fee_details_audit
(rollno, name, fee_deposited, date, changed_at, operation)
VALUES
(OLD.rollno, OLD.name, OLD.fee_deposited, OLD.date, CURRENT_TIMESTAMP, 'UPDATE');
END;

4. Explanation:

o Before any update, the trigger copies the old row to the audit table, capturing all historical changes
for each record.

b) What is view and how to create it? Can you update

view? If yes, how? If not, why not? [6]
Unit: DBMS Fundamentals → Views in DBMS (6 marks = 12 points)

1. Definition of View:

o A view is a virtual table based on the result-set of an SQL query. It does not store data physically
but dynamically displays data from one or more underlying tables.

2. How to Create a View:

o Using SQL statement:

CREATE VIEW view_name AS

SELECT column1, column2, .--
FROM table_name
WHERE condition;

o Example:

CREATE VIEW Indian_Customers AS

SELECT CustomerID, CustomerName FROM Customers WHERE Country = 'India';

3. Can You Update a View?

o Yes, a view can be updated if:

 SPPU_TE_BE_COMP Click to join telegram
▪ It is based on a single base table.

▪ It does not contain aggregates, GROUP BY, DISTINCT, or joins.

▪ The update operation can be translated unambiguously to underlying base

table changes.

4. How to Update a View:

o Use usual data manipulation statements (INSERT, UPDATE, DELETE) on the view as if it were a
table; changes are reflected in the underlying base table.

o Example:

UPDATE Indian_Customers SET CustomerName = 'Ravi' WHERE CustomerID = 101;

5. When is a View Not Updatable and Why:

o Views with multiple tables, aggregate functions, groupings, or computed columns are not
updatable because DBMS cannot map changes to the correct underlying base table rows.

o Such views act as read-only representations.

6. Summary:

o Views provide abstraction, simplify complex queries, enhance security, and may support updates
under constraints.

o Not all views support data modification due to their complexity and ambiguity.

c) Consider following schema [5]

Hotels(hotel_no,hotel_name.city)
Rooms(Room_no,hotel_no,price,type)
Write a PL/SQL procedure to list the price & type of all
rooms at the hotel ‘TAJ’
1. Assumption:
 SPPU_TE_BE_COMP Click to join telegram
o Procedure uses IN parameter for hotel name; displays price and type for matching hotel.

2. PL/SQL Procedure:

CREATE OR REPLACE PROCEDURE List_Rooms_Taj

IS
CURSOR c_rooms IS
SELECT r.price, r.type
FROM Rooms r
JOIN Hotels h ON r.hotel_no = h.hotel_no
WHERE h.hotel_name = 'TAJ';
v_price Rooms.price%TYPE;
v_type Rooms.type%TYPE;
BEGIN
OPEN c_rooms;
LOOP
FETCH c_rooms INTO v_price, v_type;
EXIT WHEN c_rooms%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Price: ' |- v_price |- ', Type: ' |- v_type);
END LOOP;
CLOSE c_rooms;
END;

3. Explanation:

o The procedure uses a cursor to retrieve price and type for all rooms at hotel ‘TAJ’, printing each
using
DBMS_OUTPUT.

4. Usage:

o Run EXEC List_Rooms_Taj; to display results.

5. Compliance:

o Syntax, logic, and interface strictly follow SPPU DBMS/PLSQL syllabus for 5-mark procedure.

Q4) a) Consider the following relation schema [6]

MOVIES(Mov_Id, Mov_Title,Mov_Year, Dir_Id)
DIRECTOR(Dir_Id, Dir_Name)
RATING(MOV_Id, Rev_Stars)

Write the SQL queries for the following.

 SPPU_TE_BE_COMP Click to join telegram
i) List the title of all the movies directed by ‘RAJ KAPOOR’
ii) Find the name of movies and number of stars for each
movie. Sort the results on movies title and from higher
stars to least stars.

iii) Assign the rating of all movies directed by ‘Steven

Spielberg’ to 9.
Unit: SQL Queries on MOVIES/DIRECTOR/RATING Schema (6 marks = 12 points)

1. i) List the title of all the movies directed by ‘RAJ KAPOOR’:

o SQL:

SELECT m.Mov_Title
FROM MOVIES m
JOIN DIRECTOR d ON m.Dir_Id = d.Dir_Id
WHERE d.Dir_Name = 'RAJ KAPOOR';

2. ii) Find the name of movies and number of stars for each movie. Sort results on movie title and from
higher stars to least stars:

o SQL:

SELECT m.Mov_Title, r.Rev_Stars

FROM MOVIES m
JOIN RATING r ON m.Mov_Id = r.MOV_Id
ORDER BY m.Mov_Title ASC, r.Rev_Stars DESC;

3. iii) Assign the rating of all movies directed by ‘Steven Spielberg’ to 9:

o SQL:

UPDATE RATING
SET Rev_Stars = 9
WHERE MOV_Id IN (
SELECT m.Mov_Id
FROM MOVIES m
JOIN DIRECTOR d ON m.Dir_Id = d.Dir_Id
WHERE d.Dir_Name = 'Steven Spielberg'
);
 SPPU_TE_BE_COMP Click to join telegram
4. SQL queries use correct joins, filtering, and ordering as per the schema.

5. All relationships and attributes strictly follow the given schema and SPPU 2019 pattern requirements.

6. Queries directly target required output and marking scheme.

b) What is the importance of creating constraints on the

table? Explain with example any 4 constraints that can be
specified when a database table is created.
1. Importance of Creating Constraints:

o Constraints enforce data integrity, accuracy, and consistency within the database automatically.
They prevent invalid data entry, avoid duplications, maintain valid relationships, and ensure
business rules are upheld at the database level.

o They reduce application-level complexity by handling validations centrally and improve overall data
reliability and system robustness.

2. Primary Key Constraint:

o Ensures unique identification of each row and disallows NULL in the key column(s).

o Example:

CREATE TABLE Employee (

Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50)
);

3. Foreign Key Constraint:

o Maintains referential integrity by ensuring foreign key values in one table exist as primary keys in
another.

o Example:

CREATE TABLE Orders (

Order_ID INT PRIMARY KEY,
Cust_ID INT,
FOREIGN KEY (Cust_ID) REFERENCES Customers(Cust_ID)
);

4. Not Null Constraint:

o Prevents NULL values in specified columns, ensuring mandatory data entry.

 SPPU_TE_BE_COMP Click to join telegram
o Example:

CREATE TABLE Employee (

Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50) NOT NULL
);

5. Check Constraint:

o Validates that values in a column satisfy a specified condition.

o Example:

CREATE TABLE Products (

Prod_ID INT PRIMARY KEY,
Price DECIMAL,
CHECK (Price > 0)
);

6. Summary Table:
Constraint Type Purpose Example

Primary Key Unique and non-null row identification Emp_ID INT PRIMARY KEY

Foreign Key Referential integrity between tables FOREIGN KEY (Cust_ID) REFERENCES
Customers

Not Null Ensures mandatory column values Emp_Name VARCHAR(50) NOT NULL

Check Enforces domain-specific validation rule CHECK (Price > 0)

7. Conclusion:

o Creating constraints ensures database consistency, enforces business logic, and prevents invalid
data entries, thereby maintaining data quality and reliability as mandated by the SPPU syllabus.

b) What is the importance of creating constraints on the

table? Explain with example any 4 constraints that can be
specified when a database table is created.
1. Importance of Creating Constraints:
 SPPU_TE_BE_COMP Click to join telegram
o Constraints ensure data integrity, accuracy, and consistency by enforcing rules on the data
entered in the database tables.

o They prevent invalid, inconsistent, or duplicate data from being stored, safeguarding the reliability
of the database.

o Constraints automate data validation at the database level, reducing errors and ensuring business
rules are upheld irrespective of application code.

2. Primary Key Constraint:

o Uniquely identifies each row in a table and does not allow NULL values.

o Example:

CREATE TABLE Employee (

Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50)
);

3. Foreign Key Constraint:

o Maintains referential integrity by ensuring foreign key values in child tables correspond to primary
key values in parent tables or are NULL.

o Example:

CREATE TABLE Orders (

Order_ID INT PRIMARY KEY,
Cust_ID INT,
FOREIGN KEY (Cust_ID) REFERENCES Customers(Cust_ID)
);

4. Not Null Constraint:

o Ensures that a column cannot contain NULL values (mandatory field).

o Example:

CREATE TABLE Employee (

Emp_ID INT PRIMARY KEY,
Emp_Name VARCHAR(50) NOT NULL
);

5. Check Constraint:
 SPPU_TE_BE_COMP Click to join telegram
o Enforces domain integrity by restricting the values accepted in a column through a specified
condition.

o Example:

CREATE TABLE Products (

Prod_ID INT PRIMARY KEY,
Price DECIMAL,
CHECK (Price > 0)
);

6. Summary Table:
Constraint Type Purpose Example

Primary Key Unique, non-null row identifier Emp_ID INT PRIMARY KEY

Foreign Key Referential integrity between tables FOREIGN KEY (Cust_ID)

Not Null Prevents NULL values in a column Emp_Name VARCHAR(50) NOT NULL

Check Ensures values satisfy condition CHECK (Price > 0)

7. Conclusion:

o Defining constraints is essential to enforce data reliability, maintain business rules, and reduce
errors, thus playing a vital role in robust database design as per the SPPU syllabus.