Database redesign

[ghost]

Our database needs to be redesigned.

I suggest we have a discussion about the changes we need to make. Zak, were you going to send out a new design for feedback?

[zak-hassan]

Lets open up the discussion first. Well the design for the DB will change with:

• Adding factor Table to the database
• We will have a foreign key from FacultyToCourseSemesterYear: factor_id that maps to Factors table.
• Removing eval foreign key from CourseInSemester table and including it in FacultyToCourseSemesterYear

[ghost]

I agree. We also need eval factors in both course and FacultyToCourseSemester year since they are not always the same.

[zak-hassan]

Do you have mysql workbench? If so could you update the model file with these changes.

I will then integrate that design with our models...

[zak-hassan]

Anil you said lets discuss the db so lets discuss it.

[ghost]

Okay.

Let's look at the tables that need to change.

CREATE TABLE IF NOT EXISTS mydb.EvalFactor ( eval_id INT NOT NULL DEFAULT 0 , eval_name VARCHAR(10) NOT NULL , eval_factor FLOAT NOT NULL , PRIMARY KEY (eval_id) ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS mydb.Course ( course_id INT NOT NULL DEFAULT 0 , course_code VARCHAR(8) NOT NULL , course_name VARCHAR(50) NOT NULL , PRIMARY KEY (course_id) ) ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS mydb.CoursesInSemester ( cis_id INT NOT NULL , course_id INT NOT NULL , semester_id INT NOT NULL , year INT NOT NULL , total_section INT NOT NULL , eval_1 INT NOT NULL , eval_2 INT NOT NULL , eval_3 INT NOT NULL , eval_1_ans FLOAT NOT NULL , eval_2_ans FLOAT NOT NULL , eval_3_ans FLOAT NOT NULL , addition_attribute INT NOT NULL ,

CREATE TABLE IF NOT EXISTS mydb.FacultyToCourseInSemesterYear ( cisy_id INT NOT NULL , faculty_id INT NOT NULL , course_id INT NOT NULL , prepType_id INT NOT NULL , addition_attribute FLOAT NOT NULL , compHour_id INT NOT NULL , compHour_allowance FLOAT NOT NULL , compHour_assigned FLOAT NOT NULL , section_number INT NOT NULL , year INT NOT NULL , semester_id INT NOT NULL ,

I think we need to drop these fields from EvalFactor: eval_factor FLOAT NOT NULL ,

I think we need to add these fields to Course: eval_1_ans FLOAT NOT NULL , eval_2_ans FLOAT NOT NULL , eval_3_ans FLOAT NOT NULL ,

I think we need to drop these fields from CoursesInSemester: eval_1 INT NOT NULL , eval_2 INT NOT NULL , eval_3 INT NOT NULL , eval_1_ans FLOAT NOT NULL , eval_2_ans FLOAT NOT NULL , eval_3_ans FLOAT NOT NULL , addition_attribute INT NOT NULL ,

I think need to add these fields to FacultyToCourseInSemesterYear: eval_1 INT NOT NULL , eval_2 INT NOT NULL , eval_3 INT NOT NULL , eval_1_ans FLOAT NOT NULL , eval_2_ans FLOAT NOT NULL , eval_3_ans FLOAT NOT NULL ,

I think we need this table: CREATE TABLE IF NOT EXISTS mydb.Factor ( factor_id INT NOT NULL , factor_name VARCHAR(25) NOT NULL , factor_val FLOAT NOT NULL ,

Additionally, I think we need to drop everything program related. We just don't use it. We don't care what programs a professor has taught courses in. We don't just don't deal with program at all. We care about courses. The only thing I can think of that is program related is when a course is offered, but are we really going to stop Mary-Lynn from adding a course to a professor because it is not offered? I'm sure she can make that decision.

Thoughts on the the design of the database and program? Sorry about the lateness of this reply, I've been having tests left and right and assignments/labs too.

[zak-hassan]

Okay I agree with all the changes you specified except the following:

Why are we adding these fields to FacultyToCourseInSemesterYear. I can get the values from the eval_1. Also we don't have any relation between Factor and any other table.

eval_1_ans FLOAT NOT NULL , eval_2_ans FLOAT NOT NULL , eval_3_ans FLOAT NOT NULL ,

[ghost]

I missed that. Yes.

FacultyToCourseInSemesterYear needs a foreign key to factor_id

As for eval_1_ans, it is a value holding the eval factor. The reason we can't use eval_1 for example is because Mary-Lynn can override the factor in rare cases. So the eval factor isn't always what's in the Course table. As a result, we need to save it separately inside FacultyToCourseInSemesterYear.

So basically we have the eval factor name and eval factor value in FacultyToCourseInSemesterYear. Make sense?

[zak-hassan]

Okay. If that is the case then I agree. Let me know if these changes are committed I will generate a new DB.

[ghost]

So with that in mind, I believe we have agreed on a design. Can I close this issue?

[zak-hassan]

Yea. its okay I closed it.

[ghost]

DB Script updated. Take a look and let me know if it's solid Zak.

[zak-hassan]

Hey Anil. The database redesign is much better but Now I will have to fix a few of the service methods and controllers and then we should be fine. It may not be ready for 1:30pm demo. I will try to fix it as quickly as possible.

[zak-hassan]

Okay problem with Eval_Factor table. It doesn't have the eval_factor field. Table Course doesn't have crossover, old_name? CourseInSemester is missing additionalAttribution . We used to have this field but now we don't so can you remind me why we need it?

[ghost]

We don't need eval_factor because evaluations are just names. The value varies on the course. additionalAttribution is in FacultyToCourseInYearSemester. As for Course not having crossover and oldname that was a mistake.

I don't remember deleting it, I copied the latest CREATE script and made changes. Based on the files shown in our project, it wasn't there in the last script, so maybe you accidentally deleted it? Did you have a reason for doing so?

[ghost]

Design was implemented. This issue has been resolved.