Database Management

Tutorial Questions for Discussions

1. What is a database?
2. Describe four categories of database users
3. What is database management system (DBMS)?
4. What are the benefits of database system over traditional file-based system?
5. Outline some examples of DBMS
6. What are the five characteristics of good database?
7. What are three levels of database architecture?
8. What are the three common database models?
9. What is E/R model?
10. At which level of database architecture does E/R apply?
11. What are the three components of ER diagram?
12. Draw symbols for each of the three components of E/R diagram
13. Describe three types of cardinality ratio
14. What it is a degree of a relationship?
15. Explain how many-to-many relation can be handled in a database design
16. Explain how one-to-one relationship can be handled in a database design
17. Explain how composite attributes are handled in database design
18. Explain how multivalued attributes are handed in database design
19. What is normalization? What does it try to achieve in database designing?
20. What is denormalization? What does it try to achieve in database designing?
21. What is the characteristic of an unnormalized relation?
22. What are the conditions for the relation to be in 1NF?
23. What are the conditions for the relation to be in 2NF?
24. What are the conditions for the relation to be in 3NF?
25. What are the conditions for the relation to be in BCNF?
26. What are the conditions for the relation to be in 4NF?

QUESTION 27

UPS prides itself on having up-to-date information on the processing and current location of each
shipped item. To do this, UPS relies on a company-wide information system. Shipped items are
the heart of the UPS product tracking information system. Shipped items can be characterized
by item number (unique), weight, dimensions, insurance amount, destination, and final delivery
date. Shipped items are received into the UPS system at a single retail center. Retail centers are
characterized by their type, uniqueID, and address. Shipped items make their way to their
destination via one or more standard UPS transportation events (i.e., flights, truck deliveries).
These transportation events are characterized by a unique scheduleNumber, a type (e.g, flight,
truck), and a deliveryRoute.

(a) Please create an Entity Relationship diagram that captures this information about the UPS
system. Be certain to indicate identifiers and cardinality constraints.

(b) Transform the above ER diagram into relational database schema

QUESTION 28

Production tracking is important in many manufacturing environments (e.g., the pharmaceuticals

industry, children’s toys, etc.). The following ER diagram captures important information in the
tracking of production. Specifically, the ER diagram captures relationships between production
lots (or batches), individual production units, and raw materials.

(a) Please convert the ER diagram into a relational database schema. Be certain to indicate
primary keys and referential integrity constraints.

(b) Implement the schema into physical database (MYSQL DBMS)

QUESTION 29

Normalize the following schema, with given constraints, to 3NF:

books(accessionno, isbn, title, author, publisher)

users(userid, name, deptid, deptname)
accessionno → isbn
isbn → title
isbn → publisher
isbn →→ author
userid → name
userid → deptid
deptid → deptname

QUESTION 30
In the given employee table
a) There exist insertion anomaly? If yes, then explain.
b) There exist modification anomaly? If yes, then explain.
c) There exist deletion anomaly? If yes, then explain.
QUESTION 31
Table 4-8 shows a portion of a shipment table for a large manufacturing company.
Each shipment (identified by Shipment#) uniquely identifies the shipment Origin,
Destination, and Distance. The shipment Origin and Destination pair also uniquely
identifies the Distance.
a) Show the functional dependencies in the SHIPMENT relation.
b) In what normal form is SHIPMENT? Why?
c) There exist insertion anomaly? If yes, then explain.
d) There exist modification anomaly? If yes, then explain.
e) There exist deletion anomaly? If yes, then explain.
f) Convert SHIPMENT to 3NF if necessary. Show the resulting table(s) with the
sample data presented in SHIPMENT.

QUESTION 32

Convert the following table in

a) 1NF
b) 2NF
c) 3NF
QUESTION 33

Write SQL command for a) to e) and write the output of f) on the basis of table MOV

a) Find the total value of the movie cassettes available in the library.

b) Display a list of all movies with price over 20 and sorted by price

c)Display all the movies sorted by QTY in decreasing order.

d)Display a report listing a movie number, current value and replacement value for each movie
in the above table. Calculate the replacement value for all movies as QTY * Price * 1.15

e) Count the number of movies where Rating is not “G”

f) Give the output of the following SQL statement

i.SELECT MAX(price) FROM MOV WHERE PRICE > 30

ii.SELECT SUM ( price * qty) FROM MOV WHERE QTY < 4

iii.SELECT COUNT ( DISTINT TYPE)

QUESTION 34

Consider the insurance database given below:

person (driver-id, name, address)

car (license, model, year)

accident (report-number, date, location)

owns (driver-id, license)

participated (driver-id, car, report-number, damage-amount)

Construct the following SQL queries for this relational database

.i) Find the total number of people who owned cars that were involved in accidents in 2004.

ii) Find the number of accidents in which the cars belonging to “Thakre” were involved.

iii) Delete the Mazda belonging to “S Khan”.