Databases | SQL | Oracle | MS Access Answers

Consider the following schedule of transactions W1(A) W2(A) W1(A) W2(A) W1(A) Which of the following properties are true for the above schedule and justify the reason for your answer with explanation? a) Conflict Serializable b) View Serializable c) Recoverable 

Write a query to create a view that finds the salesman who has the customer with the highest order at least 3 times on a day. 

Relation1: Customer

customer_id | cust_name | city | grade | salesman_id

-------------+----------------+------------+-------+-------------

3002 | Nick Rimando | New York | 100 | 5001

3007 | Brad Davis | New York | 200 | 5001

3005 | Graham Zusi | California | 200 | 5002

3008 | Julian Green | London | 300 | 5002

3004 | Fabian Johnson | Paris | 300 | 5006

3009 | Geoff Cameron | Berlin | 100 | 5003

3003 | Jozy Altidor | Moscow | 200 | 5007

3001 | Brad Guzan | London | | 5005

Relation2: elitsalesman

salesman_id | name | city | commission

-------------+------------+----------+------------

5001 | James Hoog | New York | 0.15

5002 | Nail Knite | Paris | 0.13

5005 | Pit Alex | London | 0.11

5006 | Mc Lyon | Paris | 0.14

5007 | Paul Adam | Rome | 0.13

5003 | Lauson Hen | San Jose | 0.12

Create a package named as INFO which contains procedure that is passed a student’s identification number and return student’s full name and phone number from the student table to the calling program and function, pass a department number to it. If the DEPT table does not contain the department number, return a FALSE value otherwise return a TRUE value. Print the appropriate message to the calling program.

Consider the following schedule of transactions

W1(A) W2(A) W1(A) W2(A) W1(A)

Which of the following properties are true for the above schedule and justify the reason for your answer

with explanation?

a) Conflict Serializable

b) View Serializable

c) Recoverable

. Consider the following relation with set of functional dependencies

R(ABCDEF)

ABC->D, ABD->E, CD->F, CDF->B, BF->D

a) Identify the candidate key(s) in the relation (with proper steps followed).

b) Identify which normal form this relation is in and reason for the answer.

Consider the following relation with set of functional dependencies R(ABCDEF) AB->CD, CD->EF, BC->DEF, D->B, CE->F a) Identify the candidate keys in the above relation (with proper steps followed). b) Identify which normal form this relation is in and reason for the answer.

Consider the following schedule of transactions

R1(A) W1(A) R2(A) W2(B) W1(B) Commit1 Commit2

Which of the following properties are true for the above schedule and justify the reason for your answer

with explanation?

a) Conflict Serializable

b) View Serializable

c) Recoverable

Consider a schedule with 4 transactions with 1, 2, 3, and 4 operations respectively. Calculate the possible

number of

a) Serial schedules

b) Non serial schedules

.Consider a schedule with 3 transactions with 2, 3, and 4 operations respectively. Calculate the possible

number of

c) Serial schedules

d) Non serial schedules

Consider the following schedule of transactions

R1(A) W1(A) R2(A) W2(B) W1(B) Commit1 Commit2

Which of the following properties are true for the above schedule and justify the reason for your answer

with explanation?

a) Conflict Serializable

b) View Serializable

c) Recoverable