Course Code : MCS-207

Course Title : Database Management Systems

Assignment Number : BCA_NEWOL(III)-207/Assignment/2025-26
Maximum Marks : 100
Weightage : 30%
Last Date of Submission : 31st October,2025(For July 2025 Session)
30th April, 2026 (For January 2026 Session)

There are four questions in this assignment, which carry 80 marks. Rest 20 marks are for viva
voce. You may use illustrations and diagrams to enhance the explanation. Please go through the
guidelines regarding assignments given in the Programme Guide for the format of the
presentation. The answer to each part of the question should be confined to about 300 words.
Make suitable assumptions, if any.

Question 1: (Covers Block 1) (4+4+4+4+4= 20 Marks)

a) What are the limitations of file-based systems? How does a database approach resolve those limitations?
Also, explain the physical architecture of a database management system (DBMS).

b) Explain the following terms in the context of a relational model with the help of one example of each:

(i) Key constraints

(ii) Domain constraints
(iii) Candidate key
(iv) Select operation
(v) Project Operation
(vi) Equijoin Operation
(vii) Set Difference operation
(viii) Referential Integrity constraint

c) A departmental store maintains the inventory of all the items that it sells using a database system. In
addition, this system is used for keeping a record of the sales of items to its registered customers. This
database is used to find the following details by the staff of the departmental store:
 List of the items that are out of stock.
 List of customers and the items purchased by them.
 List of all the items in the store.

Draw an ER diagram for the departmental store. Specify key attributes and constraints on each entity type
and on each relationship type. Note any unspecified requirement and make appropriate assumptions to
make the specification complete.

d) Design normalised tables in 3NF for the ER diagram drawn in part (c), with the required integrity
constraints.

e) Explain the role of the primary index in a database system. Also, compare the primary indexes with the
secondary indexes. What are clustering Indexes? Explain with the help of an example.

5
Question 2: (Covers Block 2) (4+4+2+10= 20 Marks)
(a) Consider a Relation: Course (CourseCode, CourseName, ProgrammeCode, ProgrammeName,
CourseCredit, CourseDuration, ProgrammeDuration, ProgrammeCredit). Some of the constraints on
the relation Course are:
 CourseCode uniquely identifies a course.
 ProgrammeCode is a unique code of a programme. A programme consists of many courses. A
course can be part of multiple programmes.
 A Programme consists of compulsory courses and optional courses. To complete a Programme, a
student must complete all the compulsory courses and optional courses, as per the total credit
requirements of a Programme.
Perform the following tasks for the relation given above:
(i) What is the key to the relation?
(ii) Identify and list the functional dependencies in the relation.
(iii) Make an instance of this relation consisting of at least 8 to 10 records, showing possible
redundancies.
(iv) Decompose the relation Course into 2NF and 3NF relations.

(b) What is multi-valued dependency? Explain with the help of an example. How can it be used to decompose
a relation into the 4th Normal Form? Explain with the help of an example. Also, explain the concept of
the Join dependency with the help of an example.

(c) Explain the following terms with the help of an example of each: Views, Embedded SQL, Triggers and
Dynamic SQL.

(d) Consider the following relational database:

Member (memberId, memberName, memberAddress, memberPhone)
BookIssued (memberID, BookID, IssueDate, returnDate)
Book (BookId, BookTitle, FirstAuthor)
The underlined attribute(s) in the relations given above form the primary key. In relation Member the
attribute memberId is the unique identifier of a member. The purposes of other attributes in the Member
relation are self-explanatory. You may define the domain of different attributes. Please note that the
BookIssued relation has two foreign keys, namely memberID and BookID, which reference the Member
and Book relations, respectively. Write and run the following SQL queries on the database:

(i) Create the tables with the primary and foreign key constraints.
(ii) Insert at least 5 records each in the Member and Book tables and about 10 records in the
BookIssued table.
(iii) List the name and phone number of all the members in lexicographical order.
(iv) Find all the Books issued to the member whose memberId is “0001”.
(v) Find the list of those members who have not returned more than one book. (Assume that a book is
not returned if returnDate is NULL)
(vi) Find the number of books whose first author’s name is “XYZ”.
(vii) Find the pair of members who have the same memberPhone.
(viii) Find the list of Books whose title includes the term “Database”.
(ix) Find the title of the book that has been issued the most.
(x) Find the list of members who have not issued any books.

6
Question 3:
(a) What are the ACID (Atomicity, Consistency, Isolation, Durability) properties of a transaction?
Explain each property with a suitable example related to an online banking fund transfer.
(4 Marks)

(b) Consider the following schedule involving two transactions, T1 and T2, where A and B are data
items with initial values A=1000, B=2000.

Time T1 T2
t1 READ(A)
t2 A = A - 100
t3 READ(A)
t4 A = A * 1.1
t5 WRITE(A)
t6 READ(B)
t7 WRITE(A)
t8 READ(B)
t9 B = B + 100
t10 WRITE(B)

(i) What is the final value of A and B after this schedule? (4 Marks)
(ii) Is this schedule serializable? Justify your answer. Identify the specific concurrency problem (e.g.,
Lost Update, Dirty Read) that occurs here. (4 Marks)

(c) Explain the Two-Phase Locking (2PL) protocol. How does it ensure serializability? Does 2PL
prevent deadlocks? Explain with an example of how a deadlock can occur even when 2PL is used.
(8 Marks)

Question 4:

Write short notes on any four of the following, explaining their purpose and key features. Provide an
example where applicable. (4 x 5 = 20 Marks)

(i) Distributed Databases vs. Centralized Databases

(ii) The Star Schema in Data Warehousing
(iii) NoSQL Databases (Explain one type, e.g., Document, Key-Value, or Graph)
(iv) Query Optimization in RDBMS
(v) Log-Based Recovery and the role of Checkpoints