Question

SQL code

Create entities (tables) to represent the following ternary relationship. Note that we are not storing all the required information in these entities to simplify it. The underlined attributes are the primary keys. Don’t forget to set the constraints for primary and foreign keys:

•A student entity ( bannerId, first name, last name, date of birth)

•A course entity ( crnNumber, course name, # of credits)

•An examination entity ( examId, exam_type, exam_date). (exam types can be “Midterm”, “Final”, “Exam_1”, “Exam_2”, etc)

•A StudentExamGrade entity ( bannerId, crnNumber, examId, grade)

2. Insert 4 students, 3 exams_table, 6 courses.

3. Insert for each student 2 ex_am_  grades for 2 different courses.

4. Run a SQL query to view all the data inserted in all 4 tables created.

Answer 1

STUDENT TABLE:

create table students (bannerID int NOT NULL PRMARY KEY, firstname varchar(15), lastname varchar(15), dateofbirth DATE);

COURSE TABLE:

create table courses(crnNumber int NOT NULL PRIMARY KEY, coursename varchar(20), ofcredits int);

EXAMINATION TABLE:

create table exams (examId int NOT NULL PRIMARY KEY, exam_type varchar (15), exam_date DATE);

StudentExamGrade TABLE:

create table StudentExamGrade(bannerId int FOREIGN KEY REFERENCES students(bannerID), crnNumber int FOREIGN KEY REFERENCES courses(crnNumber), examId int FOREIGN KEY REFERENCES exams(examId), grade CHAR(3));

Insertion in student table:

Insert into students values ('3161116', 'dee', 'sree', '1999-12-28');

Insert into students values ('3161117', 'meen', 'tkon', '1999-11-18');

Insert into students values ('3161118', 'balvee', 'chow', '1998-10-26');

Insert into students values ('3161119', 'vitvar', 'dant', '1998-12-22');

Insertion into course table:

Insert into courses values ('12301', 'Ethics', '10');

Insert into courses values ('12302', 'DLD', '10');

Insert into courses values ('12303', 'Robotics', '10');

Insert into courses values ('12304', 'DataStructures', '10');

Insert into courses values ('12305', 'BigData', '10');

Insert into courses values ('12306', 'Thermodynamics', '10');

Insertion into Examination table:

Insert into exams values ('101', 'Midterm', '2020-10-29');

Insert into exams values ('112', 'Fina;', '2020-11-30');

Insert into exams values ('201', 'Exam_1', '2020-10-30');

Insertion into  StudentExamGrade table:

Insert into  StudentExamGrade values ('3161116', '12302', '101', 'A');

Insert into  StudentExamGrade values ('3161116', '12303', '101', 'B');

Insert into  StudentExamGrade values ('3161117', '12302', '112', 'A');

Insert into  StudentExamGrade values ('3161117', '12301', '101', 'A');

Insert into  StudentExamGrade values ('3161118', '12304', '101', 'B');

Insert into  StudentExamGrade values ('3161118', '12305', '201', 'C');

Insert into  StudentExamGrade values ('3161116', '12303', '101', 'D');

Insert into  StudentExamGrade values ('3161116', '12306', '101', 'A');

SQL query to view all the data inserted in all 4 tables created:

• select * from students;
• select * from courses;
• select * from exams;
• select * from StudentExamGrade;

All the above queries will excute properly. I have followed every instruction and answered the given question

Please give upvote

For any queries please do comment