DAT 560G: Database Design and SQL Fall 2022, Mini A

Final Exam – Sample Questions with Solutions

Instructions

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

2. Please include only your Student ID on the submission.

3. The only allowed material is:

   1. Class notes

   2. Content posted on Canvas

   3. Textbook

4. You are not permitted to use other online resources

5. Due on Canvas. Due date is posted on Canvas.

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

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

Practice Questions

These questions are intended to help you understand the database. They will not be on the test.

A) Find all shipments from a distribution center that has a capacity of more than 15,500, which were sent to one of the Walls locations. Sort the results by shipping date. List the BatchID, ShipDate, Origin, Destination, and Capacity in your final result.

SELECT BatchID, ShipDate, Origin, Destination, Capacity FROM Batches JOIN Distributors ON Origin = DistCenter WHERE Destination Like ‘%Walls%’ AND Capacity > 15500 ORDER BY ShipDate;

/* 8 rows returned

shipping company, the number of locations the shipping company has, the date of arrival, the pharmacy it was sent to, and the city where the pharmacy is located.

SELECT BatchID, DistCenter, ArrivalDate, Shipper, Locations, Origin, Destination, Pharmacies.City

FROM ((Batches JOIN Distributors ON Origin = DistCenter) JOIN Shippers USING(Shipper))

JOIN Pharmacies ON Destination = Name WHERE Distributors.City = ‘St. Louis’;

/* 10 rows returned

1. For each pharmacy, find the number of batches being sent to it, the total quantity sent, and the pharmacist. Sort the results by the quantity sent to it. If there is no shipment, total quantity sent must be 0.

   SELECT Name, MIN(Pharmacist), COUNT(BatchID), IFNULL(SUM(Quantity),0) AS TotalShipped

   FROM Pharmacies LEFT JOIN Batches ON Destination = Name GROUP BY Name

2. For each pharmacy, list the name of the pharmacy, number of patients who need the vaccine at the pharmacy, total quantity received, and the ratio of vaccines received to the number of patients who need the vaccine. Sort the results by this ratio. Include only pharmacies that received vaccines.

SELECT Name, MIN(Patients), SUM(Quantity), SUM(Quantity)/Patients AS ReceivedRatio FROM Pharmacies JOIN Batches ON Destination = Name

GROUP BY Name

ORDER BY ReceivedRatio;

/* 10 rows returned