What is functional dependency? Explain its use in database design. Consider the following schema. Student (RollNo, Branch_code, Marks_Obtained, Exam_Name, Total_Marks) Identify the functional dependencies and check whether the given schema is in 3NF or not. If not justify and convert the schema into 3NF.

Explain following Codd’s rules with suitable examples: i) Guaranteed Access Rule ii) Comprehensive Data Sub-Language Rule iii) Integrity Independence iv) Systematic Treatment of NULL Values.

What is the impact of insert, update & delete anomaly on overall design of database? How normalization is used to remove these anomalies?

What is decomposition? Consider the relation F (FN, PN, C, D) with the following Functional Dependencies: FD1: FN, PN ->C FD2: C ->D FD3: D -> F If F is decomposed in to F1 (FN,PN,C) and F2 (C,D). check decomposition is lossless or lossy?

What is recoverable schedule? Why is recoverability of schedule desirable?Are there any circumstance under Which it could be desirable to allow non recoverable schedular? Explain your answer.

State and explain the ACID properties. During its execution a transaction passes through several states, until it finally commits or aborts. List all possible sequences of states through which a transaction may pass. Explain the situations when each state transition occours.

What is R-timestamp (Q) and W-timestamp(Q). Explain the necessary condition used by time stamp ordering protocol to execute for a read/write operation.

What is conflict serializability? Check following schedule is conflict serializable or not? Also, explain the concept of conflict equivalent schedule.

List the different NOSQL data models. Explain document store NOSQL data model with example.

Draw and explain architecture of Distributed database system. State the reasons for building distributed database systems.

Explain Structured, Semi-structured and Unstructured data types with examples.

Describe the following operations with MongoDB syntax: i) Map-Reduce ii) Aggregation pipeline

What is the significance of XML databases? Explain with proper example when to use XML database.

Explain how encoding and decoding of JSON object is done JAVA with example.

Write a short note on complex data types: i) Semi-structured data ii) Features of semi-structured data models

What is Deductive Database. Explain its features and state its advantanges over traditional database.

What is the impact of insert, update and delete anomaly on overall design of database? How normalization is used to remove these anomalies?

Elaborate the signigicance of CODD’s rule. Explain 12 rules proposed by CODD’s.

What are the desirable properties of decomposition? Explain it with example.

Explain partial, full and transitive dependency with examples.

How to ensure the atomicity using Recovery Methods? Explain the log based recovery method in detail.

When do deadlocks happen, how to prevent them, and how to recover if deadlock takes place?

What is conflict serializability? How to check schedule is conflict serializable schedule? Explain with one example.

Explain two phase locking protocol. Consider the following two transactions. T1 : read (A); read (B); if A = 0 then B : = B + 1; Write (B). T2 : read (B); read (A); if B = 0 then A : = A + 1; Write (A). Add lock and unlock instructions to transactions T1 and T2, so that they observe the two phase locking protocol. Can the execution of these transactions result in a deadlock?

Explain the CAP theorem referred during the development of any distributed application.

Explain the NOSQL database types with examples and write down the real time applications.

Explain Structured, Semi-structured and Unstructured data types with examples.

List the different NOSQL data models. Explain following NOSQL database types with examples. i) Column-oriented ii) Document-oriented

What is the significance of XML databases? Explain with example the use of XML databases.

What is Deductive Database. Explain its features and state its advantages over traditional database.

Write a short note on complex data types: i) Semi-structured data ii) Features of semi-structured data modeles

Explain how encoding and decoding of JSON object is done in JAVA with example.

Justify the impact of normalization on database? Explain 2nd normal form, 3rd normal form and BCNF with example.

Elaborate the significance of codd’s rule. Explain 12 rules proposed by codd’s.

What is the impact of insert, update and delete anomaly on overall design of database? How is normalization used to remove these anomalies?

Explain 3NF and BCNF and give its example. Also enlist their differences.

Suppose a transaction Ti issues a read command on data item Q.How time-stamp based protocol decides whether to allow the operation to be executed or not using time-stamp based protocol of concurrency control. Explain in detail time stamp based protocol.

Explain the concept of conflict serializability with suitable example. Since every conflict-serializable schedule is view serializable, why do we emphasize conflict serializability rather than view serializability?

State and explain the ACID properties. During its execution a transaction passes though several states, until it finally commits or aborts. List all possible sequences of states through which a transaction may pass. Explain the situations when each state transition occurs.

A transaction may be waiting for more time for an Exclusive (X) lock on an item, while a sequence of other transactions request and are granted as Shared (S) lock on the same item. What is this problem? How is it solved by two phase lock protocol?

Explain how NOSQL databases are different than relational databases? Describe in detail the key value store NOSQL data model with example.

Explain BASE properties with its significance. How soft state of system is depending on Eventual consistency property?

List the different NOSQL data models. Explain document store NOSQL data model with example.

State and explain the concept of CAP theorem and BASE properties with example.

Write short note on : i) Active databases ii) Deductive databases

What is the significance of XML databases? Explain with proper example when to use XML database.

Difference between relational databases and object relational databases with example

Describe the significance of JSON data type and object. Discuss with syntax all JSON data types with suitable example.

What is anomaly in relational model. Explain how normalization can be used to reduce the anomalies.

Explain 2NF and 3NF and BCNF with example.

What are relational integrity constraints. Explain with example Domain constraints, Referential-Integrity and enterprise constraints.

Elaborate the significance of codd’s rule. Explain 12 rules proposed by codd’s.

Explain the concept of conflict serializability with suitable example. Since every conflict-serializable schedule is view serializable, why do we emphasize conflict serializability rather than view serializability?

Explain the two-phase lock protocol for concurrency control. Also explain its two versions: strict two-phase lock protocol and rigorous two-phase lock protocol.

What is R-timestamp(Q) and W-timestamp(Q) Explain the necessary condition used by time stamp ordering protocol to execute for a read / write operation.

To ensure atomicity despite failures we use Recovery Methods Explain in detail following Log-Based Recovery methods with example. i) Deferred Database Modifications ii) Immediate Database Modifications

Compare SQL and NOSQL Database.

Explain BASE Properties of NOSQL Database.

Explain Document Based and Key value data model of NOSQL Database.

Explain the CRUD operations used in MongoDB with example.

State and Explain CAP Theorem.

Explain Map Reduce with example.

What are spatial data. Explain Geographic and Geometric data.

What is the significance of XML databases? Explain with proper example when to use XML database.

Write a short note on complex data types : i) Semi-structured data ii) Features of semi-structured data models

What is object relational database system. Explain Table inheritance with example.

What is the impact of insert, update & delete anomaly on overall design of 8 M database? How is normalization used to remove these anomalies? Explain with suitable example.

What is functional dependency? Explain its use in database design. Consider the instance of the relation Market (MarketName, Product, Stock): Market Name Product Stock S1 Toothpaste 14 S1 Biscuits 8 S1 Shampoo 8 S2 Toothpaste 30 M1 Chocolates 50 M2 Cakes 14 Identify the functional dependencies that can be found in the given instance.

Elaborate the significance of CODD’s rule. Explain 12 rules proposed by CODD’s.

What is decomposition? Explain the desirable properties of decomposition? Consider the relation F (FN, PN, C, D) with the following Functional Dependencies: FD1: FN, PN –> C FD2: C –> D FD3:D –> F If Fig is decomposed into F1(FN, PN, C) and F2(C, D). Check decomposition is lossless or lossy?

How to ensure the atomicity using Recovery Methods? Explain the log based recovery method in detail.

What is need of lock in DBMS? Explain shared lock and exclusive lock with the help of example.

When do deadlocks happen, how to prevent them, and how to recover if deadlock takes place?

What is R-timestamp(Q) and W-timestamp(Q). Explain the necessary condition used by time stamp ordering protocol to execute for a read/ write operation.

Explain the CAP theorem referred during the development of any distributed application.

Explain how NOSQL databases are different than relational databases? Describe in detail the column NOSQL data model with example.

Draw and explain architecture of Distributed database system. State the reasons for building distributed database systems.

Explain structured, Semi-structured and Unstructured data types with examples.

What is the significance of XML databases? Explain with example the use of XML databases.

What is object relational database? What are its advantages and disadvantages?

What are spatial data? Explain Geographic and Geometric data.

Explain how encoding and decoding of JSON object is done in JAVA with example.

For the database system to be usable, it must retrieve data efficiently. The need of efficiency has led designers to use complex data structures to represent data in the database. Developers hide this complexity from the database system users through several levels of abstraction. Explain those levels of abstraction in detail.

What is Specialization and generalization in Extended E-R diagram? What is the difference between them? Why do we not display the difference in schema diagram?

Explain with example what is physical data independence. Also explain its importance.

Draw the neat diagram of Database System Structure and explain its components in detail.

A post office has few postmen who go every day to distribute letter. Every morning post office receives a large number of registered letters. the post office intends to create a database to keep track of these letters. i) Every letter has a sender, an origin post office from where it was sent, a destination post office to which it is to be sent, a date of registration, date of arrival at destination post office, receiver and a status. ii) Every sender has a name, an address. iii) Every receiver has a name, an address. iv) Every postman has a designated area where he delivers letters. v) The area consists of a set of streets under the jurisdiction of the post office. vi) Every street consists of a set of buildings. vii) Every building has number and may be name. It may be housing more than one family. viii) The status of the letter can be not yet taken for delivery, delivered, address not available, address not known, addressee did not accept the letter, redirected to the address of address and sent to the sender. Identify the relationship among the entities along with the mapping cardinalities, keys in the E.R. diagram. Construct appropriate tables for E-R diagram designed with above requirements

Consider the following schemas Emp(Emp_no, Emp_name, Dept_no) Dept(Dept_no, Dept_name) Address(Dept_name, Dept_location) Write SQL queries for the following i) Display the location of department where employee ‘Ram’ is working. ii) Create a view to store total no of employees working in each department in ascending order iii) Find the name of the department in which no employee is working.

Explain the concept of Referential and Entity Integrity constraint with example.

Explain cursor in SQL with example.

What is trigger? How to create it? Discuss various types of triggers.

Write a PL/SQL block of code which accepts the rollno from user. The attendance of rollno entered by user will be checked in student_attendance (RollNo, Attendance) table and display on the screen.

What is sequence? How to create and use sequence in SQL?

For the database system to be usable, it must retrieve data efficiently. The need of efficiency has led designers to use complex data structures to represent data in the database. Developers hide this complexity from the database system users through several levels of abstraction. Explain those levels of abstraction in detail with example.

Draw an ER diagram for the banking system. Assume the banking requirements are as given below. • The bank is organized into branches. Each branch is located in a particular city. • The bank offers two types of accounts: saving and current. Accounts can be held by more than one customer and a customer can have more than one account. • A loan originates at a particular branch and can be held by one or more customers Identify the relationship among the entities along with the mapping cardinalities, keys in the E.R. diagram. Construct appropriate tables for E-R diagram designed with above requirements.

Explain the concept of candidate key and primary key, foreign key. Identify above listed key for the following schema: Person (driver_id, name, address, contactno) Car(licence, model, year) Owns (driver_id, licence)

Draw architecture of DBMS system and explain function of following components: i) Storage manager ii) Query Processor

What is view and how to create it? Can you update view? If yes, how? If not, why not?

Defined stored procedure. Explain the creating and calling stored procedure with example.

Consider following schema. Student_fee_details (rollno, name, fee_deposited, date) Write a trigger to preserve old values of student fee details before updating in the table.

Consider the following schemes Supplier(SNO, Sname, Status, City) Parts (PNO, Pname, Color, Weight, City) Shipments(SNO,PNO,QTY) Write SQL queries for the following: i) Find shipment information (SNO, Sname, PNO, Pname, QTY) for those having quantity less than 157. ii) List SNO, Sname, PNO, Pname for those suppliers who made shipments of parts whose quantity is larger than the average quantity iii) Find aggregate quantity of PNO 1692 of color green for which shipments made by supplier number who residing Mumbai.

What is an index? What are the advantages and disadvantages of using index on a table?

What is a trigger? How to create it? Discuss various types of triggers.

Draw the neat diagram of Database System Structure and explain its components in detail.

Construct an ER Diagram for Company having following details : • Company organized into DEPARTMENT. Each department has unique name and a particular employee who manages the department. Start date for the manager is recorded. Department may have several locations. • A department controls a number of PROJECT. Projects have a unique name, number and a single location. • Company’s EMPLOYEE name, ssno, address, salary, sex and birth date are recorded. An employee is assigned to one department, but may work for several projects (not necessarily controlled by her dept). Number of hours/week an employee works on each project is recorded; The immediate supervisor for the employee. • Employee’s DEPENDENT are tracked for health insurance purposes (dependent name, birthdate, relationship to employee). Identify the relationship among the entities along with the mapping cardinalities, keys in the E.R. diagram.

A post office has few postmen who go every day to distribute letter. Every morning post office receives a large number of registered letters. The post office intends to create a database to keep track of these letters. i) Every letter has a sender, an origin post office from where it was sent, a destination post office to which it is to be sent, a date of registration, date of arrival at destination post office, receiver and a status. ii) Every sender has a name, an address. iii) Every receiver has a name and an address. iv) Every postman has a designated area where he delivers letters. v) The area consists of a set of streets under the jurisdiction of the post office. vi) Every street consists of a set of buildings. vii) Every building has number and may be name. It may be housing more than one family. viii) The status of the letter can be not yet taken for delivery, delivered, address not available, address not known, addressee did not accept the letter, redirected to the address of address and sent to the sender. Identify the relationship among the entities along with the mapping cardinalities, keys in the E.R. diagram. Construct appropriate tables for E-R diagram designed with above requirements.

List the main characteristics of the database approach and explain how it differs from the traditional file system.

Consider the following schemas Emp(Emp_no, Emp_name, Dept_no) Dept(Dept_no, Dept_name) Address(Dept_name, Dept_location) Write SQL queries for the following i) Display the location of department where employee ‘Ram’ is working. ii) Create a view to store total no of employees working in each department in ascending order. iii) Find the name of the department in which no employee is working.

What is view and how to create it? Can you update view? If yes, how? If not, why not?

Consider following schema Student_fee_details (rollno, name, fee_deposited, date) Write a trigger to preserve old values of student fee details before updating in the table.

Explain the concept of Referential and Entity Integrity constraint with example.

Write a PL/SQL block of code which accepts the rollno from user. The attendance of rollno entered by user will be checked in student_attendance(RollNo, Attendance) table and display on the screen.

What is the importance of creating constraints on the table? Explain with example any 4 constraints that can be specified when a database table is created.

Compare DBMS and File processing system in terms of data isolation, data redundancy, data inconsistency, data integrity.

A reputed general hospital has decided to computerize their operations. In the hospital many doctors are working. Personal information of doctors is maintained to get them fixed salary per month. The patients are admitted to the hospital into the room. They are treated by various doctors. Sometimes patients perform certain pathological tests which carried out into the labs. Identify the relationship among the entities along with the mapping cardinalities, keys in the E.R. diagram. Construct appropriate tables for E-R diagram designed with above requirements.

A weak entity set can always be made into a strong entity set; by adding to its attributes the primary key attributes of its identifying entity set. Outline what sort of redundancy will result if we do so while converting to tables.

What is Specialization and generalization in Extended E-R diagram? What is the difference between them? Why do we not display the difference in schema diagram?

Draw architecture of DBMS system and explain function of following components: i) Storage manager ii) Query Processor

Consider following schema Student_fee_details (rollno, name, fee_deposited, date) Write a trigger to preserve old values of student fee details before updating in the table.

What is view and how to create it? Can you update view? If yes, how? If not, why not?

Consider following schema Hotels(hotel_no,hotel_name.city) Rooms(Room_no,hotel_no,price,type) Write a PL/SQL procedure to list the price & type of all rooms at the hotel ‘TAJ’

Consider the following relation schema MOVIES(Mov_Id, Mov_Title,Mov_Year, Dir_Id) DIRECTOR(Dir_Id, Dir_Name) RATING(MOV_Id, Rev_Stars) Write the SQL queries for the following. i) List the title of all the movies directed by ‘RAJ KAPOOR’ ii) Find the name of movies and number of stars for each movie. Sort the results on movies title and from higher stars to least stars. iii) Assign the rating of all movies directed by ‘Steven Spielberg’ to 9.

What is the importance of creating constraints on the table? Explain with example any 4 constraints that can be specified when a database table is created.

What is synonym? How to create and use synonym in SQL?