Read chapter 24 of Big Java 6th Ed on the BlackBoard course

Read chapter 24 of Big Java 6^th Ed. (on the BlackBoard course website as a PDF) and provide short answers to the following questions. Submit your answers in a text document (plain text or Microsoft Word format, please).

a. Design a set of database tables to store information about classes and instructors for the classes. For this question, an instructor has a unique ID number (such as 123456), a name (such as “John Doe”), and an email address (such as “john.doe@iastate.edu”). Every class has a unique course identification number (such as “MIS307”), course name (such as “College of Business”), number of credits (such as 3), and instructor’s ID number. Any one instructor can teach multiple classes.
Show the CREATE TABLE statements that could be used to create these two tables.

b. Give the SQL command to INSERT an instructor “John Doe” with ID number 123456 and email address “john.doe@iastate.edu” into the instructor table you showed how to create in (a) above.

c. Give the SQL command to INSERT a course number “MIS307” in the College of Business with 3 credits taught by instructor ID 123456 into the class table you showed how to create in (a) above.

Solution

a)
CREATE TABLE INSTRUCTOR
(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(30),
EMAIL VARCHAR2(30)
);

CREATE TABLE COURSE
(
CIN VARCHAR2(6) PRIMARY KEY,
NAME VARCHAR2(30),
NUMBER_OF_CREDITS NUMBER,
INSTRUCTOR_ID NUMBER FOREIGN KEY REFERENCES INSTRUCTOR(ID)
);

b)
INSERT INTO INSTRUCTOR VALUES(123456, \"John Doe\", \"john.doe@iastate.edu\");

c)
INSERT INTO COURSE VALUES(\"MIS307\", \"College Of Business\", 3, 123456);