Design a database to track the manufacture, sale, and distribution of PPE (personal protective equipment) from the perspective of a retailer (Medline for example). The details of this are as follows:
Each type of PPE has a unique name (‘N95 mask” for example) and a description. PPE can be made by any number of suppliers. These suppliers have a unique supplier code, a name, address, and primary contact. Each supplier/PPE type pairing has a capacity/month that the supplier can make. PPE is sold to customers (think large medical providers such as Baptist Health), which have a unique code, a name, address, and contact person. Customers place orders which can be split and shipped to multiple places (ie. 3000 N95 masks to Baptist South, 1000 isolation gowns to Baptist East). Customers can also schedule orders for a specific date in the future if it is not specified the order is assumed to be placed at the current time
Here are the deliverables for the homework:
A complete ER diagram showing all weak/strong entities, relationships, and cardinalities. Do this in draw.io or something similar.
A relational schema diagram showing the foreign key relationships. Also, do this in draw.io or something similar
A completed database with the tables/relationships defined.
Define a trigger (throw exceptions) on your order field to make sure the order does not push you past the supply cap for the month of the order.
