IGNOU MCS-23 - Introduction to Database Management Systems

Q1. a) Draw and explain the detailed system architecture of DBMS. b) With the help of a diagram, describe the concept of client/server model. c) Explain in detail about various key constraints used in database system. Give examples for each. d) Explain the importance of NULL values in Relational Model. e) Discuss the mechanism of attribute relationship inheritance. How is it useful? f) By considering an example describe various data update operations in SQL. Give examples. g) Explain insertion, deletion and modification anomalies with suitable examples. h) State BCNF. How does it differ from 3NF? i) How can a database recover from failure when many transactions are going on? Describe with the help of an example. j) What is a distributed database management system? How is it different to that of client server database systems? Q2. Suppose you are given the following requirements for a simple database for the National Football League (NFL): • the NFL has many teams, • each team has a name, a city, a coach, a captain, and a set of players, • each player belongs to only one team, • each player has a name, a player position (such as Centre Forward, left midfield, center midfield, right midfield, left fullback, centre back etc..), a skill level, and a set of injury records, • a team captain is also a player, • a game is played between two teams (referred to as host_team and guest_team) and has a date (such as May 11th, 2000) and a score (such as 4 to 2). Construct an ER diagram for the NFL database using the Chen notation with all conventions. List your assumptions and clearly indicate the cardinality mappings as well as any role indicators in your ER diagram. Q3. Consider a “Library Management System” which keeps the following tables: Book (isbn_no, book_title, author, publisher, edition, year_of_copyright) BookAccession (accession_no, isbn_no, date_of_purchase) Members (m_id, m_name, m_address, m_phone). Issue_return (accession_no, m_id, expected_date_of_return, actual_date_of_return) Please note that a member can be issued a book for a period of 15 days. The actual-date-of-return is kept blank for the books that have not been returned. Write and run the following SQL queries on the tables: (i) Find the m_id and m_name of the members who have got maximum number of unreturned books. (ii) List the book details along with the number of copies for that book in the library (issued or not-issued both) (iii) Find the names of all those students who have got all the books issued to him of the author named “ABC” . (iv) Find the books that are expected to be returned in this week. (iv) Find those members who have not got any book issued to him/her during last six months. Make suitable assumptions, if any. Q4. Consider the Relation R={A, B, C, D, E, F, G } and the set of functional dependencies. A→F B→CD C→G F→E What is the key for R? With the help of a suitable example discuss the Insertion, Deletion and Updation anomalies which can arise if the relation is not in 2NF and 3NF ? Decompose R into 2NF, 3NF and finally in BCNF relation.

Question 1: a. What are the advantages of having three-level database architecture? How are they related to Data Independence? Explain with the help of an example. b. What are the different forms of integrity control in database management system? Describe with the help of examples. c. What is a Transaction? What are the problems of concurrent transactions? Describe with the help of examples. d. What is locking? How does it solve the problem of concurrent transaction execution? Describe with the help of examples. e. What is a distributed database management system? How is it different to that of client server database systems? Question 2: Consider that a Departmental Store needs to maintain a database system for maintaining its inventory of items. The database is needed for the following requirements: (i) To find out the item name, item-code, item description and present stock level of all or specified items. (ii) To find the information about various Vendors of the store. A vendor can supply all or some items only. Some of the information that is needed about vendor is: the Vendor Code, Vendor name, address, office phone etc. (iii) To find the information about various orders those are placed for various items to various vendors. It also keeps information about receipt of item/items and updates the inventory. (iv) To keep track of all the issues of the items to various users. Draw an ER diagram for the departmental store. Specify key attributes and constraints of each entity type and on each relationship type. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. Also design the normalized tables with required integrity and security constraints. Question 3: Consider a “Software Management System” that maintains the database using the following tables: Project (p-id, p-title, start-date, duration, c-id) Clients (c-id, c-name, c-address, c-phone) Employee (e-id, e-name, e-address, e-phone) Project-person (p-id,e-id) Please note that an employee may be working on more than one project in the organization at a time. Write and run the following SQL queries on the tables: a. Find the c-id and c-name of the clients who have offered maximum number of projects to the company. b. Find the list of all the employees and the projects they have worked from time to time. c. Find the employees who are working in more than 2 projects. d. Find the names of all those employees who have worked on all the projects of client whose cname is “ABC University”. e. Find the projects which will be completed by 31st Dec, 2022. f. Find all the clients who have not given any project. Also find the person who is not working on any project at all. g. List all the project titles along with their p_id. h. List all the Clients along with their details. i. List all the project-persons along with their details. j. List all the employees whose name starts with “A”. Note: Make suitable assumptions, if any. Question 4: Consider the Relation R= {A, B, C, D, E, F, G, H } and the set of functional dependencies. A→C     B→CG    AD→EH    C→DF    A→H What is the key for R? Decompose R into 2NF, 3NF and finally in BCNF relation. Question 5: Consider the ER diagram of Q2 and tables designed. Implement this database using MS Access or any other similar RDBMS package. You must include the following details in your implementation: (i) Normalized tables and table relationships (ii) Constraints on data values including referential Integrity constraints. (iii) Design minimum two forms and two reports. (iv) Also prepare the system catalog / data dictionary