IGNOU MCS-207 Database Management Systems - IGNOU Solved Assignment (Latest)

IGNOU MCS-207 (July 2025 – January 2026) Assignment Questions

Question 1: 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.

Question 2: (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.

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.

(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

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

(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.

Question 4: Write short notes on any four of the following, explaining their purpose and key features. Provide an example where applicable.

(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

IGNOU MCS-207 (July 2024 – January 2025) Assignment Questions

Question 1: (Covers Block 1)

(a) Explain the three level DBMS architecture with the help of an example. Also, explain the concept of data independence in the context of database systems with the help of an example.
(b) Explain the following terms in the context of a relational model with the help of one example of each– Super key, Domain, Cartesian Product, Primary Key, Natural join, Set Intersection, Set Difference operation and referential integrity constraint.
(c) A University maintains the list of the books available in its library using a database system. In addition, this system is used for issue and return of books to its students. This database is used to find the following details by the students of the university and the staff of the library:

• List of the classification number, ISBN number, Title, Author Names, Subject Area of the books.
• Searching of books using subject area, Title and Author name.
• List of books that are issued to a specific student.
Draw an ER diagram for the library. Specify key attributes and constraints on each entity type and on each relationship type. Note any unspecified requirements 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 how the secondary index can be created in a file. Also, explain the advantages and disadvantages of using secondary indexes. When should you use secondary Indexes? Give reasons in support of your answer.

Question 2: (Covers Block 2)

(a) Consider a Relation: Student (EnrolNo, StudentName, ProgrammeCode, ProgrammeName, CourseCode, CourseName, Grade). Some of the constraints on the relation Student are:

• A student is assigned one unique Enrolment Number (EnrolNo).
• Two students may have same name.
• A student can register in one Programme only
• A Programme consists of several compulsory courses.
• Grades can be “A”, “B”, “C”, or “D” and only one grade value is recorded for a student in a course.

Perform the following task 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 showing possible redundancies.
(iv) Decompose R into 2NF and 3NF relations.
(b) Explain the concept of Multi-valued dependency and Join dependency with the help of an example of each. Also, explain the 4th Normal Form and 5th Normal form.
(c) Explain the following terms with the help of an example of each – Assertion, Cursor, Stored Procedure, Triggers.
(d) Consider the following relational database:

Customer (custId, custName, custAddress, custPhone)
Account (AccountNumber, custId, TypeOfAccount, Balance)
Transaction (DateTimeofTransaction, AccountNumber, DebitORCredit, Amount)
The underlined attribute(s) in the relation forms the primary key. In relation Customer, the custID is the unique identifier of a customer. The purposes of other attributes in Customer relation are self explanatory. You may define the domain of different attributes. The TypeOfAccount attribute can take the value (“Saving”, “Current”, “Salary”, “Other”). Please note that at a specific time of a date only one transaction can be performed from an account. Please also note that the Account relation has foreign key custID and Transaction relation has foreign key AccountNumber. 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 in the first 2 tables and 20 records in the 3rd table.
(iii) List “Saving” Account details showing the AccountNumber, custName, custPhone, Balance of all the accounts. These records should be shown in the order of custName.
(iv) Find all the transactions made by customer whose custID is “240002”.
(v) Find the list of those customers who have more than one account.
(vi) Find the total of Debit transactions made for Account Number “A0054”
(vii) Find the total of balance of all the accounts of a customer.
(viii) Find the list of customers whose name start with an alphabet “B”
(ix) List the pair of customers who share the same phone number.
(x) Find the list of the customers, who have not made any credit transaction since 1st January, 2023.

Question 3: (Covers Block 3)

(a) Explain the ACID properties of transactions with the help of an example of each.
(b) What are the problems that can be encountered, if the three transactions (given in Figure 1) are run concurrently? Explain with the help of different transaction schedules of these transactions.

(c) What is 2-Phase locking? Lock and unlock various data items of the transactions, given in Figure 1, using 2-Phase locking such that no concurrency related problem occurs, when the transactions A, B and C are executed concurrently.
(d) Explain the Log based Recovery with the help of an example. What are Redo and Undo operations. Why do you need checkpoint? Explain the process of recovery with check points with the help of an example.
(e) Explain with the help of an example, how recovery is performed when Deferred database modification scheme is used.
(f) What is the cost of selection operation, when the index scan method is used? Explain with the help of an example. Explain the cost of Join operation when Merge-Join method is used.
(g) Make the query tree for the following query (assume the database of problem 2(d)).
SELECT c.custName, c. custId, a.AccountNumber, t.DebitORCredit
FROM Customer c, Account a, Transaction t
WHERE c.custId = a.custId AND a.AccountNumber = t.AccountNumber
AND Amount > 10000;

Question 4: (Covers Block 4)

(a) What are object-relational database? Explain the concept of complex data types used in these databases. Explain the object model in the context of Object Oriented database systems. How objectoriented database systems are different from Object-relational database systems?
(b) Explain the multi-dimensional data model of a data warehouse. Also, define the concept of decision tree with the help of an example. List any four applications of data mining.
(c) Explain the need of NoSQL databases. Explain the characteristics of any two types of NoSQL databases.
(d) Write short notes on the following database technologies:

(i) Distributed databases
(ii) Block chain databases