Define the following terms: Instance, Schema, and Metadata.
[3 marks]Write down all the advantages of DBMS over traditional file processing systems and explain any one in detail.
[4 marks]What is the difference between a Database user and a Database administrator? Explain various functions of a Database Administrator.
[7 marks]Explain lossless decomposition in the normalization process.
[3 marks]Explain the natural join operation in a relational database with an example.
[4 marks]Define relational algebra. Consider the following schema and represent given statements in relation algebra form. Branch (branch_name, branch_city) Account (branch_name, acc_no, balance) Depositor (Customer_name, acc_no) ( i ) Find out list of customer who have account at ‘abc’ branch. ( ii ) Find out all customer who have account in ‘Ahmedabad’ city and balance is greater than 10,000. (iii) Find all account numbers belonging to 'Mr. TEST'.
[7 marks]What is a constraint in a database? List out and explain the types of constraints in detail with their syntax and examples.
[7 marks]Explain Mapping Cardinalities with respect to the ER diagram.
[3 marks]Define the following terms with an example: Generalization, and Aggregation. Page 1 of
[3 marks]Enlist SQL commands for DML. Consider the following relations and write SQL queries for the given statements. Assume suitable constraints. Job (job-id, job-title, minimum-salary, maximum-salary) Employee (emp-no, emp-name, emp-salary,dept-no) Deposit (acc-no, cust-name, branch-name, amount, account-date) Borrow (loan-no, cust-name, branch-name, amount) Department (dept-no, dept-name) 1. Give the names of depositors whose branch name starts with ‘S’. 2. Give employee name(s) whose salary is between Rs. 55000 and Rs. 85000 and department name is Finance. 3. Delete borrower details whose amount is less than 10000.
[7 marks]Explain the query optimization process.
[3 marks]List the aggregate functions available in SQL. Explain any three aggregate functions with examples in brief.
[4 marks]Construct the ER diagram for any social media application. Make sure the ER diagram covers all the concepts of the entity relationship model.
[7 marks]List out ACID properties and explain any one property in detail.
[3 marks]What is normalization? Why is a normalization process is needed? Explain 1 NF with an example.
[4 marks]Enlist and explain the basic steps in Query Processing.
[7 marks]Define functional dependency. Consider schema EMPLOYEE (E-ID, E-NAME, E- CITY, E-STATE) and FD = {E-ID->E-NAME, E-NAME->E-CITY, E-ID->E- STATE, E-CITY->E-STATE}. Find attribute closure for: (E-ID)+ and (E-CITY)+
[3 marks]Explain BCNF with an example.
[4 marks]Explain conflict serializability and view serializability with an example.
[7 marks]Define "Ordered Index" and state its primary advantage over an unordered index.
[3 marks]What is the difference between authorization and authentication? Explain the use of an audit trail.
[4 marks]Define a B+ Tree. Given a B+-Tree with n=4 (i.e., nodes can hold 3 keys and pointers), illustrate the insertion of the key 25 into the following tree. Root: [20, 30] Child 1 (Left): [5, 10, 15] Child 2 (Middle): [21, 22, 24] Child 3 (Right): [35, 40, 45]
[7 marks]Explain the Mandatory Access Control (MAC) with respect to database security.
[3 marks]Describe how "Dynamic Hashing" solves the main problem of "Static Hashing". Page 2 of
[3 marks]What is a cursor? List and explain various types of cursors used in PL/SQL. Page 3 of
[3 marks]