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 a ResultSet 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 teaches relation as well as to the section relation). Answer: 5.5 Write triggers to enforce the referential integrity constraint from section to time slot , 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 student relation, carry out the fol- lowing: a. Modify the trigger on updates of takes , to handle all updates that can affect 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 the course relation? Answer: 5.7 Consider the bank database of Figure 5.25. Let us define a view branch cust as 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…

Looking for solution of this Assignment?

WHY CHOOSE US?

We deliver quality original papers

Our experts write quality original papers using academic databases.  

Free revisions

We offer our clients multiple free revisions just to ensure you get what you want.

Discounted prices

All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount

100% originality

We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity

On-time delivery

We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay