Suggest a specific RDBMS solution you believe would be most appropriate for the
ID: 3752266 • Letter: S
Question
Suggest a specific RDBMS solution you believe would be most appropriate for the company.
List the benefits the company could expect to gain from implementing the recommended RDBMS
Brewton Business Scenario
Brewton Enterprises, Inc. is a clothing retail company located in the city of Eugene, Oregon. The company offers an array of products with a mix of clothing styles, appealing primarily to teens and young adults. Brewton has four satellite offices: Los Angeles, New York, Houston, and Atlanta. These offices, including headquarters in Eugene, serve as distribution centers for products sold by Brewton. The company employs 240 employees as follows:
Eugene 130
Los Angeles 27
New York 30
Houston 25
Atlanta 28
Each of these locations has a warehouse manager, an administrative assistant, a human resources representative, sales and customer service coordinators, shipping and receiving personnel, and facilities maintenance personnel. All locations stock a full complement of Brewton products for delivery to purchasing customers. In addition to these functions, headquarters, located in Eugene, also provides human resources, purchasing analysts, sales and marketing personnel, accountants, systems personnel, and operations personnel.
Mark Brewton, the company’s founder, wants to implement a relational database management system (RDBMS) that would support his long-term vision in implementing comprehensive Enterprise Resource Planning (ERP) application to support the business now and in the future. He believes the business has grown to the point that such a system will streamline business processes, help control business expenditures, and better manage inventory levels, which presently do not seem to reflect accurate numbers for each distribution center. The new system will replace almost all of the computer applications currently in use in the company.
The Current System Environment
Users in the business functions of the organization use standalone software packages, each with its own database. The standalone software packages include a purchasing system, accounting and payroll applications, and a personnel-tracking system used by Human Resources. Because these applications do not share files, there are frequently situations in which employee and customer data do not match. When this happens, research must be conducted—and data updated in multiple places--to make the data consistent across all the standalone systems. The IT organization runs special programs developed to cross-compare the information and report mismatches among data files, which are then updated to reflect the correct information. The company currently does not have a disaster recovery plan or a business continuity plan to be followed in the event of a contingency situation, such as flooding or an earthquake, and management feels this situation should be addressed. The company needs to back up its databases to prevent data loss in the case of a disaster or other emergency.
Sales Department and Order Processing Form
Below is an example of the order processing form that needs to be entered into the sales system. It currently resides in a spreadsheet and the company is hoping to migrate this system to sales-specific software with an RDBMS backend.
Order Form
Order number: 1234
Order Date: 5/12/18
Customer number 9876
Customer name: John Doe
Customer address: 456 Bishop Street Honolulu, Hawaii 96813
Tel: (808) 8650990
Sales agent: Lloyd Johnson
Sales agent number: S99
Product No.
Description
Quantity
Unit Price
Product No.
Description
Quantity
Unit Price
Explanation / Answer
In order to implement Brewton's (The founder) idea to implement RDBMS in his ERP there are many constraints need to be considered.
-> It is known that different functionalities of the company are being handled by different software.
-> There is a difficulty in sharing the details between these applications.
-> There is a data inconsistency and data duplication.
-> There is no backup of data.
In order to migrate all these functions into a single database,it is not a easy process.
-> The data stored in multiple databases with multiple applications in multiple data forms.
-> Migrating the data into a single RDBMS is complex since data is stored in different forms.
-> Firstly, the data need to be pre-processed into a single data form.
-> There is a data duplication or corrupt possibility, in order to avoid that uniformity of the data need to be maintained. A common data field is used by all of these applications need to be identified before the migration process.
-> There should be constraints defined to maintain data integrity.
-> Database models should be normalized.
-> All the data shouldn't be saved only in a single server to prevent data loss again natural calamities and other physical risk factors like fire etc.
-> Should be adapted to cloud computing for data processing and storing.
It currently resides in a spreadsheet and the company is hoping to migrate this system to sales-specific software with an RDBMS backend.
Order Form
Order number: 1234
Order Date: 5/12/18
Customer number 9876
Customer name: John Doe
Customer address: 456 Bishop Street Honolulu, Hawaii 96813
Tel: (808) 8650990
Sales agent: Lloyd Johnson
Sales agent number: S99
Product No.
Description
Quantity
Unit Price
There should be 4 entities in RDBMS to store this data. We need 4 entities to maintain data consistency, normalization, constraints and to avoid anomalies.
The data model is given below:
Orders(OrderId, OrderDate, ProductNo, Quantity, UnitPrice, AmountPaid, ModeofPayment)
OrderId - PK
ProductNo and UnitPrice - FK
Product(ProductNo, Description, UnitPrice)
ProductNo - PK
Customer(CustomerId, CustomerName, CustomerAddress, CustomerTel)
CustomerId - PK
SalesAgent(SalesAgentId, agentName)
SalesAgentId - PK
There is a primary key and foreign key relationships maintained to share the data between the entities.
Feel free to comment for any doubts and modifications.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.