Database Security for the Schumer Company Coursework

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.

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.

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 company’s logical data symbols are as shown below by a scheme called crow’s food matrix.

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.

Security and Privacy Implementation

Datasets model and SQL sequencing process

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

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.

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.

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.