PART $1-$ Database Design and Implementation

Question $1$:

Considering the business rules below, create a complete ERD for the Flower Shop. Make sure to include all primary keys and foreign keys in the ERD, as well as the relationships and all attributes of each entity. Pay attention to the normalization rules and aim to have all tables in $3$NF$.$ Add a screenshot or a photo of your ERD to the word document.

Business Rules for Flower Shop Database:

Flowers are organized into categories $($e$.$g$.,$ roses, lilies, tulips$).$ Each flower category has a unique identifier.

Each flower has a unique identification number. Flowers belong to one and only one category.

Each employee has a unique identification number, name, and contact information.

Customers have a unique identification number, name, and contact information.

Customers can place multiple orders, and each order is associated with only one customer. Each order has a unique identifier. Each order consists of one or more types of flowers. Each type of flower in an order is associated with a quantity. Each order has a date and time associated with it$.$ Each order must have a corresponding payment record, and a payment record is associated with only one order.

Payments can be made through various methods $($credit card, cash$).$

Orders may have delivery information, including delivery address and delivery date, and each delivery information record is associated with only one order.

The flower shop maintains an inventory of flowers. Each flower in the inventory has a quantity available. Each flower has a price associated with it$.$

Employees may have different roles $($e$.$g$.,$ florist, cashier$).$

The flower shop may store information about customer preferences which include the preferred flower type.

Orders can have different statuses $($e$.$g$.,$ pending, delivered$).$

Each flower belongs to one and only one flower category and each flower category can have multiple flowers.