Exercises

Exercises: In the following set of exercises, reference to chapters and figures are made to: Fundamentals of Database Systems, Elmasri and Navathe, Benjamin Cummings, 3rd edition (reference to 2nd edition in parentheses).

Exercise 1: (5 points)

Consider the following set of requirements for a university database that is used to keep track of students transcripts.

a. The university keeps track of each student's name, student number, social security number, current address and phone, permanent address and phone, birthdate, sex, class (freshman, sophomore, ..., graduate), major department, minor department (if any), and degree program (B.A., B.S., ..., Ph.D.). Some user applications need to refer to the city, state, and zip of the student's permanent address, and to the students' last name. Both social security number and student number have unique values for each student.

b. Each department is described by a name, department code, office number, office phone and college. Both name and code have unique values for each department.

c. Each course has a course name, description, code number, number of semester hours, level, and offering department. The value of code number is unique for each course.

d. Each section has an instructor, semester, year, course, and section number. The section number distinguishes different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, ...; up to the number of sections taught during each semester.

e. A grade report has a student, section, and grade.

Design an ER schema for this application, and draw an ER diagram for that schema. Specify key attributes of each entity type and structural constraints on each relationship type. Note any specified requirements, and make appropriate assumptions to make the specification complete.

Exercise 2: (5 points) (Programming Required)

The purpose of this exercise is to introduce the concepts of relational database. You will use Oracle to do the following:

(a) Define a relation called STUDENT, with the following attributes:
student-name social-security-number course-module completion-date grade

(b) Input data into this relation STUDENT: 
NAME		SSN		MOD	DATE		GRADE
John Doe	111223333	A1	11/2/90		85
John Doe	111223333	A2	11/10/90	90
John Doe	111223333	B1	10/5/90		70
Terry Smith	666778888	B1	7/6/90		60
Terry Smith	666778888	B2	9/10/90		55
Terry SMith	666778888	C2	3/6/90		80
Judy Smith	555667777	A1	5/1/90		85
Judy Smith	555667777	A3	9/1/90		60

(c) Formulate and answer the following queries. Can you formulate and answer every query in SQL? If not, sketch an approach to process the query.
Q1: List the name of students who have completed any course module.
Q2: List the name of students who have completed course module A1.
Q3: List the name of students who have completed at least two course modules.
Q4: List the name of students who have completed any course module during the period 1/1/90 to 6/30/90.
Q5: List the name of students who have skipped any course module. (For example, Judy Smith has skipped course module A2, and Terry Smith has skipped course module C1.)

Exercise 3: (5 points)

(i) Specify the following queries on the database schema shown in Figure 7.5 (Fig 6.5 in 2nd ed.) using (a) relational algebra and (b) SQL. Show the result of each query if applied to the database of Figure 7.6 (Fig. 6.6 in 2nd ed.). (There are six subproblems, each worth 0.5 points)

(ii) (2 points) Draw a flowchart and write in pseudo-code a program to translate simple SQL statements of the form:

into relational algebra. Document and explain your algorithm carefully. Test your program (or algorithm) on the following examples:

Exercise 4: (5 points)

(a) A PARTS file with Part# as hash key includes records with the following Part# values: 2369, 3760, 4692, 4871, 5659, 1821, 1074, 7115, 1620, 2428, 3943, 4750, 6975. The file uses 8 buckets, numbered 0 to 7. Each budget is one disk block and holds two records. Load these records into the file in the given order, using the hash function h(K) = K mod 8. Calculate the average number of block accesses for a random retrieval on Part#.

(b) Load the records of part (a) into expandable hash files based on extendible hashing. Show the structure of the directory and global and local depths at each step.

(c) A PARTS file with Part# as key field includes records with the following Part# values: 23, 65, 37, 60, 46, 92, 48, 71, 56, 59, 18, 21, 10. Suppose the search field values are inserted in the given order in a B+ tree of order p=4; show how the tree will expand and what the final tree looks like.

Exercise 5: (5 points) (Programming Required)

(i) (2 points) Do Exercise 3, subproblems (i)(a) to (i)(d) using Relational Calculus.

(ii) (3 points) Using Oracle, design a menu-driven user interface for a STUDENT INFORMATION SYSTEM supporting the processing of queries Q1-Q4 of Exercise 2. The relation is the same as the one you used in Exercise 2. The menu looks something like the following (you can design it better):

STUDENT INFORMATION SYSTEM

Please select an option A-D:
A. List the name of students who have completed any course module.
B. List the name of students who have completed a course module.
C. List the name of students who have completed at least two course modules.
D. List the name of students who have completed a course module during a specified period.

Note that after option B is selected, the user should be prompted to enter module name. Similarly, after option D is selected, the user should be prompted to enter nodule name, starting date and ending date.
(You may find the following hints helpful.)

Exercise 6: (5 points)

(i) (2 points) Define the 3NF relations from the following set of functional dependencies describing a university administrative database. Design a relational database by defining the schema for each 3NF relation.
(ii) (2 points) Design a hierarchical database schema for the university administrative database described in part (i), by drawing a schema diagram similar to the schema diagram for the COMPANY database shown in Figure D.4 (Fig 11.10 in 2nd ed.).

(iii) (1 point) Refer again to Figure D.4 (Fig 11.10 in 2nd ed.). The following is a PASCAL program to retrieve information from the COMPANY database. Describe the intended query. 
 $GET FIRST EMPLOYEE;
 while DB_STATUS = 0 do
  begin
  $GET NEXT DEPENDENT WITHIN PARENT EMPLOYEE;
  while DB_STATUS = 0 do
     begin
     if P_DEPENDENT.DEPNAME = P_EMPLOYEE.FNAME
      then writeln(P_EMPLOYEE.FNAME,P_EMPLOYEE.LNAME);
     $GET NEXT DEPENDENT WITHIN PARENT EMPLOYEE
     end;
  $GET NEXT EMPLOYEE
  end;