
CREATE TABLE "Allergy_Type" (
	"Allergy" VARCHAR(20), 
	"AllergyType" VARCHAR(20), 
	PRIMARY KEY ("Allergy")
)


CREATE TABLE "Has_Allergy" (
	"StuID" INTEGER, 
	"Allergy" VARCHAR(20), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Allergy") REFERENCES "Allergy_Type" ("Allergy")
)


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

CREATE TABLE "Allergy_Type" (
	"Allergy" VARCHAR(20), 
	"AllergyType" VARCHAR(20), 
	PRIMARY KEY ("Allergy")
)

/*
2 rows from Allergy_Type table:
Allergy	AllergyType
Eggs	food
Nuts	food
*/


CREATE TABLE "Has_Allergy" (
	"StuID" INTEGER, 
	"Allergy" VARCHAR(20), 
	FOREIGN KEY("StuID") REFERENCES "Student" ("StuID"), 
	FOREIGN KEY("Allergy") REFERENCES "Allergy_Type" ("Allergy")
)

/*
2 rows from Has_Allergy table:
StuID	Allergy
1001	Cat
1002	Shellfish
*/


CREATE TABLE "Student" (
	"StuID" INTEGER, 
	"LName" VARCHAR(12), 
	"Fname" VARCHAR(12), 
	"Age" INTEGER, 
	"Sex" VARCHAR(1), 
	"Major" INTEGER, 
	"Advisor" INTEGER, 
	city_code VARCHAR(3), 
	PRIMARY KEY ("StuID")
)

/*
2 rows from Student table:
StuID	LName	Fname	Age	Sex	Major	Advisor	city_code
1001	Smith	Linda	18	F	600	1121	BAL
1002	Kim	Tracy	19	F	600	7712	HKG
*/
Who advises student 1004?
SELECT Advisor FROM Student WHERE StuID  =  1004
[{'Advisor': 8423}]