Part Three - Develop an ERD - Entity Relationship Diagram Now that you have identified the steps necessary to bring this database to third normal form (3NF), you will write a diagram that displays the new layout and the relationships between the tables. It should depict how you successfully removed dependencies in the tables to create a relational database model. Use the steps as a guide Step 1: Identify the Entities Examine the normalized tables. From your SQL creation scripts, you have four main entities: Products Contains product-specific information such as ITEMID (PK), SUPPLIERID (FK), PLUCODE, PRODUCTNAME, TYPE, and STOCKQTY. Suppliers Contains supplier details with SUPPLIERID (PK), LASTDELIVERY, SPECIALTY, and ACTIVE. Supplier _ Delivery Stores the delivery schedule information. It uses a composite primary key comprising SUPPLIERID and ITEMID, which links deliveries to both suppliers and products. Purchases Tracks financial transactions of the products. Here, PURCHASEID is the primary key, and ITEMID is a foreign key linking each purchase record to the corresponding product. Step 2: Define Keys and Their Roles For each entity, specify the primary keys (PK) and any foreign keys (FK): Products Suppliers Supplier Primary Key (PK): ITEMID Foreign Key (FK): SUPPLIERID (references the Suppliers table) Primary Key (PK): SUPPLIERID _ Delivery Composite Primary Key: (SUPPLIERID, ITEMID) Foreign Keys: SUPPLIERID (references Suppliers) ITEMID (references Products) Purchases Primary Key (PK): PURCHASEID Foreign Key (FK): ITEMID (references Products) Defining these keys clearly ensures that you identify which attributes determine a unique record and how tables relate to one another. Step 3: Establish Relationships and Their Cardinalities Understanding exactly how the entities relate is key to connecting your ERD: 1. Products Suppliers Relationship: Each product is produced by one supplier. However, each supplier may provide many products. Cardinality: One-to-Many (1:N) Connection: The SUPPLIERID in the Products table is a foreign key that links back to the SUPPLIERID in the Suppliers table. 2. Products Purchases Relationship: A single product can have multiple purchase records over time. Cardinality: One-to-Many (1:N) Connection: The ITEMID in Purchases refers to the unique product in the Products table. 3. Supplier _ Delivery Suppliers & Products Relationship 1: Each supplier can have multiple delivery entries for different products. Relationship 2: Each product can be delivered by its supplier at various times. Cardinality: One-to-Many in both cases. Connection: The composite key (SUPPLIERID, ITEMID) connects back to both the Suppliers table and the Products table. This table removes the transitive dependency originally found when NXTDELIVERY was directly in the product tables. Step 4: Draft the ERD Using a Diagram Tool Choose a diagramming tool like Lucidchart, Draw.io, or even Word's drawing tools. 1. Draw the Entities: Represent each entity as a rectangle within your diagram. Inside each rectangle, list the entity's attributes. Denote primary keys (using an underline or bold text) and mark foreign keys with (FK) labels. 2. Connect the Entities: Draw lines from the foreign key in one table to the primary key in its related table. For the Products Suppliers connection, draw a line from Products.SUPPLIERID to Suppliers.SUPPLIERID and label it as 1:N. For Products Purchases, connect Purchases.ITEMID to Products.ITEMID with a similar relationship label. For Supplier _ Delivery, draw lines that connect Supplier_Delivery.SUPPLIERID to Suppliers.SUPPLIERID and Supplier_Delivery.ITEMID to Products.ITEMID. 3. Annotate Cardinality: Next to each connecting line, indicate the relationship (e.g., one supplier having many products, one product with many purchase records