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.

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 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/[email protected]//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:

Get 20% Discount on This Paper
Pages (550 words)
Approximate price: -

Try it now!

Get 20% Discount on This Paper

We'll send you the first draft for approval by at
Total price:
$0.00

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

Custom Writings Help is a Quality-Oriented Company in Online Writing as a result of hiring exceptional professionals to execute clients' tasks.

Essays

Research Papers

At Custom Writings Help,We understand the struggle of research paper writing, and that is why at Custom WritingS Help, we are all out to help you. We pride ourselves on having a team of clinical writers. The stringent and rigorous vetting process ensures that only the 'BEST' Writers are chosen for the job. We have highly qualified Ph.D. and MA writers working with us; we equally offer these experienced writers specific bonuses and incentives to make them deliver highly original, unique, and informative content at reasonably low prices.

Admissions

Thesis Writing Service

Worlwide, Many Masters Students are struggling with Thesis Completion. A thesis is likely to be the longest and most challenging piece of work a student has ever completed. However, unlike essays and other assignments, a student can pick a particular interest topic and work on their initiative. Fortunately, we are there for you. At Custom Writings Help, you are assured of an authentic, imaginative, informative, linguistically great, and advantageous thesis that adheres to all your needs. So, why continue considering different writers when you have discovered the best in the field?

Editing

Custom Papers

Not a single student can avoid writing custom papers. However, a total lack of experience, skills, and time makes it very hard to produce a superb writing piece. Therefore, if you are seeking professional help, turn to us. Our specialized and experienced writers compose a variety of model papers, including custom essays, college term papers, research papers, book reports, MBA essays, executive summaries, dissertations, Ph.D. theses, admission essays, and research proposals for college and university students at any level.

Coursework

Essay Writing

Most of the students disregard the critical principles of essay writing and compose papers below sensible guidelines. Therefore, with Custom Writings Help, one should not worry about his/her essay. Our Writers compose informative and engaging content on all complexities and topics. We write meaningful and smart essays while prioritizing all aspects that bring about a good grade, such as impeccable grammar, proper structure, zero-plagiarism, and conformance to guidelines.

Coursework

Coourse Work Writing

Don't let the seemingly never-ending onslaught of writing assignments get you down. If you are looking where to get course work assistance online, the writers at Custom Writings Help are here to assist you with all of your writing needs. We undertake to unique delivery of papers that meet the professor's requirements. The content is proofread, edited, and checked plagiarism before submission to customers. No matter how big or small your work is, we will deliver on time. Try US Now! !

Coursework

Dissertation Writing Service

High-Quality Dissertation Writing Services are rare. They require Ph.D. academicians – not easily found. However, are an exception. The years, time, and resources we have invested in the dissertation world has given us a competitive advantage over others. Choose to come to Custom Writings Help; You will find perfect Ph.D. consultants who have written hundreds of dissertations theses ready to help you. Let our dissertation-writing services help you craft your dissertation, for you are assured we will give you the results.

Open chat