Database Management Systems : FY BCS: Semester 1 Important Questions

(f) State any two types of indices.

(b) Differentiate between primary and secondary index.

(i) What is Clustering Index ?

(j) List the keywords used for set membership operation in nested

queries.

(b) What is mapping cardinality ? What are its type ? Explain

with example.

Chapter 1 Introduction to DBMS 

1 mark

(c) Explain various types of users in DBMS.

a) Enlist users of DBMS.

(a) What is a logical file ?

(i) State different types of users of DBMS.

e) What are the different functions performed by DBA?

a) What is a physical file?

b) Define Database Management System.

j) Who are Naive users?

(h) Who are Naive users ?

b) Write a short note on sorted or sequential file organization.

(a) Write a short note on data independence.

(g) List any two advantages of DBMS.

4 mark

(a) Explain Hash file organization technique in detail.

a) What is data abstraction? What are different levels of data abstraction?

(b) Write a short note on Data Abstraction.

d) Write a short note on data abstraction.

c) Explain overall DBMS structure with neat diagram.

(a) What is DBMS ? What are the advantages and disadvantages

of DBMS ?

(e) Write a note on functional components of DBMS.

Chapter 2 Conceptual Design 11 Hours 

d) What do you mean by domain of an attribute?

(i) What is prime attribute ?

(e) Define ‘domain’

(a) Define attributes with example.

Write difference between single valued attribute and multivalued attribute.

e) Define an entity.

f) What is a foreign key?

(h) Define superkey.

d) What is a candidate key?

(c) Define candidate key.

(d) Compare primary key, candidate key and super key.

c) Explain the term tuple with an example.

(g) Explain the term tuples with example.

f) Explain the use of aggregate function.

(j) What is referential integrity ?

(d) What is referential integrity constraint ? Explain in

brief.

(e) Explain different types of integrity constraint.

(b) Define entity set. Eplain strong and weak entity.

(b) List the record based logical models.

4 mark

(c) What are undesirable properties of a bad database design ?

(a) Write a short note on data model.

(c) What is aggregation ? Explain with example.

(a) What is a foreign key constraint ? Why are such constraints

important ? What is referential integrity ?

d) What is referential integrity constraint? Explain it in brief.

c) What is specialization? Explain it with an example.

(a) What are extended E-R features of E-R model ? Explain any

one in detail.

(A) In a nursery, the plants are sold to the customers. These

plants are flowering and non-flowering. Nutrients are given

to the plants with some quantity. Nutrients include pesticides,

watering and manure.

(i) Design an E-R diagram.

(ii) Convert the E-R diagram into relational database in

3NF

(A) Suyog Hotel has many rooms. Rooms are classified as AC and 5.

Non-AC. Hotel provides the discount to customers who visit

the hotel more than once. Hotel also has food section and

laundry section. This facility gives to customer as per customer’s

requirement. Every room of hotel has TV and telephone facility,

which charge with the total cost of room.

(i) Design an E-R diagram.

(ii) Convert the E-R diagram into relational database in 3NF. [7]

Construct an E-R diagram for a car insurance company that has a set of customers. Each customer owns one or more cars. Each are associated with more cars. Each can be associated with zero to any number of recorded accidents.

Now a days there are many multiplex theaters opened in Nagpur city.

These multiplex theaters can show 3 to 5 movies at a time. Theaters self

decide which movie has to be shown to people for longer time. Movie is

of two types : universal and adult’s. Adult’s movie can not allow to age

below 18 persons. Many theaters have AC and doubly digital sound

quality system. The ticket of movie is along with taxes or tax-free.

Based on above information :

i) Design an E-R diagram. (Assume suitable data if necessary)

ii) Convert the E-R diagram into relational database in 3 N.F. [7]

(A) Savitribai Phule Pune University offers choice based credit system

to all P.G. Course. For P.G. science courses students have

to complete 100 credits to get their certificate. These courses

have semesters two, three ............ Each semester has set of

subjects. The subject may be core (compulsory) or elective

(optional).

(i) Design an E-R diagram for above scenario, assume, attributes

if necessary.

(ii) Convert the E-R diagram into a relational database in

3NF.

 c) What is attribute? Explain different types of attributes.

 b) Differentiate between Generalization and Specialization.

Differentiate between specialization and generalization.

b) What is use of check constraint? Give the syntax of check constraint in

a column definition.

c) Define strong and weak entity sets.

Chapter 3 SQL 

(b) Define TCL.

(c) Explain ternary relationship with example.

i) List any two aggregate functions in SQL.

(g) List any two aggregate functions in SQL.

i) What is difference between Char & Varchar?

c) Define second normal form.

(j) Define second normal form.

g) What is DDL?

(d) What is the use of Having Clause in SQL ?

(h) Modification in table is a part of DDL statement. Justify true

or false.

h) What is view?

j) What is Right Outer Join?

(f) What is left outer join ?

(f) What is Right Outer Join ?

e) What do you mean by natural join operation?

(d) Give syntax and example of select operator.

Explain natural join with example.

2 mark

(d) What is Normalization ? Write advantages of Normalization.

e) Describe the term nested subquery with example.

a) Differentiate between 3NF and BCNF.

(e) Define BCNF.

d) What is DML? Write any one example of DML.

h) Define second normal form.

4 mark

What is DDL ? Write any two examples of DDL.

Chapter 4 Relational Database Design

1 mark

b) Define decomposition.

(d) What is decomposition ?

a) What is attribute? Explain different types of attributes.

g) What is partial dependancy?

(c) What is functional dependency ?

(c) Explain in detail lossless decomposition with example.

(e) Define partial dependency.

(e) Explain any two relational algebra operations with suitable

examples.

4 mark

(d) State and explain in short rules of inference for functional

dependencies.

(e) Give a set of functional dependencies for the relation at schema

R (A, B, C, D) with primary key AB under which R is in

1NF but not in 2NF.

(d) What is existance dependency of entity set ? Explain it with

suitable example.

a) What are desirable properties of decomposition? Explain it in brief.

b) Explain union and division operation in relational algebra.

(b) Explain union and intersection operation in relational algebra.

(b) What is Cartesian product operation in relational algebra ?

(d) Explain Cartesian product and difference operation in relational

algebra.

(B) What is Cartesian product ? Explain with example. [3]'

(e) Consider the following relation :

R(A, B, C, D, E) and the set of FD’s defined on R

as :

F = {A
B, CD
E, A
C, B
D, E
A}

Compute the closure of F i.e. F+.

) Consider R = (A, B, C, D, E) and set of FDs defined on R as F={AC, CDE, BD, ECompute closure of F i.e. F+

e) Consider the relation R(A, B, C, D, G, H, I) and the set of F.D.S. defined

on R as: {AB, AC, CGH, CGI, BH} Compute the closure

of F i.e. F+

b) Consider the following relational database :

Sailors (sid, sname, rate, age)

Boats (bid, bname, colour)

Reserves (sid, bid, day)

Write SQL statement for each of the following queries.

i) Find name and ages of all Sailors.

ii) Find all the Sailors with a rating above 6.

iii) Find the sids of Sailors who have reserved a red boat.

iv) Find colours of boats reserved by Amol.

c) Consider the following entities and relationships :

Game (g_no, gname, no_of_players, coach_name_captain)

Player (p_no, p_name)

Game and Players are related with many-to-many relationships.

Create Relational Database for the above and solve the following queries

in SQL.

i) List the name of players playing 'basketball' and 'handball'.

ii) List the name of players playing game'cricket'.

iii) Count the total numbers of players whose coach name is 'mr.sharma'

a) What are Armstrong's Axioms?

(c) Explain Armstrong axioms any five.

Q4) A) Answer any three of the following : [3×5=15]

a) Consider the following relations:

Doctor (Dno, Dname, Daddress, city)

Patient (Pno, Pname, Paddress, disease)

Doctor and Patient are related with many to many relationship. Create

a relational database in 3NF and solve the following queries in SQL.

i) Find the number of patients visited by ‘Dr. Rahane’.

ii) Find the name of doctor starting with ‘A’.

iii) Delete all the patients record suffering from ‘viral fever’.

b) Consider the following relations :

Parts (Partno, Pname, Quantity, Price)

Supplier (Sno, Sname, City)

Parts & Supplier are related with many to many relationship. Create

a relational database in 3NF and solve the following queries in SQL.

i) List the names of suppliers supplying partname ‘Wheel’.

ii) Find the names of parts in descending order of quantity.

iii) List the names of supplier supplying partno 5 with price

< 5000.

c) Consider the following relations :

Movie (Mno, Mname, Budget)

Actor (Ano, Aname, Role)

Actor and Movie are related with one to many relationship. Create a

relational database in 3N.F. and solve the following queries in SQL.

i) List the name of movie in which ‘Amir’ has acted.

ii) Count the number of actor in movie ‘Padmavat’.

iii) List the budgetwise movie.

d) Consider the following relations :

employee (eno, ename, salary, designation)

Project (Pno, Pname, location)

employee and project are related with many to one relationship.

Create a relational database in 3N.F. and solve the following queries

in SQL.

i) List all the employees working on the project ‘Web Designing’.

ii) List all the project having more than 20 employees.

iii) Give the names of all employees working on the ‘CAD/CAM’

project.

a) Consider the following relations :

Food_Product (Food_id, Food_ name, Weight)

Ingredient (In_id, Name, Quantity)

F_Ing (Food_id, In_id, Quantity)

Solve the following queries in relational algebra.

i) Give all the ingredient of ‘Mango Barfi’.

ii) List the name of ingredient used both in ‘Coconut Barfi’ and

in ‘Boondi-Laddu’.

iii) List the name of food product of weight more then 2 kg.

iv) List the name of food product who used ingredient as ‘Sugar’.

v) List the name of ingredient used either in ‘Choco-cake’ or in

‘Biscuits’.

b) Consider the following relations :

Dept (Dno, Dname, location)

employee (eno, ename, designation, dno, Pno)

Project (Pno, Pname, Status)

Solve the following queries in relational algebra.

i) List all employees of ‘Inventory’ department of ‘Pune’ location.

ii) Give the name of employees who are working on ‘Blood

Bank’Project.

iii) Give the name of manager from ‘Purchase’ department.

iv) Give all the employees working under ‘complete’ projects.

v) Find the name of employee having designation as ‘Clerk’.

b) Explain projection operation in relational algebra with an example. [3]

OR

Differentiate between strong and weak entity with suitable example.

(a) Consider the following relations :

Supplier (sid, sname, address)

Parts (pid, pname, color, cost)

Supplier and parts are related with many to many relationship.

Create a relational database in 3NF and solve the following

queries in SQL :

(i) List all the suppliers who is supplying some red parts.

(ii) Find the number of parts supplied by each supplier.

(iii) Find the supplier names of parts whose cost is more

than Rs. 

(b) Consider the following relations :

Branch (bno, bname, street, area, city, pincode, officeno);

Staff (sno, frame, iname, address, position, salary)

Branch and staff are related with one to many relationship.

Create a relational database in 3NF and solve the following

queries in SQL :

(i) List the staff who works in the branch at “Tilak Road”.

(ii) Find staff whose salary is larger than the salary of

all staff members at branch “S1”.

(iii) Give names of all branch managers in Kolhapur.

(c) Consider the following relations :

Country (contrycode, name, capital)

Population (pcode, pcount)

Country and population are related with one to one relationship.

Create a relational database in 3NF and solve the following

queries in SQL :

(i) Find the country name having lowest population.

(ii) Find the name and population of a country whose capital

name starting with a.

(iii) List the names of all countries whose population is

within the range 1,00,000 to 4,00,000.

(d) Consider the following relations :

Person (pno, name, address)

Car (cno, year, model)

Person and car are related with one to many relationship.

Create a relational database in 3NF and solve the following

queries in SQL :

(i) List all the names of people from Kharadi and have

Maruti 800

(ii) Change address of Mr. Korth to Pune.

(iii) List the name of people having car before 2010.

(B) Attempt any one of the following : [1×5=5]

(a) Consider the following relations :

Player (pno, pname, city)

Game (gno, gname, city)

Player-Game (pno, gno, date)

(i) Find all players playing “Football”.

(ii) List all games details played on 28/3/2018

(iii) List all games details played in Jaipur.

(iv) List all players playing both football and basketball.

(v) List all players who are playing in the same city where

they live.

(b) Consider the following relations :

Item (icode, name, price);

Order (ocode, cust-name, date)

Item-order (icode, ocode, quantity)

Solve the following queries in relational algebra :

(i) Find all items that are oredered by Amit Kumar.

(ii) Find order details of each item.

(iii) List all items ordered between 25 January 2018 to

28 January 2018

(iv) Find item names with lowest cost.

(v) List all the items with their price having ordered quantity

more than 100.

(a) Consider the following relations

Emp (eno, ename, salary, commission, designation)

Dept (dno, dname, location)

Emp and Dept are related with many to one relationship.

Create a relational database in 3NF and solve the following

queries in SQL :

(i) Find out employees who are working at Aurangabad

location.

(ii) Find the maximum, minimum and average salary for

every designation.

(iii) Update commission for every employee by 6% who

belong to botany department.

(b) Consider the following relations :

Wholesaler (wno, wname, address, city)

Product (Pno, Pname)

Wholesaler and product are related with many to many relationship.

Create a relational database in 3NF and solve the following

queries in SQL :

(i) List the wholesalers of product ‘Mouse’.

(ii) Count the number of wholesaler from ‘Pune’ city.

(iii) Delete records of wholesaler where product name is ‘Scanner’.

(c) Consider the following relations :

Doctor (dno, dname, address, city)

Patient (Pno, Pname, address, disease)

Doctor and patient are related with many to many relationship.

Create a relational database in 3NF and solve the following

queries in SQL :

(i) Find the number of patients visited by ‘Dr. Pawar’.

(ii) Find the number of patients suffering from ‘Cancer’.

(iii) Display doctor name and city who gives treatment to the

patient ‘Mr. Sagar’.

(d) Consider the following relations :

Movie (mno, mname, budget)

Actor (ano, aname, role)

Movie and Actor are related with one to many relationship.

Create a relational database in 3NF and solve the following

queries in SQL :

(i) List the names of movie in which ‘Salman’ has acted.

(ii) List the budgetwise movie

(iii) Count the number of actors in movie ‘PK’.

(B) Attempt any one of the following : [1×5=5]

(a) Consider the following relations :

Dept (dno, dname, location)

Emp (eno, ename, designation, dno, pno)

Project (Pno, Pname, Status)

Solve the following queries in relational algebra.

(i) List all the employees of ‘inventory’ department of ‘Delhi’

location.

(ii) Give the name of employees who are working on ‘Blood

Bank’ project.

(iii) Give the name of manager from ‘Purchase’ department.

(iv) Find all employees having designation as ‘Clerk’

(v) Give all employees working on project whose status

is ‘Incomplete’.

(b) Consider the following relations :

Student (sno, name, address, class)

Subject (subno, name)

Stud-sub (sno, subno, marks)

Solve the following queries in relational algebra :

(i) Display subjectwise student list.

(ii) Find the names of student having marks > 90

(iii) Count the total number of students for each subject.

(iv) Print the classwise student.

(v) Display the number of student having address as ‘M.G.

Road’ and class is ‘S.Y. BSC’.

(e) Consider the relation R = (A, B, C, D, E) and set of FD’s

defined on R, F as A BC, CD E, B D, E A

Find all the superkey for relation R