Grading for this assignment will be based on answer quality, logic/organization of the paper, and language and writing skills, using the following rubric found here.
Michelle_Michy
20200305184509database_design.docxDatabase Design: Franchise Management System
1
13
Database Design: Franchise Management System
Database Design: Franchise Management System
Charles Foley
CIS 498: Information Technology Capstone
2/19/20
Database Design: Franchise Management System
Introduction
Over the years, there has been a rise in the level of use of the latest technologies in facilitating the operations of various business entities. Business leaders and managers have realized that the success of their entities relies on the strategies employed and adopted in the long run. Businesses in various regions have utilized the latest technologies to create a competitive advantage over rivals. In this case, the creation of competitive advantage, in the long run, helps to achieve improved sales and hence profits. However, determining the right technology to implement remains a significant challenge for many businesses.
In this case, it, therefore, follows that the management teams should ensure that they understand the role and benefits of each of the available technologies which in the long run may aid in the improvement of the overall level of insight into the operations carried out on the day to day basis. One of the most common technologies which many businesses adopted over the years involves the aspect of the use of relational databases (Hoffer, Ramesh & Topi, 2016). The development and use of database systems help in the creation of a platform for improved business operations in the long run. While database systems help in the improvement of the operations carried out on the day to day basis, it is worth noting that the ability to achieve positive outcomes revolves around the aspects of the selection of the right approaches to design the ultimate systems and solutions.
Information system development helps in the provision of the ultimate success and efficiency of the respective entities in the long run. This project looks at the case of a franchise business which manages different branches and deals in the sale of various products. The business needs a system which will facilitate not only communication and reporting but also the overall operations carried out in the various branches. Therefore, the business will benefit from the use of a relational database based on the idea that it will facilitate the provision of a platform for improved storage and retrieval of information about the various branches, products and people involved in the various processes. The system will help in redefining the requirements for the business while at the same time aiding in process reengineering activities. The first section looks at the database schema of the proposed system. The second part looks at the aspects of normalization, tables and the entire database using SQL. On the other hand, the paper will look at the creation of a data flow diagram which graphically represents the movement of information throughout the database.
Database schema design
A database schema acts as a diagrammatic representation of a database using various attributes and aspects such as the tables, relationships, views and indexes. In this context, the creation of a schema helps to show a high-level view of the database and the various entities involved in the storage and retrieval of information. Also, a database schema helps on the variation of a high-level perception of the details which the system will store and deal with. Therefore, it is essential to look at the needs of the business in the context in the development of the schema. Hence, to ensure that all the data is covered and included in the final structure. The database, in this case, will comprise five main tables. These tables will store data such as the franchiser, franchisee, commodities, orders and products. The diagram shown in the appendix represents the graphical view of the schema of the database.
See attached appendix the Franchise management database schema.
Tables’ construction with normalized design
When it comes to the construction of tables and the entire database, it is essential to look at the aspects of normalization. Normalization helps in the creation of a platform for reducing the risks of redundancies in the final database system and structure. Failing to normalize the tables will result in storage of multiple similar records throughout the database. In the long run, such a practice will increase the level of inconsistency achieved as far as the management of data and records is concerned.
On the other hand, it is essential to look at the best ways to implement the database to guarantee referential integrity. Referential integrity helps in the creation of a platform for improved insight into the efficiency of the storage and retrieval of data (Greenstein, Grunin, Schwenger & Swamikrishnan, 2019). In addition, it helps to eliminate the problems of redundant records while at the same time promoting the creation of an efficient system. Referential integrity is achieved through the use if the right relationships and links. Therefore, to achieve referential integrity, the database will use both primary and foreign keys in the various tables. Foreign keys help in connecting database tables according to their relationships. The following code helps in the creation of the database tables for the system in the context.
The code below creates the database tables observing the aspects of referential integrity and maintaining the desired level of efficiency. The main tables are franchiser, franchisee, item, invoice, order, order-item and item type.
Franchiser
CREATE TABLE Franchiser (
Franchiser id int PK, name varchar, email varchar
);
Franchisee
CREATE TABLE Franchisee (
Franchisee id int PK, name varchar, email varchar, franchiser id int FK
);
Item
CREATE TABLE item (
Item id int PK, description varchar, franchiser id int FK, item-type id FK
);
Invoice
CREATE TABLE Invoice (
Invoice id int PK, order id FK, status BOOL
);
Order
CREATE TABLE order (
Order id int PK, franchiser id int FK, franchisee id int FK, status int
);
Item-type
CREATE TABLE item-type (
Item-type id int PK, description varchar
);
Order item
CREATE TABLE order-item (
Order-item id int PK, item id int FK, qty int, order id FK
);
Entity-relationship diagram and explanation
An entity-relationship diagram shows the links and associations of the various tables in a database. It is worth noting that the creation of an entity-relationship diagram helps in providing a high-level view of the links between or among the various tables which make up the entire system (Greenstein, Grunin, Schwenger & Swamikrishnan, 2019). The diagram presented in this case shows the connection between and among the various tables referred to as entities. The diagram shows the different types of relationships, such as one to many, which connect the various tables to enforce referential integrity and eliminate redundancies.
See the appendix for the entity-relationship diagram.
Data flow diagram for the business
See the appendix for the data flow diagram.
Example query 1
The navigation of a database involves the creation of both inbuilt and customized queries to meet the needs of the users at a given time. This section outlines two queries to retrieve data from the database and to add details to a given table.
Retrieval query
SELECT name from franchiser WHERE franchiser id = “f001”;
This query returns a name of the franchiser whose ID is F001.
Example query 2
This section presents a query for adding information into one of the tables mentioned above. The section will add a product into the item table.
INSERT INTO Item (Item id int PK, description varchar) VALUES (‘NK001’, ‘Nike Air men's sports shoes’);
Example screen layout 1
The screen layout provided below represents a platform for viewing the available stores or franchises.
See appendix for the screen layout.
Example screen layout 2
The layout below shows a stock management screen.
See appendix for the screen layout.
References
Hoffer, J. A., Ramesh, V., & Topi, H. (2016). Modern database management (p. 600). Pearson.
Elmasri, R., & Navathe, S. (2017). Fundamentals of database systems (Vol. 7). Pearson.
Greenstein, M. A., Grunin, G., Schwenger, M. N., & Swamikrishnan, P. (2019). U.S. Patent No. 10,380,083. Washington, DC: U.S. Patent and Trademark Office.
Appendix
Database schema
Entity-relationship diagram
DFD diagram
Screen layout 1
Screen layout 2
|
|
|
|
|
|
|
|
