# B. McVey-Pankratz
# Spring 2026 for Dylan Elsing's DATA 400 Capstone project
# Created tables for his database, which is stored at .knight.domains

# WARNING: These are not entirely accurate .. datatypes may have been altered as well ... sorry I did not keep an accurate record of changes ... most of this is correct


CREATE TABLE Properties ( zpid VARCHAR(25) NOT NULL UNIQUE streetAddress VARCHAR(30) NOT NULL, 
  city VARCHAR(25 NOT NULL), zipcode CHAR(5) NOT NULL,
  county VARCHAR(25), latitude DECIMAL NOT NULL, longitude DECIMAL NOT NULL, 
  yearBuilt INTEGER NOT NULL, livingArea INTEGER NOT NULL, 
  lotSize INTEGER NOT NULL,   bedrooms INTEGER NOT NULL, 
  bathrooms INTEGER NOT NULL,
  walk_score INTEGER, bike_score INTEGER, transit_score INTEGER);
  

CREATE TABLE SALE (Sale_id INTEGER AUTO_INCREMENT PRIMARY KEY, zpid VARCHAR(25) NOT NULL UNIQUE, 
price INTEGER NOT NULL, dateSold DATE NOT NULL);



CREATE TABLE School (schoolID VARCHAR(255) NOT NULL UNIQUE, zpid VARCHAR(25) NOT NULL,
                     distance INTEGER, grades VARCHAR(25));

CREATE TABLE Features (feature_id INTEGER AUTO_INCREMENT PRIMARY KEY, 
             zpid VARCHAR(25) NOT NULL UNIQUE, has_garage BOOLEAN, has_basement BOOLEAN, has_public_water BOOLEAN, has_sewer BOOLEAN);
	
 




