IGNOU MCS-207 Database Management Systems - Latest Solved Assignment

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