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.
Note that details of the Measurement and Measurement Type entities are missing, as these will be completed in your assignment work.
This lab focuses on Scientist, Sample, and Site, and getting the structure, naming, and data types right. We will leave integrity constraints (primary and foreign keys, and additional business rules) for next time, apart from the basic NOT NULL.