Database Design Concept Assignment

Top Grade Assignment Help Publishes a solution of Database Design Concept Assignment which discuss database issues and application of databases in organizations.

Task 1A why a relational database would be suitable information system for the organization

Traditional databases are flat multiple pieces of information (fields) about an object or person, grouped together as a record, which are then stored as a long text file called tab delimited text file and distinguished by a vertical bar (|). Searching sequentially through such an entire text file for specific information for customer details, transaction details or workers salary details, or to create business reports, could be a complex, time consuming and inconsistent process.

Inconsistencies may arise due to typing error while trying to enter or update data causing data summarization, retrieval and reporting to be unpredictable and unreliable.

Updating employee salary and other records according to employee names could also be a great issue in a situation whereby two or more staffers have the same name.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

 Making sure that the right data is updated is a difficult process and this might result in great loss and confusion to a business organization. The same problem would also arise with customers having same name while trying to update transaction data, creating a customer relation crises.

Adopting a structured query language (SQL) based relational database system for FreshBurgersNow will solve most of these problems by organizing data into a set of well-defined units, represented as columns (fields) and rows (records) in a table, and then interrelate the data using a unique key identifier to easily find specific information. The relationship between similar data will increase the speed and versatility of the database.

Storing the data in a collection of operating system files will require a very large storage space and main memory and new data will have to be added every time a new record is entered or updated for the customers and workers, making it a tedious and complex method whenever there is need to identify or retrieve all data items.

A relational database is able to overcome the excessive usage of storage and main memory by storing data internally and then rapidly loading only the needed data to retrieving needed information with the aid of a database management system.

Without a relational database searching and updating customers order will be an unreliable, tedious and time consuming endeavor.

For a business entity such as FreshBurgersNow that handles a large set of data that is always updated, it is more efficient to create customer and order tables and the information from the different tables will then be linked together through the use of foreign keys or indexes to uniquely identify and process sales order placed by customers within that table. This means that the kitchen staff doesn’t need to search through an entire database worth of information just to find out what a customer ordered for. And the driver to deliver the order is rest assured that the order and referenced delivery address must exist. If the address is deleted then all references to it must also have be removed from the database.

All categories of FreshBurgersNow staffers (cooks, drivers and managers) will be able to easily obtain a view of the relational database that perfectly fits their specific job roles and needs. For example, the manager might like a view or report on all customers that had ordered a particular type of menu after a certain date while the owner of the franchise could, from the same tables, obtain a report on sales performance of all the outlets (Ambler, 2013).

A relational database can be customized to reveal new areas for investigation by presenting large quantities of data in an intuitive form to carry out multidimensional analysis, to be viewed and used in different ways. This will afford the owner of the franchise the opportunity to bring together information on total sales and profit from different outlets and compare the information to quickly and easily take business decision.

The manager of each branch can also use the data as a historical fact from previous promo to predict the probability the customer will respond to an offer or purchase of a newly introduced menu into its regular menu list.                                                                                      Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Task1B three distinct advantages concerning what a database management system provides to any organization

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

A database management system is software which allows the users to manage their complex and large data into the form of tables in the database. Here in the project of Fresh Burgers Now, we have used the relational database as this business includes a lot of data and information which needs to be updated regularly and the data will increase daily. The data of Fresh Burgers Now is complex and is very large.. The database management system is used to manage our data effectively, efficiently, quickly and easily. Database Management system acts as a layer between the between the data and the programs. Programs access and handle the database management system and further the database management system controls and manages the data. The database management system includes various major components. The components are – hardware, software, data, users, procedures. The advantages of the database management systems are –

Fresh Burgers Now currently has a large collection of raw data on its employees, registered customers, sales order, products, and so on; and data will be accessed concurrently by the employees to track down customer orders and delivery, as-well as for the owner of the franchise to decide on drastic business decisions.

Read more about Enhanced Entity Relationship database Assignment Help

The workers and franchise owner should be able to get quick answers about the raw data, make and apply quick changes to the data consistently and certain part of the data such as salaries should be restricted to the workers.

To run a successful and efficient Burger business, data needs to be organized and electronically filed in a cabinet and its content managed by a collection of software programs to provide accessibility, speed, and accuracy to the data, without altering the features of the data. The collection of software programs, which also serve as an intermediary between users and the database, is called a database management system (DBMS). Using a database management system (DBMS) will help in keeping track of huge customers and service database, check sales and stock through real-time report, determine the customers and employee details, multi user capability, reduce operating expenses, as well as improve client relationship.

  • Reduced expenses on training cost

In a situation whereby the company engages in a new training requirement for its employees that are in line with mandatory government regulation, implementing an effective database management system (DBMS) will reduce training cost. This is because database management system (DBMS) is easy to learn and workers will require less effort and time to master it.

  • Multi user capability

  An effective database management system (DBMS) will allow all the categories of workers access and reach data simultaneously in a varieties of ways at one time, allowing the data in database automation and to be modified based on privileges of each workers. Without a database management system (DBMS) , all the three categories of workers will have different files and different software to maintain , update and change customers and inventory information, although they all are interested in same data.

  • Improved client relationship an business management

In many businesses, a shorter response time means better customer service. A database management system (DBMS) will help in creating an environment in which end users have better access to more and better-managed data. Such access makes it possible for salesperson to rapidly follow up and respond to sales request, track sales and inventory, and allows the administrator to make sure that the mechanic is ready and equipped to provide the parts necessary for repairs. This capability of a database model  will ultimately help to increase customer satisfaction.

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

In this screenshot, the customer 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.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 1 Customer details form

In this screenshot customer Order detail is shown. Through this form, all the orders made by the customers that are stored in the order table of the database will be shown.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 2 Customer Order detail form

In this screenshot, the employee detail form will fetch all the details of the employee including the duration for which he does his job.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 3 Employee detail form

In this screenshot, Item detail form is shown. All the items available in our stock can be seen through this form. We can add, delete and modify the items present in the stock.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 4 Items detail form

In this screenshot, the order_product subform is shown. Through this form, the ordered products can be seen. The name, price and quantity of the product can be seen and updated.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 5 Order product sub-form

In this screenshot, the order sub form is shown. Through this form, the date, total payment and the shipping address can be seen.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 6 Order sub-form

Task 1D

In this screenshot, the customer detail report is shown. In this report, the detail of the registered customers is fetched including the customer’s id, name, address, email and mobile number. The customer id is a primary key which is increasing automatically.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 7 Customer detail report

In this screenshot, the report of customer order detail is shown. Through this report, all the orders given by customers can be fetched in a tabular format. Also, in this, the sub forms of the order and order product tables are fetched. In this report, customer’s name, address, email, mobile no and order’s date, payment, shipping addresses and item name is shown.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 8 Customer order detail report

Read more about IT Programming Database Assignment Help

In this screenshot, the employee detail report is shown. Through this report, all the details of the employees working in the company are fetched in a tabular format. The employee’s id, name, address, email, mobile no, training courses, skills, duration, etc. are shown.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 9 Employee detail report

In this screen shot, report of the items is shown. Through this report, the items which are present in the stock of the company is fetched with the details. The item’s id, name, price, menu name, duration and type is fetched in this report.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 10 Items report

Task 2

Task 2A Produce a Data Dictionary

Employee Type
Field Size Description
type_id INT(11) Primary Key Auto Increment
type_name VARCHAR(45)
Customers
Field Size Description
Cust_id INT(11) Primary Key Auto Increment
Cust_name VARCHAR(45)
Cust_address VARCHAR(45)
Cust_email VARCHAR(45)
Cust_mobile VARCHAR(45)
Employee
Field Size Description
emp_id INT(11) Primary Key Auto Increment
emp_name VARCHAR(45)
emp_address VARCHAR(45)
emp_email VARCHAR(45)
emp_mobile VARCHAR(45)
training_course VARCHAR(45)
skills VARCHAR(45)
NIN VARCHAR(45)
shift_id INT(11) Foreign key
passport VARCHAR(45)
basic_pay_rate DOUBLE
driving_licence VARCHAR(45)
emp_type_id INT(11) Foreign key
outlet_id INT(11) Foreign key
Stock
Field Size Description
item_id INT(11) Primary Key Auto Increment
quantity INT(11)
reorder_level INT(11)
Shift
Field Size Description
shift_id INT(11) Primary Key Auto Increment
duration VARCHAR(45)
Payment Type
Field Size Description
pid INT(11) Primary Key Auto Increment
p_name VARCHAR(45)
Outlets
Field Size Description
outlet_id INT(11) Primary Key Auto Increment
outlet_name VARCHAR(45)
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)
saleprice DOUBLE
Order Table
Field Size Description
order_id INT(11) Primary Key Auto Increment
cust_id INT(11) Foreign key
date DATE
time TIME
total_payment DOUBLE
payment_status VARCHAR(45)
emp_id INT(11) Foreign key
ship_address VARCHAR(45)
payment_type INT(11) Foreign key
Menu Type
Field Size Description
menu_id INT(11) Primary Key Auto Increment
menu_name VARCHAR(45)
duration VARCHAR(45)
Item Type
Field Size Description
item_id INT(11) Primary Key Auto Increment
item_name VARCHAR(45)
price DOUBLE
menu_id INT(11) Foreign key
type VARCHAR(45)

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 database. 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.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

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.

Figure 11 1NF

  • Second Normalized form (2NF): The second Normal Form involves Full Functional Dependency and it is also able to solve or remove the data redundancy problem in the First normal form. It is basically defined with first normal form and each non-primary key attribute is fully functionally dependent on the primary key. The second normalized form database must be in the first normalized form.

After converting UNF to 1NF we convert the 1NF (first normalize form) of the database into the 2NF (Second normalize form) of the database. To convert it into 2NF form, we separate the Employee detail1 table in Employee_detail table and Employee_type table. And the fullOrderdetail table into the Orderdetail table and order_product table.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 12 2NF

  • Third Normalized form (3NF): The third Normal form used the concept of transitive dependency. A relation which is in 1NF and 2NF and which have not any non-primary-key attributes is transitively dependent on the primary key. Then it is called third normalized form of the data. The third normalize form database must be in the second normalize form.

When the developed database is converted into the 2NF, then we convert it into the third normalized form (3NF) by separating the Employee detail table in the employee table and outlet table. And the orderdetail table in order table and payment type table. In this form all the data of the database are separately stored in the separate tables so that the data redundancy is much minimized.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 13 3NF

Task 3

Task 3A Generate a physical database

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 10 Customer table: Design view

Employee Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 11 Employee table: Design view

Employee type Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 12 Employee type table: Design view

Item Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 13 Item table: Design view

Menu Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 14 Menu table: Design view

Order Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 15 Order table: Design view

Order Product Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 16 Order product table: Design view

Outlets Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 17 Outlets table: Design view

Payment type Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 18 Payment type table: Design view

Shift Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 19 Shift table: Design View

Stock Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 20 Stock table: Design View

Task 3B Demonstrate the entry of six (6) records into the database system

Employee Table – In this table we have inserted the data of the employees which are working for the Fresh Burgers Now.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 21 Employee table: Database View

Item Table – In this table, we have stored all the information of the items which are available in the stock of the Fresh Burgers Now.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 22 Item table: Database View

Order Table – In this table of the database, we have stored all the details of the orders which are given by the customers to Fresh Burgers Now.

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 23 Order table: Database View

Task 3C Design a comprehensive user manual that will describe your system

Firstly the user needs to boot the system. After booting the system, click on start button then click on all programs option. Next step is to search Microsoft office in all the programs. Click on Microsoft office, various applications included in Microsoft office will get displayed. Search for Microsoft office access and click on it. Then a window of Microsoft office access gets opened. Click on the office icon on the topmost left, then click on the open option. A window will get displayed. The user can select and open the Fresh Burgers Now database. Another way to open database is, on the right side of the access page, open recent database option is present the user can search for Fresh Burger Now database and can open the database from there. Read more about Online Assignment Help

After clicking on the Fresh Burgers Now database, the user will be shown with a blank page. On the left of the page, various database tables are shown under the table’s option. Also various headings are there such as Queries, Forms and Reports. The table option contains all the tables present in the Fresh Burgers Now database. The queries option contains are queries used in the database to fetch certain data form the tables. The Forms option contains all the forms of the tables present in the database. Through forms the user can view all the details stored in the tables of the database. The reports option contains the report of all the tables in the database. In report the data present in the table is fetch in a tabular method which is easy to read and understand and also no updates can be done by the user through reports.

In the table option, the user can see various tables which are present in the Fresh Burgers Now database. The tables which are visible are – Customer, Employee, Employee_type, Item, Menu, Order, Order_product, Outlets, Payment_type, Shift, and stock. All the tables contains different data and are connected though the foreign key. The user can simultaneously fetch the data or information through various tables of the database according to his need. Each table contains two views – Datasheet view and Design view. In Datasheet view, all the information within the table is shown. Also the user can add and update the data in the tables of the database through database design. In the design view, the users can decide the field names for their tables. The users can add new fields also according to the need and can delete and update the fields of the table.

In the customer table, all the details of the customers of Fresh Burgers Now is stored. In employee table, all the data of employees is stored. In the employee_type table, the position of the employee is stored. In item table, all the items present in the stock of the company is stored. In menu option, the menu provided by the company is shown. In order table, the orders of the customers are stored. In payment_type, the mode of payment provided by the company is present. In stock table, the quantity of products present and the quantity which need to be ordered are stored.

The forms included in the database are customer detail, customer order detail, employee detail, items detail, order sub-form and order product sub-form. From these forms, the information stored in the database can be viewed. The reports included in the Fresh Burgers Now are customer detail, customer order detail, employee detail and items report. Through these reports, the data or information present in the database can be fetched in a tabular format which is easy to read and understand and cannot be modified and added.

Customer Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 14 Customer table: Design view

Employee Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 15 Employee table: Design view

Employee type Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 16 Employee type table: Design view

Item Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 17 Item table: Design view

Menu Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 18 Menu table: Design view

Order Table –

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 19 Order table: Design view

Forms

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 23 customer detail form

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 24 Customer Order detail form

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 25 Employee detail form

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 26 Items detail form

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 27 Order product sub-form

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 28 Order sub-form

Reports

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 29 Customer detail report

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 30 Customer order detail report

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 31 Employee detail report

Database Design Concept Assignment , Database Assignment Help, Top Grade Assignment Help, Top Grade Assignment

Figure 32 Items report

Task 3D Reflect upon your new developed understanding of database design and provide a lessons learned

The database integration and design concepts described in this assignment are very well understood and are implemented. In this assignment, we have learned how to make the tables in the database and how to add and decide the entities and attributes in the table. We learned the concepts of data modeling and normalization. We get to learn that how we can normalize the database tables to make the information more accessible and related to other table. We learned the concepts to make the entity relationship database diagrams. In ER diagram, we understood that how to decide the entities and their attributes and how the relationships between the different entities can be defined. We learned the use of forms, queries and reports in the database and also learned how to make the forms, reports and different queries and how and where these can be accessed.

We felt that certain improvements can be done in the developed database. The tables in the database and entities and attributes in the table can be increased according to the need. The database can be further developed in MySQL and Oracle database. As these databases provides fast and more efficient access of queries and are more secure.

References

Unleashedsoftware (2015), “Multi-warehouse.jpg (JPEG Image, 420 × 278 pixels):” available from http://www.unleashedsoftware.com/content/partner-images/Multi-warehouse.jpg , viewed on 9 March 2015.

Unleashedsoftware (2015),”Watch Our Demo -unleashedsoftware.com:”, available from http://www.unleashedsoftware.com/our-product/product-overview , viewed on 8 March 2015.

Ibm (2011) , ”us__en_us__ibm100__relational_database__system_Illustration__620x350.gif (GIF Image, 620 × 350 pixels):”, available from www-03.ibm.com/ibm/history/ibm100/us/en/icons/reldb/,viewed on 16 March 2015.

Ibm (2015) ,” IBM100 – Relational Database:-ibm.com”, available from www-03.ibm.com/ibm/history/ibm100/us/en/icons/reldb/,viewed on 16 March 2015.

Ambler (2013) ,” Relational Databases 101: Looking at the Whole Picture:- agiledata.org”, available from http://www.agiledata.org/essays/relationalDatabases.html,viewed on 19 March 2015.

Thakur (2015), “What is DBMS? Advantages and Disadvantages of DBMS- ecomputernotes.com.:” available from http://ecomputernotes.com/fundamental/what-is-a-database/advantages-and-disadvantages-of-dbms  viewed on 19 March 2015.

Yarrow (2013),”RDBMS_Structure.png (PNG Image, 720 × 540 pixels):”, available from http://openscg.com/wp-content/uploads/2013/04/RDBMS_Structure.png, viewed on 16 March 2015.

Read more about Assignment Help Australia 

Top Grade Assignment help provide technical 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

Technical Assignment Help,top grade assignment help, PHP Assignment help, Top Grade Assignment, Assignment help australia, online assignment help, java assignment help

About the Author

Alice