DATABASE QUERIES

ok so ive created a database for a website that handles students swapping university classes

ER DIAGRAM / QUERIES SHOWN BELOW

"user" being the student

"class" being the classes

"has" being the classes that the student currently has

"wants" being the classes that that the student doesnt have but wants

CREATE TABLE Class

(

ClassName varchar(255),

Professor_Name varchar(255),

Start_Time varchar(10),

End_Time varchar(10),

Course_Number varchar(20),

Section_Number varchar(20),

Days varchar(10),

PRIMARY KEY(Course_Number,Section_Number));

Now before creating table 'has' and 'wants' make sure user table is already created, as both these tables will take the reference from user table.

CREATE TABLE User

(

Email VARCHAR(255),

first_name VARCHAR(255),

last_name VARCHAR(255),

password VARCHAR(255),

PRIMARY KEY (Email)

);

CREATE TABLE Has

(

Email VARCHAR(255),

Course_Number VARCHAR(20),

Section_Number VARCHAR(20),

PRIMARY KEY (Email, Course_Number, Section_Number),

FOREIGN KEY (Email) REFERENCE Userr (Email),

FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)

);

CREATE TABLE Wants

(

Email VARCHAR(255),

Course_Number VARCHAR(20),

Section_Number VARCHAR(20),

PRIMARY KEY (Email, Course_Number, Section_Number),

FOREIGN KEY (Email) REFERENCE Userr (Email),

FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)

);

ive successfully created all the tables and added multiple classes to my class table, now im trying to add classes to the students "wants" and "has"

1. once ive created that user how would i add a class to the users "has" and or "wants" table

lets say for example i wanted to add the following class to john smiths "has" table

ClassName Math

Professor_Name wendy brown

Start_Time 10:00 AM

End_Time 11:00 AM

Course_Number CDA 3103

Section_Number U01-C

Days Mo-We

Ive tried

INSERT INTO Has

VALUES ('johnsmith@school.edu', 'CDA 3103', 'U01-C');

but im getting the error: Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`spr17_fkais001`.`SEHAS`, CONSTRAINT `SEHAS_ibfk_1` FOREIGN KEY (`Email`) REFERENCES `SEUSER` (`Email`)) ?? does anybody know why this is? and how I can start adding classes to wants and has?

can someone help me write a query to do this also please, im just looking for an example so i can see how to do it myself for the website.

please help

Professor Name Password Last Name Start Time Has Email End Time O,M) (O,M) Class User First Name (O,N) Wants Days Course_Number) Section Number