Would someone please check my Homework to make sure I'm good on the answers to the questions? It is attached.

 

CIS 407: Advanced Database
Data Modeling & Single Table Query
40 Points [ AN Date Given: 3-8-17 I. Date Due: 3-13-17 DATA MODELING (20 Points) CASE STUDY:
ACME Inc. is a small consulting company with 4 employees. ACME’s employees assist many local
clients in the successful design and implementation of their projects. A client has one or more projects
contracted with ACME. An employee can work on one or more projects.
To keep track of the client and project data, Zac, an employee at ACME, created the following
database model (PKs are underlined):
PROJECT
PID
PNAME
PH
Hurricane
PT
Tornado
PC
Cyclone
PM
Monsoon
PS
Storm EID
E1
E1
E2
E1
E2 EMPLOYEE
EID
ELNAME
E1
Sutton
E2
Doe
E3
Byte
E4
Blow EFNAME
Sue
Jane
Bill
Joe CLIENT
CID
CNAME
C1
Alpha
C2
Beta
C3
Gamma
C4
Theta
C5
Kappa EID
E2
E1
E1
E2
E1 Do you think Zac’s design can answer each of the following three questions (a, b, and c) precisely and
without ambiguity? Do not just answer YES or NO. You must justify your answer with an example.
a) Which employee works on which projects? Give a specific example to justify. (2 points)
Sue Sutton (E1) works on projects: Hurricane, Cyclone, and Monsoon. While Jane Doe (E2)
works on projects: Cyclone and Storm.
b) Which employee is helping which clients? Give a specific example to justify. (2 points)
Sue Sutton (E1) is helping clients: Beta, Gamma, and Kappa. While Jane Doe (E2) is helping:
Alpha and Theta.
c) Which projects belong to which clients? Give a specific example to justify. (2 points)
There is no way to know with Zac’s design, which projects belong to which clients without some
ambiguity. We could say that since we know Sue works on three projects, and that she helps three
clients that the projects belong to those clients and likewise with Jane Doe. We can make that
assumption but we still don’t know which specific project belongs to which specific client. d) If you consider that Zac’s design is flawed and could not answer some or all of the above queries,
what would you suggest? How would you modify the above model? Draw an ERD (must show PK
and FK and all relevant attribute in the entities to get maximum credit) that you would recommend to Page 2 of 6
ACME so that all the above relevant queries can be answered precisely and without any ambiguity.
(HINT: The design is indeed flawed). (10 Points) e) Convert each entity in your ER diagram above to a database relational table and enter the actual
correct data under each field. You must juggle and use the original given data in the new tables
appropriately. If needed, make up your own new attributes that will prove that your database design is
correct. You must show PK and FK wherever relevant and maintain both integrities to get maximum
credit. (4 Points)
EMPLOYEE
EID
ELNAME
E1
E2
E3
E4 Sutton
Doe
Byte
Blow ASSIGNMENT
AID
1000
1001
1002
1003
1004 EFNAM
E
Sue
Jane
Bill
Joe DATEASSIGN
02-12-2017
02-06-2017
02-28-2017
03-03-2017
01-01-2017 PROJECT
PID
001
002
003
004
005 PNAME
Hurricane
Tornado
Cyclone
Monsoon
Storm HOURSWORKED
10
12
8
4
130 PID
001
002
003
004
005 CID
C2
C3
C1
C5
C4 CLIENT
CID
C!
C2
C3
C4
C5 EID
E1
E1
E2
E1
E2 CNAME
Alpha
Beta
Gamma
Theta
Kappa Page 3 of 6
II. SQL CODING: Single Table Query (20 Points) To submit your source SQL code, simply copy the SQL code from the QUERY area of the COBSQLSERVER and paste it into the Word document.
To submit the result of the SQL query, this is what you do:
o Click on the RESULTS TO TEXT icon at the top of the query window before you run your
query. This way you will get an output that will allow you to capture all the column
headers as well as the data under the columns. See images below. After running the
query, copy the result and paste it into the Word document. (Alternatively, use Print
Screen together with the Paint program to copy and paste the result). Copy this source code and paste so that
I can copy and run it again on COBSQLServer to counter check for partial
credit purposes. Do not submit a screen
shot (image) of SQL codes. Thanks!
Copy this output
and paste. You may
submit screen shot
of the output.
Write correct SQL codes for the following reports. You will be using the database named HOSPITAL
that is posted on the COB-SQLServer. You have read-access only to this database. In the
following, some sample images are shown for column headers reference. All queries are singletable queries. No JOINs are required. Even if you query does not run completely and correctly, please copy and paste the error
message (in addition to your code) so that I will be aware of the extent of the error.
Remember, partial credit will be given based on the degree of severity of the error. So, do
not forget to copy and paste the error.
1
. Find the average age (as of today) in number of years of all the current patients in the HOSPITAL.
OUTPUT: The final output is a single Scalar numeric quantity that must have exactly one decimal place
(such as 45.5 or 47.0 or 54.3 etc.) Use an alias “Average Age” for the column name.
SELECT CONVERT(decimal(4, 1),AVG(DATEDIFF("yyyy", pat_date_of_birth, GETDATE()))) AS [Average
Age]
FROM patient Page 4 of 6 2
. Write a query to display the date on which each staff member has completed or will complete 20
years of service experience at the HOSPITAL. Display the following column headers in the given sequence in the output. The value for the “Name” column should be in the format: “Lastname, Firstname”. The date fields must not display the time component. Display the date in the fashion, for example: 14 Dec 1999 The output must be sorted on the date-hired field in ascending order. SELECT staff_ssn AS [SSN], (staff_last_name + ', ' + staff_first_name) AS [Name],
CONVERT(VARCHAR(11),date_hired, 106) AS [Date Hired], CONVERT(VARCHAR(11),DATEADD(yyyy,
20,date_hired), 106) AS [Date with 20 Years Experience]
FROM staff
ORDER BY date_hired 3
. The HR manager needs a listing of services provided by the HOSPITAL, but only for “surgical” types
of services. Include the following column headers in the output. Instead of displaying the coded catalog ID used (i.e., SUR) in the table, your output must
display the full term “Surgical” in the output under the header “Service Type”. Order the display in increasing order of service ID. Page 5 of 6 SELECT service_id AS [Service ID], service_description AS [Service Description],
REPLACE(service_cat_id,'SUR','Surgical') AS [Service Type]
FROM service
WHERE service_cat_id = 'SUR'
ORDER BY [Service ID] 4
. A listing is desired that focuses on the count of all available beds in the ER rooms only. However,
display those situations in which the count number of available beds is greater than one (not
inclusive). Include the following headers. Order by room id in ascending order. SELECT room_id AS [Room ID], bed_type_id AS [Bed Type], COUNT(CASE WHEN bed_availability = 'Y'
THEN 1 ELSE 0 END) AS [Number Available]
FROM bed
WHERE bed_type_id LIKE 'E_'
GROUP BY room_id, bed_type_id Page 6 of 6
HAVING COUNT(*) > 1
ORDER BY room_id 5
. Find the total number of patients in each city who were born in the month of June. However, in the
final output, display only those results where the count is greater than 2 (inclusive). Use the following headers. Order the result by descending order of count. SELECT pat_city AS [Patient City], COUNT(DATENAME(MONTH, pat_date_of_birth)) AS [Count of
June Births]
FROM patient
WHERE Month(pat_date_of_birth) = 6
GROUP BY pat_city
ORDER BY [Count of June Births] DESC

Attachments:

• 1493259939-Homework.docx