# Database

## File contents

* create.sql -- drops and creates all tables
* alter.sql -- adds all primary and foreign keys
* eer_model.png -- EER diagram from [draw.io](https://www.drawio.com/)
* rel_model.pgerd -- relational ERD (pgAdmin)

## Building the database

Define environment variables:

``` sh
export PGHOST=data.cs.jmu.edu
export PGDATABASE=absent
export PGUSER=jmu_username
export PGPASSWORD=student_number
```

Run the following commands:

``` sh
psql < create.sql
psql < alter.sql
```

## Changes to EER model

While mapping EER to relational, we made the following changes:

* We forgot that vets also have a unique email address, so we added that column.

* We decided not to use ApptID to identify an appointment, because DateTime and Room was sufficient. So we removed ApptID.

* We added Breed to the Pet table, so that we would have more information than just the Pet's name.

## Relational model notes

* In the `vet_phone` table, we added a `sort` column so that phone numbers could be ordered.

* We decided that the `empid` (vet) for an appointment could be `NULL`, so that appointments could be scheduled without having a specific vet assigned at tht time.