CIS 336 iLab 3 Building the Physical CIS 336 iLab 3 Building the Physical | Page 3

· Creating Constraints
o Create all NOT NULL constraints as indicated in the ERD.
o Create all PK constraints as indicated in the ERD.
o Create all FK constraints as indicated in the ERD.
· Create all of the tables and all of the constraints before populating any of the tables with data.
· Because FK constraints will be in place when the insert statements are executed, you will need
to consider carefully which tables must be created before others in order to ensure that FK
constraints are not violated.
· The COURSE table has a self-referencing FK constraint. Specifically, some courses have
prerequisite courses. Consequently, the record for a course possessing a prerequisite course
cannot be successfully inserted into the table unless the record for the prerequisite course has
already been inserted. This may require you to reorder the insert statements to resolve FK
violations when loading the table. You may reorder the data provided for this table, but do not
alter it.
· The data for one table intentionally contains a record containing an FK constraint that is not
resolved by a record in the parent table. This orphaned record has been included as an exercise
for you to find. Because this record has an unreconciled FK constraint, it cannot be successfully
inserted. You will need to delete or comment out the insert statement for this one record in
order to produce a script that runs without errors.
· Aside from reordering the data for the COURSE table as necessary, and commenting
out/deleting the ONE record whose FK dependency cannot be resolved by the data provided,
you are NOT to modify, add to, or delete from the data provided. Your SQL script must produce
tables containing data identical to the expected solution set, or points will be deducted.
· ALL character strings must be enclosed in single quotes. This includes alpha strings and