Tables for the Assignment

Create the following tables in a database named "roster". Make sure that your database and tables are named exactly as follows including matching case.

DROP TABLE IF EXISTS Member;

DROP TABLE IF EXISTS 'User';

DROP TABLE IF EXISTS Course;

CREATE TABLE 'User'

user$\_$id INTEGER NOT NULL AUTO$\_$INCREMENT,

name VARCHAR$(128)$ UNIQUE,

PRIMARY KEY$($user$\_$id$)$

CREATE TABLE Course $($

course$\_$id INTEGER NOT NULL AUTO$\_$INCREMENT,

title VARCHAR$(128)$ UNIQUE,

PRIMARY KEY$($course$\_$id$)$

REATE TABLE Member

user$\_$id INTEGER,

$cours{e}_{i}d$ INTEGER,

role INTEGER,

CONSTRAINT FOREIGN KEY $($user$\_$id$)$ REFERENCES 'User' $($user$\_$id$)$

ON DELETE CASCADE ON UPDATE CASCADE,

CONSTRAINT FOREIGN KEY $($course$\_$id$)$ REFERENCES Course $($course$\_$id$)$

ON DELETE CASCADE ON UPDATE CASCADE,

PRIMARY KEY $($user$\_$id$,$ course$\_$id$)$

ENGINE$=$InNODB CHARACTER SET$=$ut $88$ ;

Note that we need to surround User with back$-$quotes $($i$.$e$.$ 'User' because it is a keyword in later versions of MySQL$.$

Course Data

You will normalize the following data $($each user gets different data$),$ and insert the following data items into your database, creating and linking all the foreign keys properly. Encode

instructor with a role of $1$ and a learner with a role of $0.$

Johnny, si$106,$ Instructor

Amber, sile$6,$ Learner

Havin, sile$6,$ Learner

Kaelum, si$106,$ Learner Tayyib, si$106,$ Learner

Rubyn, si$110,$ Instructor

Alekzander, si$110,$ Learner

Emelia, si$110,$ Learner Mikael, si$110,$ Learner

Oonagh, si$110,$ Learner

Peregrine, si$206,$ Instructor

Annabella, si$206,$ Learner

Tait, si$206,$ Learner

Tarne, si$206,$ Learner

You can test to see if your data has been entered properly with the following SQL statement.

You can test to see if your data has been entered properly with the following SQL statement.

SELECT 'User'. name, Course.title, Member. role

FROM "User" JOIN Member JOIN Course

ON 'User', user$\_$id $=$ Member.user$\_$id AND Member.course$\_$id $=$ Course.course$\_$id

ORDER BY Course.title, Member.role DESC, 'User'. name

The order of the data and number of rows that comes back from this query should be the same as above. There should be no missing or extra data in your query.

What Turn In

When you have the data all inserted, use phpMyAdmin to Export the data as follows:

Select the database $($do not select a table within the database$)$

Select the Export Tab

Select "Custom $-$ display all possible options"

Select "Save output to a file"

Set the format to JSON

Do not select "pretty print" the output

Leave everything else as default and run the export.

The output will be on a file named "roster.json" that should look like the following:

$\frac{?}{?}****$

Export to JSON plugin for PHPMyAdmin

@version $0\mathrm{.}1$

$*/$

$//$ Database 'roster'

$//$ roster.Course

$[\{"$course$\_$id$"$:$"6",$"title":"si$106"\},...\}]$

$//$ roster.Member

$[\{"$user$\_$id$"$:$"1",$"course$\_$id$"$:$"1",$"role":"$1"\},...\}]$

$//$ roster.User

$[\{"$user$\_$id$"$:$"15",$ "name":"Areez"$\},...\}]$

It is a somewhat strange format $-$ it is one bit of JSON for each table. You don't need to edit or even look at this file. Simply upload it above.