GTU BE03000091 Database Management System Summer 2026 Solved Paper & Solutions

A Database Management System (DBMS) is software that is used to create, store, retrieve, update and manage a database, and it controls how the users and the programs get at the data. Examples are MySQL, Oracle and PostgreSQL.

Purpose of a DBMS:

1. To store data in one place, with little duplication, so it stays consistent.
2. To give fast access with a query language such as SQL.
3. To give security, so only allowed users change the data.
4. To keep integrity, with keys and constraints.
5. To allow many users at once, and recover the data after a failure.

Database architecture describes how the data is stored and seen. The common one is the three level (ANSI-SPARC) architecture. It keeps the user's view of the data separate from the way it is stored, which gives data independence.

1. External level (view level): the highest level. Each user or group sees only the part of the database that is of interest to them, called a view. It hides the rest, and so it gives security and simplicity.
2. Conceptual level (logical level): it describes what data is stored and the relationships among the data: tables, attributes, keys and constraints. It is for the whole database, and it is the DBA's view. It does not say how the data is stored.
3. Internal level (physical level): it describes how the data is stored on the disk: the file structure, indexes, data compression and the access paths.

Mappings: the DBMS converts a request from one level to the next through two mappings, external / conceptual and conceptual / internal.

Data independence:

• Logical data independence: the conceptual schema can change (a new column, say) with no change in the user views.
• Physical data independence: the internal schema can change (a new index, a new disk) with no change in the conceptual schema.

Application architecture: in practice a DBMS is used in a two tier (client and database server) or a three tier (client, application server and database server) arrangement.

• Superkey: a set of one or more attributes that uniquely identifies each tuple (row) in a relation. It may contain extra attributes. Example: in Student(RollNo, Name, Email), {RollNo}, {RollNo, Name} and {Email} are superkeys.
• Candidate key: a minimal superkey, that is, a superkey from which no attribute can be removed. {RollNo} and {Email} are candidate keys, but {RollNo, Name} is not.
• Primary key: the one candidate key chosen by the designer to identify the tuples. It cannot be NULL and cannot repeat. {RollNo} may be chosen.

Take the relation Student(RollNo, Name, Marks).

i) Select the students with marks > 60: the selection operator $\sigma$ picks rows:

ii) Project the student names only: the projection operator $\pi$ picks columns:

If both are needed together, that is, the names of the students with marks over 60:

Selection keeps all attributes and a part of the rows, and projection keeps all the rows (without duplicates) and a part of the attributes. Both give a new relation, so they can be joined into one expression. Selection is written with the condition below the $\sigma$ sign, and projection with the list of attributes below the $\pi$ sign. The condition can use the operators $=, \neq, <, >, \le, \ge$ with AND, OR and NOT.

Specialization is a top-down process. A higher level entity is divided into sub-groups (lower level entities) that have some extra attributes or relationships of their own.

Generalization is a bottom-up process. Several lower level entities that have common features are combined into a higher level entity.

Both are shown by an ISA relationship (a triangle). The lower level entities inherit all the attributes of the higher level entity.

Example: a college has Student and Teacher entities. Both have a name, an address and a phone number. By generalization, these common attributes are moved to a higher level entity Person. The reverse, dividing Person into Student (with Enrollment_no) and Teacher (with Salary), is specialization.

The constraints on the specialization are: it may be disjoint (a person is only a student or only a teacher) or overlapping (a person is both), and it may be total (every person must be in a sub-group) or partial.

Use: it gives a clear, reusable design, and it avoids repeating the common attributes in each entity. In the tables, the sub-group entity gets the primary key of the higher entity as its own primary key and as a foreign key, so a student row is linked to its person row. A second example is Account, with the specialization into Savings and Current accounts, where Savings has an interest rate and Current has an overdraft limit.

A functional dependency (FD) $X \to Y$ means that if two tuples have the same value of $X$, they must have the same value of $Y$. $X$ determines $Y$. Example: in Student(RollNo, Name), RollNo $\to$ Name.

Armstrong's axioms are a set of rules to find all the FDs that follow from a given set. They are sound (they give only correct FDs) and complete (they give all FDs).

1. Reflexivity: if $Y \subseteq X$, then $X \to Y$.
2. Augmentation: if $X \to Y$, then $XZ \to YZ$.
3. Transitivity: if $X \to Y$ and $Y \to Z$, then $X \to Z$.

Derived rules: union (if $X \to Y$ and $X \to Z$, then $X \to YZ$), decomposition (if $X \to YZ$, then $X \to Y$ and $X \to Z$) and pseudotransitivity (if $X \to Y$ and $WY \to Z$, then $WX \to Z$).

A functional dependency $X \to Y$ is trivial if $Y$ is a subset of $X$ ($Y \subseteq X$). It is always true, as a set of values always determines its own part, and so it gives no new information about the data. It follows from the reflexivity rule.

Example: in the relation Student(RollNo, Name, City):

• $\{RollNo, Name\} \to RollNo$ is trivial, as RollNo is a part of the left side.
• $\{RollNo, Name\} \to \{RollNo, Name\}$ is trivial.
• $RollNo \to Name$ is non-trivial, as Name is not in the left side. It depends on the data and may be true or false.

A dependency is completely non-trivial if $X$ and $Y$ have no common attribute. Trivial dependencies are ignored when we check the normal forms.

Given: Employees(empId, empName, carName, parkingSlot) with $F = \{empId \to carName,\; empId \to empName,\; carName \to parkingSlot\}$.

It gives all the attributes, so empId is the candidate key. It is the only one, as no other attribute is on the left side of an FD that can give empId. The prime attribute is empId, and the non-prime attributes are empName, carName and parkingSlot.

• $empId \to carName$ and $empId \to empName$: empId is a superkey. OK.
• $carName \to parkingSlot$: carName is not a superkey, and parkingSlot is not a prime attribute. This violates 3NF.

This is a transitive dependency: $empId \to carName \to parkingSlot$.

So the relation is not in 3NF.

• $R_1(empId, empName, carName)$ with $empId \to empName, carName$. Key: empId.
• $R_2(carName, parkingSlot)$ with $carName \to parkingSlot$. Key: carName.

Check: both are in 3NF, as the left side of every FD is a key. The decomposition is lossless (the common attribute carName is the key of $R_2$) and dependency preserving (all three FDs are kept).

A transaction is a logical unit of work made of one or more database operations (read, write, update) that must be executed as a whole: either all of them are done, or none. Example: a bank transfer, which has a debit and a credit.

Types of transactions:

1. Read-only transaction: it only reads data (a balance enquiry).
2. Read-write (update) transaction: it reads and changes data (a deposit).
3. Flat transaction: a simple transaction with one start and one end.
4. Nested transaction: a transaction that contains sub-transactions.
5. Distributed transaction: it works on data at more than one site.

Authentication is the process of checking that a user is who he or she says, before access to the database is given. Common methods:

1. Password (user name and password): the user gives a secret. It is the simplest method, and it needs strong passwords stored as hashes. Example: CREATE USER ravi IDENTIFIED BY 'Pa55word';
2. Multi-factor authentication: a password plus a second proof, such as an OTP sent to a phone or an authenticator app.
3. Biometric: a fingerprint, face or iris is checked. It is hard to share or forget.
4. Smart card or token: the user has a physical card or key.
5. Digital certificate (public key): the user's identity is proved by a certificate issued by a trusted authority.
6. Operating system or single sign-on (Kerberos, LDAP): the DBMS trusts the user already logged on the OS, so a separate login is not required.

After authentication, authorization decides what the user may do.

Hashing finds a record directly from its key, without a search, by using a hash function that gives the address of a bucket (a disk block that holds records).

In static hashing, the number of buckets $B$ is fixed when the file is made, and it never changes. The hash function is, for example,

Operations:

• Insert: compute $h(K)$ and store the record in that bucket.
• Search: compute $h(K)$ and look only in that bucket.
• Delete: find the record by search and remove it.

Overflow: a bucket has a fixed capacity. If it is full, the new record goes into an overflow bucket linked to it (chaining), or into the next free bucket (open addressing).

Example: $B = 4$ buckets, capacity of 2 records per bucket, $h(K) = K \bmod 4$. Insert the keys 12, 5, 9, 16, 21, 8, 13:

To find 13: $13 \bmod 4 = 1$, go to bucket 1, look in 5, 9, then the overflow bucket, and find 13.

Advantages: very fast search (about one block read) and a simple structure.

Drawbacks: if the file grows, many overflow buckets form and the search becomes slow, and if it shrinks, the space is wasted. The bucket count cannot be changed without rehashing the whole file. Dynamic hashing removes this problem.

A deadlock is a situation in which two or more transactions wait for each other for locks, and so none of them can go ahead. Example: T1 holds a lock on X and waits for Y, while T2 holds Y and waits for X.

Two methods to handle it:

1. Deadlock prevention: rules such as wait-die and wound-wait (based on timestamps), or locking all the data items at the start.
2. Detection and recovery: find a cycle in the wait-for graph, then roll back a victim transaction.

The transfer of Rs. X from account A to account B has two updates, which must succeed or fail together (atomicity).

Where to use COMMIT and ROLLBACK:

1. COMMIT: after both updates succeed and the data is correct. It makes the changes permanent. If the COMMIT is given after only the first update, the money is lost when the second fails.
2. ROLLBACK: if any error occurs (insufficient balance, a wrong account number, a system failure) at any step before the commit. It undoes all the changes of the transaction, and the balances stay as before.

In code, the two updates are put in an exception block: on an error, the program calls ROLLBACK, and otherwise COMMIT. A check that the balance of A is at least 500 is also made before the debit.

Result: the total money in the two accounts always stays the same, and the database is consistent.

A PL/SQL procedure is a named block that is stored in the database and can be called many times. The task is to raise the salary by 5% of the customers whose age is more than 50.

Explanation:

1. CREATE OR REPLACE PROCEDURE raise_sal creates the procedure, or replaces it if it exists.
2. The UPDATE statement multiplies the salary by 1.05 (an increase of 5%) only for the rows where age > 50.
3. SQL%ROWCOUNT is an implicit cursor attribute that gives the number of rows changed, and it is shown with DBMS_OUTPUT.PUT_LINE.
4. COMMIT saves the change. In case of an error, the EXCEPTION part does a ROLLBACK.

Calling the procedure:

Example: a customer of age 55 with a salary of Rs. 40000 gets $40000 \times 1.05 = 42000$ rupees. A customer of age 45 is not changed.

The table in the question is named CUSTOMER, so the procedure works on it, though the question says "employees".