Reference no: EM13719308

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.