Database Management System 2023 Board Question Paper

Concept of Data Model

A data model is an abstract mathematical and conceptual framework that defines how data is structured, stored, organized, related, and manipulated within a Database Management System (DBMS). It provides the formal blueprint and grammar for database design.

Core Components of a Data Model:

1. Structural Component: Defines the organization of data objects, record types, data types, and inter-entity relationships (e.g., tables, rows, columns, foreign keys).
2. Integrity Component: Defines rules, constraints, and business logic that enforce data validity (e.g., Primary Key uniqueness, Entity Integrity, Referential Integrity, NOT NULL).
3. Manipulative Component: Defines the operational language and set of operations used to query, insert, update, and retrieve data (e.g., Relational Algebra, Structured Query Language - SQL).

Common Categories of Data Models:

• Relational Model (Codd, 1970): Represents data in two-dimensional tables (relations) of rows and columns (e.g., Oracle, MySQL, PostgreSQL).
• Entity-Relationship (E-R) Model: High-level conceptual model visualizing entities, attributes, and relationships.
• Hierarchical & Network Models: Legacy tree and graph-based pointer structures.
• NoSQL / Document Models: Schema-less JSON/BSON structures used in distributed big-data systems (e.g., MongoDB).

Differentiation: Simple vs. Composite Attribute

In database design and Entity-Relationship (ER) modeling, attributes describe properties of entities and are classified based on their divisibility:

Aggregate Functions in SQL

An aggregate function in SQL performs a mathematical calculation on a set of column values across multiple rows of a table and returns a single summarizing scalar value.

Four Primary Aggregate Functions:

1. COUNT():
   • Computes the total number of rows or non-null values matching the query criteria.
   • Syntax Example: SELECT COUNT(*) FROM Employees WHERE Department = ‘Sales’;
2. SUM():
   • Calculates the arithmetic sum of all numeric values in a specified column.
   • Syntax Example: SELECT SUM(Salary) FROM Employees;
3. AVG():
   • Computes the mathematical average (mean) of numeric values in a column, automatically ignoring NULL entries.
   • Syntax Example: SELECT AVG(Age) FROM Students;
4. MAX() / MIN():
   • Identifies the highest (MAX) or lowest (MIN) value in a specified numeric, string, or date column.
   • Syntax Example: SELECT MAX(Salary), MIN(Salary) FROM Employees;

Atomicity and Durability Properties (ACID)

A database transaction is a logical unit of work comprising one or more database operations. To ensure database integrity, transactions must satisfy the ACID properties:

1. Atomicity (“All-or-Nothing” Principle):

• Definition: Atomicity guarantees that either all operations of a transaction execute completely and commit successfully to the database, or none of them do.
• Mechanism: If a transaction fails mid-way due to power outage, software crash, or constraint violation, the DBMS recovery manager issues a ROLLBACK operation, undoing all partial modifications to restore the database to its pre-transaction state.
• Banking Example: When transferring Rs. 10,000 from Account A to Account B, debited money cannot vanish if the system crashes before crediting Account B; both steps must complete, or the transaction is aborted.

2. Durability (“Permanence” Principle):

• Definition: Durability guarantees that once a transaction successfully completes and issues a COMMIT, all of its updates and state modifications are permanently recorded in non-volatile secondary storage (disk/SSD).
• Mechanism: The committed changes survive even if the system suffers an immediate hardware or operating system crash. The DBMS achieves this using Write-Ahead Logging (WAL) and checkpointing so changes can be reconstructed during crash recovery.

Concept of Data Quality Management (DQM)

Data Quality Management (DQM) is an organization-wide operational and technical discipline comprising people, business processes, data governance frameworks, and automated software tools designed to ensure data is accurate, consistent, complete, reliable, and fit for business operations and analytics.

Core Dimensions of Data Quality:

1. Accuracy: Data correctly represents real-world entities without typographic errors or falsified values.
2. Completeness: Mandatory fields (e.g., Customer Phone Number, Citizenship ID) are not omitted or populated with dummy values.
3. Consistency: Synchronized data across different departmental databases (e.g., customer address in CRM matches the billing address in Core Banking).
4. Timeliness: Data is captured, updated, and made accessible in real time.
5. Uniqueness / Non-Redundancy: Absence of duplicate customer records or redundant entity instances.

Key DQM Processes:

• Data Profiling: Analyzing raw data sources to detect anomalies, missing values, and formatting defects.
• Data Cleansing / Scrubbing: Automated deduplication, standardizing postal codes, and parsing corrupted phone strings.
• Integrity Constraints Enforcement: Defining DBMS-level primary keys, foreign keys, CHECK constraints, and triggers.

Roles and Responsibilities of a Database Administrator (DBA)

A Database Administrator (DBA) is the primary technical custodian responsible for the architecture, performance, security, operational integrity, and disaster recovery of an enterprise’s database systems:

1. Schema Definition and Physical Database Design:

• Translates logical entity-relationship schemas into optimal physical database structures.
• Defines table storage parameters, partition strategies, file placement on storage arrays (SAN/NAS), data types, and primary/foreign key constraints.

2. Performance Tuning and Optimization:

• Continuously monitors database resource utilization (CPU, memory, buffer cache hit ratio, disk I/O bottlenecks).
• Analyzes execution plans of slow-running SQL queries, builds appropriate B-Tree or Hash indexes, defragments tablespaces, and tunes SQL statements to minimize query response latency.

3. Security Enforcement, User Authorization, and Access Control:

• Creates user accounts, assigns role-based permissions (RBAC) via GRANT/REVOKE commands, and ensures strict data confidentiality.
• Implements transparent data encryption (TDE) for sensitive financial and identity records, conducts security audits, and prevents unauthorized data breaches.

4. Backup, Disaster Recovery, and High Availability:

• Establishes automated daily backup schedules (full, differential, and transaction log backups).
• Tests disaster recovery protocols, implements database mirroring/clustering (e.g., Oracle Data Guard, MySQL Replication), and guarantees minimal Recovery Time Objective (RTO) and Recovery Point Objective (RPO) during system failures.

Degree of a Relationship Set in ER Modeling

The degree of a relationship set refers to the number of distinct participating entity types involved in that specific relationship:

---

1. Unary Relationship (Recursive Relationship / Degree 1):

• Definition: A relationship where an entity type participates with itself; instances of the same entity set relate to other instances of the same set under different operational roles.
• Example: In an EMPLOYEE entity, an employee is managed by another employee:
  • Relationship: Manages
  • An Employee (subordinate role) is managed by an Employee (manager role).

---

2. Binary Relationship (Degree 2):

• Definition: The most common relationship type in relational modeling, involving exactly two distinct participating entity types.
• Example: Relationship between CUSTOMER and ACCOUNT in a bank:
  • Relationship: Holds
  • Entity 1: Customer
  • Entity 2: Account
  • A Customer holds an Account.

---

3. Ternary Relationship (Degree 3):

• Definition: A relationship involving simultaneous participation of exactly three distinct entity types to describe an event or transaction that cannot be decomposed into pairs of binary relationships.
• Example: Supply chain procurement involving SUPPLIER, PART, and PROJECT:
  • Relationship: Supplies
  • A Supplier supplies a specific Part to a particular Project. All three entities are required to identify which supplier delivered what part to which project.

Strategies for Securing a Database

Database security encompasses the physical, administrative, and technical safeguards employed to protect databases against unauthorized access, malicious attacks (such as SQL injection), data theft, and accidental corruption:

---

Core Security Countermeasures

1. Authentication and Strong Password Policies:
   • Mandating multi-factor authentication (MFA) and biometric logins for database administrative accounts.
   • Enforcing cryptographic password hashing (bcrypt, SHA-256) and periodic password expiry.
2. Authorization and Role-Based Access Control (RBAC):
   • Applying the Principle of Least Privilege (PoLP): users and applications receive only the minimum permissions necessary to perform their duties.
   • Utilizing SQL commands (GRANT and REVOKE) to restrict table-level and column-level access rather than granting universal DBA permissions.
3. Data Encryption (At Rest and In Transit):
   • In Transit: Enforcing Transport Layer Security (TLS/SSL) for all communication channels between database servers and application clients.
   • At Rest: Employing Transparent Data Encryption (TDE) to encrypt database storage files, backup media, and transaction logs.
4. Defense Against SQL Injection (SQLi):
   • Rejecting dynamic concatenated SQL strings in web applications; mandating Parameterized Queries (Prepared Statements) and Stored Procedures with strict input validation.
5. Database Auditing and Activity Monitoring (DAM):
   • Maintaining tamper-proof audit trails logging who accessed, queried, modified, or deleted sensitive data, along with exact timestamps and IP addresses.
6. Network Isolation and Firewalls:
   • Placing database servers in private subnets behind dedicated firewalls, disallowing direct exposure to public internet IPs.

Types of Anomalies in Relational Databases

Anomalies are undesirable operational inconsistencies, data integrity breakdowns, and operational errors that occur in un-normalized tables containing redundant data:

---

1. Insertion Anomaly:

• Definition: Occurs when certain facts or new data cannot be recorded in the database without artificially adding unrelated data, or when inserting a record is impossible because the primary key is partially missing.
• Example: In an un-normalized Student-Course table where StudentID is part of the composite primary key, a new Course cannot be added to the database until at least one student enrolls in it, because StudentID cannot be NULL.

---

2. Deletion Anomaly:

• Definition: Occurs when the deletion of a specific piece of data unintentionally wipes out completely unrelated, critical business facts from the database.
• Example: If a student is the only individual enrolled in an “Advanced AI” course and drops out, deleting that student’s record simultaneously obliterates all information about the “Advanced AI” course (course title, instructor, credit hours).

---

3. Update / Modification Anomaly:

• Definition: Occurs when redundant data is stored across multiple rows and an update is made to some rows but not all, creating contradictory and inconsistent database states.
• Example: If a Department’s office location is duplicated across 500 employee records and the department relocates, updating only 400 rows results in inconsistent data where the department appears to be located in two different places at once.

Solution: Database Normalization (1NF, 2NF, 3NF, BCNF) decomposes un-normalized tables into multiple well-structured, non-redundant relational tables linked by foreign keys.

Types of Outer Joins in SQL

In SQL, an Outer Join returns all matching rows from participating tables as well as unmatched rows from one or both tables, filling in missing attributes with NULL values:

---

1. Left Outer Join (LEFT JOIN):

• Definition: Returns all rows from the left table, along with matched rows from the right table. If no match exists in the right table, NULL values are returned for all right-table columns.
• Syntax: SELECT C.CustomerID, C.CustomerName, O.OrderID FROM Customers C LEFT JOIN Orders O ON C.CustomerID = O.CustomerID;
• Result: Lists every single customer, even those who have never placed an order (their OrderID will be NULL).

---

2. Right Outer Join (RIGHT JOIN):

• Definition: Returns all rows from the right table, along with matched rows from the left table. If no match exists in the left table, NULL values are returned for left-table columns.
• Syntax: SELECT E.EmployeeName, D.DepartmentName FROM Employees E RIGHT JOIN Departments D ON E.DepartmentID = D.DepartmentID;
• Result: Lists every department, including newly created departments that currently have zero assigned employees.

---

3. Full Outer Join (FULL JOIN):

• Definition: Combines the results of both Left and Right Outer Joins. It returns all rows from both tables, pairing matched rows where the join condition is met, and inserting NULLs on either side where no match exists.
• Syntax: SELECT A.StudentName, B.CourseName FROM Students A FULL OUTER JOIN Courses B ON A.CourseID = B.CourseID;
• Result: Displays all students (even those without courses) and all courses (even those without students).

Part a: SQL Queries for Customer Relation

Given Schema: Customer (CID, ACCOUNTNO, CNAME, ADDRESS, AGE)

i. Insert information of two customers into Customer table:

INSERT INTO Customer (CID, ACCOUNTNO, CNAME, ADDRESS, AGE) VALUES (101, ‘ACC-98214’, ‘Alice’, ‘Kathmandu’, 28), (102, ‘ACC-77412’, ‘George’, ‘Pokhara’, 42);

ii. Display name and accountno of Customers whose name ends with ‘e’:

SELECT CNAME, ACCOUNTNO FROM Customer WHERE CNAME LIKE ‘%e’;

iii. Display the name and address of those customers who do not live in ‘Kathmandu’:

SELECT CNAME, ADDRESS FROM Customer WHERE ADDRESS <> ‘Kathmandu’; -- (Alternatively: WHERE ADDRESS != ‘Kathmandu’ OR LOWER(ADDRESS) NOT LIKE ‘kathmandu’)

iv. Count the total number of customers:

SELECT COUNT(*) AS TotalCustomers FROM Customer;

v. Display CID and age of those Customers whose age lies between 16 and 50:

SELECT CID, AGE FROM Customer WHERE AGE BETWEEN 16 AND 50;

---

Part b: E-R Model for Hospital Management System

1. Entity and Attribute Specification:

• Doctor: Did (Primary Key), dname, dspecialization, ddepartment
• Nurse: nid (Primary Key), name, ndepartment
• Patient: pid (Primary Key), pname, paddress

2. Structural Relationships and Cardinality Constraints:

• Treats (Doctor - Patient):
  • One doctor may treat many patients (1:N cardinality).
  • A patient is treated by at least one doctor.
• Assists / Helps (Nurse - Doctor):
  • A nurse assists doctors in clinical treatment (M:N or N:1).
  • Multiple nurses can assist a doctor during complex surgical operations.

3. E-R Diagram Representation:

[DOCTOR] <--- (1) --- <TREATS> --- (N) ---> [PATIENT] ^ | (M) <ASSISTS> | (N) [NURSE]

• Entities (Rectangles): DOCTOR, NURSE, PATIENT.
• Attributes (Ellipses):
  • DOCTOR: Did (underlined), dname, dspecialization, ddepartment.
  • NURSE: nid (underlined), name, ndepartment.
  • PATIENT: pid (underlined), pname, paddress.
• Relationships (Diamonds):
  • TREATS (Diamond connecting DOCTOR and PATIENT with 1 to N cardinality).
  • ASSISTS (Diamond connecting NURSE and DOCTOR with M to N cardinality).

Solution: Step-by-Step Normalization up to Third Normal Form (3NF)

Given Un-Normalized Table:

EMPLOYEE_DEPT (D-ID, D-Name, E-ID, E-Name, Address, Contact_no)

Notice: The Contact_no field contains multiple values (e.g., ‘9841,5560’), which violates atomicity. Candidate Key: E-ID (since each employee belongs to one department and has unique personal details).

---

Step 1: Conversion to First Normal Form (1NF)

• Rule for 1NF: A relation is in 1NF if and only if all attribute values are atomic (no repeating groups, multi-valued attributes, or composite arrays).
• Defect in Original Table: The Contact_no column contains multi-valued comma-separated contact numbers.
• Action: Split multi-valued rows into distinct individual atomic rows.

Table in 1NF:

In this 1NF relation, the composite primary key is (E-ID, Contact_no).

---

Step 2: Conversion to Second Normal Form (2NF)

• Rule for 2NF: The relation must be in 1NF, and no non-prime attribute should be partially dependent on any candidate key (Elimination of Partial Functional Dependencies).
• Identified Functional Dependencies:
  • (E-ID, Contact_no) $\rightarrow$ All attributes (Full Key)
  • E-ID $\rightarrow$ E-Name, Address, D-ID, D-Name (Partial Dependency! Non-prime attributes depend only on E-ID, not on Contact_no).
  • E-ID $\rightarrow$ Contact_no (Multi-valued relationship).
• Action: Decompose into two relations:

1. EMPLOYEE_CONTACT (E-ID, Contact_no)

   • Primary Key: (E-ID, Contact_no)
   • Foreign Key: E-ID references EMPLOYEE

2. EMPLOYEE_INFO (E-ID, E-Name, Address, D-ID, D-Name)

   • Primary Key: E-ID

All partial dependencies are now eliminated. The relations are in 2NF.

---

Step 3: Conversion to Third Normal Form (3NF)

• Rule for 3NF: The relation must be in 2NF, and no non-prime attribute should be transitively dependent on the primary key (Elimination of Transitive Dependencies: $X \rightarrow Y$ and $Y \rightarrow Z$).
• Analysis of EMPLOYEE_INFO:
  • E-ID $\rightarrow$ D-ID
  • D-ID $\rightarrow$ D-Name (Transitive Dependency! D-Name depends on D-ID, which in turn depends on E-ID).
• Action: Decompose EMPLOYEE_INFO into two distinct relations:

1. DEPARTMENT (D-ID, D-Name)

   • Primary Key: D-ID
   • Tuples:
     • (D01, HRM)
     • (D02, Finance)
     • (D03, CRM)

2. EMPLOYEE (E-ID, E-Name, Address, D-ID)

   • Primary Key: E-ID
   • Foreign Key: D-ID references DEPARTMENT(D-ID)
   • Tuples:
     • (E01, Ram, KTM, D01)
     • (E02, Shyam, PKH, D02)
     • (E03, Hari, BRT, D01)
     • (E04, Sita, BKT, D03)

3. EMPLOYEE_PHONE (E-ID, Contact_no)

   • Composite Primary Key: (E-ID, Contact_no)
   • Foreign Key: E-ID references EMPLOYEE(E-ID)

---

Final 3NF Normalized Schema:

• DEPARTMENT (<u>D-ID</u>, D-Name**)**
• EMPLOYEE (<u>E-ID</u>, E-Name, Address, D-ID**)**
• EMPLOYEE_PHONE (<u>E-ID, Contact_no</u>)

Conclusion: The database is completely free of insertion, deletion, and update anomalies and conforms strictly to Third Normal Form (3NF).