Question

(MMU) has decided to consolidate the functionality of three small overlapping database systems, which support applications for 1) teaching (e.g. instructor assignment and evaluation), for 2) registration (e.g. online course status, waiting lists), and for 3) student records (e.g. transcript generation) The resulting new system will support the following enterprise description: Professors and GTAs are assigned to teach the sections of each class being offered in a semester. At the end of the semester, they get a "team rating" (professors and GTAs together get one rating per section, rating is not done on individual). To support the assignment of professors to sections, a record is kept of which class each professor can teach. Classes can have one or more prerequisite classes. Students can take several sections each semester, and receive a grade for taking each section. Students may end up waiting for some sections, and receive a "rank" (determining the order they will be admitted if other students drop). However, no more than 10 students can wait on a class at the same time Note that GTAs are students, however they differ in that they have a salary. All people (e.g. students, professors) are uniquely identified by their social security number. All classes are identified by department name (e.g. "INFS") and course number (e.g. "614") Sections of classes are distinguished by their section number (e.g. "02") Given the above description, do the following:

Answer 1

ANSWER:

1. ER Diagram:

teaching_team ? id INT (11) instructor _id VARCHAR(15) ◆ taid VARCHAR(15) ● section_d VARCHAR(5) rating VARCHAR(2) rated on DATE instructor ssn VARCHAR (15) ◆ name VARCHAR(30) pre_requisites ◆ parentdepartment VARCHAR(10) phone_no VARCHAR (10) ◆ email-id VARCHAR(20) parent course VARCHAR(5) child_department VARCHAR( 10) student child_course VARCHAR(5) ssn VARCHAR(15) ◆ name VARCHAR(30) tm ent name VARCHAR(10) course-no VARCHAR(5) r + → H.phone_noVAROAR(10) ◆ email-id VARGAR(20) ◇ is_gta TINYINT( 1) semester ? id INT (11) ◆ name VARCHAR(10) | | キ enrollment id INT(11) student id VARCHAR(15) section_id VARCHAR(5) grade VARCHAR(2) appied_on TIMESTAMP ◆ enrolled on TIMESTAMP ◆ completed-on TIMESTAMP is waiting TINYINT (1) ◆ rank INT(11) salary studentid VARCHAR(15) ◆ amount D ECMAL( 10,2) キ class section ? number VAR어 AR(5) . department name VARCHAR(10) subject_jd INT (11) department name VAROHAR(10) course_no VARCHAR(5) semester-id INT(11) ㅢ course-no VARCHAR(5) subject tid INT (11) name VARCHAR(10)

2. Online course and evaluation is not captured by the ER diagram because the functional requirements for online course as in how they will be conducted, by whom, how many enrollments, which subjects are not provided. Evaluation is not captured because the grading criteria based on marks and the type of exams are not provided.

3. Two reports that are available:

• List all students who are waiting on more than on section of a class
• List all instructors that have received the minimum rating

4. Relation schemas based on MySQL 5.7 :

create database mmc;

use mmc;

create table student(
ssn varchar(15) not null,
name varchar(30) not null,
phone_no varchar(10) not null,
email_id varchar(20) not null,
is_gta tinyint(1) default 0,
primary key(ssn));

create table salary(
student_id varchar(15) not null,
amount decimal(10,2) not null,
foreign key (student_id) references student(ssn));

create table semester(
id int not null auto_increment,
name varchar(10) not null,
primary key (id));

create table subject(
id int not null auto_increment,
name varchar(10) not null,
primary key (id));

create table class(
subject_id int not null,
department_name varchar(10) not null,
course_no varchar(5) not null,
semester_id int not null,
primary key (department_name, course_no),
foreign key (semester_id) references semester(id),
foreign key (subject_id) references subject(id));

create table pre_requisites(
parent_department varchar(10) not null,
parent_course varchar(5) not null,
child_department varchar(10) not null,
child_course varchar(5) not null,
foreign key (parent_department, parent_course) references class(department_name, course_no),
foreign key (child_department, child_course) references class(department_name, course_no));

create table section(
number varchar(5) not null,
department_name varchar(10) not null,
course_no varchar(5) not null,
primary key (number),
foreign key (department_name, course_no) references class(department_name, course_no));

create table instructor(
ssn varchar(15) not null,
name varchar(30) not null,
phone_no varchar(10) not null,
email_id varchar(20) not null,
department_name varchar(10) not null,
course_no varchar(5) not null,
primary key (ssn),
foreign key (department_name, course_no) references class(department_name, course_no));

create table teaching_team(
id int not null auto_increment,
instructor_id varchar(15) not null,
ta_id varchar(15) not null,
section_id varchar(5) not null,
rating varchar(2),
rated_on date,
primary key (id),
foreign key (instructor_id) references instructor(ssn),
foreign key (ta_id) references salary(student_id),
foreign key (section_id) references section(number));

create table enrollment(
id int not null auto_increment,
student_id varchar(15) not null,
section_id varchar(5) not null,
grade varchar(2),
applied_on timestamp default current_timestamp,
enrolled_on timestamp,
completed_on timestamp,
is_waiting tinyint(1) default 0,
rank int not null,
check(rank >= 1 and rank <= 10),
primary key (id),
foreign key (student_id) references student(ssn),
foreign key (section_id) references section(number));