Question

Regrettably, the company you work for did not hire a student from this class to design their database. You are given the following table definitions. You (hopefully) take one look and are amazed at how bad the design decisions are in these two tables alone. Amazingly the database seems to be working OK.

Propose a new way to implement these tables, list and explain the changes you make including why the old way was bad and your new change is better. (If you are not finding at least 5 things, look harder)

You should provide new CREATE statements and a written description of the original problems and how you fixed them. You are free to add additional tables, rename, remove or add columns. Points will be divided equally between having a new set of CREATE statements that are problem free and indentifying at least 5 problems in the original CREATE statements.

CREATE TABLE classes(
class_name CHAR(10) PRIMARY KEY,  
enrollment int -- Number of students currently enrolled in the class  
)engine=innodb;    

CREATE TABLE students(  
full_name CHAR(10) PRIMARY KEY,  
age INT,  
birthday VARCHAR(10),  
class1 CHAR(10),  
class2 CHAR(10),  
class3 CHAR(10),  
class4 CHAR(10),  
FOREIGN KEY (class1) REFERENCES classes(class_name) ON DELETE CASCADE,  
FOREIGN KEY (class2) REFERENCES classes(class_name) ON DELETE CASCADE,  
FOREIGN KEY (class3) REFERENCES classes(class_name) ON DELETE CASCADE  
)engine=innodb;

Answer 1

Hi,
the problem with the current approach is that there is a lot of redundancy if you see in the students table,
every student has 4 rows for each class they take which is redundant.
we can eliminate this by splitting the tables like this

CREATE TABLE classes(
class_name CHAR(10) PRIMARY KEY,  
enrollment int -- Number of students currently enrolled in the class  
)engine=innodb;   

-- classes table can remain same

CREATE TABLE ENROLLMENTS(
CLASS_NAME CHAR(10) PRIMARY KEY,
full_name CHAR(10),
FOREIGN KEY (full_name) REFERENCES students(full_name) ON DELETE CASCADE,
FOREIGN KEY (CLASS_NAME) REFERENCES classes(CLASS_NAME) ON DELETE CASCADE,  
)engine=innodb;

this is a new enrollments table that will store all the enrollments for a particular class along with the students name which is referenced as foregin key from the students table

CREATE TABLE students(  
full_name CHAR(10) PRIMARY KEY,  
age INT,  
birthday VARCHAR(10)
)engine=innodb;

And finally students table can exist as an independent table with only student related meta data in it.

Thumbs up if this was helpful, otherwise let me know in comments