Skip to main content

Simple Tables and Relationships

Re-implent the AppSheet Database in MariaDB/MySQL​

Follow the in-class instructions to re-implement the AppSheet database from the previous lab in MariaDB/MySQL.

Insert 5 sample records into the table.

Course Table​

Using phpMyAdmin create a new course table in your nXXXXXXX_cps146_a2 database.

  1. Select your nXXXXXXX_cps146_a2 database.
  2. Name the new table "Course".
  3. Set number of columns to 4.
  4. Click on the "Go" button.
  5. For the subject code column:
    1. For "Name", enter "SubjectCode".
    2. For "Type", choose "ENUM".
    3. Under "Length/Values", click the "Edit ENUM/SET values" link.
    4. Enter "CPS" and "DGL"; delete the two blanks; and click the "Go" button.
    5. For the "Index", choose "PRIMARY"; click the "Go" button.
  6. For the course number column:
    1. For "Name", enter "CourseNumber".
    2. For "Type", choose "INT".
    3. For "Length/Values", enter "3".
    4. For the "Index", choose "PRIMARY"; click the "Go" button.
  7. For the title column:
    1. For "Name", enter "Title".
    2. For "Type", choose "VARCHAR".
    3. For "Length/Values", enter "50".
  8. For the credits column:
    1. For "Name", enter "Credits".
    2. For "Type", choose "INT".
    3. For "Length/Values", enter "1".
  9. Click on the "Save" button at the far bottom right of the form.
  10. Go to "Insert" tab.
  11. Fill out some sample data in the "Value" column of the form and use the "Go" button to insert the new record.
  12. Repeat until you have 5 records in the table.
  13. Try adding a record with a duplicate SubjectCode/CourseNumber and convince yourself this does not work.

Event Table​

Create an "Event" table. You will need to record the building which is one of "KMX", "DIS", "RAV", or "TYE". You will also need the room number which is a 3 digit number. Each event will also have a title and a starting and ending time.

Set a natural primary key that will at least prevent multiple events being scheduled to start in the same room at the same time. Don't worry about other overlapping for now.

Enter 5 sample records into your new table.

Relationships​

In-class​

Follow the in-class instructions to implement the following tables and relationships.

High-level model:

Implementation (table-level) model:

On Your Own​

Create the tables and some sample data (at least 5 records in each table) for a hair salon based on the following high-level model.

Be sure to create the appropriate primary and foreign keys. Also, be sure to add any additional attributes to the tables that seem relevant.