Database Management Systems — DU B.Sc. (Hons) Computer Science Previous Year Question Papers

(NEP-UGCF-2022 GE) All parts of the question must be answered together. 1. (a) Identify primary key and foreign keys in the following relations: 3 (i) Employee (Emp_id, Emp_Name, Dept_Name, Salary) (ii) Department (Dept_id, Dept_Name, Building, Budget, (iii) Job (Job_Code, Job_Des, Dept_id) Defrne the following terms, draw their standard ER diagram notations and provide suitable examples for each : 6 Uil Weak entity Oii) Attribute Uu) Key attribute (ui) Multivalued attribute. Explain the type of relationship and the cardinality ratio for the following entity pairs : - g (i) Customer and Bank Uii) Student and Faculty. @) R1 and R2 are two given relations : Find the union (U), intersection (n), and set difference (-) for R1-R2. (e) What is the data redundancy? What steps are taken to remove the data redundancy in a database system ? 3 What is concurrency control in DBMS? Why is it needed ? 3 (g) Give SQL command for the following 4 (i) To add a new attribute email with data type varchar (20) in the relational table Employee. (ii) To change Email attribute from varchar (20) to char (50). Alter table Employee and make 'empno' as the primary key. (io) To Convert the text in "CustomerName" to upper-case. (h) Explain the following relational algebra symbols. Give suitable examples and usage of each in database queries : 4 2. (a) Explain the Three-Schema Architecture of a database system giving a suitable diagram. What are the advantages and disadvantages of three-schema architecture ? 7 . (b) For given relation : 8 (i) Identify the key in above relation. (ii) Is the relation in INF, 2NF or 3NF ? Justify your answer. (iii) How would you successively normalize it in BCNF 3. (a) What are ACID properties in database transactions ? Explain each property with example. 5 (b) Consider the following schema for the BANK database : 10 BRANCH(BranchID, Bname, City, Phone) TRANSACTION(TID, T_Date, T_Type, AccountNo, Amount) On the basis of this relation schema, write the following queries in relational algebra: (i) Retrieve the ID and name of all the branches located in Delhi city. (ii) Retrieve the ID, type and amount of all the transactions of withdrawal type. (ul) List the number and type of all accounts opened in the branch having ID B101. Gu) List the number and name of account holders withdrawing an amount greater than 10000 on 31st March, 2028. (u) Calculate the ma.rimum anil minimum balance of accounts in each city. List fiue main responsibilities of DBA in DBMS. b Consider a Bank database that maintains data about Customer, Loan, Accounts, Employee and Branch Name. The data requirements are as follows : 10 (i) Each branch of bank allows customers to open accounts and borrow loans. (ii) A customer can open more than one account, and one aecount may also belong to one or morre customers. (iii) A customer can take more than one loan and a loan may be issued to more than one customer.