Database Management System (1333204) - Winter 2024 Solution

Question 1(a) [3 marks]

#

Define: Field, Record, Metadata

Answer:

• Field: A single unit of data representing one attribute of an entity
• Record: Collection of related fields that store data about an entity
• Metadata: Data about data that describes the structure, properties, and relationships of database objects

Mnemonic: “FRaMe” (Field, Record, Metadata)

Question 1(b) [4 marks]

#

Define: strong and weak entity set.

Answer:

Mnemonic: “SWing” (Strong is With own identity, weak is Not Getting own identity)

Question 1(c) [7 marks]

#

Explain 3 Levels of Data Abstraction

Answer:

Diagram:

graph LR
    A[View Level] --> B[Conceptual Level]
    B --> C[Physical Level]
    A1[End Users] --> A
    B1[Database Designers] --> B
    C1[System Administrators] --> C

Mnemonic: “PCV” (Physical, Conceptual, View - bottom to top)

Question 1(c) OR [7 marks]

#

Explain advantages and disadvantages of DBMS.

Answer:

Mnemonic: “BASIC-DV” (Benefits: Access, Security, Independence, Consistency - Drawbacks: Vulnerability)

Question 2(a) [3 marks]

#

Explain select operation in relational algebra with example

Answer:

Mnemonic: “SERVe” (Select Exactly Required Values)

Question 2(b) [4 marks]

#

Define Primary, Foreign, Super, Candidate Keys in DBMS.

Answer:

Mnemonic: “PFSC” (Person First Shows Credentials)

Question 2(c) [7 marks]

#

Draw E R Diagram of Library Management System.

Answer:

erDiagram
    BOOK {
        string book_id PK
        string title
        string author
        string publisher
        int year
    }
    MEMBER {
        string member_id PK
        string name
        string email
        string phone
        date join_date
    }
    ISSUE {
        string issue_id PK
        date issue_date
        date return_date
    }
    LIBRARIAN {
        string staff_id PK
        string name
        string position
    }
    BOOK ||--o{ ISSUE : "is_issued"
    MEMBER ||--o{ ISSUE : "borrows"
    LIBRARIAN ||--o{ ISSUE : "processes"

Mnemonic: “LIMB” (Library Items, Members, Borrowing)

Question 2(a) OR [3 marks]

#

Explain union operation in relational algebra with example.

Answer:

Example: Students_CS ∪ Students_IT

Mnemonic: “CUP” (Combining Union of Parts)

Question 2(b) OR [4 marks]

#

Define Composite attribute and Multivalued attribute with example

Answer:

Diagram:

graph TD
    A[Person] --> B[Address]
    B --> C[Street]
    B --> D[City]
    B --> E[State]
    A --> F[Phone Numbers]
    F --> G[Number 1]
    F --> H[Number 2]
    F --> I[Number n...]

Mnemonic: “CoMbo” (Composite has Multiple components)

Question 2(c) OR [7 marks]

#

Draw E R Diagram of College Management System.

Answer:

erDiagram
    STUDENT {
        string student_id PK
        string name
        date dob
        string email
        string phone
    }
    DEPARTMENT {
        string dept_id PK
        string name
        string hod
    }
    COURSE {
        string course_id PK
        string title
        int credits
        string semester
    }
    FACULTY {
        string faculty_id PK
        string name
        string designation
        string qualification
    }
    ENROLLMENT {
        string enrollment_id PK
        date enroll_date
        string grade
    }
    DEPARTMENT ||--o{ STUDENT : "enrolls"
    DEPARTMENT ||--o{ COURSE : "offers"
    DEPARTMENT ||--o{ FACULTY : "employs"
    STUDENT ||--o{ ENROLLMENT : "registers"
    COURSE ||--o{ ENROLLMENT : "includes"
    FACULTY ||--o{ COURSE : "teaches"

Mnemonic: “DECFS” (Departments, Enrollments, Courses, Faculty, Students)

Question 3(a) [3 marks]

#

List different data types in SQL and Explain in brief

Answer:

Mnemonic: “NCDBB” (Numbers, Characters, Dates, Booleans, Binaries)

Question 3(b) [4 marks]

#

Explain any two DDL Commands with Syntax and Example

Answer:

Diagram:

graph LR
    A[DDL Commands] --> B[CREATE]
    A --> C[ALTER]
    B --> D[Creates new database objects]
    C --> E[Modifies existing database objects]

Mnemonic: “CAD” (Create And Define)

Question 3(c) [7 marks]

#

Write the Output of Following Query. a. CEIL(123.57), CEIL(4.1) b. MOD(12,4), MOD(10,4) c. POWER(2,3), POWER(3,3) d. ROUND(121.413,1), ROUND(121.413,2) e. FLOOR(25.3),FLOOR(25.7) f. LENGTH(‘AHMEDABAD’) g. ABS(-25),ABS(36)

Answer:

Mnemonic: “CMPRFLA” (Ceiling, Modulus, Power, Round, Floor, Length, Absolute)

Question 3(a) OR [3 marks]

#

Explain any three Date Functions.

Answer:

Mnemonic: “AMS” (Add_months, Months_between, Sysdate)

Question 3(b) OR [4 marks]

#

Explain any two DML Commands with Syntax and Example

Answer:

Diagram:

graph LR
    A[DML Commands] --> B[INSERT]
    A --> C[UPDATE]
    B --> D[Adds new records]
    C --> E[Modifies existing records]

Mnemonic: “IUM” (Insert, Update, Manipulate)

Question 3(c) OR [7 marks]

#

For the table: EMP(emp_no, emp_name, designation, salary, deptno), Write SQL commands for following operations.

Answer:

Mnemonic: “CSDAACD” (Create, Select, Display, Aggregate, Add, Change, Delete)

Question 4(a) [3 marks]

#

List different aggregate functions and explain any one with syntax and example.

Answer:

Example for AVG:
AVG(column_name) - Calculates average of values in column
SELECT AVG(salary) FROM Employee; - Returns average salary

Mnemonic: “SCAMM” (Sum, Count, Avg, Max, Min)

Question 4(b) [4 marks]

#

Define the transaction with example.

Answer:

Example:

BEGIN TRANSACTION;
    UPDATE Accounts SET balance = balance - 5000 WHERE acc_no = 'A123';
    UPDATE Accounts SET balance = balance + 5000 WHERE acc_no = 'B456';
COMMIT;

Mnemonic: “TAPS” (Transaction As Process Set)

Question 4(c) [7 marks]

#

What is an Operator in SQL? Explain Arithmetic and Logical operators with Syntax and Example

Answer:

SQL Examples:

-- Arithmetic
SELECT product_name, price * 1.18 AS price_with_tax FROM Products;

-- Logical
SELECT * FROM Employees WHERE (salary > 30000 AND dept = 'IT') OR (experience > 5);

Mnemonic: “ASMDOLA” (Add, Subtract, Multiply, Divide, OR, AND, NOT)

Question 4(a) OR [3 marks]

#

List different numeric functions and explain any one with syntax and example.

Answer:

Example for ROUND:
ROUND(number, decimal_places) - Rounds number to specified decimal places
SELECT ROUND(125.679, 2) FROM DUAL; - Returns 125.68

Mnemonic: “RTCFA” (Round, Truncate, Ceiling, Floor, Absolute)

Question 4(b) OR [4 marks]

#

List various database operations of a transaction.

Answer:

Diagram:

graph LR
    A[BEGIN] --> B[READ/WRITE operations]
    B --> C{Success?}
    C -->|Yes| D[COMMIT]
    C -->|No| E[ROLLBACK]

Mnemonic: “BRWCRS” (Begin, Read, Write, Commit, Rollback, Savepoint)

Question 4(c) OR [7 marks]

#

What is join? Explain different types of joins with syntax and example.

Answer:

Diagram:

graph TD
    A[Types of Joins] --> B[INNER JOIN]
    A --> C[LEFT JOIN]
    A --> D[RIGHT JOIN]
    A --> E[FULL JOIN]
    A --> F[SELF JOIN]

Mnemonic: “ILRFS” (Inner, Left, Right, Full, Self)

Question 5(a) [3 marks]

#

Convert the customer relation into 1NF shown below. Customer

Answer:

Customer Table (1NF):

Mnemonic: “AFM” (Atomic values, Flatten Multivalued attributes)

Question 5(b) [4 marks]

#

List and Explain ACID properties of transaction.

Answer:

Diagram:

graph TD
    A[ACID Properties] --> B[Atomicity]
    A --> C[Consistency]
    A --> D[Isolation]
    A --> E[Durability]
    B --> F[All or Nothing]
    C --> G[Valid State]
    D --> H[Concurrent Safety]
    E --> I[Permanent Changes]

Mnemonic: “ACID” (Atomicity, Consistency, Isolation, Durability)

Question 5(c) [7 marks]

#

List different types of functional dependencies and explain each using example.

Answer:

Diagram:

graph LR
    A[StudentID] --> B[DeptID]
    B --> C[DeptName]
    A -->|Transitive| C
    D[CourseID, StudentID] -->|Partial| E[CourseName]

Mnemonic: “TNPTMv” (Trivial, Non-trivial, Partial, Transitive, Multivalued)

Question 5(a) OR [3 marks]

#

Convert the Depositor_Account relation into 2NF shown below. Where functional dependencies(FD) are as under, FD1: {cid, ano} → {access_date, balance, bname} FD2: ano → {balance, bname}

Depositor_Account

Answer:

Account Table (2NF):

Depositor Table (2NF):

Mnemonic: “RPKD” (Remove Partial Key Dependencies)

Question 5(b) OR [4 marks]

#

Explain conflict serializability.

Answer:

Diagram:

graph LR
    A[T1] -->|Write X before Read X| B[T2]
    B -->|Write Y before Read Y| C[T3]
    C -->|No cycle - Serializable| D[Serial Equivalent]

Mnemonic: “COGS” (Conflict Operations Graph Serializable)

Question 5(c) OR [7 marks]

#

Explain 3NF normalization with example

Answer:

Violation Example:

Employee(EmpID, EmpName, DeptID, DeptName, Location)

3NF Conversion:

Employee(EmpID, EmpName, DeptID)
Department(DeptID, DeptName, Location)

Diagram:

graph LR
    A[Original Table] --> B[1NF: Atomic values]
    B --> C[2NF: Remove partial dependencies]
    C --> D[3NF: Remove transitive dependencies]

Mnemonic: “APTN” (Atomic values, Partial dependencies removed, Transitive dependencies removed, Normalized)