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
Figure 3
Database and Tables Tree
Figure 4
Customer Table and SQL Statement
Figure 5
Employee Table and SQL Statement
Figure 6
OrderTbl Table with SQL Statement
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.
