Database Security for the Schumer Company

Do you need this or any other assignment done for you from scratch?
We have qualified writers to help you.
We assure you a quality paper that is 100% free from plagiarism and AI.
You can choose either format of your choice ( Apa, Mla, Havard, Chicago, or any other)

NB: We do not resell your papers. Upon ordering, we do an original paper exclusively for you.

NB: All your data is kept safe from the public.

Click Here To Order Now!

Introduction

The Schumer Company being an ecommerce company and the urge to operate 24/7 is only achievable through synchronizing their operations with technological advancements. The company should embark on customer relation management (CRM) to increase its uptime to 99%. Setting up such activities will enable Schumer Company to have the desired return on investment within four years using their $100,000 budget. The SQL sequence needed is user-friendly, provides swift operations within and outside the organization, and adheres to security policies.

Timeline

The web interface should allow multiple users to log in and browse Schumer Company products. Multiple users must have a close monitoring spectrum to enable the site to reduce its chances of having downtime. Therefore, using Oracle as the host using the IaaS, the web interface must have three primary areas. These are; Customer service, Operations, and Technology. SQL allows for the web interface to store, manipulate, and retrieve data within the national database.

Conceptual data model

Schumer Company Customer Data Platform Schumer Company should-have application servers and an internal database networked appropriately to limit security breaches and cater to customers who are not ready to create an account but want company products shipped to them. The orders from customers should be channeled to the Customer Services staff through the unique identifier before being sent to the shipping department. The orders should be flexible in the submission to mail, fax, phone, or submit online orders. The purchase scheme can be incorporated using the credit card orders through leased machines that credit card companies provide. Customers who choose to use direct deposits to Schumer Company should be automated and have the clarity to allow them to proceed to the shipping process department. The conceptual data model will have workstations, servers linked to cloud computing, and a network foundation provided by Oracle, as shown in the diagram below.

Conceptual data model

Logical data model

Security plan in public network protection

The first defense line uses the Cisco 2600 router that filters the packets and provides strong security in terms of public network issues. The Cisco IOS will enable the Schumer Company e-commerce platform to have substantial traffic. The risks of publicity accessible areas in the web interface will be under constant scrutiny every time the customer with an account or non-users log into the website. The internal network of Schumer Company will use the private IP addresses to enable the public IP addresses to reside under the public domain. The IP configurations have a global meaning since the containment of the routing information will differentiate between the company’s employees the customers interested in the products of Schumer Company. The organizational routers should be programmed to determine private address space and public address space to filter out unconfirmed routing information. When the router senses the rejection matrix, it can channel it as a routing protocol error categorized as inbound filtering or outbound filtering matrix.

Physical design

SQL explores the multiple datasets of Schumer Company. An ecommerce company has around eight datasets that represent it. These datasets are ordered, customer interface, items requested, amount to be paid, delivery details, mode of payments, and approximate product arrival. All the datasets are related, and MIS mapping can occur if the datasets are not defined elaborately. For instance, an “order” and a “customer” dataset can mix if two different customers order the same product simultaneously. To differentiate such an instance, it is fundamental for the customer to have a unique identifier. The unique customer identifier can distinguish them when they order the same product, as shown in the table below.

Customer_id Customer_zip_code Customer_city Customer_state
002da407hyry 85897 Sao Paulo SP
09thy56fhgyr4 98123 Kansas KS
003wikl76yth 94561 Minnesota MN
09tyok467hfn 65209 Lusaka ZA
09vbur467aft 35927 Ontario CA
001iyt987fhb 89641 Rio de Janeiro BR
005oplm456af 33741 Ijui RS

The SQL has an inbuilt functionality that allows one to build an ER diagram to map and draw the links between datasets and other visuals. The Schumer Company’s logical data model is as shown below.

The Schumer Company's logical data model

The company’s logical data symbols are as shown below by a scheme called crow’s food matrix.

The company's logical data symbols

Security and privacy requirements

Internal networks

The internal network will differ from others because of its stately inspection firewall. The architecture of the internal network should be aligned in layers that conform to dynamic packet filtering. Dynamic packets inspect each connection and traverse each interface of the firewall to ensure that it has validity. The internal networks should be programmed to ensure the store department closely monitors the web interface. This ensures that customers order products that are available only. Dynamic packets in the internal networks compile information and inspect the firewall, enabling the filtering of decisions based on administrators-defined rules. The administrator of Schumer Company can be the head of the IT department or the major shareholder who can be taught modes of regulating the site. To add to the security measure, port scanning should be closed through stately firewall inspection until a specific port requests some packet transfer.

The mode of requesting packets through the firewall ensures that simultaneous TCP/IP connections are synchronized between essential private internal areas within the organization and the public access framework. For instance, the customers might need insight from the ecommerce support team. Therefore, this is more like internal and external network communication. The payment services are a form of the internal network. The multi-server operations that verify the products to be shipped with the online accounting docket allow for clustering. SolarWind Network can monitor the payment traffic network. It is necessary to place a security device at the point of ingress/egress because the payment connectivity is always a separate internet connection. The diagram in the next page shows how the credit card processing service in ecommerce is channeled.

The credit card processing service in ecommerce is channeled

Security and Privacy Implementation

Datasets model and SQL sequencing process

Using five significant datasets, the formulation is as shown in the ERR diagram below.

Datasets model and SQL sequencing process

The structural links as shown by numbers have the following addresses in terms of customer and order orientations:

  1. The customer and orders sequence shows that a single customer can place multiple orders, making the styling “one or many.”
  2. The orientation engulfs the orders and order_items whereby a single charge can have multiple order_items, making the styling “one or many.”
  3. This structure makes the order and payment scheme single out each order with its payment matric, thereby making the styling be “one and only one.”
  4. The order_items and products are similar, creating a styling system known as “one and only one.”

Security testing and verification of db implementation

SQL processing allows the parsing, raw source generation, and SQL execution statement in the Oracle host orientations. SQL essentially imports the raw database and converts the cross-table into a structured database that makes the whole process digestible and easily scalable. Using the ERR diagram on the previous page, the datasets can be centralized before manipulating them further. Equally, the datasets can be manipulated from a single hub and separated in the next channel. The use of “JOIN” in option (1) datasets is termed as (orders and customers) while using “LEFT JOIN” for (2), (3), and (4) to enable the datasets to have clarity. “LEFT JOIN” identifies “null” values, and it infers all missing information from the original data input spectrum. The SQL datasets table shows the sequence analogy that Schumer Company can adopt for their web interface.

1 Create table final_database as
2 (select o.*
3 Round (timesampdiff(minute, o.order_purchase_timestamp, o.order_approved_at)/60/24,2) as purchase_to_approved_days,
4 Round (timesampdiff(minute, o.order_approved_at, o.order_delivered_carrier_date)/60/24,2) as approved_c arrier_days,
5 Round (timesampdiff(minute, o.order_delivered_carrier_date, o.order_delivered_customer_date)/60/24,2) as carrier_to_customer_days,
6 Round (timesampdiff(minute, o.order_approved_at, o.order_estimated_delivery_date)/60/24,2) as approved_to_estimated_delivery_days,
7 c.customer_city, c.customer_state,
8 oi.product_id, oi.order_item_id as product_qty, oi.price, oi.freight_value,
9 pa.payment_sequential, pa.payment_type, pa.payment_intallations, pa.payment_value,
10 p.product_category_name_english, p.product_name_length, p.product_decription_length, p.product_photos_qty
11 from orders o
12 join customers c
13 on o.customer_id= c.customer_id
14 left join order_items oi
15 on o.order_id=oi.order_id
16 left join payment pa
17 on o.order_id=oi.order_id
18 left join product final p
19 on oi.product_final p
20 )
21 ;

Key highlights of the SQL statement above is as follows:

  1. CREATE TABLE: the “create table” is a logarithm that centralizes the database hub for data input.
  2. TIMESTAMPDIFF: it converts the timestamp-format values into preferred time indicators such as hours and minutes. In the Schumer Company instance, the timestamp divides the time by 60 to personify the data into several days to get their purchase after confirming their payment.

After the SQL statement, there is a need for creating a centralized raw database to enable the conversion to look more structured and usable. The following scheme elaborates the usable format through:

  1. Timestamp column (order_delivered_carrier, order_approved) is used to calculate the time spectrum, such as the number of days that Schumer Company takes to approve an order placed by the customer to the time of delivering the products to their doorstep.
  2. The price column (includes the shipping fee and unit price of the chosen product) enables the system to purchase the total cost in every order made to the ecommerce system.

The key highlight of the SQL Statement orients to “GROUPING BY.” When the texts and aggregations are selected, such as multiplication, subtractions, and sum of products, there is a need for the package to use “GROUP BY” to identify each data into a specific group. For instance, Schumer Company might have the same multiple order_ids that contain the same set of values, so the order_ids in the table below show the database sets that generate values and columns to align with the Schumer Company web interface of desired standards of Oracle Company hosting.

Oracle Company hosting

References

Abdulsahib, G., & Khalaf, O. (2019). Comparison and Evaluation of Cloud Processing Models in Cloud-Based Networks. International Journal Of Simulation: Systems, Science & Technology. Web.

Do you need this or any other assignment done for you from scratch?
We have qualified writers to help you.
We assure you a quality paper that is 100% free from plagiarism and AI.
You can choose either format of your choice ( Apa, Mla, Havard, Chicago, or any other)

NB: We do not resell your papers. Upon ordering, we do an original paper exclusively for you.

NB: All your data is kept safe from the public.

Click Here To Order Now!