ALL FIGURES ARE AT THE BOTTOM OF THE PAGE
Instructions/Preparation:
Step1: Open the SQL Fiddle (http:
sqlfiddle.com/#!9/4895a1/92) or SQL Lite (https:
www.db-book.com/db7/university-lab-di
sqljs.html
SQLite Reading and download: https:
towardsdatascience.com/sqlite-database-setup-and-querying-cea0520272c
https:
sqlite
owser.org
log/portableapp-for XXXXXXXXXXrelease-now-available
Step2: Load EMPLOYEE database, Type by yourself or load from the link (github)
https:
github.com/tolgahanakgun/Elmasri-Database
Q3. [ 50 points] Consider the following relations for a database that keeps track of visits of various doctors working for a group of hospitals, to various hospitals under the group:
DOCTORS(DSsn, Name, Start_year, Dept_no, location)
VISIT(DSsn, From_hospital, To_hospital, Departure_date, Return_date, Visit_id)
EXPENSE(Visit_id, Account#, Amount)
(a) [ 25 points] How can you create the table using SQL? Also, show us how you can insert five data values (at least three with the location name New York).
(b) [ 15 points] Identify different database keys for this schema, draw the schema diagram, stating any assumptions you make.
(c) [ 10 points] Write the SQL to list the doctors in New York in descending order by name.
Q4. [ 50 points] Consider the COMPANY relational database schema. Write SQLs and show the result of each query if it is applied to the COMPANY database (attached).
(a) [ 10 points] Retrieve the names of all male employees in department 5 who earns more than 3000 and works on ProductZ project.
(b) [ 10 points] List the names of all employees who are from Houston, Texas and works under the manager James Borg.
(c) [ 10 points] Find the names and location of all employees who are working in the project ProductZ.
(d) [ 10 points] To give COVID-19 benefits list name of all dependents of Mr. John Smith.
(e) [ 10 points] If any employee works for 40 hours or more, we can consider him/her as a “full-time” employee. Similarly, if an employee works for 20 hours or more, let us consider him/her as a “half-time” employee. Write a SQL query to find all “half-time” employees from the employee database.
Q5. [ 50 points] Consider the employee database again and try the following complex queries using SQL. You need to answer queries in SQL, and show the results.
(a) [ 15 points] For each department, whose average employee salary is more than $28,000, retrieve the department name and the number of female employees working for that department.
(b) [ 15 points] Write a SQL to retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company.
(c) [ 20 points] Can you make an alternative
etter versions these two queries in (a) and (b) ? Use any of query optimization techniques or cost analysis. [ N.B. if you can just make an alternative version and analyze these two versions will be acceptable]
Q6. [ 10 points] Consider the employee database again and
(a) [ 20 points] Write a SQL query to retrieve the names of all employees in department 5 who work more than 20 hours per week on the ‘ProductZ’ and ‘ProductX’ projects.
(b) [ 15 points] Write PHP program (code) representation for the part of the database in problem3. You can Create any database table, Insert data, Update/Delete data etc.
(c) [ 10 points] Write XML program (core) representation for the part of the database in problem3. You can Create any database table, Insert data, Update/Delete data etc.
Figure 5.6
One possible database state for the COMPANY relational database schema.
2