$7$:$37$

LTE

$81$

emil.docx

Start Assignment $-$ Complete reading and reviewing all the learning resources specified for this module.

Use the business rules specified below to extract the entities, attributes, relationships, keys, and the type of data to be stored in each attribute.

Scenario

You just graduated college and were hired as a database developer for a growing company called Delicious Goodies. This company sells pastries of many sorts and has a large online presence. Over the last five years, it has grown tremendously and is now branching out to sell its products to groceries stores throughout the Midwest. You are assigned the responsibility of designing a new database schema to support the sales of pastries to new buyers. You have spoken with the stakeholders within the organization and read through all the required documentation outlining the business operations and existing IT systems. After careful analysis, you've identified the following business rules.

Business Rules:

$0$

ENTITY: All products requested by the customer must be tracked, and the details of the products should include the product id$,$ name, types, and cost.

ENTITY: You also need to track each customer, in order to properly serve the clients your need to maintain the id$,$ name, address, city, state, zip, phone number, and point of contact name of each client.

KEY: Each customer must reference a sales agent id$.$

ENTITY: The order must contain the order number, quantity, date of order, and total cost.

KEY: Also, each order must reference the correct customer id and product id$.$

ENTITY: You also need to capture the sales agent information. This should include the agent id$,$ name, address, city, state, zip, and phone number.

Now that you have identified your data elements, construct a conceptual view of your table using Excel. A conceptual view represents how the data will be displayed to the end user, in a relational database it will be returned in a tabular format, use Microsoft Excel to create a view of your new schema. Click the link to view a video on how to create a table in Excel. Links to an external site.

Once the entities are created, you'll need to identify any primary and foreign key columns$/$attributes based on the business rules supplied. In the heading of the column that will serve as the Primary Key $($PK$)$ of each table

include $($PK$)$ and use $($FK$)$ for any column that acts as the foreign key $($FK$)$ in the table.

Remember that a PK is a column constraint that uniquely identifies each row, it cannot be repeated. An FK is a column constraint that enforces referential integrity and establishes the relationship. It is a pointer to a related row$($s$)$ in another entity.

Include at least two rows in each table to represent the type of data that can be stored in each table