Database Schema:

Book(bookID, ISBN, title, author, publish-year, category)

Member(memberID, lastname, firstname, address, phone-number, limit)

CurrentLoan(memberID, bookID, loan-date, due-date)

History(memberID, bookID, loan-date, return-date)

Members can borrow books from the library. The number of books they can borrow is limited by the “limit” field of the Member relation (it may differ for different members). The category of a book includes fiction, non-fiction, children’s and reference. The CurrentLoan table represents the information about books that are currently checked out. When the book is returned to the library, the record will be removed from CurrentLoad relation, and will be inserted into History relation with the return-date. A library may have more than one copy of the same book, in which case each copy has its own bookID, but all copies share the same ISBN.

Write SQL statements for each of the following questions.

(1) (16 pts) Create all the relations listed above. Make sure to indicate the primary key and the foreign keys (if any) in your statements.

(2) (10 pts) Insert at least 5 members, 10 books, and enough tuples in the CurrentLoan and History relation. Add tuples as needed to be able to test the following queries for different test cases.

(3) (8 pts) Find the book ID, title, author, and publish-year of all the books with the words “XML” and “XQuery” in the title. These two keywords can appear in the title in any order and do not have to be next to each other. Sort the results by publish year in descending order.

(4) (8 pts) Find the book ID, title, and due date of all the books currently being checked out by John Smith.

(5) (8 pts) Find the member ID, last name, and first name of the members who have never borrowed any books in the past or currently.

Note:

• Please put all the SQL statements in a single file and name it as hw2_yourPirateID.sql.

• Remember to change all the dash “-” in attribute names to underscore “_”.

• For the ease of testing, please add the “drop table” statements at the beginning of your file to drop all the tables.

• Remember to add “commit;” after your last “insert” statement.

• Comment your code as needed. You can use /* … */ to have multi-line comments or use double hyphen (--) for single line commenting. You may use PROMPT … to print any message to the screen. For example, you may use PROMPT Answer for Question 3 (before the query for question 3).

• For this assignment, you need to test your solutions in Oracle and submit your .sql file

Deterioration of buildings, bridges, and other structures through the rusting of iron costs millions of dollars every day. Although the actual process also requires water, a simplified equation (with rust shown as Fe2O3) is: 4 Fe(s) + 3 O2(g) → 2 Fe2O3(s) ΔHrxn = −1.65 × 103 kJ (a) What is the ΔHrxn when 0.250 kg of iron rusts? × 10 kJ (b) How much rust forms when 2.60 × 103 kJ of heat is released? × 10 g Fe2O3

1. The city of Gadsden, Alabama (where Noah’s parents live) is considering replacing one of the bridges that cross the Coosa River that runs through town. The primary benefits of replacing the current bridge are reductions in commute time, traffic, and better foot traffic safety for people walking across the bridge. The expected cost of the bridge is $8 million. The expected benefits from primary sources over the coming years are $4 million immediately, and $1 million each year over the next 4 years. Discuss the cost-benefit analysis associated with this project. Is it possible to improve the cost-benefit analysis? Is anything missing from it?. Should the city go forward with the project? Why or why not?

Bridges and Lloyd, an accounting firm, provides consulting and tax planning services. For many years, the firm's total administrative cost (currently $250,000) has been allocated to services on the basis of billable hours to clients. A recent analysis found that 65% of the firm's billable hours to clients resulted from tax planning services, while 35% resulted from consulting services.

The firm, contemplating a change to activity-based costing, has identified three components of administrative cost, as follows:

A recent analysis of staff support found a strong correlation between the number of staff personnel and the number of clients served (consulting, 20; tax planning, 60). In contrast, in-house computing and miscellaneous office cost varied directly with the number of computer hours logged and number of client transactions, respectively. Consulting consumed 30% of the firm's computer hours and had 20% of the total client transactions.

Assuming the use of activity-based costing, the proper percentage to use in allocating staff support costs to tax planning services is:

Multiple Choice

• 20%.

• 60%.

• 65%.

• 75%.

• 80%.

1) Locate an article about a subject that is of interest to you. It could pertain to a career issue, an academic issue, or a life issue. 2) Analyze the article and identify the audience the author intended to read it, as well as the situation that might have influenced its writing. 3) Evaluate the quality of the writing; propose how the author might have revised or proofread the the article to make it more readable and appropriate for the intended audience. 4) Share with your peers your response to the ideas expressed in the article. Do you agree? Disagree? Why? Why not?

3. A telephone survey of university students was conducted to estimate the prevalence of respiratory disease in the student population. What type of study is this? (1 mark) a) Case-control b) Cohort c) Cross-sectional d) Longitudinal e) Cluster-randomised trial 3b. Could this study be affected by selection bias? If so, how? Answer in complete sentences. (1 mark) 3c. How would such bias affect the prevalence estimate? Answer in complete sentences. (1 mark)

We consider a randomized experiment, the Tennessee STAR experiment, where students and teachers are randomly assigned to either a small class (15 students) and a regular class (24 students).

We want to estimate the effect of smaller class in primary school and use the following linear model:
Score = β0 + β1ClassSize + Controls + u,
where Score is student’s academic score, Class Size is dummy for small class, and controls includes free lunch status, race, gender, teacher characteristics and so on.

However, you estimate the following model instead:
Score = α0 + α1ClassSize + v

A. Provide the conditions for the OLS estimator for α1 to be unbiased.
B. Provide the Gauss-Markov assumptions for the OLS estimator for α1.
C. Evaluate the sign and the magnitude of bias α1 if teacher’s experience has positive effect on score and more experienced teachers are more likely to be assigned to regular class.
D. Suppose that teachers and students are randomly assigned to either a small class (15 students) or a regular class. Compare α1 to β1.
E. How does the OLS estimator for β1 change as we additionally include parental characteristics as Controls?

You collect several thousand Drosophila melanogaster individuals from the UC Davis experimental orchard in Winters. Youuse1000 of these flies to establish a laboratory population, which you maintain at a census population size of 1000 each generation. You then establish from the remaining field-collected flies a series of replicated populations of size 10, 100, 200, and 500and maintain each at the starting size (10, 100, 200, 500) for several generations. After some time, you sequence each lab population.

a. If one plotted for each lab population, the frequency of each nucleotide variant vs. its true frequency in the UCD population, how would the correlations differ across lab populations?

b. Which lab populations do you think would provide the best estimate of the true UCD frequencies? Why?

c. Now imagine that one carried out the same type of correlation analysis of allele frequencies ,but instead of comparing each population to the true UCD frequencies you compare the allele frequency of the replicated populations to each other (e.g., the populations of size 10 are compared to one another, the populations of size 100 are compared to one another, etc.). How would the pairwise correlations of frequency vary from one population size to another?

d. What two aspects of the sampling of flies in this entire experiment would lead to allele frequency deviations from the true UCD frequencies for sites free of natural selection?

e. You measure sequence divergence between each lab population and the sibling species, Drosophila simulans. How will the expected divergence vary across replicated populations of different size? Why?