Joe’s Sub Shop is starting an online ordering system that will provide local del
ID: 3683671 • Letter: J
Question
Joe’s Sub Shop is starting an online ordering system that will provide local delivery within five miles of the sub shop. The customer can order a limited menu of the sandwiches the sub shop creates. The online order system will need to track the following information customer, order and product. They shop will need to know the quantity ordered, toppings and other directions for the order. The delivery drive will need to know the address and phone number of the customer.
Use this data model Make sure you define the following:
Data base :MySQL
Review data model to make sure data model is in at least 3rd normal form (as defined by the normalization process).
The normalization should be done in a step by step process from 1st normal form to 3rd normal form and each step should be explained clearly.
Explanation / Answer
Normalization of Database
Database Normalisation is a technique of organizing the data in the database. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.
Normalization is used for mainly two purpose,
First Normal Form
As per First Normal Form, no two Rows of data must contain repeating group of information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. Each table should be organized into rows, and each row should have a primary key that distinguishes it as unique.
The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key. For example consider a table which is not in First normal form
Joe’s sub shop is starting an online ordering system. In the process of designing the new system, the team had identified the following class and attributes:
Customer
Order
Order Items
Product
Customer id
Order id
Product id
Product id
Customer name
Order date
Quantity
Product name
Address
Customer id
Toppings
Product type
phone
Total value
Other directions
price
Order id
Using the First Normal Form, data redundancy increases, as there will be many columns with same data in multiple rows but each row as a whole will be unique.
Second normal form.
As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.
Customer
Customer id
Customer name
Address
phone
Order
Order id
Order date
Customer id
Total value
Order Items
Product id
Quantity
Toppings
Other directions
Order id
Product
Product id
Product name
Product type
price
To achieve second normal form, it would be helpful to split out the rows into an independent table, and match them up using the order as foreign keys.
Third Normal form
Third Normal form applies that every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form. For example, consider a table with following fields.
Customer id
Name
Address
Phone no
Order items
In this table customer id is primary key, order and product are depends on orderitems. The dependency between orderitems and other fields is called transitive dependency. Hence to apply 3NF,
Order items
Order id
Order date
Quantity
Total value
Product
In this table order items is primary key, product depends on orderitems. The dependency between product and other fields is called transitive dependency. Hence to apply 3NF,
Product
Product id
Name
Type
price
The advantage of removing transitive dependency is,
Customer
Order
Order Items
Product
Customer id
Order id
Product id
Product id
Customer name
Order date
Quantity
Product name
Address
Customer id
Toppings
Product type
phone
Total value
Other directions
price
Order id
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.