Computer Science 455

Take-home final exam

Due: 6:00 PM, Friday, May 14(last day of finals)

If you want to turn this exam in between 4:00 PM and 6:00 PM on Friday, May 14, you will need to go to Howarth 112, where I will be giving a CSci 161 final exam.

Ground rules: You may consult the textbooks and course notes, or other textbooks or published papers. It is my intention in writing this exam that you will only require the textbooks and course notes. You may not consult any person about this exam except me. Group efforts or consultations with other individuals on this exam are strictly prohibited.

Exams should be printed (word processor) and stapled. Hand-written answers will not be considered. Each question should start on a new page, and questions should be submitted in order. Points will be deducted for not following these guidelines.

Please work 4 of the following 6 questions. Please do not submit more than 4 answers – I will not look at more than the first 4 in any case.

1. In the form of a memorandum to your boss, extol the virtues of a relational database management system. In your memorandum, you should outline some of the benefits of a database management system in general (highlighting and describing such things as data independence, user views, administrative benefits, etc.), and list and describe some of the benefits of the relational model in particular.

2. In several exercises this term, we have considered an order-invoice problem, and this is a modest extension to those exercises. A customer (cno, cname, address) has a number of orders (ordno, date, total-cost) each of which consists of a number of lines (lineno, item#, description, unit-cost, amt-ordered, cost). The (item#,description, unit-cost) fields come from a catalog. Let us suppose further that for each order a company salesperson (sno, sname) has worked with the customer in developing the order.

1. Construct an entity-relationship diagram for the above.
2. Sketch (using diagrams) how you would implement this in a hierarchical database. Ignore the role of the salesperson in this design (why?).
3. Sketch (again using diagrams) how you would implement this in a network/codasyl database. Include the role of the salesperson if possible.
4. Give a relational schema for the database, and follow this by CREATE TABLE statements in SQL, making sure to create integrity constraints to enforce entity and referential integrity.

3. Yoyodyne Software Consultants, Inc. work on a number of projects for a number of clients. Each project (PNO,PNAME) is associated with one client (CNO, CNAME), but a client may have several projects going at one time. The employees of Yoyodyne are divided into two groups: managers (ENO, ENAME, SALARY), and programmers. Programmers also have (ENO, ENAME, SALARY) attributes but, in addition have a multi-valued skill code (SC) attribute (i.e., an employee can have several skill codes), and, for programmers, we want to keep track of degrees earned (UnivName, DegreeName, DegreeType, YearEarned). We also keep track of the universities our employees come from, recording (UnivName, City, State) for each of them (i.e., universities are entities in their own right, but we only store information about universities which current employees came from). Finally, projects are supervised by a manager (and only one manager, though one manager may supervise several projects), employees work on a variety of projects (devoting a percentage of their time to each), and projects have a number of employees who work on them.

1. Construct an entity-relationship diagram for this situation.
2. From the entity-relationship diagram, construct a relational schema. Indicate primary and foreign keys.
3. Write the SQL CREATE TABLE statements necessary to implement your relational schema. Include integrity constraints (including primary key, foreign key, and not null) as appropriate.
4. Construct at least one appropriate index, and say why it might be appropriate.

3. Consider the following form:

1. Customer Number => Customer Name
2. Travel Agent => Agent office => City
3. Flight numbers (Flt#) are unique for an itinerary (i.e., there will not be two flights with the same number for a given itinerary)
4. Itinerary Number => Customer Number and Travel Agent

1. Write the form as an un-normalized table. Notice the repeating fields.
2. Removing only the repeating fields, decompose the table into tables in first normal form. Describe (briefly) what was wrong with the original form, and how your decomposition solved the problem.
3. Attending only to partial key dependencies, decompose the table into tables in second normal form. Again, describe briefly what was wrong with the tables in the original form, and how your decomposition solved the problem.
4. Attending now to transitive dependencies, bring the tables into third normal form.
5. sketch a final entity-relationship diagram.

1. The Hoh River College has the following relational schema:

1. Find the names of students advised by Kerrick (FNO in STUDENT is the FNO of that student’s advisor. Assume that there is only one FACULTY entry with FNAME=’KERRICK’).
2. Find the names of students who have taken no courses offered by Matthews.
3. (SQL only) Find the names of students who have taken no courses taught by their advisor)
4. Find the names of students who have taken all of the courses offered by F12 (i.e.,for all TEACHES records with (F12, CXX) there is an ENROLL record saying that this student has taken CXX).
5. (SQL only) For each class taught, retrieve the course number, course name, and average grade in the class.

1. Some theory.

1. Begin by explaining why a relational database management system is called relational. Include in your discussion careful definitions of domains, the (mathematical) definition of relation, and provide a "key" relating relation/tuple terms with table/row/column terms.
2. One concern about current relational database management systems is that they do not provide support for domains. What do we mean by this?
3. In terms of the mathematical definition of function, give a formal interpretation of the statement that, in a given relation R, attribute A functionally determines attribute B.
4. A predicate is a Boolean function with a number of variables (i.e., the predicate older_than(X,Y) is true if X is older than Y, but false otherwise). A relation is a subset of some Cartesian product. How do these two terms relate to each other? Which is the approach taken in relational tuple calculus languages (quel, most of SQL), and which in relational domain calculus languages (QBE, Prolog)?
5. When we say that SQL is as powerful as the (formal) relational calculus (either tuple or domain) and that the relational calculus is as powerful as the (full) relational algebra, exactly what do we mean?