Computer Science 455

 

 

 

Final  Exam

 

 

 

 

 

Name __________________________

 

 

 

 

 

 

 

 

 

 

Thursday, May 11, 2000

200 pts.

I.          BASICS

 

1.         (30 pts.)           You have just been hired as the vice-president in charge of finance at YoyoDyne, Inc., a large aerospace manufacturing firm.  Your boss has asked you for an "executive summary" proposal for a database system for the financial records for the company (things have been kept in a rather haphazard fashion up to now).  Explain to your boss what a database system is, what problems it helps to solve (over a system of individual files), being careful to define such terms as data independence, reduction of redundancy, etc. and explaining why they are good things.  Your boss is an intelligent person, so explain things clearly and concisely.  The following page is available for additional writing.  Please look at the next two problems before proceeding with this one.

 

 

(additional workspace for problem 1)

 

2.         (25 pts.)           You have decided to recommend that a relational database system be selected for the financial records project (you do not need to specify a particular product).  Explain what this means to your boss (again recalling that your boss is an intelligent person), explaining any terms that you use.  Be sure to discuss the integrity constraints for relational databases, and explain some of the reasons that the relational approach may be superior to the hierarchical or network approach.  The next page contains some additional space should you need it (I think I'll do bluebooks next year).

(additional workspace for problem 2)

3.         (15 pts.)           Your  boss is aware of discussions about object oriented database systems.  Briefly outline some of the weaknesses of the relational model, and say how some of these can be resolved with an object oriented model.  List some of the chief features of the object-oriented model.

 

II.         Design

 

1.         (20 pts.)

a.         From the following written description, develop an entity-relationship diagram using the notation developed in class:

 

            An automobile dealership is divided into regions, each supervised by a regional sales manager (SSN, MNAME) who supervises a number of salespeople (SSN, SNAME), each of whom reports only to one regional sales manager.  Salespeople sell cars (VIN, MAKE, YEAR) to customers (CNO, CNAME), and the details of the sale (DATE, AMOUNT) are recorded for each sale.  Customers work with any salesperson they want to, and salespeople work with as many customers as possible.

 

            (a second part to this question follows on the next page)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

b.         From your entity-relationship diagram, develop a relational schema, indicating primary and foreign keys.

 

 

 

 

 

 

 

 

 

 

 

 

 

2.         (20 pts.)           Consider the following functional dependency diagram for the relation R(A,B,C,D,E):

 


1.         What is the key of the relation R?

 

 

 

 

 

 

 

 

(further problems  with this diagram on the next page)

 

2.         Identify violations of 2NF, and decompose the relation so as to remove these violations of 2NF (only)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3.         Identify a violation of 3NF in your answer to part (2) on the preceding page, and decompose the problem relation further to remove this violation.

 

 

 

 

 

 

 

.III.      SQL and Developer 2000 questions.

 

            The following questions are all with respect to the Hoh River College database:

 

            STUDENT(SNO, SNAME, MAJOR, ADVISOR)

                        (key:  SNO)

            FACULTY(FNO, FNAME, DEPT)

                        (key: FNO)

            CLASS(CNO, CNAME)       

                        (key:  CNO)

            ENROLL(SNO, CNO, GRADE)

                        (key (SNO, CNO))

            TEACHES(FNO, CNO)

                        (key:  FNO (i.e., each class has only one teacher)

 

            The ADVISOR field in the STUDENT table is the FNO of that student's advisor.  ENROLL(S02, C04, 3.33) indicates that the student with SNO=S02 enrolled in the class with CNO=C04 and earned a B+ in the class.  TEACHES(F12, C03) indicates that the member of the faculty with FNO=F12 teaches the class with CNO=C03.

 

            Write your answers to problems 1 - 7 of the following in SQL.

 

1.         (10 pts.)           Assuming that GRADE is a number, and that SNO and CNO are foreign keys and are each character strings of length 5, write the CREATE TABLE statement for ENROLL.

 

 

 

2.         (10 pts.)           Use SQL to create a view consisting of students majoring in CSCI (i.e., MAJOR='CSCI') together with the names of their advisors.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3.         (10 pts.)           Add 0.33 to the GRADE for all students registered in courses with CNAME='DBMS'

4.         (10 pts.)           Find the names of students taking a course from their advisor.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

5.         (10 pts.)           Find the names of students taking no courses from their advisor

 

6.         (15 pts.)           Find the names of students taking all of the courses currently taught by their advisor.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

7.         (10 pts.)           For each course, find the name of the course, the name of the person teaching the course, and the class average in the course (average GPA over ENROLL records for this course).

 

8.         (15 pts.)           (not a SQL question) Briefly outline the steps that you would follow to create a master-detail form linking student records with the ENROLL records for courses for which the student is enrolled.