Part 2:
DBMS AND file system:
Here’s a clear and structured table presenting the advantages of a Database Management
System (DBMS) over a traditional file system:
Advantage DBMS File System
Minimizes redundancy through
Data High redundancy due to duplicate
centralized storage and
Redundancy data across multiple files.
normalization.
Enforces rules and constraints to
No built-in mechanisms to ensure
Data Integrity maintain data accuracy and
data integrity.
consistency.
Allows secure and simultaneous Difficult to share data; concurrent
Data Sharing
access by multiple users. access may cause inconsistencies.
Query Supports powerful query languages Limited querying; often requires
Capabilities like SQL for complex data retrieval. custom programs for data access.
Provides granular access control and Minimal security features; lacks
Security
user authentication. user-level access control.
Backup and Includes automated backup and No built-in recovery; manual
Recovery crash recovery mechanisms. backups required.
Data Schema changes don’t affect Changes in data structure require
Independence application programs. changes in application logic.
Decision Facilitates better decision-making Inconsistent data may hinder
Support with consistent and timely data. effective decision-making.
DISADVANTAGES:
Higher Cost
• DBMS solutions require significant investment in terms of hardware, software
licenses, and maintenance.
• Example: A large enterprise may afford Oracle or SQL Server, but a small business
might prefer using spreadsheets or Microsoft Access due to budget constraints.
Complexity
• DBMS software is complex to install, configure, and manage.
• Example: While a small business owner can manage simple text files, an e-commerce
platform needs a dedicated DBA to handle user permissions, backups, and
performance tuning.
Need for Skilled Professionals
• Operating a DBMS requires trained personnel for tasks like database design,
optimization, and administration.
• Lack of skilled staff can hinder effective DBMS utilization, especially in small
organizations.
Performance Overhead
• For simple, small-scale applications, the overhead of a DBMS (due to features like
security, concurrency, and recovery) may slow down performance compared to
lightweight file system.
Initial Learning Curve
• Learning to use a DBMS and query languages like SQL requires time and training,
which may not be feasible for all users.
2) Three schema archietecture: from ans for rdbms(19)
3) ER diagram from ans for rdb(25)
4) Relational Algebra:
Here’s a complete and structured 16-mark answer on Relational Algebra and
Relational Calculus in DBMS, including definitions, differences, and the eight core
relational algebra operations with examples:
Relational Algebra and Relational Calculus in DBMS
Introduction
Relational Algebra and Relational Calculus are two formal query languages used in
relational database management systems (DBMS). They provide a theoretical
foundation for SQL and help in understanding how queries are processed internally.
Relational Algebra
Relational Algebra is a procedural query language that specifies a sequence of
operations to retrieve data from relations (tables). It uses mathematical operations
to manipulate data and produce new relations. It is domain-independent and forms
the basis for query optimization.
Core Operations in Relational Algebra
1. Selection (σ)
Selects rows that satisfy a given condition.
Example: To find employees in the 'Sales' department:
\sigma_{Department='Sales'}(Employee)
2. Projection (π)
Selects specific columns and removes duplicates.
Example: To list all unique departments:
\pi_{Department}(Employee)
3. Union (∪)
Combines tuples from two relations, removing duplicates.
Example:
\pi_{Name}(\sigma_{Department='Sales'}(Employee)) \cup
\pi_{Name}(\sigma_{Department='Marketing'}(Employee))
4. Set Difference (−)
Returns tuples in one relation but not in another.
Example:
\pi_{Name}(\sigma_{Department='Sales'}(Employee)) -
\pi_{Name}(\sigma_{Department='HR'}(Employee))
5. Cartesian Product (×)
Combines every tuple of one relation with every tuple of another.
Example:
Employee \times Department
6. Join (⨝)
Combines tuples from two relations based on a common attribute.
Example:
Employee \bowtie_{[Link] = [Link]} Department
7. Rename (ρ)
Renames a relation or its attributes.
Example:
\rho_{MonthlySalary}(Salary)(\pi_{Salary}(Employee))
8. Division (÷)
Finds tuples in one relation associated with all tuples in another.
Example:
Employee(Name, Department) \div AllDepartments
Differences Between Relational Algebra and Relational Calculus
Feature Relational Algebra Relational Calculus
Procedural (how to Declarative (what data to
Type
retrieve data) retrieve)
Uses operations like σ, π, Uses logical predicates and
Approach
∪, ⨝ quantifiers
Better for expressing
Ease of Use Easier for simple queries
complex queries
Suitable for query Less intuitive for
Optimization
optimization optimization
5) Normalisation and types: in ans for rdb(15)
6) Keys:
Types of Keys in RDBMS
Relational Database Management Systems (RDBMS) use keys to uniquely identify
records and establish relationships between tables. Keys ensure data integrity,
consistency, and accuracy.
1. Super Key
• Definition: A set of one or more attributes that can uniquely identify a row in a table.
• Characteristics:
o May include redundant attributes.
o Every table has at least one super key.
• Example: In a Student table with Student_ID, Name, and Email:
• Student_ID is a super key.
• (Student_ID, Name) and (Student_ID, Name, Email) are also super keys.
2. Candidate Key
• Definition: A minimal super key with no redundant attributes.
• Characteristics:
o Can be chosen as a primary key.
o Uniquely identifies each record.
• Example: In a Student table:
• Student_ID and Email are candidate keys.
3. Primary Key
• Definition: A candidate key selected to uniquely identify records in a table.
• Characteristics:
o Must be unique.
o Cannot contain NULL values.
• Example: If Student_ID is chosen, it becomes the primary key of the Student table.
4. Alternate Key
• Definition: A candidate key not chosen as the primary key.
• Characteristics:
o Still uniquely identifies records.
o Can be used for indexing or constraints.
• Example: If Student_ID is the primary key, then Email is an alternate key.
5. Composite Key
• Definition: A key formed by combining two or more columns to uniquely identify a
record.
• Characteristics:
o Individual columns may not be unique.
o The combination ensures uniqueness.
• Example: In a Course_Enrollment table:
• (Course_ID, Student_ID) is a composite key.
6. Foreign Key
• Definition: A column in one table that refers to the primary key in another table.
• Characteristics:
o Establishes relationships between tables.
o Enforces referential integrity.
• Example: In an Enrollment table:
• Student_ID is a foreign key referencing Student_ID in the Student table.
Referential Integrity
Referential integrity ensures that relationships between tables remain consistent.
• Consistency: Foreign key values must match existing primary key values.
• Accuracy: Prevents orphan records and broken links.
• Integrity: Ensures valid data entry and reliable relationships.
Summary Table
Null
Key Type Definition Unique Example
Allowed
Any set of
attributes
Student_ID,
that
Super Key (Student_ID,
uniquely
Name)
identify a
row
Candidate Minimal Student_ID,
Key super key Email
Chosen
Primary
candidate Student_ID
Key
key
Candidate
Alternate key not
Email
Key chosen as
primary key
Composite Combination (Course_ID,
Key of attributes Student_ID)
Null
Key Type Definition Unique Example
Allowed
for
uniqueness
References
Student_ID
Foreign primary key
in
Key in another
Enrollment
table
7) View:
SQL Views – Definition, Types, Creation & Advantages
What is a View in SQL?
A View in SQL is a virtual table created from the result of a SELECT query. It contains
rows and columns like a real table but does not store data physically (except for
materialized views). Instead, it dynamically generates data when accessed.
Views simplify complex queries, enhance security, and provide abstraction from the
underlying database schema.
Types of Views
1. Simple View
• Based on: A single table.
• Restrictions: No aggregate functions (SUM, AVG, etc.), GROUP BY, or DISTINCT.
• Data Modification: Allowed if all NOT NULL columns are included.
• Example:
CREATE VIEW IT_Employees_Contact AS SELECT Name, Email
FROM Employees WHERE Department = 'IT';
2. Complex View
• Based on: Multiple tables using JOIN, or includes aggregates, GROUP BY, ORDER BY,
or computations.
• Data Modification: Generally not allowed due to ambiguity in updating base tables.
3. Materialized View
• Storage: Physically stores the result set on disk.
• Use Case: Improves performance for complex, frequently executed queries.
• Refresh: Must be refreshed periodically to reflect base table changes.
Creation of Views
Syntax:
CREATE VIEW view_name AS SELECT column1, column2, ...
FROM table1 JOIN table2 ON condition WHERE condition;
• view_name: Name of the virtual table.
• The SELECT query defines the structure and data of the view.
Advantages of Using Views
1. Simplification of Complex Queries
• Hides JOINs, subqueries, and calculations.
• Makes data access easier for users and applications.
2. Code Reusability
• Reuse the same query logic across multiple applications.
• Ensures consistency and reduces redundancy.
3. Logical Data Independence
• Changes in base tables (e.g., adding columns) don’t affect views if definitions remain
valid.
4. Improved Performance
• Materialized views store precomputed results, speeding up access to aggregated or
joined data.
Data Security and Abstraction
Data Security
• Column-level: Hide sensitive columns (e.g., salary, passwords).
• Row-level: Restrict access using WHERE clauses (e.g., region-specific data).
• Access Control: Grant permissions on views without exposing base tables.
Abstraction
• Views hide the complexity of the database schema.
• Users interact with a simplified, customized representation of data.
8) Joins:
SQL JOINs – Types, Syntax & Examples
Definition
SQL JOINs are used to combine rows from two or more tables based on a related
column between them. They are essential for querying data from normalized
relational databases where data is split across multiple tables.
Types of SQL JOINs
Join Type Description
INNER JOIN Returns only rows with matching values in both tables.
LEFT (OUTER) Returns all rows from the left table and matched rows from
JOIN the right table. NULL for unmatched right rows.
RIGHT Returns all rows from the right table and matched rows from
(OUTER) JOIN the left table. NULL for unmatched left rows.
FULL (OUTER) Returns all rows from both tables. NULLs are filled where no
JOIN match exists.
1. INNER JOIN
• Purpose: Retrieves only the records that have matching values in both tables.
• Use Case: Find employees who are assigned to a department.
• Syntax:
SELECT [Link], [Link] FROM
Employees INNER JOIN Departments ON
[Link] = [Link];
• Result: Only employees with a valid DepartmentID in both tables are shown.
2. LEFT (OUTER) JOIN
• Purpose: Returns all records from the left table and matched records from the right
table.
• Use Case: List all employees, including those not assigned to any department.
• Syntax:
SELECT [Link], [Link] FROM
Employees LEFT JOIN Departments ON [Link]
= [Link];
• Result: Employees without a department will have NULL in DepartmentName.
3. RIGHT (OUTER) JOIN
• Purpose: Returns all records from the right table and matched records from the left
table.
• Use Case: List all departments, including those with no employees.
• Syntax:
SELECT [Link], [Link] FROM
Employees RIGHT JOIN Departments ON
[Link] = [Link];
• Result: Departments without employees will show NULL in Name.
4. FULL (OUTER) JOIN
• Purpose: Combines results of both LEFT and RIGHT JOINs.
• Use Case: Show all employees and departments, even if there’s no match.
• Syntax:
SELECT [Link], [Link] FROM
Employees FULL OUTER JOIN Departments ON
[Link] = [Link];
• Result: Includes all employees and departments, with NULLs where no match exists.
Summary Table
Includes Only
Includes
Join Type Unmatched Matching
Unmatched Left
Right Rows
INNER JOIN
LEFT
OUTER
JOIN
Includes Only
Includes
Join Type Unmatched Matching
Unmatched Left
Right Rows
RIGHT
OUTER
JOIN
FULL
OUTER
JOIN
9) Stored procedure,trigger,cursor:
Here's a comprehensive and well-structured answer on Stored Procedures, Cursors,
and Triggers in SQL, tailored for a 16-mark question:
Stored Procedures, Cursors, and Triggers in SQL
Relational Database Management Systems (RDBMS) provide powerful tools like
stored procedures, cursors, and triggers to implement business logic, automate
tasks, and manage data efficiently. Each serves a distinct purpose and operates
differently.
1. Stored Procedures
Definition:
A stored procedure is a precompiled collection of SQL statements and control-of-
flow logic (e.g., IF, WHILE) stored in the database. It is explicitly invoked using EXEC or
EXECUTE.
Need in DBMS:
• Performance Optimization: Precompiled and cached execution plans reduce
overhead.
• Security: Users can execute procedures without direct access to base tables.
• Modularity & Reusability: Centralized logic can be reused across applications.
• Reduced Network Traffic: Multiple SQL operations are bundled into a single call.
Example:
DELIMITER //
CREATE PROCEDURE GetCustomersByCountry(IN p_Country
varchar(50))
BEGIN
SELECT CustomerName,ContactName
from Customers
where Country=p_Country;
END//
DELIMITER ;
CALL GetCustomersByCountry('Sri lanka');
2. Cursors
Definition:
A cursor is a database object that allows row-by-row processing of a result set,
offering fine-grained control over data traversal.
Need in DBMS:
• Row-by-Row Operations: Useful when logic must be applied to each row individually.
• Fine-Grained Control: Enables complex procedural logic within loops.
Example:
DELIMITER //
CREATE PROCEDURE CusBackup(INOUT customerDetails_backup varchar(400))
BEGIN
declare c_id int;
declare cus_name varchar(50);
declare cus_add varchar(50);
declare cuslist varchar(100);
declare c1 cursor for Select *from CustomerDetails;
declare continue handler for not found set @done=1;
set @done=0;
open c1;
label:loop
fetch c1 into c_id,cus_name,cus_add;
if @done=1 then
leave label;
end if;
set cuslist:=concat(c_id,' ',cus_name,' ',cus_add,'');
set customerDetails_backup=concat(customerDetails_backup,cuslist);
#set customerDetails_backup=concat(customerDetails_backup,'\n');
end loop label;
close c1;
END//
DELIMITER ;
SET @customerDetails_backup="";
call CusBackup( @customerDetails_backup);
select @customerDetails_backup;
3. Triggers
Definition:
A trigger is a special type of stored procedure that automatically executes in
response to specific events (INSERT, UPDATE, DELETE) on a table or view.
Need in DBMS:
• Data Integrity: Enforces complex business rules beyond standard constraints.
• Auditing: Automatically logs changes to sensitive data.
• Automation: Updates related tables or derives values upon data changes.
Example:
DELIMITER //
CREATE TRIGGER after_update_patient_food_allergies
after update ON patient_food_allergies
for each row
BEGIN
if old.patient_food_allergy <> new.patient_food_allergy
then
insert into patient_allergy_modification_history
(patient_id,patient_food_allergy,patient_food_allergy_cha
nged)
values(new.patient_id,old.patient_food_allergy,[Link]
t_food_allergy);
END IF;
END //
DELIMITER ;
Comparison Table
Stored
Feature Cursor Trigger
Procedure
Used within Implicit (fires
Invocation Explicit (EXEC) procedural on DML/DDL
code events)
Accepts
No Cannot accept
Parameters input/output
parameters parameters
parameters
Can return
Cannot return
Return Value values or result N/A
values
sets
Full support
Can be used Limited
Transaction (BEGIN,
within transaction
Control COMMIT,
transactions control
ROLLBACK)
Automation,
Performance, Row-level
Main real-time
security, control for
Advantage enforcement,
reusability complex logic
auditing
Limited Performance Hard to debug,
Main portability, less overhead, may affect
Disadvantage flexible than resource- performance if
app code intensive overused