CP363: Assignment 05 - Fall 2024

Due 10:30 AM, Saturday, November 30, 2024

Database access: hopper phpMyAdmin

Assignments 1 through 4 all queried the DCRIS database. Now you must create your own database schema on hopper, the Department's student server. 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.

You cannot create a new database schema. You have a database schema and login available to you on hopper. The schema and login are named with your Laurier user ID. e.g. if your email address is barn4520@mylaurier.ca , then your database schema/login is barn4520 . 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 '_')

Your login also allows you to access the DCRIS database.

Use the hopper phpMyAdmin web front end to login to the hopper MySQL database and access your schema.

phpMyAdmin is fairly straightforward to use, so we aren't going to document it ourselves. There are, however, many tutorials available online - Google is your friend. Having said that, we strongly suggest not waiting until the day before the assignment is due to get started on using phpMyAdmin.

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 memberBroad)
• 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 of data (like the pubType table)
• at least one table must have a minimum of ten rows of data

(Use the checkboxes when finished to check off the requirements to make sure you have met them all.)

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.