Skip to content

create_database.sql⚓︎

Overview⚓︎

The create_database.sql file is a SQL script designed for setting up a database structure for a guestbook application. Its primary purpose is to create a new database called guestbookdb and define the schema for a table named guest_entry. This table is intended to store entries made by guests, including their names, email addresses, and comments, along with a timestamp for when the entry was created. The script is used at the beginning of the project to set up the initial database structure and can be reused whenever there is a need to reset the database to its original state.

Table of Contents⚓︎

  1. Prerequisites
  2. Usage
  3. Methods
  4. Useful details

Prerequisites⚓︎

Before running this SQL script, the following prerequisites must be met: - Have a PostgreSQL server installed and running. - Have a user account with sufficient privileges to create databases and tables. - Have a PostgreSQL client tool or command-line utility (e.g., psql) to execute the SQL script.

Usage⚓︎

To use this script, follow these steps: 1. Open your PostgreSQL client tool or command-line utility. 2. Connect to your PostgreSQL server using the appropriate credentials. 3. Execute the SQL script either by copying and pasting the commands into the client or by running the script file directly if your client supports this operation.

Here's an example of how you might run the script using the psql command-line utility: 

psql -h hostname -U username -f create_database.sql
Replace hostname with the address of your PostgreSQL server and username with the username of your PostgreSQL account.

Methods⚓︎

This SQL script does not contain methods in the traditional programming sense but consists of SQL statements which perform the following actions: - CREATE DATABASE: Creates a new database named guestbookdb. - \c guestbookdb: Connects to the guestbookdb database. - DROP TABLE IF EXISTS: Removes the guest_entry table if it already exists. - CREATE TABLE: Creates a new guest_entry table with the following columns: - id: An auto-incrementing integer that serves as the primary key. - guest_name: A variable character string to store the guest's name. - guest_email: A variable character string to store the guest's email address, which can be null. - guest_comment: A variable character string to store the guest's comment. - create_dt: A timestamp with a default value of the current date and time when the entry is created.

Useful details⚓︎

This script is typically executed only once at the start of the project to initialize the database, or it can be used to reset the database during development. It is important to note that running this script will result in the loss of any existing data in the guest_entry table due to the DROP TABLE IF EXISTS statement. Therefore, it should be used with caution in a production environment or any situation where data preservation is necessary.