DBMS Assignment
DBMS Assignment
DBMS Assignment
Unit1 1 List and explain the advantages of DBMS over file based system. OR Explain
disadvantages of files based system.
2 Draw and explain 3 level architecture of DBMS.
3 List and explain different categories/types of database users.
4 List and explain different tasks/roles/functions/duties of DBA (Database Administrator).
5 Explain DBMS architecture with block diagram. OR Explain Database System
architecture with block diagram.
6 Describe Data Definition Language and Data Manipulation Language.
Unit2 7 Write a note on mapping cardinality in E-R diagram.
8 Explain the difference between a weak and a strong entity set.
9 Explain the difference between generalization and specialization. OR Explain
specialization and generalization concept in E-R diagram with suitable example.
10 Write a note on constraints on specialization and generalization.
11 Explain aggregation in E-R diagram with example.
12 What do you mean by integrity constraints? Discuss various integrity constraints.
13 Draw E-R diagram for student management system with the necessary assumption.
14 Draw E-R diagram for Bank Management System.
15 Define E-R diagram. Draw an E-R diagram for Library Management System. Assume
relevant entities and attributes for the given system.
16 Construct an E-R diagram for a car-insurance company whose customers own one or
more cars each. Each car has associated with it zero to any number of recorded
accidents.
17 Design a generalization–specialization hierarchy for a motor-vehicle sales company. The
company sells motorcycles, passenger cars, vans, and buses. Justify your placement of
attributes at each level of the hierarchy. Explain why they should not be placed at a
higher or lower level.
18 Design a database for an airline. The database must keep track of customers and their
reservations, flights and their status, seat assignments on individual flights, and the
schedule and routing of future flights. Your design should include an E-R diagram, a set
of relational schemas, and a list of constraints, including primary-key and foreign-key
constraints.
19 Design a database 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. Your
design should include an E-R diagram, a set of relational schemas, and a list of
constraints, including primary-key and foreign-key constraints.
Unit3 20 Define Super key, Primary key, Candidate key and Alternate key.
21 Explain following Relational Algebra Operation with example.
1. Selection
2. Projection
3. Cross Product
4. Joins (Inner Join, Outer Joins)
5. Rename
6. Division
7. Set operators
22 Explain different aggregate functions with example.
23 Consider the following relational database, where the primary keys are underlined. Give
an expression in the relational algebra to express each of the following queries
employee (ssn, name, dno, salary, hobby, gender)
department (dno, dname, budget, location, mgrssn)
works_on (ssn, pno)
project (pno, pname, budget, location, goal)
1. List all pairs of employee names and the project numbers they work on.
2. List out department number, department name and department budget.
3. List all projects that Raj Yadav works on by project name.
4. List the names of employees who supervise themselves.
24 Consider the following relational database, where the primary keys are underlined. Give
an expression in the relational algebra to express each of the following queries
course (course-id, title, dept_name, credits)
instructor (id, name, dept_name, salary)
section (course-id, sec-id, semester, year, building, room_no, time_slot_id)
teaches (id, course-id, sec-id, semester, year)
1. Find the name of all instructors in the physics department.
2. Find all the courses taught in the fall 2009 semester but not in spring semester.
3. Find the names of all instructors in the Comp. Sci. department together with the
course titles of all the courses that the instructors teach.
4. Find the average salary in each department.
25 Consider the following relations and write an relational algebra:
EMP (empno, ename, jobtitle, managerno, hiredate, sal, commission, deptno)
DEPT (deptno, dname, location)
1. Find the Employees working in the department number10, 20, 30 only.
2. Find Employees whose names start with letter A or letter a.
3. Find Employees along with their department name.
4. Find the Employees who are working in Smith's department
5. Find the Employees who get salary more than Allen’s salary.
6. Display employees who are getting maximum salary in each department.
7. Find list of employees whose hire date is on or before 1-April-18.
26 Consider the relational database given below and give an expression in the relational
algebra:
Employee (person-name, street, city) , Works (person-name, company-name, salary)
Company (company-name, city) , Manages (person-name, manager-name)
1. Find the names of all employees in this database who live in the same city as the
company for which they work.
2. Find the names, street address, and cities of residence of all employees who work
for HCL and earn more than $10,000 per annum.
27 The relational database schema is given below and write the relational algebra expressions
for the given queries.
employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)
1. Find the names of all employees who work for First Bank Corporation.
2. Find the names and cities of residence of all employees who work for First Bank
Corporation.
3. Find the names, street address, and cities of residence of all employees who work
for First Bank Corporation and earn more than $10,000 per annum.
4. Find the names of all employees in this database who do not work for First Bank
Corporation.
Unit4 28 What is meant by normalization? Write its need. List and discuss various normalization
forms.
29 Consider schema EMPLOYEE(E-ID,E-NAME,E-CITY,E-STATE) and
FD = {E-ID → E-NAME, E-ID → E-CITY, E-ID → E-STATE, E-CITY → E-STATE}
Find attribute closure for: (E-ID)+
30 Compute the closure of the following set F of functional dependencies for relation schema
R(A, B, C, D, E).
F = { A → BC, CD → E, B → D, E → A}
List the candidate keys for R.
31 Consider schema R = (A, B, C, G, H, I) and the set F of functional dependencies {A →
B, A → C, CG → H, CG → I, B → H}. ( Use F+ )
Prove that AG → I Holds.
32 In the BCNF decomposition algorithm, suppose you use a functional dependency α → β
to decompose a relation schema r (α, β, γ) into r1 (α, β) and r2 (α, γ).
1. What primary and foreign-key constraint do you expect to hold on the decomposed
relations?
2. Give an example of an inconsistency that can arise due to an erroneous update, if
the foreign-key constraint were not enforced on the decomposed relations above.
3. When a relation is decomposed into 3NF, what primary and foreign key
dependencies would you expect will hold on the decomposed schema?
33 A college maintains details of its lecturers' subject area skills. These details comprise:
Lecturer Number, Lecturer Name, Lecturer Grade, Department Code, Department Name,
Subject Code, Subject Name, Subject Level. Assume that each lecturer may teach many
subjects but may not belong to more than one department. Subject Code, Subject Name
and Subject Level are repeating fields. Normalize this data to Third Normal Form.
Unit5 34 Explain query processing steps. OR Discuss various steps of query processing with
proper diagram.
35 Explain Heuristics in optimization.
36 Explain the purpose of sorting with example with reference to query optimization.
37 Explain the measures of finding out the cost of a query in query processing.
Unit6 38 Explain indexing and different types of indexes.
39 Explain B-tree.
40 Explain hashing.
Unit7 41 Write a note on two phase locking protocol.
42 Explain ACID properties of transaction with suitable example.
43 What is log based recovery? Explain immediate database modification technique for
database recovery. OR Define Failure. Write a note on log based recovery.
44 State differences between conflict serializability and view serializability.
45 Explain two-phase commit protocol.
46 Define transaction. Explain various states of transaction with suitable diagram.
47 Write differences between shared lock and exclusive lock.
48 Explain deadlock with suitable example.
49 What is locking? Define each types of locking.
50 Define wait-Die & wound-wait.
Unit8 51 Explain Authorization and access control in brief.
52 What is the difference between data security and data integrity?
53 What is constraint? Explain types of constraints.
Unit9 54 Consider following 3 tables, assume appropriate data and solve following SQL queries.
Worker(WORKER_ID INT NOT NULL PRIMARY KEY,FIRST_NAME CHAR(25),
LAST_NAME CHAR(25),SALARY INT(15),JOINING_DATE
DATETIME,DEPARTMENT CHAR(25));
Bonus(WORKER_REF_ID INT,BONUS_AMOUNT INT(10),BONUS_DATE
DATETIME,FOREIGN KEY (WORKER_REF_ID),REFERENCES
Worker(WORKER_ID));
Title(WORKER_REF_ID INT,WORKER_TITLE CHAR(25),AFFECTED_FROM
DATETIME,FOREIGN KEY (WORKER_REF_ID)REFERENCES
Worker(WORKER_ID));
Solve following SQL queries
1. Find out unique values of DEPARTMENT from Worker table
2. Print details of the Workers whose SALARY lies between 100000 and 500000.
3. Print details of the Workers who have joined in Feb’2014.
4. Fetch worker names with salaries >= 50000 and <= 100000.
5. Print details of the Workers who are also Managers.
6. SQL query to clone a new table from another table.
7. Fetch the list of employees with the same salary.
8. Fetch “FIRST_NAME” from Worker table in upper case.
9. Find the total salary of worker whose working in computer department.
10. Display total number of worker in each department.
55 Consider following table and write SQL query.
Employee (emp_no Number(3), emp_name Varchar2(30), emp_sal Number(8,2),
emp_comm Number(6,1), dept_no Number(3),hiredate Date);
1. Display all employee whose name start with ‘A’ and third character is ‘a’.
2. For each employee, display the employee number, salary, and salary increased by 15%
and expressed as a whole number. Label the column New Salary.
3. Write a query that produces the following for each employee: <employee name> earns
<salary> monthly.
4. Display the hiredate of emp in a format that appears as Seventh of June 1994.
56 When Join is used in SQL? Explain Left outer, Right outer and Full outer join with SQL
syntax.
57 When do we require to use group by clause? How aggregate functions are used with
group by clause?
58 Consider following schema and write SQL for given statements.
Student (RollNo, Name, DeptCode, City)
Department (DeptCode, DeptName)
Result (RollNo, Semester, SPI)
1. Display the name of students with RollNo whose name ends with ‘sh’.
2. Display department wise total students whose total students are greater
than 500.
3. List out the RollNo, Name along with CPI of Student.
4. Create RollNo field as primary key for existing Student table.
5. Display student name who got highest SPI in semester 1.
6. Display the list of students whose DeptCode is 5, 6,7,10.
7. Create table Student_New from student table without data.
59 Given the following relations
TRAIN (NAME, START, DEST)
TICKET (PNRNO., START, DEST, FARE)
PASSENGER (NAME, ADDRESS, PNRNO.)
Write SQL expressions for the following queries:
Note: Assume NAME of Train is a column of Ticket.
1. List the names of passengers who are travelling from the start to the destination station
of the train.
2. List the names of passengers who have a return journey ticket.
3. Insert a new Shatabti train from Delhi to Bangalore.
4. Cancel the ticket of Tintin.
60 Consider the following relations and write SQL queries for 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 name of employees whose employee number is '001'
2. Give name of depositors whose branch name starts from ‘S’.
3. Give employee name(s) whose salary is between Rs. 20000 to 30000 and department
name is Finance.
4. Update the salary of employee by 10% of their salary who is working in the Finance
department
Unit10 61 Write a PL/SQL block to print the sum of Numbers from 1 to 100.
62 Write a PL/SQL block to print the given number is prime or not.
63 Write a PL/SQL program for inserting even numbers in EVEN table and odd number in
ODD table from number 1 to 50.
64 Explain Cursor in PL/SQL.
65 Explain stored procedure with proper example.
66 What are triggers? Explain the advantages and the needs of triggers.
*** Note: Write in note book and submit assignment after completion of each Unit.