Database Management Systems 1 Computer Science

Chapter 5

5.1 Describe the circumstances in which you would choose to use embedded SQL rather than SQL alone or only a general-purpose programming language.

Answer:

5.2 Write a Java function using JDBC metadata features that takes aResultSet as an input parameter, and prints out the result in tabular form, with appropriate names as column headings.

Answer:

5.3 Write a Java function using JDBC metadata features that prints a list of all relations in the database, displaying for each relation the names and types of its attributes.

Answer:

5.4 Show how to enforce the constraint “an instructor cannot teach in two different classrooms in a semester in the same time slot.” using a trigger (remember that the constraint can be violated by changes to the teachesrelation as well as to the section relation).

Answer:

5.5 Write triggers to enforce the referential integrity constraint from sectiontotimeslot, on updates to section, and time

in Figure 5.8 do not cover the update operation.slot. Note that the ones we wrote 5.6 To maintain the tot cred attribute of the studentrelation, carry out the fol-lowing:

a. Modify the trigger on updates of takes, to handle all updates that canaffect the value of tot

b. Write a trigger to handle inserts to the takes relation.cred.

c. Under what assumptions is it reasonable not to create triggers on thecourse relation?

Answer:

5.7 Consider the bank database of Figure 5.25. Let us define a view branch custas follows:

     create view branch cust as

     select branch name, customer name

     from depositor, account

     where depositor.account number = account.account number

Answer:

5.8 Consider the bank database of Figure 5.25. Write an SQL trigger to carry out the following action: On delete of an account, for each owner of the account, check if the owner has any remaining accounts, and if she does not, delete her from the depositor relation.

Answer:

5.9 Show how to express group by cube(a, b, c, d) using rollup; your answer should have only one group by clause.

Answer:

5.10 Given a relation S(student, subject, marks), write a query to find the top n students by total marks, by using ranking.

Answer:

5.11 Consider the sales relation from Section 5.6.Write an SQL query to compute the cube operation on the relation, giving the relation in Figure 5.21. Do not use the cube construct.

Answer:

5.12 Consider the following relations for a company database:

• emp (ename, dname, salary)

• mgr (ename, mname) and the Java code in Figure 5.26, which uses the JDBC API. Assume that the userid, password, machine name, etc. are all okay. Describe in concise

English what the Java program does. (That is, produce an English sentence like “It finds the manager of the toy department,” not a line-by-line description of what each Java statement does.)

Answer:

5.13 Suppose you were asked to define a class MetaDisplay in Java, containing a method static void printTable(String r); the method takes a relation name r as input, executes the query “select * from r”, and prints the result out in nice tabular format, with the attribute names displayed in the header of the table.

import java.sql.*;

public class Mystery {

public static void main(String[] args) {

try {

Connection con=null;

Class.forName("oracle.jdbc.driver.OracleDriver");

con=DriverManager.getConnection(

"jdbc:oracle:thin:star/X@//edgar.cse.lehigh.edu:1521/XE");

Statement s=con.createStatement();

String q;

String empName = "dog";

boolean more;

ResultSet result;

do {

q = "select mname from mgr where ename = ’" + empName + "’";

result = s.executeQuery(q);

more = result.next();

if (more) {

empName = result.getString("mname");

System.out.println (empName);

}

} while (more);

s.close();

con.close();

} catch(Exception e){e.printStackTrace();} }}

a. What do you need to know about relation r to be able to print the result in the specified tabular format.

b. What JDBC methods(s) can get you the required information?

c. Write the method printTable(String r) using the JDBC API.

Answer:

5.14 Repeat Exercise 5.13 using ODBC, defining void printTable(char *r) as a function instead of a method.

Answer:

5.15 Consider an employee database with two relations

     employee (employee name, street, city)

     works (employee name, company name, salary)

     where the primary keys are underlined. Write a query to find companies

     whose employees earn a higher salary, on average, than the average salary at “First Bank Corporation”.

a. Using SQL functions as appropriate.

b. Without using SQL functions.

Answer:

5.16 Rewrite the query in Section 5.2.1 that returns the name and budget of all

departments with more than 12 instructors, using the with clause instead of using a function call.

Answer:

5.17 Compare the use of embedded SQL with the use in SQL of functions defined in a general-purpose programming language. Under what circumstances would you use each of these features?

Answer:

5.18 Modify the recursive query in Figure 5.15 to define a relation

     prereq depth(course id, prereq id, depth)

where the attribute depth indicates how many levels of intermediate prerequisites are there between the course and the prerequisite. Direct prerequisites have a depth of 0.

Answer:

5.19 Consider the relational schema

     part(part id, name, cost)

     subpart(part id, subpart id, count)

A tuple (p1, p2, 3) in the subpart relation denotes that the part with part-id p2 is a direct subpart of the part with part-id p1, and p1 has 3 copies of p2.

Note that p2 may itself have further subparts. Write a recursive SQL query that outputs the names of all subparts of the part with part-id “P-100”.

Answer:

5.20 Consider again the relational schema from Exercise 5.19. Write a JDBC function using non-recursive SQL to find the total cost of part “P-100”,including the costs of all its subparts. Be sure to take into account thefact that a part may have multiple occurrences of a subpart. You may userecursion in Java if you wish.

Answer:

5.21 Suppose there are two relations r and s, such that the foreign key B of r references the primary key Aof s. Describe how the trigger mechanism canbe used to implement the on delete cascade option,when a tuple is deleted from s.

Answer:

5.22 The execution of a trigger can cause another action to be triggered. Most database systems place a limit on how deep the nesting can be. Explain why they might place such a limit.

Answer:

5.23 Consider the relation, r , shown in Figure 5.27. Give the result of the following query:

uilding

room

number

time

slot

id

course

id

sec

id

Garfield Garfield Saucon Saucon Painter Painter

359

359

651

550

705

403

A B A C D D

BIO-101 BIO-101 CS-101 CS-319 MU-199 FIN-201

1 2 2 1 1 1

     select building, room number, time slot id, count(*)

     from r

     group by rollup (building, room number, time slot id)

Answer:

5.24 For each of the SQL aggregate functions sum, count, min, and max, show how to compute the aggregate value on a multiset S1 ∪S2, given the aggregate values on multisets S1 and S2.

On the basis of the above, give expressions to compute aggregate values with grouping on a subset S of the attributes of a relation r (A, B,C, D, E), given aggregate values for grouping on attributes T ⊇S, for the following aggregate functions:

a. sum, count, min, and max

b. avg

c. Standard deviation

Answer:

5.25 In Section 5.5.1, we used the student grades view of Exercise 4.5 to write a query to find the rank of each student based on grade-point average.

Modify that query to show only the top 10 students (that is, those students whose rank is 1 through 10).

Answer:

5.26 Give an example of a pair of groupings that cannot be expressed by using a single group by clause with cube and rollup.

5.27 Given relation s(a, b, c), show how to use the extended SQL features to generate a histogram of c versus a, dividing a into 20 equal-sized partitions

(that is, where each partition contains 5 percent of the tuples in s, sorted by

a).

Answer:

5.28 Consider the bank database of Figure 5.25 and the balance attribute of the account relation. Write an SQL query to compute a histogram of balance values, dividing the range 0 to the maximum account balance present, into three equal ranges.

Answer: