Today’s Learning Outcomes

• Understanding ERDs
• Connecting to a PosgreSQL server using DBeaver
• Creating a schema using SQL CREATE TABLE statements
• SQL data types
• Default Column Values
• Mandatory columns
• Populate and query tables

Introduction

Over the next few labs, you will learn how to create and use a database using the SQL language a PostgreSQL database and the DBeaver database management system. Databases form the foundation of most information systems, and they provide a powerful and consistent way to organise and manage large volumes of shared data.

{{/Labs/03/Images/ERD_full.svg}}

Scenario and Recap

Today’s lab continues on from the last lab, taking the ERD for the water quality scenario and beginning to implement it as a real database. The database approach should avoid many of the problems that could occur with ad-hoc management of data, such as duplicated or missing data, inconsistencies in how values are recorded, and fragmented “islands of information”.

In the last lab you used sample data (Excel spreadsheets), a sample form, information from members of the organisation, and your wits to analyse the business requirements and created the ERD shown in the figure (this is the solution to the last lab). The next step is to map the ERD to a set of SQL CREATE TABLE statements.

This lab focuses on Scientist, Sample, and Site, and getting the structure, naming, and data types right. Then you will add integrity constraints (primary and foreign keys, NOT NULL for mandatory columns, and additional business rules).

We will be creating the Measurement and Measurement_Type entities later.