Experiential Learning & DBMS Lab Exam

The Warmup☀️

Before starting to read this, I want you to ask yourself a question. What is a foreign key? If you can answer that you are well read, otherwise I would recommend watching https://www.youtube.com/watch?v=hlGoQC332VM for the end-to-end understanding of this article.

The Starting Whistle🏁

Yesterday I had my DBMS Lab Exam and the question was to create two tables one employee table and one department table, each employee had a corresponding department(connected by a foreign key) and each department had an eemployee(connected to the employee table by a foreign key).

So in short both the tables(i.e. the employee and department) were connected by a foreign key and I managed to create both tables with the help of the following queries

 --Created the first table without the foreign key contraint
 --to department as the table wasn't created untill now.
 CREATE TABLE employee217108 (
 SSN INT PRIMARY KEY,
 name VARCHAR(50),
 Dno INT
 );

--Created the department table with foreign key contraint to 
--the employee table.
CREATE TABLE department217108 (
 Dno INT PRIMARY KEY,
 Dname VARCHAR(50),
 MgrSSN INT,
 CONSTRAINT CT1217108 FOREIGN KEY (MgrSSN) 
 REFERENCES employee217108(SSN)
);

--Altered the employee table to add foreign key restriction.
ALTER TABLE employee217108
ADD CONSTRAINT CT2217108 FOREIGN KEY (Dno)
REFERENCES department217108(Dno);

--I am such a genius 🧠

Half Time Whistle 🕛

The exam was for three hours and since I am a frontend developer who doesn't know the syntax of the ALTER command, by the time I figured this out. It was half-time, the only change here was that instead of break, Here I was called for VIVA

Now here the real fun begins,

The exam continued after the VIVA and I tried inserting a row to the employee table which is when the opponent(computer) brought this new player(error) in.

 Error Code: 1452. Cannot add or update a child row: 
 a foreign key constraint fails (`college`.`employee217108`,
 CONSTRAINT CT2217108 FOREIGN KEY (`Dno`)
 REFERENCES department217108 (`Dno`))

Tried inserting it into the department table as well, here we go again the same error.

That's when I understood the mistake, Instead of
CREATing Table >> CREATing Table >> CREATing Table >> Inserting

I should have gone for
CREATing Table >> INSERTing Values >> CREATing Table >> INSERTing

Final Whistle 💯

So what is the role of experiential learning in this story?

This creation and insertion of multiple tables with foreign key was taught as the last experiment in the lab(college), and since I was absent then. For the sake of getting things done, I copied aka inspired a nearby student's queries and submitted the record.

And a day before the exam, I only tried out table creating and inserting without a foreign key.

and Hence Chaos.

Wait a minute, I forget to tell the first score, So I ended up getting the Table Creation, Insertion and 1/5 Queries Verified.