CP363: Assignment 02 - Spring 2026 Section E (Mr. Habib-ur Rehman)

Due June 14, 11:59 pm

Database access: hopper phpMyAdmin

You must design your own database on hopper, the Department's student server.

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:

The default password is:

cp363_Pass

which you may 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.

You may also use a database front end, as discussed at: CP363 : Connecting to MySQL - DBMS

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 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 two-step foreign key relationship (like memberNarrow → narrow → broad)
  • 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
• 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. Make sure you double check everything for correctness by the due date.