The following questions are based on the CLASS SCHEDULING 3NF relations along with some sample data...

  

Transcribed Image Text:

The following questions are based on the CLASS SCHEDULING 3NF relations along with some sample data shown in Figure 6-11. Note that values of the SectionNo column do not repeat across semesters. STUDENT (StudentID, StudentName) Student 30214 54907 66324 70542 2143 3467 4756 StudentName Letersky Altvater FACULTY (Faculty D, FacultyName) Faculty D Coursel D ISM 3113 ISM 3112 ISM 4212 ISM 4930 Aken Marra FacultyName Birkin Berndt Collins COURSE (CourselD. CourseName) CourseName Syst Analysis Syst Design Database Networking QUALIFIED (Faculty D, CourselD, DateQualified) Faculty Coursel Date Qualified 2143 2143 3467 3467 4756 4756 ISM 3112 ISM 3113 Student 38214 54007 54907 66324 ISM 4212 ISM 4930 ISM 3113 ISM 3112 FIGURE 6-11 Class scheduling relations (missing ASSIGNMENT) SECTION (Section No, Semester, CourselD) Section No CourselD 2712 2713 2714 2715 Semester 1-2015 1-2015 -2015 11-2015 9/2005 9/2005 9/2012 9/2013 9/2008 9/2008 REGISTRATION (StudentiD, Section No) Section No 2714 2714 2715 2713 ISM 3113 ISM 3113 ISM 4212 ISM 4930 1. (6 pts) For each of the table displayed above, write the SQL statements that will create the table. Use the following information to define your data types : StudentID (integer, primary key) StudentName (25 characters) FacultyID (integer, primary key) FacultyName (25 characters) CourselD (8 characters, primary key) CourseName (15 characters) DateQualified (date) Semester (7 characters) Section No (integer, primary key) 2. (3 pts) Write the SQL code allowing to perform the following operations: a. How would you add an attribute, Department, to the Faculty table? b. How would you remove the Student table? C. How would you change the CourseName field from 15characters to 10 characters? 3. (3 pts) Write SQL commands for the following: a. Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table. b. Now write a command that will remove Lopez from the Student table. c. Create an SQL command that will modify the name of course ISM 4212 from Database to Introduction to Relational Databases. 4. (8pts) Write SQL queries to answer the following questions: a. List the numbers of all sections of course ISM 4212 that are offered during the semester "II-2015." b. List the course IDs and names of all courses that ends with the letters "Analysis" c. List the IDs of all faculty members who are qualified to teach both ISM 4212 and ISM 3113. Make sure to only list one ID for each faculty. d. Modify the query above in part c. so that both qualifications must have been earned after the year 2005. The following questions are based on the CLASS SCHEDULING 3NF relations along with some sample data shown in Figure 6-11. Note that values of the SectionNo column do not repeat across semesters. STUDENT (StudentID, StudentName) Student 30214 54907 66324 70542 2143 3467 4756 StudentName Letersky Altvater FACULTY (Faculty D, FacultyName) Faculty D Coursel D ISM 3113 ISM 3112 ISM 4212 ISM 4930 Aken Marra FacultyName Birkin Berndt Collins COURSE (CourselD. CourseName) CourseName Syst Analysis Syst Design Database Networking QUALIFIED (Faculty D, CourselD, DateQualified) Faculty Coursel Date Qualified 2143 2143 3467 3467 4756 4756 ISM 3112 ISM 3113 Student 38214 54007 54907 66324 ISM 4212 ISM 4930 ISM 3113 ISM 3112 FIGURE 6-11 Class scheduling relations (missing ASSIGNMENT) SECTION (Section No, Semester, CourselD) Section No CourselD 2712 2713 2714 2715 Semester 1-2015 1-2015 -2015 11-2015 9/2005 9/2005 9/2012 9/2013 9/2008 9/2008 REGISTRATION (StudentiD, Section No) Section No 2714 2714 2715 2713 ISM 3113 ISM 3113 ISM 4212 ISM 4930 1. (6 pts) For each of the table displayed above, write the SQL statements that will create the table. Use the following information to define your data types : StudentID (integer, primary key) StudentName (25 characters) FacultyID (integer, primary key) FacultyName (25 characters) CourselD (8 characters, primary key) CourseName (15 characters) DateQualified (date) Semester (7 characters) Section No (integer, primary key) 2. (3 pts) Write the SQL code allowing to perform the following operations: a. How would you add an attribute, Department, to the Faculty table? b. How would you remove the Student table? C. How would you change the CourseName field from 15characters to 10 characters? 3. (3 pts) Write SQL commands for the following: a. Create two different forms of the INSERT command to add a student with a student ID of 65798 and last name Lopez to the Student table. b. Now write a command that will remove Lopez from the Student table. c. Create an SQL command that will modify the name of course ISM 4212 from Database to Introduction to Relational Databases. 4. (8pts) Write SQL queries to answer the following questions: a. List the numbers of all sections of course ISM 4212 that are offered during the semester "II-2015." b. List the course IDs and names of all courses that ends with the letters "Analysis" c. List the IDs of all faculty members who are qualified to teach both ISM 4212 and ISM 3113. Make sure to only list one ID for each faculty. d. Modify the query above in part c. so that both qualifications must have been earned after the year 2005.

Answer rating: 100% (QA)

Answer 1 CREATE TABLE STUDENTStudentId integer PRIMARY KEY StudentName varchar25 CREATE TABLE FACULT... View the full answer