Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

MET CS 779 Advanced Database Management

Assignment 1.0: Normalization

Overview of the Assignment:

This first exercise is a review of database design and normalization. Recall that normalization applies to single relations (tables). Your task is to review the following spreadsheet example and create a normalized design with extensions to handle missing data such as payment status.

Topic: Food truck database

You have received a spreadsheet containing data for a food truck which has been using an excel spreadsheet to track inventory and orders. The food truck has been having trouble making business decisions and tracking all the relevant information. You have been asked to prepare a database for the food truck. Note that the food truck does track specific customers as it offers catering events and also may simply park on a busy intersection on lunch hour

Please select what kind of food truck you would like to envision (i.e. ice cream truck, sandwich shop, tacos, pizza, etc). The food truck does specialize in custom ordering, meaning it’s not a set menu.

The food truck has the following issues within their spreadsheet design, among others:

· There are data anomalies and redundancy.

· Food truck keeps running out of ingredients during catering events.

· It’s hard to predict which ingredients should be in stock.

· It’s difficult to track what specifically customers order, and what the favorite food combinations are which in turn results in difficulty predicting what ingredients should be on hand.

Here is a sample to get you thinking of what might be tracked.

Column name	Example/Notes
Customer Company name	a string such as “MET Computer Science Department”. It could also be simply the street corner if there are no specific customers
Schedule and Location	dates of catering and location
Primary Contact and contact info	The name of the contact, as well as primary phone and email info.
Ingredients for that day	A listing of ingredients for that day, and how much they cost to purchase.
Balance sheet	Additional costs for the day, and how much money was collected from sales
Notes	Can be anything, i.e. “great tippers!”, “not a very sanitary group”, “ran out of chocolate syrup and sprinkles”, “really liked steak tips with fries in the sandwich”, “needed a translator”

Consider normalization rules through BCNF and fourth normal form if you feel this is needed.

1. Create a reasonable normalized design for the Food truck database. You may use Lucidchart, Microsoft Visio or another similarly capable drawing application to produce your ERD. Relationship connectivities using Crow’s foot or UML are required as well as distinction between strong and weak relationships. Your design should contain 12-15 tables. If you would like to include more tables, this is ok.

Insert a logical entity relationship diagram below. You can alternatively add the file as a separate attachment. If you choose to attach a file, please note here for your facilitator the name of that file.

2. Explain your design choices of the entities in your normalized design. You will want to provide sample data to demonstrate your normalization process. You do not need to create sample data for every table, just the more complex tables in your design or where normalization needs to be tested.

3. Select one of the more complex tables (i.e. a bridge table) in your design and explain and if it is in BCNF and or not, explain specifically by demonstrating through a sample data set that the table is in BCNF or not.