Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

DAT 560G: Database Design and SQL

Fall 2022, Mini A

Final Exam (1PM)

Instructions

This is an individual assignment. You may not discuss your approach to solving these questions with anyone, other than the instructor or TA. We will only answer clarification questions.

Please show your Student ID and face to the zoom camera.  Students who do not follow the zoom instructions or keep their camera on, will be disqualified.

Please write your SID at the bottom of this page.

Please place your answers in this word file and complete your answers in only this file. DO NOT MAKE A NEW WORD FILE! 

Allowed time is: 70 minutes (65 minutes to answer +5 minutes to submit on Canvas)

The only technology allowed is MySQL Workbench and Word. Course content on Canvas is allowed. Digital and physical copies of the course notes are allowed.

You are only allowed a laptop. A tablet or iPad are prohibited. You can see the course notes on Canvas on your laptop.

Make sure to disable all communication and other Internet technology. If anything “pops up” during the test (e.g., e-mail, WeChat, notifications), your exam will be disqualified.

Phones must be used only for zoom as instructed in previous emails/canvas announcements (the same email/announcement you received your zoom invitation)!  The name displayed on zoom must match the English spelling of your name on Canvas.

You are not permitted to use any other online resources.

Submit the pdf file on Canvas page. Keep track of your time.

Don’t worry if you can’t complete this exam. Grades in the course will be curved.

GOOD LUCK

Background

There are numerous challenges with the rollout of the Covid-19 vaccine. Getting vaccines to pharmacies, and vaccinating patients requires overcoming multiple logistical challenges. One of these challenges is keeping track of shipments of vaccines to pharmacies.

There are several aspects of the vaccine supply chain, which need to be coordinated. On the one hand the distribution centers have vials of vaccine ready to be shipped. These distribution centers are owned by pharmaceutical supply chain companies. These companies developed expertise in supplying medical products to pharmacies and others.

Using shippers such as FedEx and UPS, vaccines are sent to pharmacies. Covid-19 vaccines are in short supply and especially vulnerable to spoilage. Each shipment needs to be tracked. Additional information about the batch shipped is also maintained.

Finally, pharmacies need to receive the vaccines. Pharmacies will eventually administer the vaccine to patients.

Additional Information 

Details of the database are described below. The information stored in the database is the following.

There are several distribution centers scattered around the Midwest. The data stored for a distribution center includes:

· DistCenter is the name of the distribution center

· City

· Start Date, which is the first date that the distribution center received vaccinations to distribute.

· Annual capacity of the distribution center. This is the number of vials of vaccine the distribution center can ship out in a year.

· The number of bays in the distribution center where trucks can load vaccines.

· The corporate affiliation of the distribution center. Most distribution centers are affiliated with a national company. You can think of the distribution center as a franchise.

· The manager of the distribution center

· Manager’s gender

Shippers are used to send vaccines from distribution centers to pharmacies. For shippers we have information:

· Shipper is the name of the shipping company

· Annual revenue, in millions of dollars

· Number of employees, in thousands

· Number of locations in the country

· The name of the CEO

· Gender of the CEO

· The date the shipping company was established

Vaccines are shipped to pharmacies. For pharmacies, we have the following information:

· Name of the pharmacy

· City

· Corporate affiliation of the pharmacy. Similar to shippers, pharmacies belong to a national network.

· First case of vaccine that the pharmacy administered. This is the date and time of vaccinating the first patient.

· Number of patients the pharmacy wants to vaccinate

· Type of pharmacy. There are several different types in the database

· Name of the pharmacist

· Gender of the pharmacist

Each batch of the vaccines is monitored throughout its useful lifetime. The database stores information about each batch, including:

· BatchID, which is also used for tracking the batch during shipping

· The date the batch was shipped from the distribution center

· The number of vaccine vials in the batch. Assume each vial is one vaccine, for one patient

· Shipper is the shipping company, which ships the batch. This is a Foreign Key.

· The date the batch arrived (or will arrive) at the pharmacy

· Origin is the distribution center, which sent the batch. This is a Foreign Key.

· Destination is the pharmacy, which will receive the batch. This is a Foreign Key.

· Distance the batch will travel from origin to destination

· Cost of shipping the batch

· Driver, who works for the shipping company

· Gender of the driver

· Type of vehicle used for shipping. Several different types of vehicles are used.

The database is posted on Canvas

The E/R diagram for this database is below:

Distributors (DistCenter, City, StartDate, Capacity, Bays, Company, Manager, Gender)

Pharmacies (Name, City, Corporate, FirstCase, Patients, Type, Pharmacist, Gender)

Shippers (Shipper, AnnualRevenue, Employees, Locations, CEO, Gender, Establishe)

Batches (BatchID, ShipDate, Quantity, Shipper, ArrivalDate, Origin, Destination, Distance, Cost, Driver, Gender, Vehicle)

 

For each question, submit your SQL code and a screen-shot of the results. If the results are too long, partial results are fine. Include relevant attributes for each result, to explain that the result is correct. Do NOT include many unnecessary attributes. Do NOT use SELECT *.

1. For each pharmacy corporation except the ones in Aurora find the number of male and female pharmacists. List only corporations with at least three pharmacists (either male or female).

2. For each shipment sent before March 1, 2021 to one of the pharmacies owned by CXS, calculate the lead-time in hours. Lead-time is the difference between the shipping and arrival dates. List the BatchID, destination, ship date, and lead time. If the arrival time is not available, lead time should be tagged as “Not available”. Sort results by lead-time.

select batchid,destination, shipdate, (if(arrivaldate is Null,"Not available",DateDiff(arrivaldate , shipdate))) as lead_time

from batches left join pharmacies

on destination = name

where corporate = "CXS" and month(shipdate)<3

order by lead_time;

 

3. For each distribution center, find the number of shipped batches, total quantity, and total distance. Sort the results by the total number of shipments in reverse order.

select distcenter, count(batchid),sum(quantity),sum(distance)

from distributors join batches

on distcenter = origin

group by distcenter

order by sum(quantity) desc;

 

4. For each batch, find the ship month, Quantity, shipper’s CEO, Distributor’s manager name, and Pharmacist name. Sort the results

select shipmonth, quantity, ceo, manager, name

from

(select origin, month(shipdate) as shipmonth, quantity, ceo from

batches join shippers using(shipper)) as ceomatch

join

(select origin, manager from

distributors join batches on Origin=distcenter) as managermatch

join

(select origin, name from

pharmacies join batches on name=destination) as namematch

 

5. Find the total number of pharmacists and patients per Pharmacy Company. List only companies that their pharmacies have received less than 6 batches. Have a second look at your results! Make sure your stats are right.

select count(pharmacist),sum(patients)

from pharmacies

where name in (select name from pharmacies join batches

on name = destination

group by name

having count(batchid)<6)

group by corporate;

 

No need to treat nulls. List only the top 10 rows.

select batchid,(cost/quantity) as ratio

from batches

where (cost/quantity) <

(select avg(cost/quantity)

from batches)

order by ratio

limit 10;

 

7. Find the number of all men and women who have any role in this database. Sort results by the numbers. Do not report null genders.

select gender, sum(num)

from(

(select gender, count(manager)as num from distributors

  group by gender having gender is not null)

  union

 (select gender,count(pharmacist)as num

 from pharmacies

 group by gender 

having gender is not null)

join (select gender,count(CEO)as num

 from shippers

 group by gender

 having gender is not null) )as Q7

 group by gender;

 

8. For each city (cities from pharmacy table), calculate the ratio of total quantity of vaccines received to the number of patients who need the vaccine in the city. Sort the results by this ratio. Include ONLY pharmacies that received vaccines in this calculation. Do not report NULL city. Hint: It is not as simple as you think! Take another look at your results and check the result. You need to use subqueries.

select city, (sum(quantity)/patients) as ratio

from pharmacies join batches on destination = name

where city is not Null

group by name

order by ratio;

 

9. Find the number of received batches per pharmacy. List only pharmacies that have received more than average number of batches received by all pharmacies.

10. We define 3 types of distributors. “Large” distributors have capacity of at least 20,000. “Small” distributors have capacity less than 20,000. There are also distributors with unknown capacity. These are called “Unknown”. Find the number of batches shipped for each of these 3 types of distributors.