Database Management System (1333204) - Summer 2024 Solution

Question 1(a) [3 marks]

#

Define: DBMS, Instance, Metadata

Answer:

• DBMS (Database Management System): Software that enables users to create, maintain, and access databases by controlling data organization, storage, retrieval, security, and integrity.
• Instance: The actual data stored in a database at a particular moment in time. It’s the current state or snapshot of a database.
• Metadata: Data about data that describes database structure, including tables, fields, relationships, constraints, and indexes.

Mnemonic: “DIM view” - Database system, Instance snapshot, Metadata description

Question 1(b) [4 marks]

#

Define and Explain with example: 1.Entity 2. Attribute

Answer:

Table: Entity vs Attribute

Diagram:

erDiagram
    STUDENT {
        int student_id
        string name
        string address
    }
    BOOK {
        string ISBN
        string title
        string author
    }

Mnemonic: “EA-PC” - Entities Are Physical/Conceptual, Attributes Provide Characteristics

Question 1(c) [7 marks]

#

Write the full form of DBA. Explain the roles and responsibilities of DBA.

Answer:

DBA stands for Database Administrator.

Table: DBA Responsibilities

Diagram:

mindmap
  root((DBA))
    Design
      Schema
      Tables
      Relationships
    Security
      Users
      Permissions
      Encryption
    Performance
      Query Optimization
      Indexing
      Monitoring
    Backup
      Regular Backups
      Recovery Plans
    Maintenance
      Updates
      Space Management

Mnemonic: “SPMBU” - Security, Performance, Maintenance, Backup, Updates

Question 1(c) OR [7 marks]

#

Explain relational and network data models in detail.

Answer:

Table: Relational vs Network Data Models

Diagram:

graph TD
    subgraph "Relational Model"
    A[Table: Students] --- B[Table: Courses]
    A --- C[Table: Grades]
    end
    
    subgraph "Network Model"
    D[Record: Student] --> E[Record: Course1]
    D --> F[Record: Course2]
    F --> G[Record: Grade]
    end

Mnemonic: “RSPEN” - Relational uses Sets, Pointers Enable Networks

Question 2(a) [3 marks]

#

Draw figure and Explain Generalization.

Answer:

Generalization: The process of extracting common characteristics from two or more entities to create a new higher-level entity.

Diagram:

classDiagram
    Vehicle <|-- Car
    Vehicle <|-- Truck
    Vehicle <|-- Motorcycle
    
    class Vehicle{
        +vehicle_id
        +manufacturer
        +year
    }
    class Car{
        +num_doors
        +fuel_type
    }
    class Truck{
        +cargo_capacity
        +towing_capacity
    }
    class Motorcycle{
        +engine_size
        +type
    }

Mnemonic: “BUSH” - Bottom-Up Shared Hierarchy

Question 2(b) [4 marks]

#

Explain Primary Key and Foreign Key Constraints.

Answer:

Table: Primary Key vs Foreign Key

Diagram:

erDiagram
    DEPARTMENT {
        int dept_id PK
        string dept_name
    }
    EMPLOYEE {
        int emp_id PK
        string name
        int dept_id FK
    }
    DEPARTMENT ||--o{ EMPLOYEE : "has"

Mnemonic: “PURE FIRE” - Primary Uniquely References Entities, Foreign Imports Referenced Entities

Question 2(c) [7 marks]

#

Construct an E-R diagram for Hospital Management System.

Answer:

E-R Diagram for Hospital Management System:

erDiagram
    PATIENT ||--o{ APPOINTMENT : makes
    DOCTOR ||--o{ APPOINTMENT : conducts
    APPOINTMENT ||--o{ PRESCRIPTION : generates
    DEPARTMENT ||--o{ DOCTOR : employs
    ROOM ||--o{ PATIENT : admits
    
    PATIENT {
        int patient_id PK
        string name
        string address
        date DOB
        string phone
    }
    DOCTOR {
        int doctor_id PK
        string name
        string specialization
        int dept_id FK
    }
    DEPARTMENT {
        int dept_id PK
        string name
        string location
    }
    APPOINTMENT {
        int app_id PK
        int patient_id FK
        int doctor_id FK
        datetime date_time
        string status
    }
    PRESCRIPTION {
        int pres_id PK
        int app_id FK
        date date
        string medications
    }
    ROOM {
        int room_id PK
        string type
        boolean availability
    }

Mnemonic: “PADRE” - Patients Appointments Doctors Rooms Entities

Question 2(a) OR [3 marks]

#

Draw figure and Explain Specialization.

Answer:

Specialization: The process of creating new entities from an existing entity by adding unique attributes to distinguish them.

Diagram:

classDiagram
    Employee --> FullTime
    Employee --> PartTime
    
    class Employee{
        +emp_id
        +name
        +address
        +phone
    }
    class FullTime{
        +salary
        +benefits
    }
    class PartTime{
        +hourly_rate
        +hours_worked
    }

Mnemonic: “TDSB” - Top-Down Specialized Breakdown

Question 2(b) OR [4 marks]

#

Explain single valued v/s multi-valued attributes with suitable examples.

Answer:

Table: Single-valued vs Multi-valued Attributes

Diagram:

erDiagram
    EMPLOYEE {
        int emp_id
        string name
        date birth_date "Single-valued"
    }
    EMPLOYEE ||--o{ PHONE_NUMBERS : has
    EMPLOYEE ||--o{ SKILLS : possesses
    
    PHONE_NUMBERS {
        int emp_id
        string phone_number "Multi-valued"
    }
    SKILLS {
        int emp_id
        string skill "Multi-valued"
    }

Mnemonic: “SOME” - Single One, Multiple Entries

Question 2(c) OR [7 marks]

#

Construct an E-R diagram for Banking Management System.

Answer:

E-R Diagram for Banking Management System:

erDiagram
    CUSTOMER ||--o{ ACCOUNT : owns
    ACCOUNT ||--o{ TRANSACTION : has
    BRANCH ||--o{ ACCOUNT : maintains
    EMPLOYEE }|--|| BRANCH : works_at
    LOAN }o--|| CUSTOMER : takes
    
    CUSTOMER {
        int customer_id PK
        string name
        string address
        string phone
        string email
    }
    ACCOUNT {
        int account_no PK
        int customer_id FK
        int branch_id FK
        float balance
        string type
        date opening_date
    }
    TRANSACTION {
        int trans_id PK
        int account_no FK
        date trans_date
        float amount
        string type
        string description
    }
    BRANCH {
        int branch_id PK
        string name
        string location
        string manager
    }
    EMPLOYEE {
        int emp_id PK
        string name
        string position
        float salary
        int branch_id FK
    }
    LOAN {
        int loan_id PK
        int customer_id FK
        float amount
        float interest_rate
        date start_date
        date end_date
    }

Mnemonic: “CABLE” - Customers Accounts Branches Loans Employees

Question 3(a) [3 marks]

#

Explain WHERE and DESC clause with example.

Answer:

Table: WHERE and DESC Clauses

Diagram:

-- Original data in Students table
| ID | Name   | Marks |
|----|--------|-------|
| 1  | Alice  | 85    |
| 2  | Bob    | 92    |
| 3  | Carol  | 78    |
| 4  | David  | 65    |

-- Using WHERE: SELECT * FROM Students WHERE Marks > 80
| ID | Name   | Marks |
|----|--------|-------|
| 1  | Alice  | 85    |
| 2  | Bob    | 92    |

-- Using DESC: SELECT * FROM Students ORDER BY Marks DESC
| ID | Name   | Marks |
|----|--------|-------|
| 2  | Bob    | 92    |
| 1  | Alice  | 85    |
| 3  | Carol  | 78    |
| 4  | David  | 65    |

Mnemonic: “WDF” - Where filters Data, DESC orders First-highest

Question 3(b) [4 marks]

#

List DDL commands. Explain any two DDL commands with examples.

Answer:

DDL (Data Definition Language) Commands:

1. CREATE
2. ALTER
3. DROP
4. TRUNCATE
5. RENAME

Table: CREATE and ALTER Commands

CodeBlock:

-- CREATE example
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    dept VARCHAR(30),
    salary DECIMAL(10,2)
);

-- ALTER example
ALTER TABLE employees 
ADD COLUMN hire_date DATE;

Mnemonic: “CADTR” - Create Alter Drop Truncate Rename

Question 3(c) [7 marks]

#

Perform the following Query on the table “Company” having the field’s eno, ename, salary, dept in SQL. 1. Display all records in Company table. 2. Display only dept without duplicate value. 3. Display all records sorted in descending order of ename. 4. Add one new column “cityname” to store city. 5. Display name of all employees who do not stay in city “Mumbai”. 6. Delete all employees having salary less than 10,000. 7. Display the employee names starts with “A”.

Answer:

CodeBlock:

-- 1. Display all records in Company table
SELECT * FROM Company;

-- 2. Display only dept without duplicate value
SELECT DISTINCT dept FROM Company;

-- 3. Display all records sorted in descending order of ename
SELECT * FROM Company ORDER BY ename DESC;

-- 4. Add one new column "cityname" to store city
ALTER TABLE Company ADD COLUMN cityname VARCHAR(50);

-- 5. Display name of all employees who do not stay in city "Mumbai"
SELECT ename FROM Company WHERE cityname != 'Mumbai';

-- 6. Delete all employees having salary less than 10,000
DELETE FROM Company WHERE salary < 10000;

-- 7. Display the employee names starts with "A"
SELECT ename FROM Company WHERE ename LIKE 'A%';

Table: SQL Operations

Mnemonic: “SODA-WDL” - Select Order Distinct Alter - Where Delete Like

Question 3(a) OR [3 marks]

#

Explain SELECT and DISTINCT clause with example.

Answer:

Table: SELECT and DISTINCT Clauses

Diagram:

-- Original data in Departments table
| dept_id | dept_name |
|---------|-----------|
| 1       | Sales     |
| 2       | IT        |
| 3       | HR        |
| 4       | IT        |
| 5       | Sales     |

-- Using SELECT: SELECT dept_name FROM Departments
| dept_name |
|-----------|
| Sales     |
| IT        |
| HR        |
| IT        |
| Sales     |

-- Using DISTINCT: SELECT DISTINCT dept_name FROM Departments
| dept_name |
|-----------|
| Sales     |
| IT        |
| HR        |

Mnemonic: “SUD” - Select Unique with Distinct

Question 3(b) OR [4 marks]

#

List DML commands. Explain any two DML commands with examples.

Answer:

DML (Data Manipulation Language) Commands:

1. INSERT
2. UPDATE
3. DELETE
4. SELECT

Table: INSERT and UPDATE Commands

CodeBlock:

-- INSERT example
INSERT INTO employees (emp_id, name, dept, salary)
VALUES (101, 'John Smith', 'IT', 65000);

-- UPDATE example
UPDATE employees 
SET salary = 70000 
WHERE emp_id = 101;

Mnemonic: “IUDS” - Insert Update Delete Select

Question 3(c) OR [7 marks]

#

Write the Output of Following Query. 1. ABS(-34),ABS(16) 2. SQRT(16),SQRT(64) 3. POWER(5,2), POWER(2,4) 4. MOD(15,3), MOD(13,3) 5. ROUND(123.456,1), ROUND(123.456,2) 6. CEIL(122.6), CEIL(-122.6) 7. FLOOR(-157.5),FLOOR(157.5)

Answer:

Table: SQL Function Outputs

Diagram:

graph TD
    A[SQL Math Functions]
    A --> B["ABS: Absolute value<br>ABS(-34) = 34<br>ABS(16) = 16"]
    A --> C["SQRT: Square root<br>SQRT(16) = 4<br>SQRT(64) = 8"]
    A --> D["POWER: Exponents<br>POWER(5,2) = 25<br>POWER(2,4) = 16"]
    A --> E["MOD: Remainder<br>MOD(15,3) = 0<br>MOD(13,3) = 1"]
    A --> F["ROUND: Round decimal<br>ROUND(123.456,1) = 123.5<br>ROUND(123.456,2) = 123.46"]
    A --> G["CEIL: Round up<br>CEIL(122.6) = 123<br>CEIL(-122.6) = -122"]
    A --> H["FLOOR: Round down<br>FLOOR(-157.5) = -158<br>FLOOR(157.5) = 157"]

Mnemonic: “ASPRCF” - Absolute Square Power Remainder Ceiling Floor

Question 4(a) [3 marks]

#

List data types in SQL. Explain 1.VARCHAR() and 2.INT() data types with example.

Answer:

SQL Data Types Categories:

1. Numeric (INT, FLOAT, DECIMAL)
2. Character (CHAR, VARCHAR)
3. Date/Time (DATE, TIME, DATETIME)
4. Binary (BLOB, BINARY)
5. Boolean (BOOL)

Table: VARCHAR and INT Data Types

CodeBlock:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100)
);

Mnemonic: “VIA” - Variable strings, Integers for Ages

Question 4(b) [4 marks]

#

Explain 2NF (Second Normal Form) with example and solution.

Answer:

2NF Definition: A relation is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key.

Table: Before 2NF

Problem: Non-prime attributes (course_name, instructor) depend only on course_id, not the entire key (student_id, course_id).

Diagram: 2NF Solution

erDiagram
    ENROLLMENT {
        string student_id PK
        string course_id PK
    }
    COURSE {
        string course_id PK
        string course_name
        string instructor
    }
    ENROLLMENT }o--|| COURSE : references

Table: After 2NF Enrollment Table:

Course Table:

Mnemonic: “PFPK” - Partial Functional dependency on Primary Key

Question 4(c) [7 marks]

#

Explain function dependency. Explain Partial function dependency with example.

Answer:

Functional Dependency: Relationship between attributes where one attribute’s value determines another attribute’s value.

Notation: X → Y (X determines Y)

Partial Functional Dependency: When a non-prime attribute depends on part of a composite key rather than the whole key.

Table: Order Details (Before Normalization)

Functional Dependencies:

• (order_id, product_id) → quantity
• product_id → product_name
• product_id → price

Diagram:

flowchart TD
    A["(order_id, product_id)"] -->|"Fully determines"| B[quantity]
    C[product_id] -->|"Partially determines"| D[product_name]
    C -->|"Partially determines"| E[price]
    
    style C fill:#f9f,stroke:#333,stroke-width:2px
    style D fill:#bbf,stroke:#333,stroke-width:2px
    style E fill:#bbf,stroke:#333,stroke-width:2px

Solution (Normalized Tables): Orders Table:

Products Table:

Mnemonic: “PDPK” - Partial Dependency on Part of Key

Question 4(a) OR [3 marks]

#

Explain commands: 1) To_Char() 2) To_Date()

Answer:

Table: Conversion Functions

CodeBlock:

-- TO_CHAR examples
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;  -- '14-JUN-2024'
SELECT TO_CHAR(1234.56, '$9,999.99') FROM DUAL;    -- '$1,234.56'

-- TO_DATE examples
SELECT TO_DATE('2024-06-14', 'YYYY-MM-DD') FROM DUAL;
SELECT TO_DATE('14/06/24', 'DD/MM/YY') FROM DUAL;

Mnemonic: “DCS” - Date Conversion Strings

Question 4(b) OR [4 marks]

#

Explain Full function dependency with example.

Answer:

Full Functional Dependency: When an attribute is functionally dependent on a composite key, and dependent on the entire key, not just part of it.

Table: Exam Results

Full Functional Dependency:

• (student_id, course_id) → score (score depends on both student and course)

Diagram:

flowchart LR
    A["(student_id, course_id)"] -->|"Fully determines"| B[score]
    
    style A fill:#f9f,stroke:#333,stroke-width:2px
    style B fill:#bbf,stroke:#333,stroke-width:2px

Explanation: The score attribute fully depends on the composite key (student_id, course_id) because:

• Different students can have different scores for the same course
• Same student can have different scores for different courses
• We need both student_id and course_id to determine a specific score

Mnemonic: “FCEK” - Fully dependent on Complete/Entire Key

Question 4(c) OR [7 marks]

#

Define normalization. Explain 1NF (First Normal Form) with example and solution.

Answer:

Normalization: Process of organizing data to minimize redundancy, improve data integrity, and eliminate anomalies by dividing larger tables into smaller related tables.

1NF Definition: A relation is in 1NF if all attributes contain atomic (indivisible) values only.

Table: Before 1NF

Problems:

• Non-atomic values (multiple courses per cell)
• Cannot easily query or update specific courses

Diagram:

flowchart LR
    A[Non-1NF Table] --> B[Problem: Multiple values in one column]
    B --> C[Solution: Each value in separate row]
    C --> D[1NF Table]

Table: After 1NF

Mnemonic: “ASAV” - Atomic Single-value Attributes only Valid

Question 5(a) [3 marks]

#

Explain the concept of Transaction with example.

Answer:

Transaction: A logical unit of work that must be either completely executed or completely undone.

Table: Transaction Properties

Example:

-- Bank Account Transfer Transaction
BEGIN TRANSACTION;
    -- Deduct $500 from Account A
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 'A';
    
    -- Add $500 to Account B
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 'B';
    
    -- If both operations successful
    COMMIT;
    -- If any operation fails
    -- ROLLBACK;
END TRANSACTION;

Mnemonic: “ACID” - Atomicity Consistency Isolation Durability

Question 5(b) [4 marks]

#

Explain equi join with syntax and example.

Answer:

Equi Join: A join that uses equality comparison operator to match records from two or more tables based on a common field.

Syntax:

SELECT columns
FROM table1, table2 
WHERE table1.column = table2.column;

-- Alternative syntax (explicit JOIN)
SELECT columns
FROM table1 JOIN table2
ON table1.column = table2.column;

Table Example: Employees Table:

Departments Table:

CodeBlock:

-- Equi Join Example
SELECT e.name, d.dept_name, d.location
FROM employees e, departments d
WHERE e.dept_id = d.dept_id;

Result:

Diagram:

graph TD
    subgraph Employees
    E1[emp_id: 101<br>name: Alice<br>dept_id: 1]
    E2[emp_id: 102<br>name: Bob<br>dept_id: 2]
    E3[emp_id: 103<br>name: Carol<br>dept_id: 1]
    end
    
    subgraph Departments
    D1[dept_id: 1<br>dept_name: HR<br>location: New York]
    D2[dept_id: 2<br>dept_name: IT<br>location: Chicago]
    D3[dept_id: 3<br>dept_name: Finance<br>location: Boston]
    end
    
    E1-->|equals|D1
    E2-->|equals|D2
    E3-->|equals|D1

Mnemonic: “MEET” - Match Equal Elements Every Table

Question 5(c) [7 marks]

#

Explain Conflict Serializability in detail.

Answer:

Conflict Serializability: A way to ensure correctness of concurrent transactions by guaranteeing that the execution schedule is equivalent to some serial execution.

Table: Key Concepts in Conflict Serializability

Diagram:

graph LR
    A[Conflict Serializable Schedule] --> B{Is the precedence graph acyclic?}
    B -->|Yes| C[Equivalent to some serial schedule]
    B -->|No| D[Not conflict serializable]
    
    subgraph "Example Precedence Graph"
    direction LR
    T1 --> T2
    T2 --> T3
    end
    
    subgraph "Cycle Example (Not Serializable)"
    direction LR
    T4 --> T5
    T5 --> T6
    T6 --> T4
    end

Example: Consider transactions T1 and T2:

• T1: Read(A), Write(A)
• T2: Read(A), Write(A)

Schedule S1: R1(A), W1(A), R2(A), W2(A) - Serializable (equivalent to T1→T2) Schedule S2: R1(A), R2(A), W1(A), W2(A) - Not serializable (contains cycle in precedence graph)

Steps to Determine Conflict Serializability:

1. Identify all pairs of conflicting operations
2. Construct the precedence graph
3. Check if the graph has cycles
4. If no cycles, the schedule is conflict serializable

Mnemonic: “COPS” - Conflicts, Operations, Precedence, Serializability

Question 5(a) OR [3 marks]

#

Explain the properties of Transaction with example.

Answer:

ACID Properties of Transactions:

Table: ACID Properties

Diagram:

graph TD
    A[ACID Properties] --> B[Atomicity]
    A --> C[Consistency]
    A --> D[Isolation]
    A --> E[Durability]
    
    B --> B1[All or Nothing]
    C --> C1[Valid State Transition]
    D --> D1[Concurrent Execution]
    E --> E1[Permanent Changes]

Example:

-- ATM Withdrawal Transaction
BEGIN TRANSACTION;
    -- Check balance
    SELECT balance FROM accounts WHERE account_id = 'A123';
    
    -- If sufficient, update balance
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A123';
    
    -- Record the withdrawal
    INSERT INTO transactions (account_id, type, amount, date)
    VALUES ('A123', 'WITHDRAWAL', 100, SYSDATE);
    
    -- If all operations successful
    COMMIT;
    -- If any operation fails
    -- ROLLBACK;
END TRANSACTION;

Mnemonic: “ACID” - Atomicity Consistency Isolation Durability

Question 5(b) OR [4 marks]

#

Write the Queries using set operators to find following using given “Faculty” and “CT” tables. 1. List the name of the persons who are either a Faculty or a CT. 2. List the name of the persons who are a Faculty as well as a CT. 3. List the name of the persons who are only a Faculty and not a CT. 4. List the name of the persons who are only a CT and not a Faculty.

Answer:

Table Data: Faculty Table:

CT (Class Teacher) Table:

CodeBlock:

-- 1. List the name of the persons who are either a Faculty or a CT
SELECT FacultyName AS Name FROM Faculty
UNION
SELECT CTName AS Name FROM CT;

-- 2. List the name of the persons who are a Faculty as well as a CT
SELECT FacultyName AS Name FROM Faculty
INTERSECT
SELECT CTName AS Name FROM CT;

-- 3. List the name of the persons who are only a Faculty and not a CT
SELECT FacultyName AS Name FROM Faculty
MINUS
SELECT CTName AS Name FROM CT;

-- 4. List the name of the persons who are only a CT and not a Faculty
SELECT CTName AS Name FROM CT
MINUS
SELECT FacultyName AS Name FROM Faculty;

Diagram:

graph TD
    subgraph Faculty
        F1[Ronak]
        F2[Kinjal]
        Both1[Prakash]
        Both2[Rakesh]
    end

    subgraph CT
        C1[Jigar]
        C2[Gunjan]
        Both1
        Both2
    end

Results:

1. UNION: Prakash, Ronak, Rakesh, Kinjal, Jigar, Gunjan
2. INTERSECT: Prakash, Rakesh
3. MINUS (Faculty - CT): Ronak, Kinjal
4. MINUS (CT - Faculty): Jigar, Gunjan

Mnemonic: “UIMM” - Union Intersect Minus Minus

Question 5(c) OR [7 marks]

#

Explain View Serializability in detail.

Answer:

View Serializability: A schedule is view serializable if it is view equivalent to some serial schedule, meaning it produces the same “view” (or final state) of the database.

Table: Comparison with Conflict Serializability

Diagram:

graph LR
    A[View Serializable Schedules] -->|subset of| B[All possible schedules]
    C[Conflict Serializable Schedules] -->|subset of| A
    
    subgraph "View Equivalence Requirements"
    D[Initial Reads Match]
    E[Final Writes Match]
    F[Read-Write Dependencies Match]
    end

View Equivalence Conditions:

1. Initial Reads: If T1 reads an initial value of data item A in schedule S1, it must also read the initial value in S2.
2. Final Writes: If T1 performs the final write on data item A in S1, it must also perform the final write in S2.
3. Read-Write Dependency: If T1 reads a value of A written by T2 in S1, it must also read the value written by T2 in S2.

Example of View Serializable but not Conflict Serializable Schedule: Consider transactions with blind writes (writes without reading):

• T1: W1(A)
• T2: W2(A)

Schedule S: W1(A), W2(A) - View serializable to both T1→T2 and T2→T1 (final write is always T2) But W1(A) and W2(A) conflict, so a conflict graph would have an edge in both directions.

Mnemonic: “IRF” - Initial reads, Result writes, Final view