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).

SQL Statement to Delete Table

To completely remove the definition of the student table along with all its data, constraints, indexes, and privileges from the database schema, the SQL Data Definition Language (DDL) DROP TABLE statement is used:

DROP TABLE student;

Important Distinction:

• DROP TABLE student; (DDL): Permanently removes the table structure and its contents from the data dictionary.
• DELETE FROM student; (DML): Deletes all data rows from the table while retaining the table structure, schema definition, and constraints for future insertions.
• TRUNCATE TABLE student; (DDL): Rapidly deallocates all data storage pages of the table while keeping the table definition intact.

Difference Between Simple and Composite Attributes

Types of Mapping Constraints in ER Model

Mapping constraints express constraints to which the contents of a database must conform. In an Entity-Relationship (ER) model, the two primary categories of mapping constraints are:

1. Mapping Cardinalities (Cardinality Ratios): Express the number of entities to which another entity can be associated via a relationship set:

   • One-to-One (1:1): An entity in A is associated with at most one entity in B, and vice versa (e.g., Citizen and Passport).
   • One-to-Many (1:N): An entity in A is associated with any number of entities in B, but an entity in B is associated with at most one entity in A (e.g., Department and Employee).
   • Many-to-One (N:1): Multiple entities in A are associated with at most one entity in B (e.g., Student and Class).
   • Many-to-Many (M:N): An entity in A is associated with any number of entities in B, and vice versa (e.g., Student and Course).

2. Participation Constraints (Existence Dependencies):

   • Total Participation (Existence Dependency): Every entity in the entity set must participate in at least one relationship instance (represented by a double line).
   • Partial Participation: Some entities in the entity set may not participate in any relationship instance (represented by a single line).

Syntax for CHECK Constraint in SQL

The CHECK constraint is an integrity constraint that ensures all values in a column satisfy a specific boolean condition.

1. Column-Level Syntax (During Table Creation):

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18),
    salary DECIMAL(10, 2) CHECK (salary > 0)
);

2. Table-Level Syntax (With Explicit Constraint Name):

CREATE TABLE Student (
    roll_no INT PRIMARY KEY,
    marks DECIMAL(5, 2),
    CONSTRAINT chk_marks_range CHECK (marks >= 0 AND marks <= 100)
);

3. Syntax for Existing Table (ALTER TABLE):

ALTER TABLE Employee
ADD CONSTRAINT chk_salary CHECK (salary >= 15000);

Definition of Transaction in DBMS

A transaction is a logical unit of database processing that includes one or more database operations (such as reading, inserting, updating, or deleting records). It is treated as an indivisible atomic action that must either execute completely or have no effect whatsoever, transitioning the database from one valid, consistent state to another while satisfying all ACID properties.

Concept of NoSQL

NoSQL (“Not Only SQL”) refers to a broad class of non-relational database management systems designed to provide high scalability, flexible data models, and high performance for massive volumes of unstructured, semi-structured, or rapidly changing data.

Core Characteristics:

• Schema-less / Dynamic Schema: Records do not require fixed tabular rows and columns; schemas evolve without downtime.
• Horizontal Scalability: Uses scale-out distributed architecture across commodity hardware clusters rather than vertical hardware upgrades.
• BASE Consistency Model: Relies on Basically Available, Soft-state, and Eventual consistency rather than strict ACID guarantees.

Four Major Categories:

1. Document Stores: Store data in semi-structured JSON/BSON documents (e.g., MongoDB, CouchDB).
2. Key-Value Stores: High-speed associative arrays (e.g., Redis, DynamoDB).
3. Column-Family Stores: Store columns together for high-throughput analytics (e.g., Apache Cassandra, HBase).
4. Graph Databases: Optimized for complex entity network relationships (e.g., Neo4j).

Definition of Big Data

Big Data refers to extremely large, diverse, and complex collections of structured, semi-structured, and unstructured data generated at high speed from numerous sources that exceed the processing and storage capabilities of conventional relational database management systems (RDBMS).

The 5 V’s of Big Data:

1. Volume: Immense scale of data generated (terabytes to exabytes) from IoT, social media, and web logs.
2. Velocity: Real-time generation and streaming speed requiring immediate processing and ingestion.
3. Variety: Diverse formats including structured (tables), semi-structured (JSON, XML), and unstructured (video, audio, text).
4. Veracity: Trustworthiness, accuracy, noise level, and quality of incoming data.
5. Value: The ultimate business insights, competitive advantage, and actionable intelligence extracted.

First Normal Form (1NF)

A relation $R$ is in First Normal Form (1NF) if and only if:

1. The domain of every attribute contains only atomic (indivisible) values.
2. The value of any attribute in a tuple is a single value from the domain of that attribute.
3. It contains no repeating groups, array attributes, composite attributes, or nested relations.
4. Each column contains values of a single data type, and each row is uniquely identifiable (has a primary key).

Formal Statement:

$$\forall t \in R, \forall A \in \text{Attributes}(R) \implies t[A] \text{ is an atomic scalar value}$$

Why Level of Abstraction is Maintained in Database

A DBMS maintains multiple levels of data abstraction (the Three-Schema Architecture) to hide low-level hardware and storage complexities from end users and developers.

Key Reasons:

1. Data Independence:
   • Physical Data Independence: Changes to physical storage structures, indexing, or disks do not alter conceptual or external schemas.
   • Logical Data Independence: Changes to conceptual schemas (adding attributes or tables) do not require rewriting user application views.
2. Simplified User Interaction: End users interact with clean logical entities without concerning themselves with B-trees, page offsets, block pointers, or hashing.
3. Enhanced Security and Privacy: Different user groups access only their designated external view, preventing unauthorized exposure of sensitive enterprise data.

Two-Phase Locking (2PL) Mechanism

The Two-Phase Locking (2PL) protocol is a fundamental concurrency control technique that guarantees serializability in transaction schedules by dividing the lock acquisition and release process into two distinct, non-overlapping phases.

Number of
Locks Held
    ^
    |          /------------\  <- Lock Point
    |         /              \
    |        /                \
    |       /                  \
    |      / Growing            \ Shrinking
    |     /  Phase               \  Phase
    |    /                        \
    +---+--------------------------+--------> Time

The Two Phases:

1. Growing (Expanding) Phase:
   • The transaction may acquire new locks (Shared S or Exclusive X).
   • The transaction is strictly forbidden from releasing any lock.
   • The point where a transaction holds its final lock is called the Lock Point.
2. Shrinking (Contracting) Phase:
   • The transaction may release existing locks.
   • The transaction is strictly forbidden from acquiring any new lock once the first lock is released.

Variants of 2PL:

• Basic 2PL: Guarantees conflict serializability but may suffer from cascading aborts and deadlocks.
• Strict 2PL: Exclusive (X) locks are held until transaction commits or aborts; completely prevents cascading aborts.
• Rigorous 2PL: Both Shared (S) and Exclusive (X) locks are held until commit; ensures strict serializability.

Types of Database Users

Database users are categorized based on their technical expertise, job roles, and how they interact with the DBMS:

+-------------------------------------------------------------------+
|                        Database Users                             |
+-------------------+--------------------+--------------------------+
|  Administrative   |    Development     |        End Users         |
|  - DBA            |  - DB Designers    |  - Naive / Parametric    |
|                   |  - App Programmers |  - Sophisticated         |
|                   |                    |  - Casual / Ad-hoc       |
+-------------------+--------------------+--------------------------+

1. Database Administrator (DBA):
   • Responsible for overall administrative control, installation, configuration, security authorization, schema definition, backup/recovery, and performance tuning.
2. Database Designers:
   • Responsible for identifying data requirements, defining conceptual schemas (ER diagrams), logical schemas, constraints, and physical storage layouts prior to database deployment.
3. Application Programmers:
   • Software developers who write programs (in Java, C#, Python, PHP) that embed DML statements to interact with the database engine.
4. End Users:
   • Naive / Parametric Users: Unsophisticated users who interact through pre-built graphical user interfaces or forms without knowing underlying database structures (e.g., bank tellers, airline booking agents).
   • Sophisticated Users: Engineers, data analysts, and researchers who write complex custom queries in SQL or analytical tools to extract specialized information.
   • Casual / Standalone Users: Occasional users who access the database directly using interactive query interfaces.

Importance and Methods of Database Recovery

Importance of Database Recovery:

Database recovery is the process of restoring a database to the most recent correct and consistent state following a hardware failure, software crash, system abort, or catastrophic event. It guarantees the Atomicity and Durability properties of transactions, preventing data corruption and business financial loss.

Core Methods of Database Recovery:

                         Recovery Methods
                                |
        +-----------------------+-----------------------+
        |                                               |
Log-Based Recovery                               Non-Log Recovery
        |                                               |
  +-----+-----+                                   +-----+-----+
  |           |                                   |           |
Deferred   Immediate                           Shadow      Database
 Update     Update                             Paging       Backups
(NO-UNDO/  (UNDO/REDO)
 REDO)

1. Log-Based Recovery (Write-Ahead Logging - WAL):
   • All database modifications are first recorded sequentially in a non-volatile log file before being applied to the physical disk database pages.
   • Deferred Update (NO-UNDO / REDO): Disk writes occur only after the transaction reaches its commit point. If a crash occurs before commit, no rollback (UNDO) is needed; only REDO is executed for committed transactions.
   • Immediate Update (UNDO / REDO): Disk writes can occur while the transaction is still active. Recovery requires UNDO for uncommitted transactions and REDO for committed ones.
2. Checkpointing Technique:
   • Periodically flushes all modified dirty cache buffers to disk and records a [CHECKPOINT] log record, bounding how far back the log scanner must search.
3. Shadow Paging:
   • Maintains two page tables: a Current Page Table in memory and a Shadow Page Table on disk. Writes go to newly allocated pages; commit simply points the shadow pointer to the new root page.
4. Database Backups:
   • Periodic full and incremental off-site dumps to recover from catastrophic disk head crashes.

Importance and Syntax of Creating Views in SQL

A View is a virtual relation based on the result set of an underlying SQL query. It does not store physical data rows itself (except materialized views), but dynamically executes its query when accessed.

Importance of Views:

1. Data Security & Confidentiality: Restricts user access to specific rows and columns without exposing base tables (e.g., hiding salary column from general staff).
2. Query Simplification: Pre-packages complex joins, aggregations, and subqueries into a single reusable virtual table name.
3. Logical Data Independence: Insulates front-end client applications from changes in base table schemas.
4. Customized Presentation: Provides different user departments with personalized views of the same underlying data.

Standard SQL Syntax:

CREATE VIEW view_name [(column_list)] AS
SELECT column1, column2, ...
FROM table_name
WHERE condition
[WITH CHECK OPTION];

Concrete Example:

-- Create a view showing only IT department employees without exposing salary
CREATE VIEW IT_Staff_View AS
SELECT emp_id, name, designation, email
FROM Employee
WHERE department = 'IT'
WITH CHECK OPTION;

-- Querying the View
SELECT * FROM IT_Staff_View;

Database Security and Securing Techniques

Concept of Database Security:

Database security refers to the comprehensive collection of policies, procedures, tools, and technical safeguards designed to preserve the Confidentiality, Integrity, and Availability (CIA Triad) of database systems against unauthorized access, accidental misuse, cyber attacks, and data breaches.

How a Database is Secured:

1. Authentication & Password Management:
   • Verifying user identities via cryptographic passwords, multi-factor authentication (MFA), or enterprise directory services (LDAP/Active Directory).
2. Authorization & Access Control (DAC & RBAC):
   • Discretionary Access Control (DAC): Using SQL commands GRANT and REVOKE to assign least-privilege permissions.

   GRANT SELECT, INSERT ON student TO accountant_role;
   REVOKE DELETE ON student FROM accountant_role;
   

   • Role-Based Access Control (RBAC): Grouping privileges into functional business roles.
3. Data Encryption:
   • Encryption at Rest: Encrypting physical storage files using AES-256 (Transparent Data Encryption - TDE).
   • Encryption in Transit: Enforcing TLS/SSL for all network client-database communication.
4. Input Sanitization & Prepared Statements:
   • Defends against SQL Injection (SQLi) attacks through parameterized queries.
5. Database Auditing and Monitoring:
   • Maintaining tamper-proof audit trails logging user logins, administrative commands, and data modifications.

Importance of Normalization with Example

Importance of Normalization:

Normalization is a formal, scientific process of organizing data attributes and relations to minimize data redundancy and prevent undesirable modification anomalies:

1. Insertion Anomaly: Inability to record certain facts without artificially adding unrelated data.
2. Deletion Anomaly: Accidental loss of vital data when deleting an unrelated fact.
3. Update (Modification) Anomaly: Inconsistent data caused by updating redundant values in multiple rows.
4. Storage Efficiency: Eliminates wasteful duplicate values across records.

Illustrative Example:

Consider an unnormalized table Emp_Dept:

Anomalies Present:

• Update Anomaly: If IT changes Dept_Head, multiple rows must be updated; missing one leaves the database inconsistent.
• Insertion Anomaly: Cannot insert a newly created Department D03 (Finance) until an employee is assigned to it.
• Deletion Anomaly: If employee 103 (Hari) leaves, deleting this row permanently deletes the entire HR department record!

Normalized Solution (Decomposed into 2NF/3NF Relations):

1. Employee Relation:
   • Schema: Employee(Emp_ID [PK], Emp_Name, Dept_ID [FK])
2. Department Relation:
   • Schema: Department(Dept_ID [PK], Dept_Name, Dept_Head)

Now, departments exist independently, employee updates touch a single row, and all three anomalies are completely eliminated.

Types of Database Languages

A Database Management System provides specialized interfaces and languages to define, manipulate, control, and secure data. In relational databases (SQL), these languages are categorized into four major subsystems:

+-------------------------------------------------------------------+
|                        Database Languages                         |
+---------------+-------------------+---------------+---------------+
|      DDL      |        DML        |      DCL      |      TCL      |
| Data Def.     | Data Manipulation | Data Control  | Trans. Ctrl.  |
| CREATE, ALTER | SELECT, INSERT,   | GRANT,        | COMMIT,       |
| DROP, TRUNCATE| UPDATE, DELETE    | REVOKE        | ROLLBACK      |
+---------------+-------------------+---------------+---------------+

---

1. Data Definition Language (DDL)

• Purpose: Used to define, alter, and destroy database structures, schemas, tables, views, indexes, and constraints in the data dictionary.
• Execution: Commands are auto-committed and alter the metadata.
• Key Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.
• Example:

CREATE TABLE Department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    budget DECIMAL(12, 2) CHECK (budget > 0)
);

---

2. Data Manipulation Language (DML)

• Purpose: Used to retrieve, insert, update, and delete stored user data instances within existing relations.
• Categories:
  • Procedural DML: User specifies what data is needed and how to get it (Relational Algebra).
  • Declarative / Non-procedural DML: User specifies what data is needed without specifying algorithms (SQL).
• Key Commands: SELECT, INSERT, UPDATE, DELETE.
• Example:

-- Insert tuple
INSERT INTO Department VALUES (1, 'Computer Science', 750000.00);

-- Update salary
UPDATE Department SET budget = budget + 50000 WHERE dept_id = 1;

---

3. Data Control Language (DCL)

• Purpose: Used by administrators to control database permissions, privileges, and access rights, enforcing security policies.
• Key Commands: GRANT (confers privileges), REVOKE (cancels privileges).
• Example:

-- Grant select privilege to user 'teacher'
GRANT SELECT, INSERT ON Department TO teacher;

-- Revoke write privilege
REVOKE INSERT ON Department FROM teacher;

---

4. Transaction Control Language (TCL)

• Purpose: Manages changes made by DML operations, ensuring transaction boundaries and ACID compliance.
• Key Commands: COMMIT (permanent save), ROLLBACK (undo changes), SAVEPOINT (intermediate checkpoint).
• Example:

BEGIN TRANSACTION;
UPDATE Department SET budget = budget - 100000 WHERE dept_id = 1;
SAVEPOINT sp1;
-- If an error occurs:
ROLLBACK TO sp1;
-- When successful:
COMMIT;

Desirable Properties of Transactions (ACID Properties)

A transaction is a logical unit of work that performs operations on a database. To preserve data consistency and integrity through concurrent execution and system failures, every DBMS must enforce the four ACID properties:

+--------------------------------------------------------------------+
|                         ACID PROPERTIES                            |
+-------------------+-------------------+----------------------------+
| Property          | Meaning           | Enforced By                |
+-------------------+-------------------+----------------------------+
| A - Atomicity     | All or Nothing    | Recovery Subsystem / Logs  |
| C - Consistency   | Preserves Rules   | Application + Constraints  |
| I - Isolation     | Independent Exec. | Concurrency Control (Locks)|
| D - Durability    | Permanent Changes | Non-volatile Log / Recovery|
+-------------------+-------------------+----------------------------+

---

1. Atomicity (“All or Nothing”)

• Concept: A transaction is an indivisible unit of work. Either all its database modifications are successfully executed and permanently written, or none of them take effect.
• Example: In a fund transfer of NPR 10,000 from Account A to Account B:
  1. Deduct 10,000 from Account A.
  2. Add 10,000 to Account B. If the system crashes after step 1, the recovery manager rolls back step 1 so Account A does not lose money without Account B receiving it.
• Enforced by: Recovery Manager through undo logs.

---

2. Consistency (State Invariant Preservation)

• Concept: Execution of a transaction in isolation must preserve database consistency; it must transition the database from one valid consistent state (satisfying all integrity constraints, foreign keys, assertions) to another valid state.
• Example: The sum of balances of Account A and Account B before the transfer must equal the sum of balances after the transfer.
• Enforced by: Database integrity constraints (Primary Keys, Foreign Keys, CHECK constraints) and application logic.

---

3. Isolation (Independence of Concurrent Execution)

• Concept: Intermediate states of a transaction must remain invisible to other concurrently executing transactions. The execution of multiple concurrent transactions must result in a state equivalent to running them sequentially.
• Prevents: Dirty reads, non-repeatable reads, lost updates, and phantom reads.
• Enforced by: Concurrency Control Manager using protocols such as Two-Phase Locking (2PL) or Timestamp Ordering.

---

4. Durability (Permanence of Committed Data)

• Concept: Once a transaction commits successfully, its updates are permanently recorded in the database and will never be lost, even in the event of subsequent power failure or OS crash.
• Enforced by: Write-Ahead Logging (WAL) and non-volatile storage caching.

---

Transaction State Diagram:

                +--------------+
                |    Active    |
                +-------+------+
                        |
            +-----------+-----------+
            |                       |
            v                       v
    +---------------+       +---------------+
    |   Partially   |       |    Failed     |
    |   Committed   |       +-------+-------+
    +-------+-------+               |
            |                       |
            v                       v
    +---------------+       +---------------+
    |   Committed   |       |    Aborted    |
    +---------------+       +---------------+

Five Aggregate Functions Used in SQL

SQL aggregate functions perform a calculation on a set of values across multiple rows and return a single summary scalar value. They are extensively used with SELECT, GROUP BY, and HAVING clauses.

---

1. COUNT()

• Purpose: Returns the total number of rows matching the query criteria or non-null values in a specified column.
• Syntax: COUNT(*) | COUNT([DISTINCT] column_name)
• Example:

-- Counts total employees and distinct departments
SELECT COUNT(*) AS total_employees,
       COUNT(DISTINCT dept_id) AS total_departments
FROM Employee;

---

2. SUM()

• Purpose: Calculates the mathematical summation of all numeric values in a column, ignoring NULL values.
• Syntax: SUM([DISTINCT] column_name)
• Example:

-- Computes total salary expenditure for the IT department
SELECT SUM(salary) AS total_it_salary
FROM Employee
WHERE department = 'IT';

---

3. AVG()

• Purpose: Computes the arithmetic mean of all numeric values in a column, ignoring NULL values.
• Syntax: AVG([DISTINCT] column_name)
• Example:

-- Finds average salary per department for departments with avg > 50000
SELECT department, AVG(salary) AS average_salary
FROM Employee
GROUP BY department
HAVING AVG(salary) > 50000;

---

4. MIN()

• Purpose: Identifies the minimum (lowest) value in a numeric, string, or date/time column.
• Syntax: MIN(column_name)
• Example:

-- Returns the lowest salary in the organization
SELECT MIN(salary) AS lowest_salary FROM Employee;

---

5. MAX()

• Purpose: Identifies the maximum (highest) value in a numeric, string, or date/time column.
• Syntax: MAX(column_name)
• Example:

-- Returns the highest salary and most recent hire date
SELECT MAX(salary) AS highest_salary, MAX(hire_date) AS newest_employee
FROM Employee;

Generalization and Specialization in ER Modeling

Generalization and Specialization are advanced conceptual modeling mechanisms introduced in the Extended Entity-Relationship (EER) model to capture inheritance, sub-typing, and class hierarchies.

---

1. Specialization (Top-Down Design Approach)

• When Used: Used when a high-level entity set contains distinctive subgroups of entities that possess specialized attributes or participate in specific relationships not shared by all entities in the superclass.
• Approach: Top-down design, splitting a broad entity type into finer specialized sub-entities.
• Example: In a university database, a general entity EMPLOYEE can be specialized into FACULTY (with specific attribute rank, research_area) and STAFF (with specific attribute hourly_wage, shift).

---

2. Generalization (Bottom-Up Design Approach)

• When Used: Used when several distinct entity types share common structural characteristics (attributes and relationships) and need to be synthesized into a single higher-level generalized superclass to eliminate design redundancy.
• Approach: Bottom-up design, unifying multiple lower-level entity types into a common parent entity type.
• Example: An organization may initially identify CAR (attributes: license_no, max_speed, no_of_doors) and TRUCK (attributes: license_no, max_speed, cargo_capacity). Generalization extracts the shared attributes into a generalized superclass VEHICLE(license_no, max_speed).

---

Architectural ER Representation (IS-A Hierarchy):

                       +-------------------------+
                       |         PERSON          |
                       | (pid, name, email, dob) |
                       +------------+------------+
                                    |
                                    |
                                  / d \  [Disjoint Constraint]
                                 +-----+
                                  /   \
                                 /     \
           +--------------------+       +--------------------+
           |      CUSTOMER      |       |      EMPLOYEE      |
           | (credit_rating)    |       | (emp_id, salary)   |
           +--------------------+       +---------+----------+
                                                  |
                                                / o \ [Overlap Constraint]
                                               +-----+
                                                /   \
                       +-----------------------+     +-----------------------+
                       |        TEACHER        |     |     ADMINISTRATOR     |
                       | (courses_taught)      |     | (department_assigned) |
                       +-----------------------+     +-----------------------+

Constraints on Specialization / Generalization:

1. Disjointness Constraint:
   • Disjoint (d): An entity can belong to at most one subclass (e.g., a person cannot be both a Car and a Truck).
   • Overlap (o): An entity can simultaneously belong to multiple subclasses (e.g., an Employee can be both a Teacher and an Administrator).
2. Completeness Constraint:
   • Total Completeness (double line): Every superclass entity must belong to at least one subclass.
   • Partial Completeness (single line): Some superclass entities may not belong to any subclass.

Comprehensive ER Diagram Design: Project Management System

1. Problem Scenario Analysis

• Enterprise: A software/project company.
• Key Requirements:
  1. EMPLOYEE works on PROJECT under one or more PROJECT_MANAGER.
  2. CLIENT pays for PROJECT developed by COMPANY.
  3. CLIENT provides FEEDBACK for the PROJECT.

---

2. Identification of Entity Sets and Attributes

1. COMPANY: comp_id (PK), name, address, contact_email
2. EMPLOYEE: emp_id (PK), emp_name, email, role, salary
3. PROJECT_MANAGER: Subclass of EMPLOYEE (Specialization) with certification_level, pm_id
4. PROJECT: project_id (PK), title, start_date, end_date, budget
5. CLIENT: client_id (PK), client_name, organization, phone, email
6. PAYMENT: payment_id (PK), amount, payment_date, method, invoice_no
7. FEEDBACK: feedback_id (PK), rating, comments, submission_date

---

3. Relationships and Structural Constraints (Cardinalities)

• EMPLOYEE — Works_On — PROJECT: Many-to-Many ($M:N$). An employee can work on multiple projects; a project has multiple employees.
• PROJECT_MANAGER — Manages — PROJECT: Many-to-Many ($M:N$). A project is managed by one or more project managers; a PM can manage multiple projects.
• COMPANY — Employs — EMPLOYEE: One-to-Many ($1:N$). Total participation for employee.
• COMPANY — Develops — PROJECT: One-to-Many ($1:N$).
• CLIENT — Makes — PAYMENT: One-to-Many ($1:N$). Total participation for payment.
• PAYMENT — For_Project — PROJECT: Many-to-One ($N:1$).
• CLIENT — Submits — FEEDBACK: One-to-Many ($1:N$).
• FEEDBACK — Evaluates — PROJECT: Many-to-One ($N:1$).

---

4. Architectural Textual ER Diagram

 +------------------+             1:N              +------------------+
 |     COMPANY      |------------------------------|     EMPLOYEE     |
 | [comp_id, name]  |        (Employs)             | [emp_id, name]   |
 +--------+---------+                              +--------+---------+
          | 1:N                                             | (IS-A)
          | (Develops)                                      v
          |                                        +------------------+
          v                                        | PROJECT_MANAGER  |
 +------------------+                              | [pm_id, cert]    |
 |     PROJECT      |                              +--------+---------+
 | [project_id,     |                                       |
 |  title, budget]  |                                       |
 +---+----+-----+---+                                       |
     ^    ^     ^                                           |
     |    |     |                                           |
     |    |     +==== (Works_On) [M:N] =====================+ (Employee)
     |    |
     |    +========== (Manages)  [M:N] =====================+ (PM)
     |
     | (For_Project) [N:1]
     |                                    1:N
 +---+----+---------+             +-----------------+
 |     PAYMENT      |<------------|     CLIENT      |
 | [payment_id,     |   (Makes)   | [client_id,     |
 |  amount, date]   |             |  name, email]   |
 +------------------+             +--------+--------+
                                           | 1:N
     | (Evaluates) [N:1]                   | (Submits)
     v                                     v
 +------------------+             +-----------------+
 |     FEEDBACK     |<------------+                 |
 | [feedback_id,    |                               |
 |  rating, comment]|                               |
 +------------------+-------------------------------+

---

5. Relational Schema Conversion:

1. Company(comp_id [PK], name, address, contact_email)
2. Employee(emp_id [PK], emp_name, email, role, salary, comp_id [FK])
3. Project_Manager(pm_id [PK], emp_id [FK], certification_level)
4. Project(project_id [PK], title, start_date, end_date, budget, comp_id [FK])
5. Works_On(emp_id [FK], project_id [FK], hours_logged, PRIMARY KEY(emp_id, project_id))
6. Project_Supervision(pm_id [FK], project_id [FK], assigned_date, PRIMARY KEY(pm_id, project_id))
7. Client(client_id [PK], client_name, organization, phone, email)
8. Payment(payment_id [PK], amount, payment_date, method, client_id [FK], project_id [FK])
9. Feedback(feedback_id [PK], rating, comments, submission_date, client_id [FK], project_id [FK])

SQL Implementation for Banking Relational Database

Given Relational Database Schema:

• Account(ac_no, type, balance, branch)
• Customer(cid, name, address, phone_no, dob)
• Depositer(ac_no, cid, date)

---

i) Insert a tuple {1001, checking, 30000} in Account relation:

INSERT INTO Account (ac_no, type, balance, branch)
VALUES (1001, 'checking', 30000.00, NULL);

Note: Since branch is not supplied in the tuple, it is explicitly set to NULL or omitted in the column list.

---

ii) Delete those accounts which haven’t owned by any customer:

DELETE FROM Account
WHERE ac_no NOT IN (
    SELECT ac_no
    FROM Depositer
    WHERE ac_no IS NOT NULL
);

Alternative using NOT EXISTS:

DELETE FROM Account a
WHERE NOT EXISTS (
    SELECT 1
    FROM Depositer d
    WHERE d.ac_no = a.ac_no
);

---

iii) Change the phone_no of Ram to 11111111:

UPDATE Customer
SET phone_no = '11111111'
WHERE name = 'Ram';

---

iv) Find name of customer who owns an account with balance greater than 2000:

SELECT DISTINCT c.name
FROM Customer c
JOIN Depositer d ON c.cid = d.cid
JOIN Account a ON d.ac_no = a.ac_no
WHERE a.balance > 2000;

Subquery Formulation:

SELECT name
FROM Customer
WHERE cid IN (
    SELECT cid
    FROM Depositer
    WHERE ac_no IN (
        SELECT ac_no
        FROM Account
        WHERE balance > 2000
    )
);

---

v) Display those records of customers whose name starts with “R” and ends with “A”:

SELECT *
FROM Customer
WHERE UPPER(name) LIKE 'R%A';

Explanation: UPPER(name) ensures case-insensitive matching where R is the first character, % represents zero or more arbitrary characters, and A is the terminal character.