Shaping data into information involves a database. Below is a basic tutorial with SQL script images and a data model. It is a step toward shaping data in information systems. Today, we will discuss the basic building blocks of a database management system.
DATABASE MANAGEMENT SYSTEMS
What is a Database?
A database is a collection of organized data and stored in a computer system. It is a central location that stores data in a structured format, allowing it to be easily accessed, managed, and updated. Databases are used in many applications, from simple contact lists to complex enterprise systems.
What is SQL?
SQL (Structured Query Language) is a programming language that manages data stored in a database. It is the standard language for communicating with relational databases, such as PostgreSQL®, MySQL, and Oracle. SQL allows users to create, read, update, and delete data in a database. It performs tasks such as querying, inserting, updating, and deleting data from a database. SQL is a powerful tool for managing data and is essential for anyone working with databases.
How to Use SQL
You need a database management system (DBMS) and a graphical user interface (GUI) to use SQL. A DBMS software application allows users to interact with a database. A GUI is a visual interface that allows users to interact with the DBMS. Many DBMS options are available, such as PostgreSQL®, MySQL, and Oracle. We will use PostgreSQL® and pgAdmin for this tutorial as DBMS and GUI, respectively.
SQL Basics
SQL statements are used to interact with a database. The most common SQL statements are:
SELECT: used to retrieve data from a database
INSERT: used to insert data into a table
UPDATE: used to update data in a table
DELETE: used to delete data from a table
CREATE: used to create a new database, table, or view
DROP: used to delete a database, table, or view
ALTER: used to modify the structure of a table or view
SQL statements are written in a specific syntax and are case-insensitive. They are terminated with a semicolon (;). SQL statements can be written in a text editor, executed in the DBMS, or written directly in the DBMS's GUI.
POSTGRESQL TUTORIAL
In this tutorial, we will create a database, create tables, insert data, query data, delete tables, delete databases, update data, and delete data. We will also make a data model to visualize the database structure.
Creating a Database
Hardware Requirements
You will need a computer with at least 2GB of RAM and 1GB of free disk space to create a database. For the most efficient operations, it is recommended that the hardware meets or exceeds the following requirements:
4GB of RAM
2GB of free disk space
Dual-core processor
Software Requirements
Database management system (DBMS) creation is straightforward when software is installed correctly on adequate hardware. You will need to install PostgreSQL® and pgAdmin on your computer. PostgreSQL® is an open-source relational database management system, and pgAdmin is a popular GUI for managing PostgreSQL® databases. PostgreSQL® and pgAdmin are free and can be downloaded from their websites.
Installing PostgreSQL® and pgAdmin
To install PostgreSQL® and pgAdmin, follow these steps:
Download PostgreSQL® from https://www.postgresql.org/download/.
Run the installer and follow the prompts to install PostgreSQL®.
Download pgAdmin from https://www.pgadmin.org/download/.
Run the installer and follow the prompts to install pgAdmin.
Connecting to PostgreSQL®
To connect to PostgreSQL®, open pgAdmin and click the Add New Server button. Give the server a name and enter the following information:
Host: localhost
Port: 5432
Maintenance database: postgres
Username: postgres
Password: (the password you set during the installation process)
Click Save, and you will be connected to the PostgreSQL® server. You can now create databases and tables and perform other tasks using SQL.
Creating a Database in PostgreSQL®
To create a database in PostgreSQL®, open pgAdmin and connect to the PostgreSQL server. Right-click on the Databases folder and select Create > Database. Give the database a name and click Save. The new database will appear in the Databases folder.
Alternatively, you can create a database in PostgreSQL® with an SQL statement. Below is a sample statement for creating a database in PostgreSQL®. The GUI makes database creation more manageable, but knowing how to create a database with SQL is good.
CREATE DATABASE database_name;
with
OWNER = postgres
ENCODING = 'English_United States.1252'
LC_COLLATE = 'English_United States.1252'
TABLESPACE = pg_default
CONNECTION LIMIT = -1;The statement above will create a new database in PostgreSQL®.
Creating Tables
Tables are used to store data in a database. To create a table in PostgreSQL®:
Right-click on the Tables folder in the database and select Create > Table.
Give the table a name and add the columns and data types.
Click Save, and the new table will appear in the Tables folder.
Alternatively, you can create the tables using SQL statements. For example, to create a customer table with the columns id, first_name, last_name, email, and country, you would enter the following SQL statement:
CREATE TABLE customer (
id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100),
country VARCHAR(50));The SQL statement above will create a customer table with the specified columns and data types. You can also use SQL to add constraints, such as primary and foreign keys, and check constraints in your tables. Constraints ensure data integrity and help maintain the quality of your data.
Inserting Data
To insert data into a table:
Right-click on the table and select View/Edit Data > All Rows.
Click on the Insert Row button and enter the data into the table.
Click Save, and the data will be inserted into the table.
Data can also be inserted using SQL statements. SQL Statements are helpful when inserting large amounts of data into a table. For example, to insert a new customer into the customer table, you would enter the following SQL statement:
INSERT INTO customer (first_name, last_name, email, country)
VALUES ('John', 'Doe', 'johndoe@email.com', 'United States');The SQL statement above will insert a new customer into the customer table. You can also use SQL to insert data into multiple tables at once, making managing large amounts of data easier.
Querying Data
To query data from a table, right-click on the table and select Query Tool. A new window will open where you can enter your SQL query. For example, to query all the data from the customer table, you would enter the following query:
SELECT * FROM customer;The SQL statement above will return all the data from the customer table. You can also use SQL to filter, sort, and group data. For example, to query all the customers from the United States, you would enter the following query:
SELECT * FROM customer WHERE country = 'United States';The SQL statement above will return all the customers from the United States. You can also use SQL to join data from multiple tables, perform calculations, and create new tables. Joining various tables and performing calculations are more advanced techniques. SQL is a powerful tool for managing data and is essential for anyone working with databases.
Deleting Tables
To delete a table, right-click on the table and select Delete/Drop. This will permanently delete the table and all its data. Use this with caution as it cannot be undone.
Alternatively, you can delete a table using SQL statements. For example, to delete the customer table, you would enter the following SQL statement:
DROP TABLE customer;The SQL statement above will permanently delete the customer table and all its data.
Deleting Databases
To delete a table, right-click on the database and select Delete/Drop. Caution: Delete/Drop databases permanently delete the table and all its data. Use this with caution, as it cannot be undone.
Alternatively, you can delete a database using SQL statements. For example, to delete the customer database, you would enter the following SQL statement:
DROP DATABASE customer;The SQL statement above will permanently delete the customer table and all its data.
Updating Data
To update data in a table, right-click on the table and select View/Edit Data > All Rows. Click on the row you want to update, make the changes, and click Save.
Alternatively, you can update data using SQL statements. For example, to update the email address of a customer, you would enter the following SQL statement:
UPDATE customer
SET email = 'newemail@email.com'
WHERE id = 1;The SQL statement above will update the customer's email address with an ID of 1.
Deleting Data
To delete data from a table, right-click on the table and select View/Edit Data > All Rows. Click on the row you want to delete and click the Delete Row button.
Alternatively, you can delete data using SQL statements. For example, to delete a customer with an id of 1, you would enter the following SQL statement:
DELETE FROM customer
WHERE id = 1;The above SQL statement will delete the customer with an ID of 1.
Data Model
A data model is a visual representation of the data in a database. It shows the relationships between tables and the attributes of each table. A data model is essential for understanding the structure of a database and is used to design and maintain databases. Many tools, such as Lucidchart and ER/Studio, are available to create data models. For this tutorial, we will use Lucidchart to create a data model.
To create a data model in Lucidchart, follow these steps:
Go to https://www.lucidchart.com/ and sign up for a free account.
Click on Create a New Document and select Entity Relationship Diagram.
Drag and drop the Entity, Attribute, and Relationship shape onto the canvas.
Double-click on the shapes to add text and customize the colors and styles.
Connect the shapes with lines to show the relationships between them.
Save the data model and export it as an image or PDF.
Below is a basic data model for a customer database. It illustrates two parent tables, a child table, the primary keys, the foreign keys, the data fields, and their corresponding data types.
CONCLUSION
I hope you enjoyed this brief tutorial. The basic building blocks of a database are essential to shaping data. The more you understand databases, the better you can manage and analyze data. You can create and maintain databases with the right tools and knowledge to help your business thrive. Happy data modeling!
ACKOWLEDGEMENTS
Sondra Hoffman wrote this blog post in collaboration with AI technology. The AI large language model developed by OpenAI, called ChatGPT, was used to assist updating the post.
Any AI-generated text has been reviewed, edited, and approved by Sondra Hoffman, who takes full responsibility for the content of this publication.
ABOUT THE AUTHOR
Sondra Hoffman is a seasoned MIS professional with over ten years of experience in strategic planning, implementation, and optimization of MIS solutions. She is passionate about helping small businesses thrive through technology and data management. Connect with her on LinkedIn to learn more about her professional background.
CONTACT ME
Contact me today for a consultation on improving your existing information system. Cost-effective approaches are available to drive your business with data.
Email: info@sondrahoffman.online
Phone: (707) 862-3937
LinkedIn: https://www.linkedin.com/in/shoffman4/
Request an Appointment Online:
REFERENCES
Brooks, C. (2022, February 1). What Is SQL? Business News Daily. https://www.businessnewsdaily.com/5804-what-is-sql.html
Lucidchart. (2022). [Web application for diagramming]. Lucid Software, Inc. https://www.lucidchart.com/pages/
pgAdmin (4.28). (2020). [PostgreSQL open-source graphical user interface for database administration]. The pgAdmin Development Team. https://www.pgadmin.org/
PostgreSQL (Version 12). (2022). [Client/server open-source database program]. PostgreSQL Global Development Group. https://www.postgresql.org/
Comments