Database management systems 1 | Computer Science homework help


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.


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.



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.


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).


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.
c. Under what assumptions is it reasonable not to create triggers on the
course relation?


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

     create view branch cust as
branch name, customer name
     from depositor, account
     where depositor.account number account.account number


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.



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



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



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.



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.)



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;



“jdbc:oracle:thin:star/[email protected]//”);

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 =;

if (more) {

empName = result.getString(“mname”);

System.out.println (empName);


} while (more);



} 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.



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



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.



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.



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?



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.



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.



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.



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.



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.



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












Garfield Garfield Saucon Saucon Painter Painter








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)



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



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).



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




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.



Academic Brigade
Calculate your paper price
Pages (550 words)
Approximate price: -

Why Work with Us

Top Quality and Well-Researched Papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional and Experienced Academic Writers

We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.

Free Unlimited Revisions

If you think we missed something, send your order for a free revision. You have 10 days to submit the order for review after you have received the final document. You can do this yourself after logging into your personal account or by contacting our support.

Prompt Delivery and 100% Money-Back-Guarantee

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & Confidential

We use several writing tools checks to ensure that all documents you receive are free from plagiarism. Our editors carefully review all quotations in the text. We also promise maximum confidentiality in all of our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.


Essay Writing Service

No matter what kind of academic paper you need and how urgent you need it, you are welcome to choose your academic level and the type of your paper at an affordable price. We take care of all your paper needs and give a 24/7 customer care support system.


Admission Essays & Business Writing Help

An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.


Editing Support

Our academic writers and editors make the necessary changes to your paper so that it is polished. We also format your document by correctly quoting the sources and creating reference lists in the formats APA, Harvard, MLA, Chicago / Turabian.


Revision Support

If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.