Project

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

Student ID

Product ID

Amount

Normalized Product Table

Product ID

Order ID

Customer ID

Weight

Normalized Orders Table

Orders ID

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.