Theory Assignments
Course: B. Tech in Information Technology Sem: VI
Subject Name: Database Management Systems Subject Code: BTITC602
***************************************************************************
Unit 1
1) Differentiate between DBMS and file processing system.
2) State and explain the major responsibilities of DBA.
3) Construct an E-R diagram for a car-insurance company whose customers own one or
more cars each. Each car is associated with zero to any number of recorded accidents
4) Construct an ER diagram that models the University Examination Controller's office.
Consider entities such as Students, Paper Setters, Examiners, Course syllabus, etc.
5) Construct an E-R diagram for a hospital with a set of patients and a set of medical
doctors. Associate with each patient a log of the various tests and examinations
conducted.
6) Explain database system architecture with a diagram in detail.
7) Explain the Specialization and Aggregation features of the extended E-R diagram
with a suitable diagram
8) Define the term entity and attribute. Explain different attribute types with respect to
E-R model.
__________
Unit 2
1) Explain record insertion, deletion, and update operations using relational algebra.
Give suitable examples.
2) Explain SQL commands with syntax to:
1) Create Table
2) Modify Table
3) Insert record
3) Explain relational algebra operators with notations.
4) Consider the Banking Schema given below:
branch (branch-name, branch-city, assets)
customer (customer-name, customer-street, customer-only)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
Construct SQL queries for the following statements:
1) Find the name, loan number and loan amount of all customers having a loan at the
Perryridge branch.
2) Find all loan numbers for loans made at the Perryridge branch with loan amounts
greater than $1200.
3) Find the name, loan number and loan amount of all customers; rename the column
name loan_number as loan_id.
4) Find the loan number of those loans with loan amounts between $90,000 and
$100,000 (that is, ³$90,000 and £$100,000)
5) Given relation schemas R= (A, B, C) S= (D, E, F). Let relations r(R) and s(S) be given.
Convert following SQL statements in relational form.
1. select * from r where B=17
2. select, F from r, s where r.c=s.D
3. select distinct * from r, s
6) Consider the employee database given below, where the primary keys are underlined.
employee (employee-name, street, city)
works (employee-name, company-name, salary)
company (company-name, city)
manages (employee-name, manager-name)
Give an expression in SQL for each of the following queries.
i) Find the names of all employees who work for First Bank Corporation.
ii) Find the names and cities of residence of all employees who work for First
Bank Corporation.
iii) Find the names, street addresses, and cities of residence of all employees
who work for First Bank Corporation and earn more than $10,000.
7) Explain set operations in SQL.
__________
Unit 3
1) Consider schema given below and construct SQL queries.
i) Find the name, loan number and loan amount of all customers having a loan at the
Perryridge branch.
ii) Find the names of all branches that have greater assets than some branch located in
Brooklyn.
iii) List in alphabetic order the names of all customers having a loan in Perryridge branch
2) Consider schema given in 1 and attempt following (Construct SQL queries):
i) Create a view consisting of branches and their customers.
ii) Delete all accounts at every branch located in Needham city.
iii) Find all customers who have either an account or a loan (but not both) at the bank.
3) Let R = (A,B,C), and let r1 and r2 both be relations on schema R. Construct an expression in
SQL that is equivalent to each of the following queries.
i) r1 ∪ r2
ii) r1 ∩ r2
iii) r1 − r2
4) Explain Stored Procedures and Triggers with suitable examples.
5) Explain all join types in SQL with a suitable example.
6) Explain aggregate functions in SQL with syntax.
7) What is the need for embedded SQL? Explain dynamic SQL with an example.
__________
Unit 4
1) List and justify all functional dependencies satisfied by the relation of the figure given
below:
2) Construct a B+-tree for the following set of key values:
(2, 3, 5, 7, 11, 17, 19, 23, 29, 31)
Assume that the tree is initially empty and values are added in ascending order.
Construct B+ trees for the cases where the number of pointers is FOUR and will fit
in one node.
3) Explain RAID with a suitable diagram.
4) Explain the concept of ‘Lossy decomposition’ with a suitable example.
5) Write note on:
Full functional dependency
Trivial functional dependency
6) Explain the advantages and disadvantages of normalization.
7) List and explain Armstrong’s axioms.
8) Compare 3NF with BCNF.
__________
Unit 5
1) Define transaction. List and explain ACID properties of transaction with suitable
examples.
2) Define Conflict Serializability. Predict and justify whether following the schedule is
conflict serializable or not.
3) Justify with suitable example: Every conflict serializable schedule is also view
serializable.
4) Explain states of transaction with suitable diagram.
5) What is transaction schedule? Explain serial and non-serial schedule with suitable
example.
6) Differentiate between conflict serializability and view serializability with a suitable
example.
______________
