0 Replies - 788 Views - Last Post: 30 November 2014 - 09:00 AM

#1 DylanLyon  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 24-September 14

Really need some help understanding this semi-beginner SQL work.

Posted 30 November 2014 - 09:00 AM

All of a sudden my database class has rocketed up in difficulty and I'm having a difficult time keeping up. There are all these new terms and words that I'm having a difficult time making heads or tails of and how they all relate to each other.

For this problem you will recreate your database schema, adding specifications for keys, referential integrity, and other constraints.

1.1) Modify your TABLE statement from assignment 3 as follows (15 marks).

For each relation in your schema from assignment 3, if the relation has one or more keys then modify the CREATE TABLE statement to declare one PRIMARY KEY and to declare all other keys as UNIQUE.

For each referential integrity constraint that should hold in your schema, specify the constraint using a REFERENCES clause within the appropriate CREATE TABLE statement.

You may use the default option for handling referential integrity violations (violations will generate an error). We expect that everyone’s PDA should include at least one referential integrity constraint. Add at least two attribute-based CHECK constraints to relations of your database schema.


1.2) You don’t necessarily need to modify your program for generating data if it creates violations. Write data modification commands to illustrate the following seven scenarios (35 marks – each 5 marks):

a) An INSERT command creating a key violation
b ) An UPDATE command creating a key violation
c) An INSERT command creating a referential integrity violation
d) A DELETE command creating a referential integrity violation
e) An UPDATE command creating a referential integrity violation
f) An INSERT command creating a CHECK constraint violation
g) An UPDATE command creating a CHECK constraint violation


For the past few weeks we've been creating and slightly modifying three different tables that contain information about different manufactures of parts. The information ranges from part type and colour, to location of those parts and order size. Up until now it's been pretty easy, just basic commands to modify or read certain aspects of the tables.

Here is a screenshot of all the tables.

Posted Image

Now though I'm not quite sure whats going on. In question 1.2 I'm pretty sure I just need to create commands that will returns errors but to do so I'm fairly certain I need to modify my tables first.

I'm not looking for people to write the code for me, I really just need some clarification/a point in the right direction.

In the first question what exactly does it mean when it says for each relation? Also, as far as I can tell each of my tables only have, if even that, one primary key and a couple of checks for some of the columns.

After that it mentions referential integrity constraint, which means table level constraints, the problem being I don't think I have any table level constraints, only some column level ones and a few checks.

Does anyone else understand this?

Is This A Good Question/Topic? 0
  • +

Page 1 of 1