Project Deliverable 4: Infrastructure and Security

Due Week 8 and worth 150 points

This assignment consists of two (2) sections: an infrastructure document and a revised Gantt chart or project plan. You must submit both sections as separate files for the completion of this assignment. Label each file name according to the section of the assignment for which it is written. Additionally, you may create and / or assume all necessary assumptions needed for the completion of this assignment.

The last section of the project plan will present the infrastructure in accordance with the parameters set forth at the outset of the project. The network solution that is chosen should support the conceived information system and allow for scalability. The network infrastructure will support organizational operations; therefore, a pictorial view of workstations, servers, routers, bridges, gateways, and access points should be used. In addition, access paths for Internet access should be depicted. A narrative should be included to explain all the nodes of the network and the rationale for the design. Lastly, using the Confidentiality, Integrity and Availability (CIA) Triangle, define the organizational security policy. CIA is a widely used benchmark for evaluation of information systems security.

Section 1: Infrastructure Document

1. Write a five to ten (5-10) page infrastructure document in which you:

a. Design a logical and physical topographical layout of the planned network through the use of graphical tools in Microsoft Word or Visio, or an open source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length.

b. Illustrate the possible placement of servers including access paths to the Internet and firewalls. Note: Facility limitations, workstations, printers, routers, switches, bridges and access points should be considered in the illustration.

c. Explain the rationale for the logical and physical topographical layout of the planned network.

d. Create a comprehensive security policy for the company that will:

i. Protect the company infrastructure and assets by applying the principals of CIA. Note: CIA is a widely used benchmark for evaluation of information systems security, focusing on the three core goals of confidentiality, integrity and availability of information.

ii. Address ethical aspects related to employee behavior, contractors, password usage, and access to networked resources and information.

Your assignment must follow these formatting requirements:

· This course requires use of 

Strayer Writing Standards (SWS)

. The format is different than other Strayer University courses. Please take a moment to review the SWS documentation for details.

· Include a cover page containing the title of the assignment, the student’s name, the professor’s name, the course title, and the date. The cover page and the reference page are not included in the required assignment page length.

· Include charts or diagrams created in MS Visio or Dia as an appendix of the infrastructure document. All references to these diagrams must be included in the body of the infrastructure document.

Section 2: Revised Gantt Chart / Project Plan

Use Microsoft Project or an open source alternative, such as Open Project, to:

2. Update the Gantt chart or project plan (summary and detail) template, from Project Deliverable 3: Database and Programming Design, with all the project tasks.

The specific course learning outcomes associated with this assignment are:

· Demonstrate an understanding of existing and emerging information technologies and strategic impact on organizational operations.

· Evaluate the issues and challenges associated with information technology integration.

· Evaluate the ethical concerns that information technology solve or present in a global context.

· Use technology and information resources to research issues in information technology.

· Write clearly and concisely about strategic issues and practices in the information technology domain using proper writing mechanics and technical style conventions.

DATABASE AND PROGRAMMING DESIGN 1

Database and Programming Design

Tom Anderson

Professor Urimindi

February 14, 2021

Table of Contents

A. Database Schema That Supports the Company’s Business and Processes…………………..3

B. Explanation of the Database Schema…………………………………………………………3

C. Database Tables……………………………………………………………………………….4

D. Normalize the Database Tables to Third Normal Form (3NF)………………………………9

E. Create an Entity-Relationship (E-R) Diagram………………………………………….……10

F. Explaining Rationale behind the Design of the E-R Diagram……………………….………11

G. Data Flow Diagram (DFD)……………………………………………………………….….12

H. Explaining the Rationale behind the Design of the DFD……………………………………13

I. Two (2) Sample Queries………………………………………………………………………13

J. Two (2) Screen Layouts ………………………………………………………………………14

K. References…………………………………………………………………………………….15

Part One: Database and Programming Design

a. Database Schema that Supports the Company’s Business Processes

b. Explanation of the Database Schema

The process of developing OnBuy’s e-commerce website may encounter challenges due to a lack of database standards that define the design and application of the database systems. To avoid the challenge of a lack of database, this particular project will be guided by a database schema based on the Database Task Group (DBTG) standards. Therefore, the above database schema comprises three major components: a database schema, a subschema, and a database management language. A schema refers to the database’s conceptual organization as viewed by the Database Administrator (Angrish et al., 2017). It includes a definition of the database name, the type of each record, and the components, which are the fields or columns that make up the records. The above database schema has entities and their respective attributes. Entities refer to the essential items in any data model. It is a thing or an object in the real world that can be distinguished from all other objects. Entities can be viewed as the real-world atomic item. For example, in the above schema, entities include an invoice, address, credit card, and user account.

Each of the uniquely identifiable instances of an entity type in the diagram is an entity occurrence or entity instance. Each entity has a name and a list of properties, as indicated in Figure 1. For instance, the properties of credit card include credit card number, holder’s name, and the expiry date. Therefore, while each entity type of attributes, they will alcove their values for each attribute. Additionally, each entity will have relationships with others. For instance, the relationship between invoice and user satisfaction allows the system to compute the purchasing rates, which include the number of sessions that emerge from the invoice, and eventually, an actual order.

c. Database Tables with Appropriate Field Naming Conventions

A database table refers to a collection of stored data that are managed by the database management system. Figure 3 shows the table that comprises different entities and their respective lists of attributes. For example, the customer entity includes different sets of features, such as customer ID, address, first name, last name, age, and billing address. Similarly, the payment entity encompasses attributes such as payment ID and payment type. The table also shows that entities have different types of keys. Keys are attributes that can enable a database administrator to identify rows in a relational table. In so doing, they enable professionals to determine the relationship between two tables and help to distinctively determine a row in a table by a combination of one or more columns within that particular table.

One of the most prominent keys in a table and refers to a minimal set of attributes that distinctly identifies the rows or entity. For instance, in the above table, the main attribute that uniquely identifies the customer entity is the customer ID. Therefore it is the primary key. Other attributes, such as customer name and age, do not fall under primary keys. The customer name alone cannot qualify as a primary key since more than one customer may have the same name. In the same vein, the customer’s age alone cannot be a primary key because more than one customer may have the same age. Therefore, the only key that uniquely identifies the customer is the customer ID because each customer has a distinct ID that can identify their records in the table.

Another defining characteristic of primary keys is that they are columns or groups of columns that distinctly identify every row in the table of the relational database management system. It cannot be duplicated, implying that the same value should never appear more than once in the table. Primary keys may not necessarily be a single attribute in a column. On the contrary, a set of columns can make up a primary key. For example, in the following table, a combination of customer ID and customer name collectively identifies the tuple in the above table. It cannot be chosen as a primary key alone because customer ID is sufficient to identify rows in a table distinctively and always go for the minimal set. It is important to select more than one column that can distinctly identify the entity in a table in light of the above.

Another example of an entity with a primary key in the table is ORDER. This table stores the daily records of the purchases that customers make. For instance, the table has three attributes, which include Customer ID, Product ID, and Order Quantity. Customer ID alone cannot be a primary key because a single customer may place more than one order, thereby, more than one row of the same customer ID value. For instance, in the table, a customer may place two or more orders with different product IDs. Thus, product ID alone cannot be a primary key because more than one customer can place an order for the same product, thus more than a single row with the same product ID. For instance, in the table, two or more different customer IDs may place the same order for the same product. Furthermore, order quantity alone may not act as the primary key because more than one customer can place the order for the same quantity. Considering that one of the attributes alone can become a primary key, it is essential to explore a set of attributes that play the primary key role. For instance, a combination of customer ID and Product ID can identify the rows uniquely in the table, thereby setting a primary key for the table.

Apart from the primary keys, the tables also have their respective foreign keys. A foreign key can be defined as a column that creates relationships between two or more tables. Its purpose is to maintain data integrity and facilitate easy navigation between two different instances of an entity. Doing so serves as a cross-reference between two tables as it references the primary key of another table. Every relationship within a database is often supported by a foreign key. Hence, a foreign key also acts as an attribute whose value matches the primary key. For instance, the customer ID is the primary key in the customer table, but it also occurs as a foreign key in the product table. This implies that a foreign key acts as the columns of a table that points to another table’s primary key. In so doing, it acts as a cross-reference between the tables. For instance, in the Product ID table, the customer ID acts as a foreign key because it points to the primary key of the Customer table.

The foreign may have a key that has nothing to do with another table’s primary key tag. In this respect, if it points to a unique column of another table, then it will also act as the foreign key. In light of the above, the correct definition of a foreign key is a column of a table that points to another table’s candidate key. Thus, while selecting a set of attributes for a primary key, it is essential to choose the minimal background with a minimum number of attributes. For instance, if two sets can identify a row in a table, the set that has a minimum number of attributes should be chosen as the primary key.

From these evaluations, it is essential to explore the differences between primary and foreign keys. A primary key constraint is a column that distinctly identifies every row in the relational database system’s table. In contrast, a foreign key is a column that establishes a relationship between the two tables. Moreover, primary keys never accept null values, while foreign keys may accommodate multiple null values. Additionally, a table can only have a single primary key, whereas the same table can accommodate multiple foreign keys.

Additionally, the primary key’s value may not be removed from the parent table, while the value of foreign keys can be removed from the child table. Similarly, no two rows should have any identical values of a primary key. On the other hand, a foreign key can comprise duplicate values. Finally, there are no limits to the insertion of the values into the table column. Including any value in the foreign key table goes a long way in ensuring that such matters are present in a primary key column.

Achieving referential Integrity

Referential integrity is one of the essential global constraints in relational databases. It ensures that the foreign key value exists in the referenced relations. Common referential integrity issues are often found in database integration, data quality assurance, warehousing, and data modeling. Such integrity may be violated or relaxed for practical reasons. However, database integration represents a common situation in which similar tables emerging from multiple data sources have different referential integrity constraints. Each DBMS provides unique mechanisms and rules to enforce referential integrity (Wei et al., 2019). Thus, source databases may violate referential integrity, and their integration may unravel additional referential integrity problems.

Various steps will be taken to address the referential integrity of the database system. For instance, the database will be adjusted to ensure that a foreign key has a matching primary key or null. This constraint will be specified between two tables: the parent and the child. It will be adjusted to maintain the correspondence between rows in these tables. This will imply that the reference from a row in one table to another must be valid. To ensure that no orphan records exist, referential integrity will be enforced. An orphan record is one whose foreign key value is not found in the corresponding entity, the entity where the primary key is located. The referential integrity constraint states that the Order table’s customer ID must match a valid Customer ID in that Customer table.

d. Normalizing the Data Table to Third Normal Form Normalized Customer Table

Normalized Product Table

Normalized Orders Table

Customer ID

Amount

e. Entity Relationship Diagram

f. Explanation of the Relationship

The entity-attribute definitions only show the essential meanings of real-world items, which are the entities. In the real world, however, the items may have relationships to each other. For instance, a product is provided by the organization for a customer. The association or relationship between the entities relates data items to each other in a meaningful manner (Panneerselvam, 2019). This data relationship is often captured by the database schema or model if the resulting database is to be a reasonable depiction of the real-world entities that it models. The link between two or more entities is defined by the existence of data association.

Various forms of relationships are present in the above entity relationship diagram. For instance, the Order entity is related to the Product entity in many ways. The Order entity has attributes for the Product entity. Thus, these relationships are employed to support the database in conceptualizing how data are to be used within the database. Usually, the name of the relationship is an active verb. For instance, if a customer orders a product, the relationship between Customer and Product is the verb “order.” Similarly, if a Customer pays for a product, then the relationship between Customer and Payment is the verb “pays.”

g. Dataflow Diagram

User

Seller
Customer

Payment
Order Details
Category
Product
Orders
Shipment Details
h. Explaining the Rationale Behind the DFD
A dataflow diagram is a depiction of any procedure or system’s flow of information. It maps out the processes or system’s data flow, thereby helping systems administrators understand the processes or systems. Moreover, it can guide the process of implementing new processes or systems. The two types of DFDs include logical and physical. The former displays the theoretical processes of moving information via a system, where the data stems from, including how to changes and where it ends up (Chopra, 2018). The latter, on the other hand, indicates the practical processes of moving data through a system. In this DFD, for example, the Customer is the source of information. The data store and the payment system are examples of destinations of the information.
i. Sample Queries

Query 1
SELECT * FROM Customers WHERE Last_Name=’Smith’;
The Result of the query is the following table:
+———+———–+————+
| Cust_No | Last_Name | First_Name |
+———+———–+————+
| 1001 | Smith | John |
| 2039 | Smith | David |
| 2098 | Smith | Matthew |
+———+———–+————+
3 rows in set (0.05 sec)
Query 2
SELECT Cust_No, First_Name FROM Customers WHERE Last_Name=’Smith’;
Result of the query:
+————+————+
| First_Name | Nickname |
+————+————+
| Ben | Brainiac |
| Glen | Peabrain |
| Steven | Nobrainer |
+————+————+
j. At Least Two Screen Layouts

Layout 1

Layout 2

References
Angrish, A., Starly, B., Lee, Y. S., & Cohen, P. H. (2017). A flexible data schema and system
architecture for the virtualization of manufacturing machines (VMM). Journal of

Manufacturing Systems, 45, 236-247.
Chopra, R. (2018). Database Management System (DBMS) A Practical Approach. S. Chand
Publishing.
Panneerselvam, R. (2019). Database Management Systems. PHI Learning Pvt. Ltd.
Wei, Z., Leck, U., & Link, S. (2019). Entity integrity, referential integrity, and query
optimization with embedded uniqueness constraints. In 2019 IEEE 35th International

Conference on Data Engineering (ICDE) (pp. 1694-1697). IEEE.