Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

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...

1 answer below »

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
Answered 5 days After Mar 02, 2022

Solution

Salony answered on Mar 03 2022
101 Votes
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).
Answer3)
CREATE TABLE DOCTORS (DSsn INT NOT NULL, PRIMARY KEY (DSsn) , Name VARCHAR(50) NOT NULL, Start_year VARCHAR(20) NOT NULL, Dept_no VARCHAR(20), location VARCHAR(20))
INSERT INTO doctors
VALUES (1001,'John',2002,'D-01','New York'),
(1002,'John',2005,'D-01','New York'),
(1004,'Franklin',2009,'D-02','New York'),
(1005,'Smith',2010,'D-03','New York'),
(1006,'Pussy',2012,'D-01','California');
[ 10 points]Write the SQL to list the doctors in New York in descending order by name.
SELECT * FROM `doctors` WHERE location='New York' ORDER BY Name DESC;
[ 15 points] Identify different database keys for this schema, draw the schema diagram, stating any assumptions you make.
Answe
(
Expenses
Visit_id
Account
Amount
) (
VISIT
DSsn
From_hospital
To_hospital
Departure_date
Return_date
Visit_id
) (
Doctors
DSsn
Name
Start Yea
Dept No
Location
)2)
4)[ 10 points]Retrieve the names of all male employees in department 5 who earns more than 3000 and works on ProductZ project
i)...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here