ADB & DM
Retail 1
Retail What is Business (Education, Finance, Health Care, Retail, or a business of your choice?
Retail
What is the purpose of the DB?
This DB is used to store wide veraity of data related to the retail field for example a retail store has data related to employees, customers, orders, products, offices, payments, orderdetails, productlines. This database can have all the necessary relationships and constraints in order to perform a smooth and reliable operations while trying ot be fail proof.
Who is the DB for? Who are the users?
This DB is for the day to day activity in a retail store from front desk, payments, billing to inventory management in backedn of the store.
Users for the DB shall mostly be the employees of the store but with different roles, such as employee at the billing counter will access payments and customer details, while products table is managed or maintained by a backend worker dealing with inventory management responsibilities. In this way retail DB can be accessed by different employees with different responsibilities.
Why is the DB necessary?
This Database can help in streamlining the operations in a retail store as it maintains the information about the products available in the store, details frequent customers and frequent products bought which helps in performing related analytics on the customers preferences which might also be based on the seasons and other factors. Such collection and storage of data in the database can help the retail store in optimizing operations verticals in the store.
Where is the DB located both Physical and Logical location?
Typically, mysql will store the data in the default diectory of /var/lb/mysql. This can be accessed acordingly depending on the location of the database, i.e where
Retail 2
it is located in remote server using any hosting services or if it is stored in on-site server. Based on the server location the db can be located.
How will it be hosted?
Since the retail store influx is volatile i.e huge amount of customers might shop on a particular date which is unpredictable, scaling is an important factor which favours the use of hosting services, including AWS, Microsoft Azure, and Google Cloud Platform. The database may also be kept on site, i.e in the retail store premise, depending on its requirement, in an internal server.
How will the data be stored and backed up?
The process of creating, maintaining, and preserving copies of data in case it is lost, corrupted, or destroyed is known as database backup. Users can restore data from databases before it becomes useless. Using a database backup system, this may be carried either automatically or manually.
Key stages of the database backup and storage process include:
Identify critical data sources that must be backed up and secured, including tables, instances, and logs
Determine the type of backup required for the environment
Choose the storage location of the backup data. Businesses can store the backup on-premises, cloud, or a hybrid location.
Execute the backup at a scheduled time. DBAs can automate the execution process if required.
Regularly validate the data by restoring it to a different database location
Establish Business Rules.
Main users of the DB should be the employees of the retail store
since the inventory is not updated frequently, we can place the products table as static table
Previliages such as changing the MSRP of a product should be kept out of access to all employees.
employess should be given limited access accross the database.
Retail 3
Payments table should be taken care of with checknumber and amount column being most important columns.
Design map
Database Tables:
Customers Table :
Retail 4
Employees Table
Offices Table
Order Details
Retail 5
Orders
Payments
Retail 6
productlines
Products
Retail 7