Rachael Herman

Relational Data Model for Order Entry Table (SQL Part 1)

Relational Data Model – Order Entry Table Creation

February 27, 2022

For this SQL project, I used the Customer, Order, and Employee tables provided in Database design, application development and administration. The focus for this project was to create the database and tables and then demonstrate the appropriate connections between these tables in an entity-relationship diagram (ERD). Below are images of the process using the GUI, pgAdmin.

Primary Challenges and Solutions

With regard to the project, the biggest challenge I faced was due to a user error. Following the first database creation, I did a test run by writing the SQL statement for the customer table alone. It worked, but I did not know how to make changes to the table. Also, new tables were not showing up through the psql shell or Query Tool. This was because I was not properly refreshing the database.

At this point, I thought I’d broken something, so I decided to start from scratch; however, the database was active, so it would not allow deletion. I found instructions on how to deactivate it through services.msc, the shortcut to Windows OS services list. I then deleted the database and tried again. This time, I was able to create both the database and tables without a problem using the Query Tool. Following this, I discovered I could just delete a table, rather than the entire database by right clicking on the table in the tree.  Figures 1-6 below demonstrate successful completion of this project.

Figure 1

Customer, Employee, and Order Tables

Figure 2

The Original SQL Statements

Note: This is a screenshot to show the SQL statements for creating the tables using appropriate data types and Primary and Foreign keys.

Figure 3

Database and Tables Tree

Note: In the left menu tree, I have included the database name, as well as extended the Schemas and Tables options to show successful creation of each. 

Figure 4

Customer Table and SQL Statement

Note: This image demonstrates the side-by-side view of the customer SQL statement and table.

Figure 5

Employee Table and SQL Statement

Note: This image demonstrates the side-by-side view of the employee SQL statement and table.

Figure 6

OrderTbl Table with SQL Statement

Note: This image demonstrates the side-by-side view of the Ordertbl SQL statement and table.

Figure 7 shows the ERD diagram, which was generated in the pgAdmin GUI. The boxes show the tables are in a public schema and lists the name of the table, the primary key, and the columns in those tables. The single line endpoint at employee.emp_no and customer.cust_no show the columns being referred to in the ordertbl table. The ordertbl table uses the three-line endpoint to show the columns that are referring to the employee and customer tables (pgAdmin Documentation, n.d.). According to Juba & Volkov (2017), “When a table inherits another table or tables, it is called a child table. The table that is inherited is a parent table” (pg. 269). The child table in this diagram is the ordertbl table, as it is referencing the parent customer and employee tables (Juba & Volkov, 2017; Mannino, 2019). The ERD diagram demonstrates successful connections in the SQL statements. 

Figure 7

The Entity-Relationship Diagram

Looking for SQL statements that add information to the tables? Click Here.

References

Juba, S., & Volkov, A. (2017). Learning PostgreSQL 10 (2nd ed.). Packt Publishing

Mannino, M. V. (2019). Database design, application development and administration, 7e. http://platform.virdocs.com/r/s/0/doc/592088/sp/176800379/mi/566374934?cfi=%2F4%2F4

pgAdmin 4 6.4 Documentation (n.d.) Creating or modifying a table: Table dialog. pgAdmin.