Database Management Systems — DU B. Voc – Software Development Previous Year Question Papers

Database Management Systems B.VOC Software Development Section A a) What is meant by the closure of a set of functional dependencies? Explain 2 with an example, b) Describe the TCL (Transaction control Language) commands along with their 2 c.) Explain types of anomalies. d) Define database. Briefly discuss the advantages of using DBMS. e) Given the relation R(A, B, C, D, E) with the following functional dependencies: Normalize the relation to 2NF. 2 a) What is an ER model? Write about different types of attributes in ER model. Draw the notation of each. Design an ER Diagram for keeping track of Information about Bank Database, Taking into account 4 entities: BANK, ACCOUNT, BRANCH and LOAN. Write any Five Codd's Rules. 3 a) What is a data model? Describe various data models. 10 b) Given the following two tables: 5 Orders(Order_ID, Customer_ID, Order_Date) Customers(Customer _ID, Name, Address) Write SQL queries to: i. Retrieve the names of all customers who have placed orders. ii. List the customers who have never placed an order (use a LEFT 4 a) What are the three different types of DBMS architectures? Explain 3-tier 10 architecture in detail. Given the following table: 5 Student_Courșe(Student_ID, Course_ID, Instructor, Instructor_Phone, Grade) with the functional dependencies: Course,D → Instructor Instructor → Instructorphone 1. Identify if the table is in INE and 2NF ii. Normalize the table to 2NF and explain the reasoning behind your decomposition. 5 a) Write Short notes on the following (any 4): 10 i. Generalization and specialization ii. Different types of keys in DBMS ili. DDL COMMANDS iv. DML COMMMANDS v. Composite attributes vi. Loss less-join dependency b) Write SQL Queries for following set of tables: 5 EMPLOYEE (EmpNo, Name, DoB, Address, Gender, Salary, DNumber) i. Display the Age of "male employees. ii. Display all employees in Department named 'Marketing'. iii. Display the name of highest salary paid 'female' employee. iv. Which employee is oldest manger in company? Display the name of department of the employee 'SMITH'. 6 a) Given the following two relations: 10 Employee(Emp_ID, Name, Dept_ID, Salary) Department(Dept_ID, Dept_Name) i. Write the relational algebra expression for retrieving the names of employees who earn more than $50,000. ii. Convert the relational algebra expression into an SQL query. iii. Discuss the differences between relational algebra and SQL, focusing on their usage and advantages. Given the set of functional dependencies for a relation R(A, B, C, D): 5 Find the canonical cover (minimal cover) of the given set of functional dependencies. Show the steps involved in simplifying the dependencies.