BCA PAPER CODE BCAC401 SEM -4 [2024]

 

MAULANA ABUL KALAM AZAD UNIVERSITY OF TECHNOLOGY, WEST BENGAL

Paper Code: BCAC401
Database Management System
UPID: 400086

Time Allotted: 3 Hours
Full Marks: 70

The figures in the margin indicate full marks.
Candidates are required to give their answers in their own words as far as practicable.

---

Group-A (Very Short Answer Type Questions)

Answer any ten of the following: (1 × 10 = 10)

1. Data about data is known as ______.

   • Answer: Metadata.

2. "Not applicable" condition in a relation is represented as ______.

   • Answer: NULL.

3. Command to add attributes to an existing relation: ______.

   • Answer: ALTER TABLE.

4. Symbol used to select a tuple in relational algebra: ______.

   • Answer: σ (sigma).

5. One cause of file system failure: ______.

   • Answer: Data redundancy or Data inconsistency.

6. Main goal of indexing: ______.

   • Answer: Speed up data retrieval.

7. A transaction proceeds only after the concurrency control manager ______ the lock.

   • Answer: Grants.

8. In the relational model, rows are called ______.

   • Answer: Tuples.

9. Entity set without sufficient attributes for a primary key is a ______.

   • Answer: Weak entity set.

10. Command to remove records without affecting table structure: ______.

    • Answer: DELETE.

11. A relation is in 2NF if it has no ______ dependencies.

    • Answer: Partial functional.

12. Files of unordered records are called ______.

    • Answer: Heap files.

---

Group-B (Short Answer Type Questions)

Answer any three of the following: (5 × 3 = 15)

2. Data Abstraction in DBMS

• Definition: Hiding complex implementation details from users.

• Levels:

  1. Physical: How data is stored (files, indexes).

  2. Logical: Tables, relationships (schema).

  3. View: User-specific data presentation.

3. Mapping Constraints

• Types:

  • One-to-One (1:1): A student has one unique ID.

  • One-to-Many (1:N): A department has many employees.

  • Many-to-Many (M:N): Students enroll in multiple courses.

4. DDL vs. DML vs. DCL

Category	Purpose	Examples
DDL	Define structure	CREATE, ALTER, DROP
DML	Manipulate data	SELECT, INSERT
DCL	Control access	GRANT, REVOKE

5. Functional Dependency

• Full FD: {StudentID, CourseID} → Grade (both keys needed).

• Trivial FD: {StudentID} → StudentID (always true).

6. Data Independence

• Physical: Change storage without affecting apps (e.g., switching from HDD to SSD).

• Logical: Change schema without altering apps (e.g., adding a column).

---

Group-C (Long Answer Type Questions)

Answer any three of the following: (15 × 3 = 45)

7. Database Schema vs. Instance

• Schema: Blueprint (tables, relationships). Changes rarely.

• Instance: Actual data at a time. Changes frequently.

8. ER Diagram for a Company

Entities:

• Department (Name, Manager, StartDate).

• Project (Name, Number, Location).

• Employee (Name, ID, Salary, Supervisor).

Relationships:

• Manages (1:1), Controls (1:N), Works_On (M:N with Hours/Week).

9. Relational Algebra Queries (SBR Example)

(i) Sailors reserving ≥2 boats:

Copy

Download

π Sname (σ COUNT(Bid)≥2 (Sailors ⨝ Reserves))  

(ii) Sailors >20 years not reserving red boats:

Copy

Download

π Sname (σ Age>20 (Sailors)) - π Sname (Sailors ⨝ Reserves ⨝ σ Color='Red' (Boats))  

10. Keys in DBMS

• Primary Key: Unique identifier (e.g., StudentID).

• Candidate Key: Potential primary keys (e.g., Email or SSN).

• Foreign Key: Links tables (e.g., DeptID in Employee table).

11. DBMS vs. File System

• DBMS: Centralized, avoids redundancy, supports transactions.

• File System: Decentralized, prone to inconsistency.

**********************************End of Paper************************************

Solutions

********************************************************************************

Group-A (Very Short Answer Type Questions)

Solutions (Any 10):

1. Metadata

   • Data describing other data (e.g., table definitions in a catalog).

2. NULL

   • Represents missing/unknown data in a relational table.

3. ALTER TABLE

   • Example: ALTER TABLE Students ADD COLUMN Phone VARCHAR(10);

4. σ (sigma)

   • Relational algebra selection operator:

     Copy

     Download

     σ_(Age>20)(Students)

5. File System Failure Cause

   • Data redundancy (duplicate data) or inconsistency (conflicting updates).

6. Indexing Goal

   • Accelerate query performance (e.g., B-tree indexes).

7. Grants

   • The concurrency control manager grants locks to transactions to ensure isolation.

8. Tuples

   • Rows in a relational table.

9. Weak Entity Set

   • Depends on a strong entity (e.g., Dependent relies on Employee).

10. DELETE

    • Removes rows: DELETE FROM Employees WHERE Salary < 30000;

11. Partial Functional Dependencies

    • Non-prime attribute depends on part of a composite key (violates 2NF).

12. Heap Files

    • Unordered records with no indexing.

---

Group-B (Short Answer Type Questions)

Solutions (Any 3):

2. Data Abstraction in DBMS

• Levels:

  1. Physical: Storage details (files, indexes).

  2. Logical: Schema (tables, relationships).

  3. View: User-specific interfaces.

• Purpose: Simplify user interaction while hiding complexity.

3. Mapping Constraints

• Types:

  • 1:1: One passport per person (Passport ↔ Person).

  • 1:N: One department has many employees (Department → Employee).

  • M:N: Students enroll in courses (resolved via junction table).

4. DDL vs. DML vs. DCL

Type	Purpose	Example
DDL	Define structure	CREATE TABLE Employees (...);
DML	Manipulate data	INSERT INTO Employees VALUES (...);
DCL	Manage permissions	GRANT SELECT ON Employees TO Alice;

5. Functional Dependency

• Full FD: {StudentID, CourseID} → Grade (needs both keys).

• Trivial FD: {StudentID} → StudentID (always true).

6. Data Independence

• Physical: Change storage (e.g., SSD → HDD) without app changes.

• Logical: Add a column (e.g., Employees.Address) without modifying queries.

---

Group-C (Long Answer Type Questions)

Solutions (Any 3):

7. Database Schema vs. Instance

• Schema:

  • Structure (tables, constraints).

  • Rarely changes (e.g., adding a Birthdate column).

• Instance:

  • Current data (rows).

  • Changes frequently (e.g., inserting new employees).

8. ER Diagram for a Company

Entities:

• Department (DeptID, Name, ManagerStartDate).

• Project (ProjID, Name, Location).

• Employee (EmpID, Name, Salary, SupervisorID).

Relationships:

• Manages (1:1 between Employee and Department).

• Controls (1:N from Department to Project).

• Works_On (M:N between Employee and Project with attribute Hours/Week).

Diagram:

Copy

Download

[Department]-(Manages)-[Employee]  
[Department]-(Controls)-[Project]  
[Employee]-(Works_On)-[Project]

9. Relational Algebra (SBR Example)

(i) Sailors reserving ≥2 boats:

Copy

Download

π Sname (σ COUNT(Bid)≥2 (Sailors ⨝ Reserves))  

(ii) Sailors >20 years not reserving red boats:

Copy

Download

π Sname (σ Age>20 (Sailors)) - π Sname (Sailors ⨝ Reserves ⨝ σ Color='Red' (Boats))  

10. Keys in DBMS

• Primary Key: StudentID in Students table.

• Candidate Key: Email or SSN (could be primary keys).

• Foreign Key: DeptID in Employees referencing Departments.

11. DBMS vs. File System

Aspect	DBMS	File System
Data Redundancy	Minimized via normalization	High (duplicate files)
Consistency	Enforced via ACID transactions	No guarantees
Querying	SQL (structured)	Manual search (unstructured)

---

Key Concepts Summary

1. Normalization:

   • 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies).

2. SQL Commands:

   • DDL (CREATE, ALTER), DML (SELECT, INSERT), DCL (GRANT).

3. ER Modeling:

   • Entities, weak/strong entities, relationships (1:1, 1:N, M:N).

4. Relational Algebra:

• σ (select), π (project), ⨝ (join), ∪ (union).

Thanks

SK Institute

New chat