Scribd
Upload
56 views2 pages

DBMS Assignment Test Key Answers

The document provides answers to an assignment test on Database Management Systems, covering topics such as ER diagrams for a library database, DBMS component modules, relational algebra queries for employee and student tables, and SQL queries for bank and shopping mall databases. It includes details on entities, relationships, and constraints in the ER diagram, as well as various SQL commands for data manipulation. The content is structured in a question-and-answer format, addressing specific database operations and queries.

Uploaded by

akshayandani05
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
56 views2 pages

DBMS Assignment Test Key Answers

The document provides answers to an assignment test on Database Management Systems, covering topics such as ER diagrams for a library database, DBMS component modules, relational algebra queries for employee and student tables, and SQL queries for bank and shopping mall databases. It includes details on entities, relationships, and constraints in the ER diagram, as well as various SQL commands for data manipulation. The content is structured in a question-and-answer format, addressing specific database operations and queries.

Uploaded by

akshayandani05
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Database Management Systems (BCS403) - Assignment Test Key Answers

Q1. ER Diagram for Library Database


- Entities & Primary Keys:
1. Book (Book_ID as Primary Key)
2. Member (Member_ID as Primary Key)
3. Librarian (Librarian_ID as Primary Key)
4. Issued_Books (Loan_ID as Primary Key)
5. Fine (Fine_ID as Primary Key)
- Relationships & Constraints:
- A Member can borrow multiple Books but one Book can be issued to only one Member at a time.
- Issued_Books links Book and Member.
- Fine is linked to Issued_Books via Loan_ID.
- Librarian maintains Books and Issued_Books.
- Structural constraints: (1:N, M:N relationships are depicted in ER Diagram)

Q2. DBMS Component Modules & Interactions


- Diagram: Shows how modules interact.
- Modules:
1. Query Processor: Parses, translates, and optimizes SQL queries.
2. Storage Manager: Handles disk storage and retrieval.
3. Transaction Manager: Ensures ACID properties.
4. Buffer Manager: Manages memory for efficient access.
5. Database Engine: Executes queries and maintains integrity.
6. Catalog Manager: Stores metadata about tables, constraints, views.

Q3. Relational Algebra Queries for Employee Table


1. Projection (Name, Salary) where Department='IT' (Employee)
2. Projection (ID, Name) where Department='IT' AND Salary>7000 (Employee)
3. Projection (ID, Name) where Department='HR' OR Salary>6500 (Employee)
4. Projection (Name) where Department!='IT' (Employee)
5. Projection (Name, Department) (Employee JOIN Department)

Q4. Relational Algebra Queries for Student Table


1. Rename Student as Learner
2. Projection (Name) where NOT EXISTS (Student.ID = Enrollment.Student_ID)
3. Projection (Name) where NOT EXISTS (Project - Enrollment)
4. Same as Q2 (Duplicate)
5. Projection (Name) where Project='Data Science' AND Project='Web Dev' (Enrollment)

Q5. SQL Queries for Bank Database


1. SELECT Customer_Name FROM ACCOUNTS WHERE Balance > 40000;
2. SELECT COUNT(*) FROM ACCOUNTS WHERE Account_Type='Savings';
3. SELECT * FROM ACCOUNTS ORDER BY Balance DESC;

Q6. SQL Queries for Shopping Mall Database


1. INSERT INTO PRODUCTS VALUES (204, 'Laptop', 55000, 'Electronics', 10);
2. UPDATE PRODUCTS SET Price = Price * 1.10 WHERE Category='Clothing';
3. DELETE FROM PRODUCTS WHERE Stock < 20;

You might also like