E Book: Ricardo, Catherine M., and Susan Urban..
(2017). Databases Illuminated (3). Burlington, MA: Jones & Bartlett Learning.
1) Instructions
For each of the following, write SQL statements to create views where needed and to grant the indicated privileges for the University database with this schema:
Student (stuId, lastName, firstName, major, credits)
Faculty (facId, name, department, rank)
Class (classNumbe, facId, schedule, room)
Enroll (stuId, classNumber, grade)
Give permission to read the tables Student and Class to user 201.
Create a view of Enroll that does not include the grade attribute, and give user 201 permission to read and update the view.
Create a role that includes reading Student, Class, and the view created in 2. Give that role to all clerks in the Deans office, which includes users 202, 203, 204, and 205.
Give permission to user 206, an assistant dean, to read and modify (insert, delete, update) the Faculty and Class tables. This user can authorize others to read and modify Class but not Faculty.
User 206 authorizes user 300 to read Class. Write the command to do this.
You will submit a Word document with the required SQL statements.
2)Instructions
Assume the following transactions are to be performed:
Transaction S:
read(a);
a=a+10;
write(a);
read(b);
b=b*5;
write(b);
Transaction T:
read(a);
a=a*2;
write(a);
If the initial value of a is 10 and the initial value of b is 20, what are their final values if we perform the transactions serially, using order S,T?
Using the same initial values, what are the final values of a and b if the order of execution is T,S?
Does this result have any implications for serializability?
Write a concurrent schedule for transactions S and T that illustrates the lost update problem.
Apply the standard two-phase locking protocol to the schedule you devised in step 4. Will the protocol allow the execution of that schedule? Does deadlock occur?
You will upload a Word document with the answers to the questions.
3) Instructions
Use the data distribution scheme for a distributed University example described below.
A University has multiple locations across a wide geographic area (large city?). There are servers located at each of the five campuses (North, South, East, West, and Main campuses).
Each of the campuses is connected to the University network. The servers at each site contain the following tables: Student, Faculty, Class, and Enroll. The primary copy of the Enroll table is on the Main campus server; secondary copies of the Enroll table exist on the North, South, East and West campus servers.
Describe two strategies for this query:
Find the ClassNumber and the names of all students enrolled in all classes with schedule MWF9.
Describe two strategies for this query:
Find the ClassNumber, facId of the teacher, and the campus of all classes being taken by RosemaryHughes, who is a student at the North campus.
Consider the query “Find the ClassNumber, schedule, and lastName of all enrolled students for all classes being taught by Professor Smith of the North campus.” The query is entered at the North campus.
Describe a strategy that does not use the semijoin.
Consider the query “Find the ClassNumber, schedule, and lastName of all enrolled students for all classes being taught by Professor Smith of the North campus.” The query is entered at the North campus.
The post E Book: Ricardo, Catherine M., and Susan Urban. first appeared on COMPLIANT PAPERS.
