Description

Project Purpose
In this project you will analyze, specify, design, implement, document, and demonstrate an online system. You are required to use the classical methodology for relational database development. The system will be implemented using a relational DBMS that supports standard SQL queries. You will use your localhost MySQL Server (Version 5.1 or above) to implement your database and the application. You also cannot use any other software like Access or SQLite. Ask the professors or TAs if you have questions.
Project Phases
Inputs (we give you)
• Text description • Revised text description
• Advanced ERD
• Raw initial data • Database schema
• Initialized database
• Procedure shell
Phase I Phase II Phase III
• Entity Relationship Diagram
• Logical constraints • Relational schema
• Database schema
• Initialized database • Implemented procedures
Outputs (you turn in)
Directions for Phase I
In Phase I, your task is to read the entire description of the system and then translate this textual description into an Entity-Relationship Diagram (ERD) for a potential database system. For any system requirements that cannot be expressed using ERD notation, you must list as logical constraints (we expect at least 3).

1. ERD
a. Will use strong entities, binary relationships, and attributes of all types (simple, composite, derived, etc.).
b. Will not use weak entities, identifying relationships, recursive relationships, or nary relationships.
c. Most entities are pretty explicit in the description (i.e. the headings), but a few are more implicit and require more thought to recognize
d. All relationships should specify mandatory or partial participation as well as cardinalities in ERD notation.
e. You should represent all attributes mentioned in the description. Additionally, you are expected to come up with at least 1 derived attribute per entity.
2. This ERD must capture the functionalities of the application system whenever possible using the components listed in #1.
3. Logical constraints must be ones that cannot be specified using ERD notation, nor can they be related directly to data types or values (i.e. “the product’s ID must be a number” is not a logical constraint; it’s a domain constraint).

Submission Checklist
Your submission should include the following, compiled into one pdf:
1. Cover Page (found on Canvas) with all fields filled in
2. Entity-Relationship Diagram
3. List of 3 or more logical constraints

Version History
0 6/8/20 Initial Release
1 6/10/20 Clarifications for ambiguity

Description

Product
A product is a type of PPE (i.e., mask, gown, gloves, etc.). Each product offering can be produced by a manufacturer, and hospitals can purchase these products from any manufacturer that produces them. A product can be identified in 2 ways, both of which should be stored in the system:
1. Canonically, with a unique name that consists of a color and a type. For example, “blue mask” and “green gown” are all valid products in the system but merely “gown” without a color is not.
2. A unique 5-character product identifier.

Manufacturer

Each manufacturer maintains a single product catalog of its different PPE offerings. This catalog is composed of different catalog items that are uniquely identified by the manufacturer with a 5-character alphanumeric reference number. A catalog item represents a specific product by a specific manufacturer to be sold. The catalog item also stores a price, which represents how much the manufacturer is pricing the product in its catalog. A product can only appear in one catalog item per manufacturer. However, multiple manufacturers can sell that product using a separate reference number in their catalog.

Examples of 2 manufacturer’s catalogs can be found below. Note that each product has its own universal product ID, but it can have a different reference number and price for each manufacturer that produces it.

Marietta Mask Production Company

Product ID Reference Number Product Name
Price
GNMSK MMPC0 Green Mask $1.25
BLMSK MMPC1 Blue Mask $1.10
RDMSK MMPC2 Red Mask $1.05

Product ID Reference Number Product Name Price
GNMSK GTPL0 Green Mask $1.00
BLMSK GTPL1 Blue Mask $2.00
GNGWN GTPL2 Green Gown $5.00

Hospital
Conceptually, a hospital is a consumer of the PPE that is made by manufacturers. Each hospital has a unique name, an address, a budget (money the hospital has to purchase PPE), and the maximum number of doctors that can work there. A hospital needs to have at least one doctor hired to take care of patients.

Transaction ID: 0001
Hospital: Grady

Product
Count
Unit Price
Line Total
Green Mask 100 $1.00 $100.00
Green Gown
200
$5.00
$1000.00

Order Total: $1100.00

After a transaction occurs, the hospital’s budget and partner warehouse’s inventory should be reflected accordingly.

Warehouse

The warehouse has an overall capacity that limits the total number of products it can hold.

Examples of warehouses can be found below:

Name: Midtown Warehouse
Partner Hospital: Grady
Address: 123 Atlantic Dr, Atlanta, GA 30318
Capacity: 1400

Warehouse Inventory
Product Count
Green Mask 1000
Green Gown 200
Blue Gown 100

Partner Hospital: Kennestone
Address: 123 Marietta Pkwy, Marietta, GA 30068
Capacity: 2000

Warehouse Inventory
Product Count
Green Mask 1000
White Gown 500
Blue Mask 100
Red Gloves 100

Name: Dunwoody Warehouse
Partner Hospital:
Address: 123 Abernathy Rd., Dunwoody, GA
30301
Capacity: 1000

Warehouse Inventory
Product Count
Green Mask 1000
Blue Gown 4s00
Blue Gloves 300

Doctor

An example of a usage log can be found below:

Usage Log

ID:

55111
Hospital: Grady
Doctor: gpburdell
Timestamp:

Product Count
Gray Scrub 2
Blue Mask 1
White Goggles
1

Total Count 4