Unit 2- Entity Relationship Model (Week 2 IP), Assignments of Database Management Systems (DBMS)

Download Unit 2- Entity Relationship Model (Week 2 IP) and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!

Running Head:Unit 2- Entity Relationship Model (Week 2 IP) Database Systems (CS660-2301A-01) Unit 2 IP Week 2: Entity Relationship Model Student Name 29 th^ Jan, 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 Administration Plan (Week 4 IP)...........................................................................................
• Future Database System Implementation Plan (Week 5 IP)................................................................
• 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 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 cutomers 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 levl 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. Thi 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 section. Thi number is also dependent on the amount of products in that section. The more the products in a certain ection the more the employees that particular section will be assigned. Cardinality, Relationships and their Constraints. Cardinality constraints is used to express the maximum amount of relationships instances that an entity can participate (Vincent 2022). In the second relationship shown, (1,1) means that each employee can only show themselves one time only in the relationship. This means that a single employee can only work for one department only att a

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

interconnected later on. The ERD further has had all the columns not dependent on the primary key prior to its development (Peng, 2017). Importance of the ERD. The ERD above goes a long way in helping the store attain its crucil need to increase its sales by 8% in that the inclusion of discount on some products will led to attraction of new customers which also help the customer increase its needed 20% increase in clientele. The dtabase also helps the store increase its product offerings in the different categories due to the existent minimal reorder level policy which will assist Luna in knowing which products are needed and which ones are selling the most.

Database Administration Plan (Week 4 IP).

Future Database System Implementation Plan (Week 5 IP).