PART– A

1.
a. Describe what metadata are and what value they provide to the database system.
b. What are the advantages of having the DBMS between the end user’s applications and the database?
c. Discuss some considerations when designing a database.

2.
a. List and briefly describe the different types database maintenance activities.
b. Database backups can be performed at different levels. List and describe these.
c. What are the classical approaches to database design?

3.
a. Explain the differences between a centralized and decentralized approach to database design.
b. Explain how database designers design and normalize databases.
c. Explain the BCNF. How is it related to other normal forms?

4.
a. What is a schema? How many schemas can be used in one database?
b. What command is used to save changes to the database? What is the syntax for this command?How do you delete a table from the database? Provide an example.
c. What is a subquery? When is it used? Does the RDBMS deal with subqueries any differently from normal queries?

5.
a. Explain normalization and its different forms.
b. Describe the need for convenient and safe backup storage.
c. Explain user requirements gathering as part of the DBA's end‐user support services.

PART– B

1.
a. Explain heterogeneous distributed database systems.
b. A fully distributed database management system must perform all of the functions of a centralized DBMS. Do you agree? Why or why not?
c. Describe the five types of users identified in a database system.

2.
a. Describe the DBA’s managerial role.
b. What are the three basic techniques to control deadlocks?
c. How does a shared/exclusive lock schema increase the lock manager’s overhead?

3.
a. Describe a conceptual model and its advantages. What is the most widely used conceptual model?
b. What is a key and why is it important in the relational model? Describe the use of nulls in a database.
c. Explain single‐valued attributes and provide an example. Explain the difference between simple and composite attributes. Provide at least one example of each.

4.
a. Describe specialization and generalization.
b. Explain the different types of join operations. What are they and how do they work?
c. What are SQL functions and when are they used? Provide a couple of examples of situations in which they are necessary.

5.
a. Describe the characteristics of an Oracle sequence.
b. Triggers are critical to proper database operation and management. What are some of the ways that triggers are used?
c. Describe query optimization.

PART– C

1.
a. Explain ORDER BY and GROUP BY clause with an example.
b. What is the difference between a nonprocedural language and a procedural language? Give an
example of each.

2.
a. What are views? Most database management systems support the creation of views. Give
reasons.
b. How can you use the COMMIT, SAVEPOINT and ROLLBACK commands to support transactions?

3.
a. What is an index? What are the disadvantages of using an index?
b. Describe the format for the UPDATE command.

4.
a. Describe the format of the ALTER TABLE command to add a new column.
b. There are three set operations: union, intersection, difference. Define each of these operations. Which are supported by Oracle?

5.
a. Should a user be allowed to enter null values for the primary key? Give reasons for your answer.
b. What is Data Independence? Explain Logical data independence Physical data independence.

CASE STUDY - I

Laxmi Cycles, located in Ahmedabad Gujarat, sell road, mountain, hybrid leisure, and children bicycles. Currently, Laxmi Cycles purchases bikes from three suppliers but plans to add new suppliers in the near future. This rapidly growing business needs a database system to manage this information. Initially, the database should house information about suppliers and products. The database will contain two tables: a supplier table and a product table. The recorder level refers to the number of items in inventory that triggers a decision to order more items to prevent a stock out. (In other words, if the number of units of a particular item in inventory falls below the recorder level, the item should be recorded.) The user should be able to perform several queries and produce several managerial reports based on the data contained in the two tables.

Questions:

1. Design the database system for Laxmi Cycles.
2. Draw the corresponding ER Diagram for the above
3. Write a brief description of how the database could be enhanced to further improve the management of the business.

CASE STUDY-II

A Cab service company’s employees are categorized into sections. Each section has a section manager. Section No and a fixed basic salary rate is assigned to a section. They keep data on Emp‐no, NIC_No, name, address, sex, DOB, contact no. of employees. If the employee is a driver, his driving licence no should be added. Each employee is assigned only to one section. For a travel, driver is assigned to a vehicle. They keep following data about vehicles; Reg No, Insurance no, Model, vehicle category, current mileage. In this case customer is the person who is making a reservation. They keep name, NIC_no, address, contact no. regarding the customer. Once a customer reserved a cab, he cannot make any other reservations until the end of the reserved duration. Customer can reserve one or more vehicles from a reservation. They are keeping index no, payment method, amount, No. of vehicles, distance, start date and time, end date and time regarding a reservation. They are supplying various kinds of supplies to maintain vehicles. A supplier can supply more than one supplying category. Company always keeps data on additional suppliers. Every supplier has a Reg No, Name, address, contact no. An employee is responsible for one or more supplying categories. They keep data like cat_no, cat_description, cost, quantity, net cost regarding every supplying category.

Questions:
1. Design a database that stores the Cab service company’s information. Identify the entities of interest and show their attributes.
2. What relationships exist among these entities? Explain. Draw the corresponding ER Diagram.
3. Write a brief description of how the database could be enhanced to further improve management of the business.
4. In your opinion, is it advisable for the Cab service company to go for an in‐house development of DBMS? Give reasons for your answer.