Consider the following schemas and write the SQL select statements for the follo
ID: 3575803 • Letter: C
Question
Consider the following schemas and write the SQL select statements for the following queries. The underlined fields of the table are its primary key field(s)
Schemas:
Employee (SSN, empName, gender, birthDate, department}
Employee_Education (SSN, degree, graduationDate, GPA) (please note: SSN is also a FK which references the PK field of Employee table)
The Field description for above mentioned table fields is given below
Field Name
Description
SSN
Employee's Social Security Number
empName
Employee Name
gender
Employee's Gender (Male / Female)
birthDate
Employee's Birth Date
department
Department Name where employee is working
degree
Degree name (Example: Masters / Bachelors / doctorate )
graduationDate
Graduation Date
Queries: Write SQL Select Statement for all of the following Queries
1) Find the female employees who have earned a "Master's" degree before "12/07/2016" (MM/DD/YYYY) and whose name begins with the letter "K". Also display the employee records sorted by their name.
2) Find all the employees, who has secured a Bachelor's degree with a GPA >= 3.0 but have no master's degree.
3) Find the average GPA of all female employees grouped by their Degree.
Following Questions A and B are related to Transaction Management and Concurrency Control.
A) Consider a database with two tables "Employee and "Employee_Education" (Refer the table schema from Q-4) and assume that there are two transactions T1 and T2.
Transaction T1 reads records from "Employee" Table and "Employee_Education" Table and then updates (writes) the records in the "employee" table.
Transaction T2 reads the records from the "Employee" Table and "Employee_Education" Table and then updates (writes) the records in the "employee" and "Employee_Education" tables.
1. Give an example schedule with actions of transactions T1 and T2 on tables "Employee" and "Employee_Education" such that these actions results in a read-write conflict (Assume that there is no locking mechanism implemented)
2. Give an example schedule with actions of transactions T1 and T2 on tables "Employee" and "Employee_Education" that results in a write-write conflict (Assume that there is no locking mechanism implemented)
3. For each of the two schedules, show how strict two Phase Locking protocol disallows the schedule.
B) Consider the following classes of schedules: serializable, conflict serializable, view serializable. For following schedule, state which of the preceding classes it belongs to (It can belong to more than one classes) and explain why.
If you cannot decide whether a schedule belongs to a certain class based on the listed actions then explain briefly why. The actions are listed in the order they are scheduled and each table header represents the transaction name. (In the following table the term "Employee" is a Table name and the schema may be referred from Q-4. 'R' in the following table represents a read operation and 'W' represents a write operation)
Schedule:
T1
T2
R(Employee)
W(Employee)
W(Employee)
Commit
Commit
Field Name
Description
SSN
Employee's Social Security Number
empName
Employee Name
gender
Employee's Gender (Male / Female)
birthDate
Employee's Birth Date
department
Department Name where employee is working
degree
Degree name (Example: Masters / Bachelors / doctorate )
graduationDate
Graduation Date
Explanation / Answer
Query 1 :
select * from Employee e,Employee_Education Ed where e.gender='Female' AND e.ssn=Ed.ssn AND e.empName LIKE 'K%' AND e.degree='Masters' AND e.graduationDate<TO_DATE('12/07/2016') order by e.empName ASC
Query 2:
select * from Employee e,Employee_Education ed where e.ssn=ed.ssn AND ed.gpa>=3.0 AND ed.degree='Bachelors' AND e.ssn not in(select ssn from Employee_Education where degree='Masters')
: Query 3 :
select ed.degree,avg(ed.gpa) from Employee e,Employee_Education ed where e.ssn=ed.ssn AND e.gender='Female' group by ed.degree
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.