MGM’s College of
Engineering, Nanded.Department of IT
Semester I (2017-18)
Class: TE(IT) Subject: DBMS Assignment I
1. List four significant differences between a
file-processing system and a DBMS.
2. Explain the concept of physical data independence, and
its importance in database systems.
3. Describe at least 3 tables that might be used to store
information in a social-networking system such as Facebook.
4. Explain the distinctions among the terms primary key,
candidate key, and superkey.
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. 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.
7. Design a
database for a world-wide package delivery company (e.g., DHL or FedEX). The
database must be able to keep track of customers (who ship items) and customers
(who receive items); some customers may do both. Each package must be
identifiable and trackable, so the database must be able to store the location
of the package and its history of locations.
Locations
include trucks, planes, airports, and warehouses. 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.
8. Explain
various operations of relational algebra.
9. Consider
the following relational database:
employee (person
name, street, city)
works (person
name, company name, salary)
company (company
name, city)
Give an
expression in the relational algebra to express each of the following queries:
a. Find the
names of all employees who live in city “Miami”.
b. Find the
names of all employees whose salary is greater than $100,000.
c. Find the
names of all employees who live in “Miami” and whose salary is greater than
$100,000.
10. Consider
the following database:
employee (personname,
street, city )
works (personname,
company name, salary)
company (companyname,
city)
manages (personname,
managername)
Give an
expression in the relational algebra to express each of the following queries:
a. Find the
names of all employees who work for “First Bank Corporation”.
b. Find the
names, street addresses, and cities of residence of all employees who work for
“First Bank Corporation” and earn more than $10,000.
c. Find the
names of all employees in this database who live in the same city as the
company for which they work.
11. For the
above database (QNo. 10), write the expression in tuple calculus.
12. What is
data type? What are the various data types supported by standard SQL?
13. What is
the use of constraints? What are the different types of constraints that can be
specified? Explain with examples.
14. Write
SQL statements for queries in QNo 9 (a), (b), (c).
15. Write
SQL statements for queries in QNo 10 (a), (b). (c).
16.Consider
the following table called Persons
|
Pid
|
Last name
|
Firstname
|
Address
|
city
|
|
1
|
Hansen
|
Ola
|
Timetoeivn-10
|
Sandnes
|
|
2
|
Svendson
|
Tove
|
Brazil-50
|
Sandnes
|
|
3
|
Petterson
|
Kari
|
Storgt-20
|
Stavanger
|
|
4
|
Joseph
|
ole
|
Brazil-20
|
Sandnes
|
a.
Write a query to select the persons with first name ‘Tove’ and last name
‘Svendson’?
b.
Write a query to select the persons living in city that starts with S?
c.
Write a query to select persons living in a city that contains the pattern ‘tav’?
Faculty
Incharge: Hashmi S A
