Database Management System

Describe major drawbacks of file processing system that led to the development of DBMS.

Define data independence. Explain logical and physical data independence with examples.

Draw $a^n$ ER diagram for $a^n$ online pharmacy that manages medicines supplied by manufacturers and distributed via warehouses. The pharmacy also records online sales of medicines through a virtual shopping cart. Medicines have a unique Drug_ID, price, generic name, and expiration date. Manufacturers have a name, location, contact number, and website. Warehouses have a warehouse code, address, and contact phone. Customer details include name, address, email, and phone number. Assume any other necessary attributes and relationships to make the system complete. Explain how you can convert a many to many relationship in $a^n$ ER diagram to a relational schema diagram along with $a^n$ illustrative example.

What are the different types of keys? A hospital plans to develop a hospital management system. The system should maintain patient details and allow patients to book appointments with doctors. A patient can book many appointments, but each appointment is linked to one patient and one doctor. Similarly, a doctor can attend many appointments. Doctors work in departments. Each department can have many employees, doctors, nurses, and other staffs, but each belongs to only one department. Patients may receive treatment from multiple doctors, and a doctor may treat multiple patients. Doctors can also issue prescriptions to patients; a doctor can write many prescriptions, and a patient can receive many prescriptions. If a patient requires hospitalization, the patient may be admitted multiple times, but each admission is associated with only one patient. For every admission, exactly one bill is generated, and each bill corresponds to only one admission. Design $a^n$ ER diagram for this system, clearly representing the entities, their attributes, and the relationships with appropriate cardinalities.

Consider the following relational database model: [3×2] FARMER (FarmerID, FarmerName, Address, Phone) FARM (FarmID, Location, Area, FarmerID) CROP (CropID, CropName, Season) CULTIVATION (FarmID, CropID, Year, QuantityProduced) MARKET (MarketID, MarketName, City) SALE (SaleID, FarmID, CropID, MarketID, SaleDate, Amount) Write relational algebra expressions for the following queries: (a) Retrieve the names of farmers who cultivate crops during the "Winter" season. (b) Find the farm locations where no crop cultivation has been recorded. (c) List the CropID and CropName of crops that are cultivated in more than one farm.

Considering the same database in question 3, write SQL expressions for the following queries: [3×2] (a) Display the crop name and total quantity produced for each crop, showing only those crops whose total production exceeds 1,000 units. (b) Consider there is a database user FarmOfficer. Grant insert and update privilege and Revoke delete privilege from FarmOfficer on FARMER table. (c) Write stored procedure to insert a new record in FARMER table using $a^n$ INSERT SQL query.

Consider the following relational schema. Student (Rno, Sname, Address, city, Mobile) Department (Did, Dname) Academic (Rno, Did, SPI, CPI, Backlog_count) Guide (Rno, Project_name, Fid) Faculty (Fid, Faculty_name, Subject, Did, Salary) Write relational algebra for the following: [$4\times 2$] (a) List the name of students with their department name having backlog 0. (b) List the name of faculties with their department name and salary having salary more than 125,000 and belongs to "BCT" department. (c) Insert a new data (10, 'Dhawa', 'Prithivi Chowk', 'Pokhara', '9891123456'). (d) List the name of students with their department name doing project "Chatbot" under the guidance (faculty_name) of "Ram".

Consider the above relational schema. [$4\times 2$] (a) Write a SQL to count the faculty per department only if the department have more than 5 employees. (b) Write a SQL query to display the total salary of teachers according to department name in descending order of total salary. (c) Write SQL to update the salary of employees by 15% if salary is less than 50 k, by 10% if salary is in between 50 k to 100 k and by 5 % if salary is greater than 100 k. (d) Write SQL query to show the details of employee with $2^{\text{nd}}$ highest earning.

Why is normalization necessary? Explain. What are the criteria for a relational schema to be in BCNF and what factors differentiate it from 3NF.

How do you convert $a^n$ un-normalized database schema to 3NF? Explain in detail with the help of $a^n$ example.

In a given relational schema(A,B,C,D,E) we have the following FDs • $A\to B$ • $B\to ACE$ • $C\to D$ Find attribute closure of AB. Using the concept of attribute closure, determine the combination of keys that best fit to be candidate keys.

Explain with diagram about process of query processing in RDBMS. How are equivalence rules for relational algebra helpful for query optimization? Explain with example.

What is indexing in a database? Explain primary indexing and secondary indexing with example.

Explain the working of B+ tree index along with suitable example. Differentiate between static hashing and dynamic hashing along with examples.

Explain ACID properties in database transaction with an example. Define deadlock and explain how wait-die scheme and wound-wait scheme helps to prevent deadlock.

Explain the state transition diagram for database transactions. Consider a schedule having 4 transaction having data items A, B and C ordered as $R1(A);R2(A);W1(A);R3(B);W2(A);R4(C);W3(B);R1(C);W4(C)$ Check whether the schedule is conflict serializable or not.

What are the types of failures in database? What is check pointing in a database system? Explain its purpose and describe how it is performed to improve database recovery.

Explain in detail the working of log-based recovery method.

Write short notes of NoSQL and distributed databases.

Write short notes on: (Any Two) [2x4] (a) Object oriented database (b) Serializability (c) Functional dependency and its types

What are the different steps involved in query processing? Explain with an example.

What are the criteria for a relational schema to be in BCNF and what factors differentiate it from 3NF.

Explain the basic steps in query processing. Make distinctions between cost based optimization and heuristic optimization.

Why is normalization necessary? Explain.

Explain primary indexing and secondary indexing with example.

Explain the basic steps in query processing. Make distinctions between cost based optimization and heuristic optimization.

How does Log Based Recovery help in recovering from DBMS crashes? Explain with $a^n$ example.