Assignment 1 Clarifications and Corrections

Check this page regularly for any hints, clarifications or corrections related to Assignment 1

• More Clarifications::
  1. Some students have noted that we refer to Vendor ID or Employee ID. Though these fields have not been explicitly specified, they are implicitly required. Therefore, it is recommended that you create an ID field as a primary keythat uniquely identifies each Employee or Vendor.
  2. In question 2, a dependency is missing — the doctor is an employee. Again, it is implicit and obvious, but just in case, here it is:
        PatientRoomAssignment(prDoctor) Employee(employeeID)

• Suggestions to help you test your ddl for question 1: Here is some advice that was shared with one of the students, perhaps it will help you as well.
  Assuming you can ssh to dbsrv1 or dbsrv2 and are able to get as far as "psql csc343h-yourCDFUserId" to bring up psql and connect to your database (if not look at the Postgres page and figure it out.):
  1. If you enter \? you will get a list of psql commands that are available to you
  2. One of these commands is \cd which allows you to change directory to the directory in which you have your text file (which contains your ddl statements).
  3. Once you have changed directory, you can read your text file in using the command \i filename where filename is the name of the text file that you have created which contains your ddl statements. This will load the text file in and give feedback as it creates the tables described in your ddl.
  4. Note that this only tests whether your ddl creates a table or not. It does not test your constraints.
  
  You can test constraints a few ways:
  • By trying to insert, update and delete rows of data. For example, if an attribute is a foreign reference to another table attribute, you can test whether you can add a value that doesn't exist in the foreign table. You can test if your on update cascade or on delete cascade or whatever you put as an action works. You can test if not nulls work, you can test if restricted values (implemented using check statement) works. There are slides on inserting and updating values in Week 4's lecture slides. You may also find it helpful to look at the tutorial or SQL reference in the Postgres documentation web page. It is up to your ingenuity to discover ways to ensure that you have done it correctly.
  • Also, you can use the psql command \dt to list all of the tables in your database. You can look at each table and how it was created using the \d tablename which gives the details of table tablename. (which is probably the easiest way to check how your tables were created.)
  
  If you made a mistake and need to recreate your tables, make a text script that drops each table individually so that you can reload your script with any corrections. For example:
  drop tablename1;
  drop tablename2;
  ... etc.
  
  Hope this helps

• Constraint Clarification: In the first paragraph on the top of page 2, question 1, it states:
  … A single purchase order item received at the warehouse may be distributed to ten different locations.
  This is not a constraint. This statement was added to illustrate how one item from a shipment may be distributed to many different locations, not to limit the number of distribution locations You should NOT add a constraint which limits the distribution to 10 items.

• Word Template: If you are using Word to format your assignemt, you may use this template to get the operation symbols you may require.