Sarah Mullin - Coding Activities for Building Databases with SQL

Published in 2022 by The Rosen Publishing Group, Inc.

29 East 21st Street, New York, NY 10010

Copyright 2022 by The Rosen Publishing Group, Inc.

First Edition

All rights reserved. No part of this book may be reproduced in any form without permission in writing from the publisher, except by a reviewer.

Library of Congress Cataloging-in-Publication Data

Names: Mullin, Sarah, author.

Title: Coding activities for building databases with SQL / Sarah Mullin.

Description: First edition. | New York: Rosen Publishing, 2022. | Series: Code creator | Audience: Grades 712. |

Includes bibliographical references and index.

Identifiers: LCCN 2019011068| ISBN 9781725340992 (library bound) | ISBN 9781725340985 (pbk.)

Subjects: LCSH: SQL (Computer program language)Juvenile literature. | Database designJuvenile literature. | DatabasesJuvenile literature.

Classification: LCC QA76.73.S67 M85 2022 | DDC 005.75/6dc23

LC record available at https://lccn.loc.gov/2019011068

Manufactured in the United States of America

Some of the images in this book illustrate individuals who are models. The depictions do not imply actual situations or events.

CPSIA Compliance Information: Batch #CSRYA22. For further information contact Rosen Publishing, New York, New York at 1-800-237-9932.

Contents

Introduction

Data consists of facts that need to be processed to gain information about something. For instance, here are some facts: a man has an opened umbrella, a woman is wearing a rain jacket, puddles are on the ground, and the man and the woman are in the same location. Processing those facts, it is possible to gain the information that it is raining. Data is constantly being produced in the world, both by official agencies and regular, everyday people. When someone goes to the doctors office, nurses collect information about their health and store it in electronic health records. When someone shops online, the items on the site and the items they purchase become data. Data is everywhere.

How is all this data stored? A database allows someone to store data related to some topic in an organized way. It has to be well organized so that the database owners can use the data to make decisions and find patterns. Data can be stored in a variety of ways, ranging from very simple tables to complex database design. The way data is stored depends on many factors: the type of data it is, the size of the data, and how complex the data is. Data is commonly organized in rowscalled recordswith each column containing a piece of data for that record. The simplest database, a flat file database, is a single table that can store a limited amount of data. These are typically files that have one record per line and each column is separated by a delimiter. Delimiters can be commas, tabs, spaces, and many other characters. For instance, comma-separated value (CSV) files store data that is separated by commas.

However, data stored in separate files can create problems with inconsistent data formats, data that does not have any relationships across files, and data that does not have any rules or constraints. In addition to these problems, there is also big datahuge quantities of data of all types. What does someone do if they have a lot of data that cannot be put all in one table? Relational databases link multiple tables together using an index or key field. This allows users to easily find records from search criteria across the tables.

Dataand lots of itis everywhere; it can be stored in an organized way by designing a database.

Relational databases require a database management system (DBMS) to manage and access data. Data in these types of databases tends to be structured, meaning that data can be defined as data types that are easily searchable. Object-relational databases are a combination of object-orientated databases and relational databases. What about data that is not easily searchable, like social media posts, text messages, music, images, or videos? This data is unstructured and typically stored in NoSQL or non-relational databases.

However, the most beginner-friendly format is structured data. What does that look like? Structured data consists of numerical information, data that can be organized on a scale, or data that can be organized in categories. Data can be entered by machines; automatically generated by computer processes, applications, or other mechanisms without the help of humans; or entered by humans. Machine-generated data is used in mobile cell phone logs, manufacturing field equipment performance, and medical devices that collect health information. Human-entered data can be entered manually and includes things like answers on quizzes, where someone lives, and birthdates.

Database management systems offer a way to efficiently manage, store, and retrieve data from databases.

Databases consist of data in two forms: records and metadata. The latter is data that describes how the database is set up and the relationships among the data. A DBMS is responsible for creating, managing, and retrieving information, as well as acting to manage and interact with a group of databases. A DBMS also has data in the form of metadata and operational data, which includes data dictionaries, relationships to other records and objects, and administrative information. DBMS software uses database languages to access, modify, store, and retrieve data. One such database language is Structured Query Language (SQL), which will be used in these exercises.

SQL is a language that allows users to create databases, add new data, and query data from them. It is composed of statements that perform certain types of data manipulation and administration; it allows users to create objects and define them, and transform and modify the data. SQL is used to communicate with relational databasesyou tell SQL what you want and how to get what you want from the database. These activities will use PostgreSQL, an objectrelational database management system, and the SQL database language.

In the following exercises, your goal is to help save the world by designing a database for Mithrasa secret government agencyand querying the database for Jupiter, the head of the agency. Jupiter has asked you to keep a record of the missions, supplies, money, and agents that will allow anyone with clearance in Mithras to easily query and gain data from the database.

SQL allows users to store, query, and manipulate data in an effective and organized way. This is highly important in commercial applications.

To get started with database management, head to the PostgreSQL site (https://www.postgresql.org/download) and download the binary packages. The download option will depend on the operating system on your computer: Windows, MacOS, or Linux. Select the appropriate system and follow the instructions to get the download started. Once PostgreSQL is downloaded and installed, start the interactive terminal programcalled psqlwhich allows users to run SQL commands and create databases. Open a terminal window and type: