Objectives

Demonstrate all you have learned in the class, by creating a database schema to satisfy a set of requirements.

Background

Expanding upon the success of Project $2,$ we will expand our research subject database to include users, projects, and user's access to projects. In the research world, many research projects may exist in a database, but employees are only allowed to access for some of those projects. You will need to create a schema to allow user access control to projects.

The Assignment

$1)$ Expand your research database by creating the following tables. Create columns with appropriate datatypes, and make sure each table has a primary key. The userproject table will link the users and projects; each user$\_$id will have multiple rows with the project$\_$ids they have access to$.$

a$.$ users $-$ stores user information like email address, name, password, etc

b$.$ projects $-$ stores project information like name, start$\_$date, end$\_$date

c$.$ userproject $-$ stores information about which projects users have access to

$2)$ Create $5$ users and $5$ projects. Give each user access to at least one project, and give other users access to more projects, just to demonstrate that your schema works.

a$.$ There is already an enrollment table which has a project$\_$id column with data filled in$.$ Make sure your projects table has project$\_$id values that match some of the existing project$\_$ids in the enrollment table.

$3)$ Perform the following queries and get the row counts if it was a select statement:

a$.$ insert $5$ users and $5$ projects

b$.$ insert the user$/$project permissions

c$.$ select the total number of mr$\_$series that user $1$ has access to

d$.$ select the total number of mr$\_$series that user $1$ DOES NOT have access to

e$.$ Add an index on the mr$\_$series table for the series$\_$desc column

f$.$ A fun select query of your choice

What to submit

A document containing the following:

$1)$ The SQL used to create the new tables

$2)$ The SQL statements and row counts for each of the queries

$3)$ The SQL for the added index