Future Database System Implementation Plan for Luna's Organic Food Store, Assignments of Database Management Systems (DBMS)

Download Future Database System Implementation Plan for Luna's Organic Food Store and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!

Running Head:Unit 5- Future Database System Implementation Plan (Week 5 IP) Database Systems (CS660-2301A-01) Unit 5 IP Week 5 IP: Future Database System Implementation Plan Student Name 19 th^ Feb, 2023

Table of Contents

• Database System Overview (Week 1 IP).....................................................................................
  • Description of the Organization..................................................................................................
  • Database system goals and objectives Statement......................................................................
  • Measure of how the proposed system aligns to the mission statement and goals....................
• Entity Relationship Model (Week 2 IP)......................................................................................
  • Business Rules.............................................................................................................................
  • Cardinality, Relationships and their Constraints.........................................................................
  • Entity-Relationship (E-R).............................................................................................................
  • Importance of the ERD..............................................................................................................
• Structured Query Language (SQL) Scripts (Week 3 IP).........................................................
  • Database Definition Language..................................................................................................
• Database Administration Plan (Week 4 IP)..............................................................................
  • Database Administration Plan...................................................................................................
  • Transaction Management Plan.................................................................................................
  • Database Security.....................................................................................................................
  • Backup plan and recovery model..............................................................................................
• Future Database System Implementation Plan (Week 5 IP)...................................................
  • Object-oriented and object-relational database systems and Web-based database systems..
  • Data Warehouse.......................................................................................................................
• References....................................................................................................................................

one-stop shopping experience for community members who are seeking to eat healthier, learn about and engage in sustainable lifestyle practices. The goals that the database ought to help Luna’s Health food achieve includes the objectivity of increasing the retail store’s customer base by a proposed 20%. This is so critical to the survival of the retail store in that it also mens that Luna’s business patner will remain in the business and not pull their finaces from the business that migt be detrimental to the business. The database also ought to help in the expansion of the product category offerings. This will go a long way in achieving the much needed convenience by customers from the business’ website. All the information on product availability, recipes and prices will be available on the website. This will increase the retail store’s competitiveness against other big food retail companies around. This will also help in increasing the customer base of the company and eventually achieving the needed sales target set by the business partners. The other goal that the database is supposed to help achieve is to help in the making of product, health and recipe information available to customers. This help the retail store remain in competition against the other big companies that are gunning for its customers. A visit to Luna’s Health Food’s website ought not just give information about the food available but also better recipes and natural homeopathic remedies as added incentives to using the store’s website. This will go a long way in making sure busy users will just use the store’s website as a one-stop for their grocery shopping. This move will also go a long way in helping the business achieve another goal of increasing the overall store’s sale by 8%. This is one of the incentives that Luna has to achieve in order to ensure the longevity of his business. This can only be achieved by the store’s ability to attract new customers and be able to fully satisfy and maintain its existing customers. The new website’s main objectives to be able to help the business achieve its goals.

A database system is ideally a software that caters to the collection of electric and digital records to extract useful information and store that information (Syeda 2021). The main purpose of a database system is for storage and retrieval of information (Syeda 2021). The data stored in a database sytem can then be easily accessed, managed, modified, updated, controlled and organized (Ming 2003). The existing database currently in use by Luna’s store does not possess the capacity to relay all the intended information that Luna wants it to show to his customers and also it does not relay all the information needed by the customers especially the busy ones. These challenge can lead to the store losing its customers to other big companies who are able to show the customers whatever they need to know. By counteracting this threat, the store is able to stay in business and also due to the nature of the product they offer, increase its sales and most importantly while ammersing new customers they are able to hold on to their current ones. A database is seen as importantly as a source of information (Ming 2003) and in this case, Luna’s Organic Food Store’s website’s database should be able to correctly and articulately relay whichever information Luna wants the customer to know about his store. The challenges that Luna is currently facing can be solved by this new proposed system by first of all making it possible for the system to be able to print out the recipes and the other literature he wants to offer to the public. To help with the set goal of expanding his product category offerings, the system could be able to conduct a customer survey to evaluate how satisfied they are with the current array of products in offer and what they would like to be added to the list in future in terms of new products or improved quality of existing products. This will give Luna the capacity to see the trends in terms of customer taste and also be able to change with it. This helps the store achieve customer satisfaction which is critical to its survival. Measure of how the proposed system aligns to the mission statement and goals. The proposed system will have a number of databases within the main system. These databases will include customers database, product categories database, recipes database, articles database, workshops

Entity Relationship Model (Week 2 IP).

Business Rules. The database being proposed to Luna’s Organic Food Store is being made to adhere to the current and reigning business rules and also in addition a few rules that will go a long way in helping the business attain some of its goals and also achieve some of its goals like increasing number of customers and also education of its customers. The business has a list of fundamental rules to the day-to-day running of the firm in line with the set goals, these rules include:  Each and every product is allocated a maximum of one category.  Aperson who has placed at least one order is considered a customer.  All the products on sale at the store have a minimum reorder level.  An invoice can contain one or more invoice line clearly stating the amount and total price of the products enlisted in the invoice.  There will be a minimum of 2 employees and a maximum of 5 per section in the store.  All the recipes enlisted at the store’s website must always be available in printed form at the stores. The business has a policy that each and every product can only get a maximum of one category. This rule is to be enforced in the new database that is being proposed to Luna’s Organic Food Store. This means that the store will be able to avail a wide variety of organic goods to its customers who in turn will

have a wide range of different products in each category to pick from. This rule also means that the store will have to improve it relationship with the farmers who do usually supply the store with their goods so to get the wide variety needed to achieve customer satisfaction. Luna will also have to gain access to more farmers or even supplier so as to make sure all the product as advertised in the new business website will always be available. The other business rule that is to be implemented in the new system will be that any person who has ever placed at least 1 order will be considered a customer. This, in accordance with the new business policy on education of its customer, means that the person will be ble to receive information on any newly added products on all the categorie and also receive recipes and informational pieces that will be sent to them about the various products at sale at Luna’s Organic Food Store. The store also has a minimum reorder level on all of its products. A reorder level which is also known as reorder point is used to refer to the inventory level of a store at which a seller will place an order for new products with their suppliers to replenish the stock of that certain product. Luna’s Organic Food Store has a 35% reorder level on all of its products. This basically means that incase the numbers of a certain product in sale reaches 35% or below of the original amount, then the store will place an order to replenish the diminishing stock. This is in line with attaining cutomer convenience and always making sure that the store has enough stock of all products all the time. The other business policies is on invoicing process. The store policy is that an invoice can contain one or more invoice line clearly stating the amount and total price of the products enlisted in the invoice. Also, each invoice line is associated with a singular invoice. This means that each line of invoice will accommodate all the products of a certain type taken. The store also has a policy of 2-5 employees per

represent the entities or objects as boxes and relationships are the lines joining these boxes (Vincent 2022). Different types of lines are used to show the cardinality of the relationship and they are used in pairs. The cardinality of the relationships are as follows: the customer can add a product to the shopping cart one at a time and a certain cart can only be associated with one customer. The product and product are added as the customer will need to choose from an assortment. The product category has a name, ID, and decription. A product can only be associated with one cart while the cart can have zero or a lot of products. The product and product category have a one and many in both sides. Discount has a one or zero relationship s some customers will lack the discount code. Order has zero to many relationship with the client, as they may have zero or a lot of orders. The customer and payment relationship is one and one as each customer is subject to their payment made to the store (Hingorani, 2017). Entity Primary Key Attribute Customer Customer_id Customer Name Shopping Cart Product_id Product Name Product Product_id Product Name Product Category Category_id Category Name Order Order_id Number of Orders Payment Payment_id Payment Method

The ERD above does achieve the 3NF because as stipulated by the laws of the 3NF, the ERD above has been drawn with no duplicative column tables for each group of related data is available in accordance with 1NF. The ERD also has had all the subsets of data that apply to multiple rows and have them placed in different tables. This is shown in the separation of the payment and discount tables. These tables are then

Structured Query Language (SQL) Scripts (Week 3 IP).

Database Definition Language. A Data Definition Language is in essence a computer language that is used in the creation and modification of a structure of database objects in a database. These database objects are inclusive of the views, schemas, tables and indexes (Sergio 2021). The Microsoft SQL Server Express Edition was chosen as the database platform inclusive with its management tools. Microsoft SQL Server Management Studio is an integrated environment for managing, configuring and administering any SQL infrastructure. Some of the advantages of using Micrsoft SQL which are in line with Luna’ stores goals and objectives include faster data processing which will help with the projected increase in clientele who will be accessing the store either online or physically and also the upload of recipes and informantion to the webite for the customers. The store is also looking to integrate working from home to increase the hours being put to work thus with the fact that this script is portable most worker can even put in after work hours while at home to improve sales and customer services being offered by the tore Data Definition Language (DDL) is a subset of SQL and a part of Database Management System; it consiste of various commands like CREATE, ALTER, TRUNCATE and DROP. These commands are the ones used to modify tables created in SQL (Sergio 2021). The DDL has been used in creation of the needed tables in the proposed database that is to be used by Luna’s Organic Food Store. The database definition language scripts to CREATE as used in the E-R diagram above will look as follows for the separate stores and the departments found in each and every store. USE master GO ..CHECK TO SEE IF DATABASE EXISTS AND IF SO, DROP AND RECREATE DATABASE IF EXISTS (SELECT name FROM sys.databases

WHERE name = ‘storemanager’) GO ..SWITCH TO DATABASE USE store manager ..CREATE TABLES CREATE TABLE tblStore ( StoreID char(10) NOT NULL PRIMARY KEY StoreName varchar(25)NOT NULL StoreAddress1 char(35) StoreAddress2 char(35) StoreCity varchar(20) StoreState char(2) StoreZip char(10) ) CREATE TABLE tblDepartments ( DeptID char(10) NOT NULL PRIMARY KEY DeptName varchar (20) NOT NULL StoreID char (10) FOREIGN KEY REFERENCE tblStores(StoreID) Database Manipulation Scripts These are ways of data organization or arrangement in order to make it easier to understand or interpret. DML is a coding language that allow one to reorganize data within the database program (Ganchev, 2021).

Report Structured Query Language. This part of the database helps with the analyzation to produce formatted reports with different tables in the various forms of graphs, image, charts and data (Sergio 2021). The functions being incorporated into the database include SELECT, CROSSTAB and AGGREGATE FUNCTIONS. These functions will assist the employees in interpretation of the data and eventually Luna will be able to see the trajectory his store is headed. USE storemanager ..SELECT ALL STORES AND THEIR RELATED DEPARTMENTS SELECT tblStores. storeID, StoreName, DeptName FROM tblStores INNER JOIN tblDepartments on tblStores.storeID = tblDepartments.storeID ..SELECT ASTOREAND ITS RELATED DEPARTMENTS SELECT tblStores, storeID, StoreName, DeptName FROM tblStores INNER JOIN tblDepartments ontblStores.storeId = tblDepartments.storeID WHERE tblStores.StoreName = ‘ABC Company’ AGGREGATE QUERY SHOWING TOTAL COUNT OF DEPARTMENTS PER STORE SELECT StoreID, COUNT(DeptID) a ‘Number of Departments’ FROM tblDepartments

GROUP BY StoreID The database platform chosen, Microsoft SQL, has a number of tasks that it is supposed to run for the efficient running of the store. Luna needs to keep tabs of all of his stores and the product that they are selling the most. He also needs to know which recipes are most preffered by the customers so that he can make them easily accessible either at the store or online at the store’s website, by keeping tabs of the number of customers using his store for their grocery shopping he will be aware whether or not he can attain the set 10% goal increase in customers using the shop. The cutomer and sales database can also help him observe whether he is on track to attain the 20% increase in ales that he is supposed to attain as set by his investors. The database will now act more like an assistant at his disposal at whatever the time he needs it.

should be included in the database includes product information, hierarchy rules, categories, subcategories, families, and variants. The other important entities in this database will be the order and payment entities. These two will help track how far the store is concerning their set goal of an increase in sales by 8% and an increase in customers visiting the store either physically or online via the store's website. To create the store's database, first, on the database server, a store database will be created then the initial data collected will be distributed on the server. A POS register with an offline database will be created. Next, the data between the store and offline databases will be synchronized. The POS will be operated when the database is offline and then reconnected to the store's database. Transaction Management Plan. A transaction is a logical unit of task performed on a database. A transaction results in one or more modifications to a database. COMMIT in SQL lets the user save any changes made in the transactions permanently. This means tha the database cannot go back to its initial pha once the COMMIT command is executed. ROLLBACK in SQL lets the user reverse or delete any unsaved changes in the database. This command can be executed to undo any alterations the COMMIT command brings. The COMMIT command can be used to confirm a transaction as complete. This can be the case when a customer has had all their products prized and then paid via cash, online money transfer, or even through the bank. This command can confirm that a particular is complete and that the products stated in the invoice have been taken and can be deducted from the inventory. On the other hand, the ROLLBACK command can be used when a customer returns some of the products they had taken and were invoiced for. This command type will help reverse

the COMMIT command in play. This command can help when the transaction is unsuccessful due to abortion, incorrect execution, power failure, or even system failure. Database Security Database security refers to the array of tools, controls, and measures taken to establish the database's confidentiality, integrity, and availability. Database security ought to address the safety of the data in the database, the database management system (DBMS), the physical and, in some cases, the virtual database server, and the computing and network infrastructure used to access the database (Larsen 2020). For ample security, especially in Luna's store, where the servers will be a cloud data center, the cloud provider will be in charge of the security of the online servers. This is in line with mitigating costs related to having security to protect physical servers. The server must also have a minimum number of users who will be allowed access to the system at a given time and given their level of clearance. They ought to have limited access to the servers. All the data in the servers ought to be encrypted just to increase the security of the