Database Design Concepts part 3

This is a solution of Database Design Concepts part 3 in which we discuss in the organization a huge number or volume of data accumulated such as: customer data, product data, employee data, product delivery data, and many others data.

Database Design Concepts part 3

Task 1C Design a set of Initial screens that can be shown to the client

In this screenshot, the customer service detail from is shown. Through this form, all the details of the customer will get displayed from the database. From here, we can add, update and delete the customer’s information. We can also directly move on to the first and last record of the customer detail. It is an easy access to go through the record of customers on a one go. The manager keeps track of the customer’s details for delivery purposes and to know which customer is a regular one.

Task 2A Produce a Data Dictionary

Employee Type
Field Size Description
type_id INT(11) Primary Key that uniquely identifies each record owith an automatic increment
type_name VARCHAR(45) Name of the employee
Customers
Field Size Description
Cust_id INT(11) Primary Key that uniquely identifies each record of customer with an automatic increment
Cust_name VARCHAR(45) Name of the customer
Cust_address VARCHAR(45) His address details
Cust_email VARCHAR(45) His email details
Cust_mobile VARCHAR(45) His mobile details
Employee
Field Size Description
emp_id INT(11) Primary Key that uniquely identifies each record with an automatic increment
emp_name VARCHAR(45)
emp_address VARCHAR(45)
emp_email VARCHAR(45)
emp_mobile VARCHAR(45)
training_course VARCHAR(45) What course that employee has done
skills VARCHAR(45) What are his qualification skills to work in FBN
NIN VARCHAR(45) National insurance no of employee
shift_id INT(11) Foreign key
passport VARCHAR(45) His passport details
basic_pay_rate DOUBLE Amount the company pays
driving_licence VARCHAR(45) Scanned copy of his driving licence
emp_type_id INT(11) Foreign key
outlet_id INT(11) Foreign key
Stock
Field Size Description
item_id INT(11) Primary Key that uniquely identifies each record with an automatic increment
quantity INT(11) Number of items available in the stock
reorder_level INT(11) Count of items need to be reordered
Shift
Field Size Description
shift_id INT(11) Primary Key that uniquely identifies each record with an automatic increment
duration VARCHAR(45) Duration for which the employee works in the company, for e.g. 9AM to 6PM
Payment Type
Field Size Description
pid INT(11) Primary Key that uniquely identifies each record for payment with an automatic increment
p_name VARCHAR(45) Payment mode: cash or credit card
Outlets
Field Size Description
outlet_id INT(11) Primary Key Auto Increment
outlet_name VARCHAR(45) Name of the outlet associates with FreshBurgerNow
Order Product Table
Field Size Description
op_id INT(11) Primary Key Auto Increment
order_id INT(11) Foreign key
item_id INT(11) Foreign key
quantity INT(11) No of items ordered
saleprice DOUBLE Selling price of the item
Order Table
Field Size Description
order_id INT(11) Primary Key Auto Increment
cust_id INT(11) Foreign key
date DATE Date on which item is ordered
time TIME time on which item is ordered
total_payment DOUBLE
payment_status VARCHAR(45) Amount paid or due
emp_id INT(11) Foreign key
ship_address VARCHAR(45) Shipping address where the item need to be delivered
payment_type INT(11) Foreign key
Menu Type
Field Size Description
menu_id INT(11) Primary Key Auto Increment
menu_name VARCHAR(45) Regular or saver
duration VARCHAR(45) Between what hours it is available
Item Type
Field Size Description
item_id INT(11) Primary Key Auto Increment
item_name VARCHAR(45) Name of the item like burger, French fires etc
price DOUBLE Cost of the item
menu_id INT(11) Foreign key
type VARCHAR(45) Type of the item like food or drinks

Task 2B Propose an Entity - Relationship diagram

The entity relationship diagram is a diagram in which various entities are defined and the relationships between the entities is shown. As we have discussed earlier the various concepts that to make the tables in the database, we first need to make the entity relationship diagrams. In entity relationship diagram, we first need to decide the entities of the tables of the database. Then we should define the attributes of the entities decided for the table. Then we give primary key to one attribute of each table to make each and every entry, a unique entry in the database. We also define the foreign keys to the attributes of the table. The foreign key helps to relate the data of the different tables of the database. We have normalized our database as normalization is used to organize the attributes and relations of the relational database to minimize the redundancy of data within the table of the database developed. We have normalized our tables to third normal form and have minimized the data redundancy problem in the developed database.

Task 2C Develop the Normalized Schema for the system up to 3rd normal form
Normalization

Normalization is the process which is used to organizing the data into the database while creating the database, so that the result of using the database is always unambiguous. Simply it minimizes the data redundancy, update, delete, and insert anomaly in a relational database by organizing the tables and columns or attribute of the database.

Normalization forms

The process of normalization is further divided in the several parts:
•Un-Normalized form (UNF): The Un-Normalized form of normalization is the types of normalization which contains the repeating value of attributes and tables. For Un-Normalized Form (UNF) we must use these points:
First of all maintain the attributes of the entity
Main key must be identified
The repeating group of attributes must be identified
Identify its key
Initially the developed database is in un-normalized form. It means all the attributes, such as employee id, name, address, training course, skills and many others are used and stored in the single table named as FullEmployeeDetail table.
•First Normalized form (1NF): A relational database table consist first normal form (1NF) which meets to certain criteria. The following points show the criteria of first normal:
In this there are no similar and duplicated rows in the table
Single value is used in each cell that is there are no repeating or duplicate groups
Entries in a column such as attribute, field and so on
Then we convert the database in the first normalize formed. To convert it into the first normalize form, we separate the FullEmployeeDetail into the several table and manage the respective data in the respective tables. The tables we made in the first normalize form of the database are: shift table, customer table, employee detail1 table, item table, menu table, full order detail table and stock table.

Cheap Assignment Help UK provide assignment writing service based on case study requirements in affordable prices and we are providing most flexible online assignment writing help, so book your Assignment with us, order now