6. Database Systems — Computer Science | CSS Solved Past Papers

FPSC CSS Computer Science past papers (2016–2026) — 34 questions: 6 MCQs, 28 subjective parts. Newest first.

Part A — Subjective Questions

2025 · Paper-II · Q6(a) (10 marks)

Question

Answer the questions i, ii, according to given schema (standard HR schema: REGIONS, COUNTRIES, LOCATIONS, DEPARTMENTS, JOBS, EMPLOYEES, JOB_HISTORY). (i) Display the length of first name and length of second name for employees where last name contains character 'b' after 3rd position. (2) (ii) Display job title, department name, employee last name, starting date for all jobs from 1992 to 1998. (3) (iii) Differentiate between Left outer join, Right outer join and Full outer join. Explain your answer with the help of Venn Diagram. (5)

Solution

(i)

SELECT LENGTH(FIRST_NAME) AS FIRST_NAME_LEN,
       LENGTH(LAST_NAME)  AS LAST_NAME_LEN
FROM EMPLOYEES
WHERE INSTR(LAST_NAME, 'b', 4) > 0;

INSTR(LAST_NAME,'b',4) searches for 'b' starting from the 4th character (i.e., strictly after the 3rd position); if found, it returns a position > 0, satisfying the condition. (Equivalently: SUBSTR(LAST_NAME,4) LIKE '%b%'.)

(ii)

SELECT J.JOB_TITLE, D.DEPARTMENT_NAME, E.LAST_NAME, JH.START_DATE
FROM JOB_HISTORY JH
JOIN JOBS J        ON JH.JOB_ID = J.JOB_ID
JOIN DEPARTMENTS D ON JH.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN EMPLOYEES E   ON JH.EMPLOYEE_ID = E.EMPLOYEE_ID
WHERE EXTRACT(YEAR FROM JH.START_DATE) BETWEEN 1992 AND 1998;

(iii) Consider two tables A and B being joined:

LEFT OUTER JOIN: returns ALL rows from the left table (A), matched with rows from B where a match exists; unmatched left rows get NULLs for B's columns. Venn diagram: the entire circle A, plus only the overlapping (intersection) portion with B.
RIGHT OUTER JOIN: the mirror image — returns ALL rows from the right table (B), matched with A where possible, NULLs for A's columns where no match. Venn diagram: the entire circle B, plus only the intersection with A.
FULL OUTER JOIN: returns ALL rows from BOTH A and B — matched rows combined where they match, and unmatched rows from either side padded with NULLs on the other side's columns. Venn diagram: the union of the entire circle A and the entire circle B (both circles fully shaded). In short: LEFT keeps everything from the left side, RIGHT keeps everything from the right side, and FULL keeps everything from both sides, with INNER JOIN (just the overlapping intersection) as the common base all three build upon.

2025 · Paper-II · Q6(b) (10 marks)

Question

Provide brief answers to the following questions: (i) Differentiate between Single row Sub-Query and Multi row Sub-Query and write a sample query too. (5) (ii) Discuss the role of Primary Keys, foreign keys, and indexes in database schema. Also, explain their significance in ensuring data accuracy, enforcing referential integrity and improving query performance. (5)

Solution

(i) A single-row subquery returns exactly one row (one value) and must be used with single-row comparison operators (=, <, >, <=, >=, <>). A multi-row subquery returns more than one row and must be used with multi-row operators (IN, ANY, ALL, EXISTS). Single-row example:

SELECT LAST_NAME FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES);

Multi-row example:

SELECT LAST_NAME FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOCATION_ID = 1700);

Using = with a subquery that unexpectedly returns multiple rows causes a runtime error, which is the key practical reason to distinguish the two.

(ii) A Primary Key uniquely identifies each row in a table and cannot be NULL or duplicated; it is the anchor that every other reference to a row relies on, directly ensuring entity integrity/data accuracy (no two rows can be confused with each other). A Foreign Key is a column (or set of columns) in one table that references the primary key of another table; the DBMS enforces that any foreign key value must either be NULL or match an existing primary-key value in the referenced table, which enforces referential integrity — preventing orphaned records (e.g., an order pointing to a customer that doesn't exist) and preventing deletion/update anomalies that would break such links. An Index is an auxiliary data structure (commonly a B-tree or hash structure) built on one or more columns to speed up data retrieval; instead of scanning every row (a full table scan), the DBMS can use the index to jump almost directly to matching rows, dramatically improving the performance of SELECT queries with WHERE/JOIN/ORDER BY clauses on indexed columns — at some cost to INSERT/UPDATE/DELETE speed since indexes must also be maintained. Together, these three mechanisms are central to a well-designed relational schema: primary keys guarantee accurate row identity, foreign keys guarantee accurate relationships between tables, and indexes ensure that these guarantees can be checked and queried efficiently even as data volume grows.

2024 · Paper-II · Q6(a) (7 marks)

Question

Elaborate on the evolution of database systems, highlighting major milestones. Discuss the impact of emerging technologies on the field of database systems.

Solution

1960s: hierarchical and network database models (e.g., IBM's IMS) organized data as tree/graph structures with rigid navigational access. 1970s: E. F. Codd introduced the relational model, leading to the development of SQL and relational database management systems (RDBMS) like System R and Oracle, which became the dominant paradigm due to their mathematical foundation (set theory, normalization) and declarative query language. 1980s-1990s: object-oriented and object-relational databases emerged to better handle complex data types used in engineering/multimedia applications. 2000s: the rise of the web and big data drove the NoSQL movement - key-value stores, document databases (MongoDB), column-family stores (Cassandra), and graph databases (Neo4j) - trading strict relational consistency for horizontal scalability and flexible schemas. 2010s onward: NewSQL databases attempted to combine relational guarantees (ACID) with NoSQL-like scalability; distributed and cloud-native databases became standard for global-scale applications. Recent/emerging impact: cloud computing enables managed, elastically scalable database services; AI/ML integration is driving demand for vector databases (storing embeddings for similarity search) and in-database machine learning; and real-time analytics needs are pushing streaming/in-memory database technologies forward.

2024 · Paper-II · Q6(b) (7 marks)

Question

Write a SQL query involving multiple tables and incorporating JOIN operations. Discuss the potential pitfalls and optimizations related to complex SQL queries.

Solution

SELECT e.EmpName, d.DeptName, p.ProjectName
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID
JOIN Project p ON e.ProjectID = p.ProjectID
WHERE d.DeptName = 'IT';

This query joins Employee, Department, and Project tables to list employees in the IT department along with their project assignments.

Potential pitfalls: an incorrect or missing join condition can produce a Cartesian product, returning a huge, meaningless result set; ambiguous column names across joined tables cause errors if not qualified with table aliases; using INNER JOIN when an OUTER JOIN is actually needed silently drops rows with no match (e.g., employees without an assigned project would disappear); performance degrades sharply on large tables without proper indexing on join columns.

Optimizations: create indexes on foreign key/join columns; avoid SELECT * and retrieve only needed columns; choose the correct join type (INNER vs LEFT/RIGHT/FULL OUTER) deliberately; examine the query execution plan (EXPLAIN) to spot full table scans or inefficient join orders; and, for very complex reporting queries, consider denormalization or materialized views where appropriate.

2024 · Paper-II · Q6(c) (6 marks)

Question

What are distributed databases, and why are they used? Discuss the advantages and challenges of managing data in a distributed environment.

Solution

A distributed database is a single logical database whose data is physically stored across multiple, possibly geographically separated, computer nodes, while appearing to applications as one unified database. They are used to improve scalability (handle more data/load than a single machine could), availability (the system can continue operating even if some nodes fail), and performance (data can be stored close to the users who access it most, reducing latency).

Advantages: horizontal scalability (add more nodes as data/load grows), high availability and fault tolerance through replication, improved read/write performance via load distribution and locality, and support for disaster recovery (data replicated across sites survives a site-level failure).

Challenges: maintaining data consistency across nodes is difficult, especially during network partitions (the CAP theorem states a distributed system can guarantee only two of Consistency, Availability, and Partition tolerance at once); network latency and potential partitioning between nodes; complex distributed transaction management (e.g., two-phase commit protocols) to keep multi-node updates atomic; synchronization/replication overhead; and significantly increased administrative and design complexity compared to a single centralized database.

2023 · Paper-II · Q6(a) (12 marks)

Question

Explain the following different SELECT statement search conditions with examples using any database schema of your choice: 1. Compound Comparison Search Condition 2. Range search condition (BETWEEN/NOT BETWEEN) 3. Set membership search condition (IN/NOT IN) 4. Pattern match search condition (LIKE/NOT LIKE)

Solution

Using an Employee(EmpID, Name, Salary, DeptID, Age) schema:

Compound comparison search condition: combines multiple simple comparisons using AND/OR/NOT.

SELECT * FROM Employee WHERE Salary > 50000 AND DeptID = 3;

Returns employees who both earn more than 50000 AND belong to department 3.

Range search condition (BETWEEN/NOT BETWEEN): tests whether a value falls within (or outside) an inclusive range.

SELECT * FROM Employee WHERE Age BETWEEN 25 AND 40;

Returns employees whose age is between 25 and 40, inclusive. NOT BETWEEN would return employees outside that range.

Set membership search condition (IN/NOT IN): tests whether a value matches any value in a given list or subquery result.

SELECT * FROM Employee WHERE DeptID IN (1, 2, 5);

Returns employees belonging to department 1, 2, or 5. NOT IN excludes those departments instead.

Pattern match search condition (LIKE/NOT LIKE): tests whether a string matches a pattern using wildcards ('%' for any sequence of characters, '_' for a single character).

SELECT * FROM Employee WHERE Name LIKE 'A%';

Returns employees whose name starts with 'A'. NOT LIKE would exclude such names.

2023 · Paper-II · Q6(b) (4 marks)

Question

Explain ACID property of a transaction

Solution

ACID describes the four properties that guarantee reliable database transaction processing:

Atomicity: a transaction is treated as a single indivisible unit - either all of its operations complete successfully, or none of them take effect at all (all-or-nothing).
Consistency: a transaction moves the database from one valid state to another, preserving all defined integrity constraints and rules.
Isolation: concurrently executing transactions do not interfere with each other's intermediate states - the result is as if transactions executed one after another (serially), even though they may run concurrently.
Durability: once a transaction commits, its changes are permanently saved and survive any subsequent system failure (crash, power loss).

2023 · Paper-II · Q6(c) (4 marks)

Question

Explain Transitive dependency using any example.

Solution

A transitive dependency occurs when a non-key attribute depends on another non-key attribute, which in turn depends on the primary key, rather than depending on the primary key directly - i.e., if A -> B and B -> C, then A -> C is a transitive dependency (A determines C only indirectly, through B).

Example: in a table Student(StudentID, DeptID, DeptName), StudentID -> DeptID (each student belongs to one department) and DeptID -> DeptName (each department has one name); therefore StudentID -> DeptName is a transitive dependency, since a student's department name is determined only via the intermediate DeptID attribute, not directly by the key. Transitive dependencies violate Third Normal Form (3NF) and are removed by splitting the table into Student(StudentID, DeptID) and Department(DeptID, DeptName).

2021 · Paper-II · Q6(a) (8 marks)

Question

Consider the following database schema and write the relational algebra expression for the following queries. Students(Stu_Reg,Stu_Name,Stu_Address), Instructor(Ins_ID,Ins_Name,Ins_Dept_ID), Course(Cr_Code,Cr_Title,Cr_CrHours), Dept(Dept_ID,Dept_Name), Results(Res_Stu_Reg,Res_Cr_Code,Res_Marks,Res_Ins_ID,Res_Grade). (i) To display all the students details in section BSSE. (ii) To display all Instructor Names and their respective Department Names, (if an instructor is not assigned any Dept his name should be displayed as well). (iii) To display Stu_Reg, Stu_Name, Cr_Code, Cr_Title and Res_Marks for students who got 75 or more marks. (iv) To display Res_Stu_Reg, Ins_Name, Res_Grade for Instructors whose Dept_ID is 1.

Solution

(Note: the Students relation as given only lists Stu_Reg, Stu_Name, Stu_Address; query (i) refers to a 'section' filter, so it is answered assuming the relation also carries a Stu_Section attribute as implied by the question.)

(i) σ_(Stu_Section='BSSE') (Students)

(ii) A LEFT OUTER JOIN is required so instructors with no assigned department still appear: π_(Ins_Name, Dept_Name) (Instructor ⟕_(Ins_Dept_ID=Dept_ID) Dept)

(iii) π_(Stu_Reg, Stu_Name, Cr_Code, Cr_Title, Res_Marks) (σ_(Res_Marks≥75) (Students ⋈(Stu_Reg=Res_Stu_Reg) Results ⋈(Res_Cr_Code=Cr_Code) Course))

(iv) π_(Res_Stu_Reg, Ins_Name, Res_Grade) (σ_(Ins_Dept_ID=1) (Results ⋈_(Res_Ins_ID=Ins_ID) Instructor))

2021 · Paper-II · Q6(b) (12 marks)

Question

What is the concept of gerund in database? Give one example of gerund. Is this essential to eliminate gerunds from the database? If yes, how and if no, why?

Solution

A gerund (also called an objectified relationship or associative entity) arises when a RELATIONSHIP itself needs to carry its own attributes, or needs to participate in further relationships with other entities — something the relational model cannot express directly on a relationship line, so the relationship is instead turned into (modelled as) an entity in its own right. Example: an ENROLLMENT relationship between STUDENT and COURSE. If it merely connected the two entities it would be a simple M:N relationship, but because it needs its own attributes (Grade, Semester) and might itself relate to an INSTRUCTOR, it is objectified into a gerund entity ENROLLMENT(StudentID, CourseID, Grade, Semester), with a composite primary key (StudentID, CourseID) and its own foreign-key relationships to STUDENT, COURSE, and INSTRUCTOR. Is it essential to eliminate gerunds? — It depends: YES, it is necessary/essential to convert a relationship into a gerund (associative table) whenever (a) the relationship is many-to-many (the relational model has no way to store an M:N relationship directly without an intermediate linking table), (b) the relationship itself has descriptive attributes, or (c) the relationship must participate in another relationship. In these cases the conversion is done by creating a new table whose primary key is the combination of foreign keys referencing the participating entities, plus any of the relationship's own attributes as ordinary columns. NO, it is not necessary for a simple 1:1 or 1:N relationship with no attributes of its own — such a relationship is adequately represented by a plain foreign key in one of the two existing tables, and forcing it into an unnecessary gerund table would only add complexity (extra joins) without any modelling benefit.

2020 · Paper-II · Q6(a) (8 marks)

Question

What is the purpose of a join in SQL? Explain inner, left, right and full join with the help of examples.

Solution

A JOIN combines rows from two or more tables based on a related column between them, letting a query retrieve data that is spread across multiple normalized tables as if it were one combined result. INNER JOIN returns only the rows where a match exists in BOTH tables.

SELECT s.Name, e.CourseCode FROM Student s INNER JOIN Enrollment e ON s.ID = e.StudentID;

LEFT (OUTER) JOIN returns all rows from the left table, plus matching rows from the right table; unmatched right-side columns appear as NULL.

SELECT s.Name, e.CourseCode FROM Student s LEFT JOIN Enrollment e ON s.ID = e.StudentID;

RIGHT (OUTER) JOIN returns all rows from the right table, plus matching rows from the left; unmatched left-side columns appear as NULL.

SELECT s.Name, e.CourseCode FROM Student s RIGHT JOIN Enrollment e ON s.ID = e.StudentID;

FULL (OUTER) JOIN returns all rows from BOTH tables, with NULLs wherever no match exists on either side.

SELECT s.Name, e.CourseCode FROM Student s FULL JOIN Enrollment e ON s.ID = e.StudentID;

2020 · Paper-II · Q6(b) (7 marks)

Question

Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate with each patient a log of the various tests and examinations conducted.

Solution

Entities: PATIENT(PatientID [key], Name, Address, DOB), DOCTOR(DoctorID [key], Name, Specialty), and TEST_LOG(TestID [partial key], TestName, Date, Result) as a weak entity, since a test record only has meaning in relation to the patient it belongs to. Relationships:

TREATS: a many-to-many relationship between PATIENT and DOCTOR (a patient can be treated by several doctors, and a doctor treats many patients), with attribute ConsultationDate.
UNDERGOES: a one-to-many identifying relationship between PATIENT and the weak entity TEST_LOG (one patient has a whole log/history of many tests), drawn with a double-diamond and double-rectangle since TEST_LOG is existence-dependent on PATIENT. Diagram (described): [PATIENT] --(1)----(N)--[[TEST_LOG]] (double box = weak entity), and [PATIENT] --(M)----(N)--[DOCTOR]. This structure captures that every test/examination belongs to exactly one patient's history while a patient can be linked to multiple doctors and vice versa.

2020 · Paper-II · Q6(c) (5 marks)

Question

Explain Two-phase locking (2PL) as a concurrency control mechanism in the database systems.

Solution

Two-Phase Locking is a concurrency-control protocol that guarantees conflict-serializable schedules of concurrent transactions. Each transaction's locking behaviour is split into exactly two phases: (1) Growing phase — the transaction may acquire (request) any locks it needs, but may not release any lock; (2) Shrinking phase — once the transaction releases its FIRST lock, it enters the shrinking phase and may only release locks from then on, never acquiring any new ones. This strict 'acquire-then-release, never mixed' discipline prevents certain interleavings that would violate serializability. A common variant, Strict 2PL, requires a transaction to hold all its exclusive (write) locks until it actually commits or aborts, which additionally prevents cascading rollbacks (other transactions reading dirty/uncommitted data) and is the version implemented by most commercial DBMSs.

2019 · Paper-II · Q8(a) (8 marks)

Question

Demonstrate the use of ER Model in database designing process using an example.

Solution

The Entity-Relationship (ER) model is used early in database design to capture the real-world data requirements conceptually, before converting them to relational tables. It represents: Entities (distinct real-world objects, drawn as rectangles), Attributes (properties of entities, drawn as ellipses), and Relationships (associations between entities, drawn as diamonds), along with cardinality (1:1, 1:N, M:N) on the connecting lines. Example: entity STUDENT(StudentID [key], Name, Age) and entity COURSE(CourseCode [key], Title, Credits), connected by a many-to-many relationship ENROLLS_IN, which itself carries the attribute Grade (since a grade only makes sense per student-course pair). Diagrammatically: [STUDENT] --(M)-- <ENROLLS_IN {Grade}> --(N)-- [COURSE]. This conceptual model is then mapped to relations: STUDENT(StudentID,Name,Age), COURSE(CourseCode,Title,Credits), and — because it is an M:N relationship with its own attribute — a separate linking table ENROLLS_IN(StudentID,CourseCode,Grade) with a composite key, directly guiding the physical database schema from the conceptual design.

2019 · Paper-II · Q8(b) (6 marks)

Question

Describe an appropriate security scheme for a database maintained by a bank. Justify your answer using an example.

Solution

An appropriate scheme layers several mechanisms: (1) Role-Based Access Control (RBAC) — permissions are granted to roles (teller, branch manager, auditor) rather than individual users, so a teller can only view/update customer transactions up to a limit, while only a manager role can authorise large transfers. (2) Views — restrict which rows/columns a role can see, e.g. a teller's view hides a customer's full account balance history or another branch's records. (3) Encryption — data encrypted at rest (AES) and in transit (TLS) so stolen disks or intercepted traffic reveal nothing usable. (4) Strong authentication — multi-factor authentication for staff logins. (5) Audit logging — every read/write on sensitive tables is logged with user, timestamp, and old/new values, enabling forensic tracing of fraud. (6) Regular backups with tested recovery procedures. Justification: banking data is extremely sensitive (PII and finances) and heavily regulated, so a single mechanism is insufficient — e.g. RBAC alone would not stop data theft from a stolen unencrypted backup, so encryption plus RBAC plus auditing together provide defence-in-depth against both external attackers and insider misuse.

2019 · Paper-II · Q8(c) (6 marks)

Question

Explain the difference between top-down and bottom-up approaches in the context of distributed database design using a suitable example.

Solution

Top-down approach: design starts from an overall global conceptual schema capturing the enterprise's complete data requirements, which is then progressively fragmented (horizontally/vertically) and allocated to distributed sites. It suits a brand-new system being designed from scratch. Example: a bank building a new nationwide system designs one global CUSTOMER/ACCOUNT schema first, then fragments customer records by region and allocates each fragment to the database server at that region's data centre. Bottom-up approach: design starts from EXISTING, already-operational local databases/schemas at different sites, which are then integrated (schema integration, resolving naming/structural conflicts) into a single unified global schema. It suits situations where independent databases already exist and must be combined. Example: after a merger of two companies, each with its own pre-existing customer database using different attribute names/types, a bottom-up process reconciles and merges both local schemas into one consistent global view for the combined organisation.

2018 · Paper-II · Q5(A) (8 marks)

Question

Given two relations R and S, where R contains M tuples, S contains N tuples, and M > N > 0, give the minimum and maximum possible sizes (in tuples) for the resulting relation produced by each of the following relational algebra expressions. i. R - S ii. R U S iii. R ∩ S iv. R ⋈ S

Solution

i. R - S (set difference): Minimum size = M - N (occurs when S is entirely a subset of R, so all N tuples of S get removed from R's M tuples). Maximum size = M (occurs when R and S are completely disjoint - no tuples of S exist in R, so nothing is removed).

ii. R U S (union, requires union-compatible schemas): Minimum size = M (occurs when S is entirely a subset of R, so the union equals R itself, contributing no new tuples). Maximum size = M + N (occurs when R and S are completely disjoint, so the union simply concatenates both sets).

iii. R ∩ S (intersection): Minimum size = 0 (occurs when R and S are completely disjoint, sharing no common tuples). Maximum size = N (occurs when S is entirely a subset of R - since S has the fewer tuples, N is theoretically the most that can possibly be common to both).

iv. R ⋈ S (natural join): Minimum size = 0 (occurs when no tuples of R and S agree on the values of their common attributes, so no pairs join at all - this includes the case where R and S share no common attributes' matching values). Maximum size = M x N (occurs in the extreme case where the join condition is trivially satisfied for every pair - e.g. when R and S have no common attribute names at all, the natural join degenerates into the Cartesian product of size M x N).

2018 · Paper-II · Q5(B) (6 marks)

Question

Elaborate the concepts of super key, candidate key and foreign key with examples.

Solution

Super Key: any set of one or more attributes that, taken together, can uniquely identify a tuple (row) in a relation - it may contain extra, unnecessary attributes beyond what's strictly needed for uniqueness. Example: in a Student(RollNo, CNIC, Name, Email) table, {RollNo}, {CNIC}, {RollNo, Name}, and {RollNo, CNIC, Email} are all super keys, since each uniquely identifies a row (some contain redundant extra attributes).

Candidate Key: a MINIMAL super key - one that uniquely identifies a tuple with NO redundant attributes (removing any single attribute from it would destroy the uniqueness property). A relation can have multiple candidate keys. Example: both {RollNo} and {CNIC} alone are candidate keys in the Student table above (each independently and minimally identifies a row), but {RollNo, Name} is not a candidate key since it's not minimal (RollNo alone already suffices).

One candidate key is chosen as the Primary Key of the table; the rest are called alternate keys.

Foreign Key: an attribute (or set of attributes) in one relation that references the primary key of another (or the same) relation, establishing a link/relationship between the two tables and enforcing referential integrity (a foreign key value must either match an existing primary key value in the referenced table, or be NULL). Example: in an Enrollment(RollNo, CourseCode) table, RollNo is a foreign key referencing the primary key RollNo in the Student table, ensuring every enrollment refers to a genuinely existing student.

2018 · Paper-II · Q5(C) (6 marks)

Question

Discuss the difference between physical data independence and logical data independence.

Solution

These concepts relate to the three-schema architecture (external, conceptual, internal levels) of a DBMS, and describe the ability to change one schema level without needing to change the level(s) above it.

Physical Data Independence: the ability to change the internal (physical storage) schema - e.g. changing file organization, storage devices, adding/removing indexes, changing compression or access methods - WITHOUT needing to change the conceptual (logical) schema or any application programs/queries that use it. Since applications interact with data through the logical schema (not the raw physical storage), physical-level changes made purely for performance/storage reasons are transparent to users and applications. This is comparatively easier to achieve.

Logical Data Independence: the ability to change the conceptual (logical) schema - e.g. adding a new attribute or table, modifying relationships, restructuring how entities are represented - WITHOUT needing to change the external schema (the views/interfaces application programs actually depend on) or rewrite existing application programs, as long as the specific external view an application relies on remains unaffected. This is generally harder to achieve than physical data independence, since logical/conceptual-level changes are more likely to affect what applications actually see and depend upon.

In short: physical data independence insulates the conceptual/logical level from changes in physical storage; logical data independence insulates the external/application level from changes in the conceptual/logical schema - logical independence is the harder of the two to guarantee fully.

2017 · Paper-II · Q6(a) (8 marks)

Question

What is Normalization? Discuss 1NF, 2NF and 3NF with example(s).

Solution

Normalization is the systematic process of organizing a relational database's tables/attributes to minimize data redundancy and avoid update, insertion, and deletion anomalies, by decomposing tables according to a series of increasingly strict rules (normal forms).

First Normal Form (1NF): a table is in 1NF if all attribute values are atomic (indivisible - no repeating groups or multi-valued attributes within a single cell), and each record is uniquely identifiable. Example violation: Student(RollNo, Name, Courses='CS101,CS102') - Courses holds multiple values. Fix: split into separate rows, one course per row: Student(RollNo, Name, Course).

Second Normal Form (2NF): a table is in 2NF if it is in 1NF, and every non-key attribute is fully functionally dependent on the WHOLE primary key (no partial dependency on part of a composite key). Example violation: Enrollment(StudentID, CourseID, StudentName, Marks) with composite key (StudentID, CourseID) - StudentName depends only on StudentID (a partial dependency). Fix: split into Student(StudentID, StudentName) and Enrollment(StudentID, CourseID, Marks).

Third Normal Form (3NF): a table is in 3NF if it is in 2NF, and it has no transitive dependency (no non-key attribute depends on another non-key attribute). Example violation: Student(StudentID, DeptID, DeptName) - DeptName depends on DeptID, not directly on StudentID (transitive). Fix: split into Student(StudentID, DeptID) and Department(DeptID, DeptName).

Progressing through 1NF -> 2NF -> 3NF systematically removes redundancy and the anomalies it causes.

2017 · Paper-II · Q6(b) (6 marks)

Question

Write short notes on the following: (i) Data (ii) Database (iii) Database Management System

Solution

Data: raw, unprocessed facts and figures (numbers, text, dates, symbols) that by themselves carry no inherent meaning until they are organized, processed, or interpreted in context - e.g. the number '25' alone is just data, but 'Ali's age is 25' becomes meaningful information.

Database: an organized, structured collection of related data, stored electronically, designed so that it can be efficiently accessed, managed, and updated - typically organized into tables (in a relational database) representing entities and their relationships, following a defined schema.

Database Management System (DBMS): the software system that manages a database, providing an interface for users/applications to define, create, query, update, and administer the data, while handling concerns such as data integrity, security, concurrent multi-user access, backup/recovery, and query optimization - without requiring the user to know the physical storage details. Examples: MySQL, Oracle, PostgreSQL, Microsoft SQL Server.

Relationship: Data is the raw content; a Database is the organized container holding that data; a DBMS is the software layer that manages, protects, and provides controlled access to the database.

2017 · Paper-II · Q6(c) (6 marks)

Question

Differentiate between Centralized Database and Distributed Database.

Solution

Centralized Database: all the data is stored and managed at a single physical location/server, and all users/applications access this single central site over a network for their queries.

Advantages: simpler to design, administer, secure, and keep consistent (a single copy of the data, so no synchronization issues); easier backup and integrity enforcement.
Disadvantages: single point of failure (if the central server goes down, the whole system becomes unavailable); can become a performance bottleneck under heavy concurrent load; higher latency for geographically distant users.

Distributed Database: data is stored across multiple physical locations/servers (which may be geographically dispersed), interconnected via a network, but logically appearing to users as a single, unified database.

Advantages: improved reliability/availability (failure of one site doesn't necessarily bring down the whole system); better performance for geographically distributed users (data can be placed close to where it's most used); easier incremental scalability (add more nodes).
Disadvantages: much more complex to design, manage, and query (need distributed query processing, distributed transaction/concurrency control); maintaining data consistency across sites is challenging (replication/synchronization overhead); higher security/administration complexity across multiple sites.

In short, centralized databases favour simplicity and consistency, while distributed databases favour scalability, availability, and locality of access at the cost of complexity.

2016 · Paper-II · Q5(a) (8 marks)

Question

A complete SELECT statement embedded within another SELECT statement. The results of this inner SELECT statement (or subselect) are used in the outer statement to help determine the contents of the final result. Differentiate among following nested sub-queries operators 'IN', 'ANY' and 'ALL'.

Solution

IN: checks whether a value from the outer query matches ANY value in the list returned by the subquery (equivalent in meaning to '= ANY'). Example: SELECT name FROM Employee WHERE dept_id IN (SELECT dept_id FROM Dept WHERE location='Lahore');

ANY (also written SOME): compares a value against every value returned by the subquery using a comparison operator (>, <, =, etc.), and is true if the comparison holds for AT LEAST ONE row of the subquery result. Example: WHERE salary > ANY (SELECT salary FROM Employee WHERE dept_id=5); -- true if salary exceeds at least one employee's salary in dept 5.

ALL: compares a value against every value returned by the subquery, and is true only if the comparison holds for EVERY row of the subquery result. Example: WHERE salary > ALL (SELECT salary FROM Employee WHERE dept_id=5); -- true only if salary exceeds the maximum salary in dept 5.

Key difference: IN tests exact membership in a set (no relational operator involved besides equality); ANY is satisfied by the 'easiest' match (e.g. '> ANY' behaves like '> MIN'); ALL is the strictest, requiring the condition to hold against every returned value (e.g. '> ALL' behaves like '> MAX').

2016 · Paper-II · Q5(b) (6 marks)

Question

Explain how Pattern match search condition (LIKE/NOT LIKE) can be used in SELECT statement part of SQL in database management system.

Solution

The LIKE operator in SQL's WHERE clause performs pattern matching on character/string columns using two wildcard characters:

% (percent): matches any sequence of zero or more characters.
_ (underscore): matches exactly one character.

Examples:

SELECT * FROM Student WHERE name LIKE 'A%'; -- names starting with 'A'
SELECT * FROM Student WHERE name LIKE '%an'; -- names ending with 'an'
SELECT * FROM Student WHERE name LIKE '%ali%'; -- names containing 'ali' anywhere
SELECT * FROM Student WHERE rollno LIKE 'CS_ _1'; -- 'CS' followed by exactly two any-characters then '1'

NOT LIKE returns rows where the pattern does NOT match:

SELECT * FROM Student WHERE name NOT LIKE 'A%'; -- names not starting with 'A'

LIKE is essential for flexible/partial text searches (e.g. search boxes) where the exact string is unknown, unlike the '=' operator which requires an exact match. An optional ESCAPE clause can be used if the literal '%' or '_' character itself needs to be searched for.

2016 · Paper-II · Q5(c) (6 marks)

Question

Differentiate between Data Manipulation Language (DML) and Data Definition Language (DDL) of structured query language (SQL) in database management system (DBMS).

Solution

DDL (Data Definition Language): used to define, modify, and remove the structure/schema of database objects (tables, indexes, views, schemas) - it deals with the database's design, not its data. Common commands: CREATE, ALTER, DROP, TRUNCATE. Example: CREATE TABLE Student (id INT, name VARCHAR(50)); DDL statements are auto-committed in most DBMSs (cannot normally be rolled back).

DML (Data Manipulation Language): used to insert, retrieve, update and delete the actual data stored within existing database structures - it deals with the data itself. Common commands: SELECT, INSERT, UPDATE, DELETE. Example: INSERT INTO Student VALUES (1, 'Ali'); DML statements (except SELECT) are typically transactional and can be rolled back/committed.

Key difference: DDL changes the schema/structure (what the database looks like); DML changes/reads the content (what data lives inside that structure). A database must be defined via DDL before DML statements can operate on it.

2016 · Paper-II · Q6(a) (8 marks)

Question

A transaction is a unit of program execution that accesses and possibly updates various data items. Usually, a transaction is initiated by a user program written in a data manipulation language. Explain the ACID property of transaction processing.

Solution

ACID is the set of four properties that guarantee reliable processing of database transactions:

Atomicity: a transaction is treated as a single indivisible unit - either ALL of its operations are completed successfully (commit) or NONE of them are applied at all (rollback/abort). There is no partial execution.
Consistency: a transaction takes the database from one valid (consistent) state to another valid state, preserving all defined integrity constraints (e.g. a bank transfer must keep total balance unchanged across accounts).
Isolation: concurrently executing transactions must not interfere with each other - the intermediate state of one transaction is invisible to other transactions, as if transactions executed serially, one after another (enforced through concurrency control mechanisms like locking or timestamp ordering).
Durability: once a transaction has committed, its changes must persist permanently, even in the event of a system crash or power failure (typically ensured via write-ahead logging and recovery mechanisms).

Example: transferring money from Account A to Account B involves debiting A and crediting B - ACID ensures that either both operations succeed together (atomicity), the total money is conserved (consistency), no other transaction sees a state where money has left A but not yet arrived in B (isolation), and once confirmed to the user, the transfer survives a subsequent crash (durability).

2016 · Paper-II · Q6(b) (6 marks)

Question

Distinguish among functional dependency, Fully functional dependency and Transitive dependency.

Solution

Functional Dependency (FD): an attribute (or set of attributes) Y is functionally dependent on X (written X -> Y) if, for every value of X, there is exactly one associated value of Y - i.e., X uniquely determines Y. Example: RollNo -> Name (a roll number determines exactly one student name).

Fully Functional Dependency: Y is fully functionally dependent on a composite key X if Y depends on the whole of X, but NOT on any proper subset of X. Example: in a table with composite key (StudentID, CourseID), Marks is fully functionally dependent on (StudentID, CourseID) together, since neither StudentID alone nor CourseID alone determines Marks.

Transitive Dependency: occurs when X -> Y and Y -> Z, and therefore X -> Z indirectly, where Y is not a candidate key (Y does not determine X back). Example: StudentID -> DeptID and DeptID -> DeptName, so StudentID -> DeptName is a transitive dependency (via the non-key attribute DeptID).

Significance: fully functional dependency is required for Second Normal Form (2NF - eliminating partial dependency on part of a composite key), while the absence of transitive dependency is required for Third Normal Form (3NF).

2016 · Paper-II · Q6(c) (6 marks)

Question

A trigger is a statement that the system executes automatically as a side effect of a modification to the database. What are the different forms of triggers and how they are defined?

Solution

A trigger is a special stored procedure that automatically executes ('fires') in response to a specified data-modification event on a specific table.

Classified by timing:

BEFORE triggers: fire before the triggering DML statement executes (useful for validation or modifying the incoming data).
AFTER triggers: fire after the triggering statement has completed (useful for logging, auditing, or cascading updates).
INSTEAD OF triggers: replace the triggering statement entirely (commonly used on views to make them updatable).

Classified by the triggering event:

INSERT triggers, UPDATE triggers, DELETE triggers - fired respectively when a row is inserted, updated, or deleted.

Classified by granularity:

Row-level trigger: fires once for each row affected by the statement (FOR EACH ROW).
Statement-level trigger: fires once for the entire statement, regardless of how many rows it affects.

Definition syntax (general SQL form): CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} ON table_name [FOR EACH ROW] BEGIN -- trigger body (SQL statements) END;

Example: a BEFORE INSERT trigger on an Orders table could automatically check stock availability before allowing the order row to be inserted.

Part B — MCQs

2025 · Paper-II · Q8 (MCQ)

A ________ in a table represents a relationship among a set of values.

A. Column
B. Key
C. Row
D. Entry

Answer: C

Explanation: In the relational model a row (tuple) represents one specific relationship/association among the values of the table's attributes.

2025 · Paper-II · Q9 (MCQ)

Which forms are based on the concept of functional dependency?

A. 1NF
B. 2NF
C. 3NF
D. 4NF

Answer: C

Explanation: Third Normal Form (3NF) is defined using functional dependencies — it removes transitive functional dependencies of non-key attributes on the primary key — whereas 4NF is based on multivalued dependencies and 1NF only requires atomic values.

2025 · Paper-II · Q10 (MCQ)

In case of any shutdown during transaction before commit, which of the following statement is done automatically?

A. View
B. Commit
C. Rollback
D. Flashback

Answer: C

Explanation: If a transaction is interrupted before it commits, the DBMS's recovery manager automatically rolls it back to maintain atomicity, undoing any partial changes.

2024 · Paper-II · Q11 (MCQ)

A child entity in ER diagrams is:

A. The entity on the one side of a one to many relationship
B. Entity that inherits attributes and relations from another entity
C. A row of a table
D. None of these

Answer: B

Explanation: In ER modeling's specialization/generalization (ISA) hierarchies, a child (sub-type) entity inherits the attributes and relationships of its parent (super-type) entity, analogous to inheritance in OOP.

2024 · Paper-II · Q12 (MCQ)

Boyce Codd Normal Form:

A. Addresses certain type of multivalued dependencies
B. Makes sure that data in each column is atomic
C. Makes sure that every determinant is a candidate key
D. None of these

Answer: C

Explanation: BCNF requires that for every non-trivial functional dependency X->Y in a relation, X must be a superkey (candidate key) - i.e., every determinant must be a candidate key; multivalued dependencies are addressed by 4NF, and atomicity of columns is the requirement of 1NF.

2024 · Paper-II · Q13 (MCQ)

DDL is used to:

A. Represent the database structure
B. Define and manage the structure of a database
C. Deals with manipulation of data stored in the database
D. None of these

Answer: B

Explanation: Data Definition Language (DDL) commands like CREATE, ALTER, and DROP are used to define and manage the schema/structure of database objects; manipulating stored data is the role of DML (INSERT/UPDATE/DELETE/SELECT).