Week 1 - Data modeling

Please use Canvas to return the assignments: https://ucsb.instructure.com/courses/19301/assignments/224311

Create a table definition for the Snow_survey table that is maximally expressive, that is, that captures as much of the semantics and characteristics of the data using SQL’s data definition language as is possible.

In the class data GitHub repository, week 1 directory you will find the table described in the metadata (consult 01_ASDN_Readme.txt) and the data can be found in ASDN_Snow_survey.csv. You will want to look at the values that occur in the data using a tool like R, Python, or OpenRefine.

Please consider:

• the data types of columns (pick from TEXT, REAL, INTEGER, DATE for this exercise)
• if the table has a primary key and what it might be
• any foreign key(s)
• whether NULL values are allowed
• uniqueness constraints, on individual columns and across columns
• other column value constraints, again, on individual columns and across columns

You may (or may not) want to take advantage of the Species, Site, Color_band_code, and Personnel supporting tables. These are also documented in the metadata, and SQL table definitions for them have already been created and are included below.

Please express your table definition in SQL, but don’t worry about getting the SQL syntax exactly correct. This assignment is just a thought exercise. If you do want to try to write correct SQL, though, your may find it helpful to consult the DuckDB CREATE TABLE documentation.

Finally, please provide some explanation for why you made the choices you did, and any questions or uncertainties you have. Don’t write an essay! Bullet points are sufficient. But do please explain your thought process.

Appendix

CREATE TABLE Species (
    Code TEXT PRIMARY KEY,
    Common_name TEXT UNIQUE NOT NULL,
    Scientific_name TEXT,
    Relevance TEXT
);

CREATE TABLE Site (
    Code TEXT PRIMARY KEY,
    Site_name TEXT UNIQUE NOT NULL,
    Location TEXT NOT NULL,
    Latitude REAL NOT NULL CHECK (Latitude BETWEEN -90 AND 90),
    Longitude REAL NOT NULL CHECK (Longitude BETWEEN -180 AND 180),
    "Total_Study_Plot_Area_(ha)" REAL NOT NULL
        CHECK ("Total_Study_Plot_Area_(ha)" > 0),
    UNIQUE (Latitude, Longitude)
);

CREATE TABLE Color_band_code (
    Code TEXT PRIMARY KEY,
    Color TEXT NOT NULL UNIQUE
);

CREATE TABLE Personnel (
    Abbreviation TEXT PRIMARY KEY,
    Name TEXT NOT NULL UNIQUE
);