A. Cut and paste the questions below into a Word document (or your favorite text editor if you do not have Word) then enter the required answer below each question.
1. Modify the ../demos/w05notes03.sql script to load the tables with the following data:
|
student: |
course: |
schedule: |
|
(1, Jimbob) |
(1, cs360) |
(1, 1) |
|
(2, JaneDoe) |
(2, pe180) |
(1, 2) |
|
(3, McGee) |
|
(2, 1) |
<Paste your modified sqlscript here>
2. Write sql commands to do the following in order. Paste the sql command you wrote to do the operation below each bullet.
· Update a record in the course table that causes two records in the schedule table to be updated.
· Delete a record in the student table that causes no cascaded deletes in the schedule or course tables
· Delete a record in the student table that causes cascaded deletes in the schedule table.
3. Write sql commands to do the following in order (paste the sql below each bullet):
· Alter both the student and course tables to make the key an auto_increment field.
(NOTE: you must use the ‘alter’ command to receive full credit for this bullet.)
· Demonstrate that your auto_increment is functioning by inserting a new record into both the student and course tables where your insert commands do not specify a value for the key field.
4. Write sql commands to do the following in order (paste the sql below each bullet):
· Create a table named section, which lists all sections of a course. A section has the following attributes:
courseID (foreign key to the course table).
year, e.g., 2009;
term (1, 2, 3, 4 -- representing fall, winter, spring, summer);
number (1,2,3,… -- differentiates sections of the same course taught in the same term. Notice this field is an id number for a section. It does not mean the total number of sections of a course.);
There may be no more and no fewer attributes than those shown here.
The attributes must be entered in the order shown here.
There must be a primary key constraint and a referential integrity constraint.
The table definition must prevent the section table from having duplicate records.
The relation must support the following records, e.g.:
2, 2012, 1, 1
2, 2012, 1, 2
2, 2012, 2, 1
2, 2013, 1, 1
3, 2012, 1, 1
· Add records to the section table to describe:
o 1 section of CS360 in winter 2010
o 3 sections of PE180 in fall 2009
· Update a record in the course table that demonstrates your referential integrity constraint works.
B. Submit your assignment on moodle.