1. What is a database transaction? List and define the transaction properties. It is important that you answer in your own words. Please use diagrams to enhance your answer.
2. Discuss Concurrency control. What is it use for? Why is it important? It is important that you answer in your own words. Please use diagrams to enhance your answer
Q3. The following question are based on these database tables:
EMPLOYEE
|
FNAME
|
MIDINT
|
LNAME
|
SSN
|
BDATE
|
ADDRESS
|
SEX
|
SALARY
|
SUPER_SSN
|
Dept_No
|
|
James
|
A
|
Borg
|
123123123
|
|
Houston, TX
|
M
|
55000
|
Null
|
1
|
|
Franklin
|
S
|
Wong
|
234234234
|
|
Houston, TX
|
M
|
40000
|
123123123
|
5
|
|
John
|
Q
|
Smith
|
345345345
|
|
Houston, TX
|
M
|
30000
|
234234234
|
5
|
|
Jennifer
|
L
|
Wallace
|
456456456
|
|
Bellaire, TX
|
F
|
43000
|
123123123
|
4
|
|
Alicia
|
M
|
Zalaya
|
567567567
|
|
Spring,
TX
|
F
|
25000
|
456456456
|
4
|
|
Ramesh
|
R
|
Narayan
|
678678678
|
|
Humble, TX
|
M
|
38000
|
234234234
|
5
|
|
Joyce
|
V
|
English
|
7897789789
|
|
Houston, TX
|
F
|
25000
|
234234234
|
5
|
|
Ahmed
|
B
|
Jabbar
|
890890890
|
|
Houston, TX
|
M
|
25000
|
456456456
|
1
|
DEPARTMENT
|
Dept_No
|
Dept_Name
|
|
1
|
Headquarter
|
|
4
|
Administration
|
|
5
|
Research
|
Write out the SQL queries pertaining to the following questions. You must write the SQL query such that if they was copied and pasted on a database, the query would run.
a) Give me every thing you have on employee.
b) Give me only the social security numbers, last names and the first names of all employees.
c.) Show me all the salaries, but do not show me duplicate salaries figures.
D) Show me the ssn of employees that are paid 25, 000 in salary.
e) Show me the last name of all male employees
f) Show me the department number, the first and last name of all employees. Make sure that the department's number are displayed in order from low to high.
g) Give me a list of all those employees whose last name is English and whose sex is female
h) Give me the total salary paid to all the employees who work in department 5
i) Give me average salary for each department.
j) Show me the employees' first name, last name along with the department name they work for.
Q4 Include these business rules:
- An employee could be staff or a professor or an administrator.
- A professor may also be an administrator.
- Staff employees have a work level classification, such a Level I and Level II.
- Only professors can chair a department. A department is chaired by only one professor.
- Only professors can serve as the dean of a college. Each of the university's colleges is served by one dean.
- A professor can teach many classes.
- Administrators have a position title.
Given that information, create the complete ERD containing all primary keys, foreign keys, and main attributes. In VIZIO.