DATABASE QUERIES ok so ive created a database for a website that handles student
ID: 3741471 • Letter: D
Question
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 NumberExplanation / Answer
Answer)
Correct create query is as below:
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));
CREATE TABLE User
(
Email VARCHAR(255),
first_name VARCHAR(255),
last_name VARCHAR(255),
password1 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) REFERENCES User (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) REFERENCES User (Email),
FOREIGN KEY (Course_Number, Section_Number) REFERENCES Class (Course_Number, Section_Number)
);
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
Answer)
Your query failed as the foreign key constraint failed.
Insert statements into respective tables are as below:
INSERT INTO Class
VALUES ('Math', 'wendy brown', '10:00 AM', '11:00 AM','CDA 3103', 'U01-C', 'Mo-We');
Class record is inserted in the above Class table.
INSERT INTO Has
VALUES ('johnsmith@school.edu', 'CDA 3103', 'U01-C');
This is the much information needed in the Has table.
select * from Has;
Output:
Email Course_Number Section_Number
johnsmith@school.edu CDA 3103 U01-C
INSERT INTO Wants
VALUES ('johnsmith@school.edu', 'CDA 3103', 'U01-C');
This is the much information needed in the Wants table.
select * from Wants;
Output:
Email Course_Number Section_Number
johnsmith@school.edu CDA 3103 U01-C
Execute another query before all of this:
INSERT INTO User
VALUES ('johnsmith@school.edu', 'john', 'smith', 'star');
select * from User;
Output:
Email first_name last_name password1
johnsmith@school.edu john smith star
I hope this solved your query.
/*Hit like if you like my asnwer*/
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.