labs / tiddlers / content / labs / lab03 / _Labs_03_Part 1_ Building a Relational

Today’s Learning Outcomes

  • Understanding ERDs
  • Connecting to PosgreSQL database using DBeaver DBMS
  • SQL CREATE TABLE statements
  • SQL data types
  • Default Column Values
  • Mandatory columns
  • Populate and query tables


    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.


Scenario and Recap

Today’s lab continues from last time, 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 you encountered earlier with ad-hoc management of data, such as duplicated or missing data, inconsistencies in how values are recorded, and fragmented “islands of information”.

Using sample data (Excel spreadsheets and a sample form), information from members of the organisation, and your wits, you analysed the business requirements and designed the ERD shown in the figure. 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.