CP363: Assignment 5 - Fall 2022

Due 11:00 PM, Monday, April 10, 2023

General Assignment Notes

When writing and submitting your assignments follow these requirements:

• Name your Eclipse project with your Network login, an underscore, 'a' (for 'assignment', then the assignment number: login_en.zip. For example, if the user barn4520 submits Assignment 1, the name should be: barn4520_a01. Give your .zip file the same name when exporting your project. The project above would be exported in a .zip file named barn4520_a01.zip. Use only Eclipse's built-in archive capability to create these .zip files. No other format will be accepted.

• Some assignments consist of multiple Eclipse projects where one project references another. Use the same naming scheme for all your projects. Thus, a project named data_structures should be barn4520_data_structures.

• Test your programs thoroughly - show us how it works. Copy the outputs from your testing to a file in your Eclipse/Pydev project named testing.txt. Make sure the tests are well labelled so that the markers know which program the results represent.

• There should be no errors or warnings in your code. Watch out particularly for bad or unused imports - these generate warnings and must be removed.

• If you are asked to provide discussion put this discussion in your Eclipse/Pydev project in a file named discussion.txt.

• Put all your programs, testing, and discussion files into one Eclipse/Pydev project.

• Zip the entire project using Eclipse and submit the resulting .zip file through MyLearningSpace.

• Use the Validate Assignment link to make sure that your project and .zip file are named correctly and have the proper contents. Improper assignment submissions are given a grade of zero.

Marks will be deducted from any questions where these requirements are not met.

Links

Pages of use for this assignment:

• Connecting to MySQL - DBMS

Assignments 1 through 4 all queried the DCRIS database. Now you will create your own database schema on hopper. Your database must be in at least Boyce-Codd Normal Form.

As the hopper database administrator, we will access your database directly after the due date. There is no dropbox, and nothing to submit through MLS, and no programming in Python required.

You have a database schema available to you on hopper. The schema is named with your Laurier network name. e.g. if your network login is barn4520, then your database schema is barn4520. The user name to access this is also your Laurier network login. The default password is:

cp363_Pass

which we suggest you change at your earliest opportunity. You may change your password from a DBMS query dialog box with the following command:

      
ALTER USER your login IDENTIFIED BY 'new password';

    

Passwords must have:

• a minimum length of 8 characters
• mixed case characters (i.e. at least one each of upper and lower case characters
• at least one digit
• at least one special character (we suggest an '_')

Use a DBMS of your choice to login to the hopper MySQL database and access your schema.

In your schema, create a database that consists of:

• at least six database tables
• each table must have a primary key
  • at least one of the primary keys must be an auto-incremented integer primary key (like a Member ID)
  • at least one of the primary keys must be a 'natural' primary key that relies on its data (like a student ID)
  • at least one of the primary keys must consist of more than one field
• at least one lookup table (like member_broad)
• at least one each of the data types INT, CHAR, VARCHAR(n), DATE, DATETIME, LONGTEXT, DECIMAL in at least one of the tables
• foreign keys such that:
  • all tables must be a foreign key source or destination
  • at least one table must be both a foreign key source and destination (like narrow)
  • at least one view that displays the results of a two table foreign key relationship (like vBroadNarrow)
  • at least one two-step foreign key relationship (like memberNarrow → narrow → broad)
  • at least one view that displays the results of a three table foreign key relationship (like vMemberBroad)
  • at least one foreign key with an ON DELETE/ON UPDATE CASCADE constraint
  • at least one foreign key with an ON DELETE/ON UPDATE RESTRICT constraint
• at least one field that allows NULL as the default value
• at least one DATETIME field with a default value of the current date/time
• at least one non-DATE/TIME field with a custom default value of your choice
• at least one unique index on a non-primary key field (like broadDesc in broad)
• at least one non-unique index on non-primary key fields (like inFullName in member)
• at least one procedure
• at least one function
• at least one trigger that acts like a CHECK constraint
• at least one event
• no table may have less than three rows (like the pubType table)
• at least one table must have a minimum of ten rows

The point of using the techniques above is to make sure that your database meets all of the normal forms.

The data stored in your database is up to you. No two students in the course may have the same database design. For example, even if two students have a hockey database, the databases must have different data and structures.

Your database must be error-free. Note that if you set up an event, or a view and then delete the table(s) associated with that event or view, that event or view will have errors. Make sure you double check everything for correctness by the due date.