CSE 340: Web Backend Development

W02 Learning Activity: Creating Tables and Inserting Data

Overview

In this activity, you will create the necessary tables for your PostgreSQL database and insert sample data into those tables. This will help you understand how to structure your database and populate it with initial data for testing and development purposes.

Preparation Material

Before you begin this activity, make sure you have completed the previous activity: Creating and Connecting to your Database. You should have a PostgreSQL database set up and be able to connect to it using PGAdmin.

Activity Instructions

As explained in previous activities, during this course you will be building a web application to track service organizations and their available service projects. For this part of the activity, you will create the table for Organizations and add data to it. Then, in future activities, you will add tables for Service Projects and Categories.

Determine the Database Details

In this application, Organizations should have the following information:

Think for a minute about the data types you will need for these and any constraints that are necessary.

NOT NULL Constraints

For many of these fields, you might argue that this could be left empty, but it is often easier to start with tighter constraints and force the data to adhere to it, than it is to try to tighten them later.

At a company, these decisions should be made when the requirements are determined for the project, so you should not have to make this decision when you create the tables. But from a database perspective it is usually preferred to have as tight of constraints as possible to keep the data clean and consistent.

Create the table

Using the information above, create the SQL statement needed to create the organization table in your database. You can use the Query Tool in PGAdmin to execute the SQL statement.

  1. Open PGAdmin and connect to your PostgreSQL database.
  2. Open the Query Tool by right-clicking on your database and selecting Query Tool.
  3. Enter the SQL statement to create the organization table based on the details provided above.

    4. Remember: AI can be helpful

    Remember that AI can be very helpful in creating the exact SQL statement you need to create a table like this. There are two important things to remember when using AI like this:

    1. You should first design the table and determine the columns, data types, and constraints you need. So that you can supply a specific detailed list to AI.
    2. You should verify the SQL statement that AI generates. If there are any parts of it that you do not understand, you should ask the AI to explain them, or look up the syntax directly yourself.
    Example SQL Statement (click to expand) 
    
-- ========================================
-- Organization Table
-- ========================================
CREATE TABLE organization (
    organization_id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT NOT NULL,
    contact_email VARCHAR(255) NOT NULL,
    logo_filename VARCHAR(255) NOT NULL
);
  4. Execute the SQL statement by clicking the Execute button (the triangle, play icon) or pressing F5.
  5. Verify that the table has been created successfully by checking the Tables section under your database in PGAdmin. (Expand your database name, then Schemas, then public, then Tables. You may need to right-click Tables and select Refresh.)
  6. Save this SQL query into a file in your project. In your src directory create a subdirectory named, models. Then, in the src/models directory, create a new file named setup.sql . Copy and paste your Create Table query into the src/models/setup.sql to save it for use in the future.

Important: Save this SQL statement

In addition to running this SQL statement in pgAdmin, make sure to save it in the setup.sql file. This is important in case you ever need to re-create your database in a new development environment.

This setup.sql files will be very important in the case of using a database at Render.com, because the free tier will require you to delete and recreate your database after one month.

In addition, when your project is graded, the setup.sql file will be used to evaluate your database design.

Insert Sample Data

Now that you have created the organization table, you will insert some sample data into it. This will help you test your database and ensure that the table is functioning correctly.

For this project, you should enter data for the following Organizations. Previously, you hardcoded information about these companies into your HTML/EJS pages, but now you will enter it into your database so these pages can be populated based on any data in the database, including new organizations that may be added in the future.

Your database should contain information on the following organizations:

Create the SQL insert statements you need to insert these organizations by typing them yourself or using AI to assist you.

Use the Query Tool in PGAdmin to execute the SQL insert statements to add the sample data to the organization table.

  1. Open PGAdmin and connect to your PostgreSQL database.
  2. Open the Query Tool by right-clicking on your database and selecting Query Tool.
  3. Enter the SQL insert statements to add the sample data for the three organizations listed above. (Again, remember that AI can be very helpful here.)
    Example SQL Statements to insert sample data (click to expand) 
    
-- ========================================
-- Insert sample data: Organizations
-- ========================================
INSERT INTO organization (name, description, contact_email, logo_filename)
VALUES
('BrightFuture Builders', 'A nonprofit focused on improving community infrastructure through sustainable construction projects.', 'info@brightfuturebuilders.org', 'brightfuture-logo.png'),
('GreenHarvest Growers', 'An urban farming collective promoting food sustainability and education in local neighborhoods.', 'contact@greenharvest.org', 'greenharvest-logo.png'),
('UnityServe Volunteers', 'A volunteer coordination group supporting local charities and service initiatives.', 'hello@unityserve.org', 'unityserve-logo.png');
  4. Execute the SQL statements by clicking the Execute button or pressing F5.
  5. Add these SQL statements to your setup.sql file.

Important: Save these SQL statements

Remember to save these SQL statements into your setup.sql file so that you will have them in the future when you need to re-create your database.

Verify the Data Insertion

After inserting the sample data, you should verify that the data has been inserted correctly into the organization table.

  1. In PGAdmin, open the Query Tool for your database.
  2. Enter the following SQL query to retrieve all records from the organization table: 
    SELECT * FROM organization;
  3. Execute the SQL query by clicking the "Execute/Refresh" button (lightning bolt icon) or pressing F5.
  4. Review the results to ensure that all three organizations have been inserted correctly with the appropriate details.

Optional Video Walkthrough

While the following video may be helpful to see the steps in action, make sure to walk through the written instructions directly because there may be important steps that are not covered in the video.

Direct link: Creating Tables and Inserting Data

Next Step

Complete the other Week 02 Learning Activities

After you have completed all the learning activities for this lesson, return to Canvas to submit a quiz.

Other Links: