IGNOU MCS-23 - Introduction to Database Management Systems

Q1. a) What is SQL? Explain its important features. b) Consider the following schemas: BOOK (Book_ID, Title, Publisher_ID, Year_of Pub, Price) AUTHOR (Author_ID, Book_ID, Author Name) PUBLISHER (Publisher_ID, Book_ID, Address, Name_of Pub, No._of Copies) Write a query in SQL for the following: (i) Find the name of authors whose books are published by "ABC Press". (ii) Find the name of the author and price of the book, whose Book_ID is '100'. (iii) Find the title of the books which are published by Publisher_ID '20' and are published in year 2011. (iv) Find the address of the publisher who has published Book_ID "500". Make suitable assumptions, if any. Q2. a) With the help of a suitable example, discuss the insertion, deletion and updation anomalies that can occur in a database. Briefly discuss the mechanism to remove such anomalies. b) Write SQL commands for each of the following. Also illustrate the usage of each command through suitable example. (i) Creation of views (ii) Creation of sequences (iii) Outer join (iv) To give access permission to any user Q3. a) What are integrity constraints? Discuss the various types of integrity constraints that can be imposed on database. b) How are database security and database integrity related? Briefly discuss the different levels of security measures which may be considered to protect the database. c) Consider the relation R (A, B, C, D, E) and the set of functional dependencies :- F(A → D, {A,B} → C, D → E) Assume that the decomposition of R into {R1 (A, B, C) and R2 (A, D, E)}. Is this decomposition lossless? Justify? Q4. a) Explain the Log-based recovery scheme with the help of an example. b) Compute the closure of the following set F of functional dependencies for relation schema R = (A, B, C, D, E). A → BC CD → E B → D E → A List the candidate keys for R. Q5. a) Give the limitations of file based system. How can they be overcome using DBMS? b) Discuss the importance of file organisation in databases. Mention the different types of file organisations available. Discuss any one of the mentioned file organisations in detail. Q6. a) For what reasons is 2-phase locking protocol required? Explain. Discuss the disadvantages of basic 2-phase locking protocol. List the ways and means that can be used to overcome the disadvantages. b) List and explain the 4 basic properties of a Transaction with the help of appropriate examples. Q7. a) What do you mean by fragmentation of a database? What is the need of fragmentation in DDBMS environment? Explain different types of fragmentation with an example of each. b) Explain the need of Distributed DBMS over Centralized DBMS. Also give the structure of Distributed DBMS. Q8. An organization needs to provide Medical facilities to its employees and their dependents. Organization is having a list of Doctors, Hospitals and Test centres for the employees facility. An employee may get Medical facility from the list of Doctors, Hospitals and Test centres provided by the organization to them. Employee does not need to pay anything for the facilities availed. The Doctors, Hospitals and Test centres directly raise their bill to the organization. Identify the entities, relationships, constraints and cardinality and construct an ER diagram for the above mentioned specifications. List your assumptions and clearly indicate the cardinality mappings as well as any role indicators in your ER diagram.

Q1: a) Give the limitations of file based system. How can they be overcome using DBMS? b) Discuss the 3-level architecture of DBMS. Explain how it leads to data independence. c) What are integrity constraints? Discuss the various types of integrity constraints that can be imposed on database. d) What is the two phase locking protocol? How does it guarantee Serializability? Explain. e) Discuss the anomalies due to insertion, updation and deletion in a relation that is not in QNF. Illustrate with the help of an example. f) List and explain the 4 basic properties of a Transaction with the help of appropriate examples. g) Explain the Log-based recovery scheme with the help of an example. h) Explain the need of Distributed DBMS over Centralized DBMS. Also give the structure of Distributed DBMS. i) Write SQL commands for each of the following. Also illustrate the usage of each command through suitable example. (i) Creation of views (ii) Creation of sequences (iii) Outer join (iv) To give access permission to any user Q2: A bookshop has a huge collection of books to sell them online and therefore requires a database to track its sales. For each book they store the Title, Author(s), name, Publisher, Volume, ISBN No., Price, Stock (no. of copies), Year of publication, etc. To help the customers to search the book, they require that each book is assigned to one or more categories such as Engineering, Sciences, Fiction, Literature, Applications, etc. If at all, any discounts that are there for certain books, need to be notified on the site (best-buy offer). To buy a book, a customer needs to register on the site. Also it maintains the profile of the user and also their earlier purchases. The bookshop also sends "Newsletter" to all the registered users to update them about the publications. Identify the entities, relationships, constraints and cardinality and construct an ER diagram for the above mentioned specifications. List your assumptions and clearly indicate the cardinality mappings as well as any role indicators in your ER diagram. Q3: Consider the following tables: WORKS(Pname, Cname, Salary) LIVES(Pname, Street, City) LOCATED(Cname, City) MANAGER(Pname, Mname) Write a query in SQL for the following: (i) List the names of the people who work for the company Wipro' along with the cities they live in. (ii) Find the people who work for the company `Infosys' having salary greater than 50,000. (iii)List the names of the people, along with the street and city addresses. (iv) Find the persons whose salaries are more than that of all of the 'Oracle' employees. (v) Find the names of the persons who do not work in `Infosys'. Make suitable assumptions, if any. Q4: (a) Compute the closure of the following set F of functional dependencies for relation schema R = (A, B, C, D, E). A → BC CD → E B → D E → A List the candidate keys for R. (b) Consider the relation R (A, B, C, D, E) and the set of functional dependencies :- F(A → D, {A,B} → C, D → E) Assume that the decomposition of R into {R1 (A, B, C) and R2 (A, D, E)}. Is this decomposition lossless? Justify?