Database management systems 1 | Computer Science homework help

 

Don't use plagiarized sources. Get Your Custom Essay on
Database management systems 1 | Computer Science homework help
Get an essay WRITTEN FOR YOU, Plagiarism free, and by an EXPERT!
Order Essay

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:

Order a unique copy of this paper
(550 words)

Approximate price: $22

Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Professor’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
  • 2-hour delivery
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

We value our customers and so we ensure that what we do is 100% original..
Getting into any engagement is not as simple as it sounds, especially when done remotely. Thus, you as our client need solid assurances that your expectations will be met and exceeded. Taking this into consideration, we are very serious when it comes to honoring our end of the bargain. On this page, we have clearly stipulated our assurances to clear any anxiety, doubts, or uncertainties that you may have. We are sure that after reading the below short guarantee highlights, you will be entirely confident that your needs will be exceeded!

Money-back guarantee

Our money-back guarantee assures you of the safety of your money. Several reasons may make you want to get a refund, and we have the best refund system since it works INSTANTLY! Yes, I said instantly! Many of our competitors place complex processes for you to get a refund. Some websites make you wait for 14 days! For what? We know that you obviously have other uses for the money; that is why we refund on the spot. Here at Employed Professors, we simply look into the issue and press a button, and your money is back. No waiting! No back and forth!

Zero-plagiarism guarantee

Being the worst academic offense that is severely punished by all institutions, plagiarism is a NO at Employed Professors! In fact, we are always ready with evidence that your paper is plagiarism-free.

Our elite algorithm for detecting plagiarism is updated regularly, as our developers work 24/7 to make sure that it captures even the slightest instance. We provide a plagiarism report FREE OF CHARGE so that you are confident when submitting your paper to that stickler professor or TA. This way, we ensure that you will never get expelled for this heinous act!

Free-revision policy

Sometimes, for absolutely no reason, your TA or professor might require you to make some changes to your paper. Other times, further customization can be needed as you deem fit; of course, you are the king, and you command, and we obey! As such, if you feel that a specific aspect has not been captured as you like, you can always send the paper back for revision, and this is also FREE OF CHARGE! All you need to clearly state are the new guidelines that you want our professor to use to accomplish your desired objective in the way that you want it.

Privacy policy

Our clients' confidentiality is our priority; this is a fact that is undeniable in our company. We CANNOT give out the data/information that we request from you even at GUNPOINT! This is how far we can go to protect your identity. Furthermore, our databases are regularly services and secured with an impenetrable security system. Over the years that we have been in this essential industry, we have never had a data breach, thanks to our top-notch technical department that ensure the confidentiality of your information. A detailed summary of our privacy policy is as below:

24/7 and on-the-spot-response system

Unlike our competitors who will make you wait even 30 minutes to respond to your inquiries, order messages, and support help, we have developed a seamless communication system that notifies our always-standby support personnel to respond to you. Furthermore, we have given our professors access to this high-tech system so that they can respond promptly to ensure that no time is wasted. Remember, this business' most valuable element is time, and delays are usually catastrophic. The last thing we would imagine is you having to lose points for lateness. Thus, we ensure that prompt communication is ever-present, without failure!

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency