$1.$ Setup and Creation

o $(10$ Points$)$ Set up your MySQL database on your system or with Docker.

o $(10$ Points$)$ Set up your folder with the proper file structure and naming convention.

o $(10$ Points$)$ Inside your users.sql file, create three users for your database. The first will be named admin$\_$user with a password of admin$1234,$ the next will be read$\_$only with a password of read$1234,$ and the last will be modify$\_$user with a password of modify$1234.$ Set this up so that the user can be used on any ip address that requests access to the database with the correct password.

o $(10$ Points$)$ Inside your permissions.sql file, create three roles for your database. The first will be called admin$\_$role and you will grant it access to the entire database with the option to grant privileges to other users. The next role will be read$\_$role and it will be granted the privilege to read but not modify any database in the system. The last role will be the modify$\_$role and it will be granted privileges to modify any database on the system.

o $(10$ Points$)$ Grant the corresponding role to each user. admin$\_$role $->$ admin$\_$user, read$\_$role $->$ read$\_$user, and modify$\_$role $->$ modify user. Don't forget to set the default role for each user!!

Create a Database and Table

o $(20$ Points$)$ Inside your DDL$.$sql file, create a database with the database name SCHOOL. Check to make sure that the script can be run regardless if there is a database with that name already or not.

o $(20$ Points$)$ Create a table in your database. Here's your schema: Student$($id: integer autoincrement Primary Key, first$\_$name: varchar with up to $50$ character, last$\_$name: varchar with up to $50$ characters, email$\_$address: varchar with up to $100$ characters, date$\_$of$\_$birth: Date, student$\_$grade: Integer, teacher$\_$first$\_$name: varchar with up to $50$ characters, teacher$\_$last$\_$name: varchar with up to $50$ characters, teacher$\_$email: varchar with up to $100$ characters, room$\_$number: Integer, subject: varchar with up to $100$ characters, class$\_$grade: char with only $1$ character $).$

Inside your DML$.$sql file, insert at least one tuple $($DML$)$ into your table

$2.$ Decompose your student table into three separate tables.

$$ Your three tables should be named: Student, Teacher, and Classes. They should contain the same attributes that you had in your first monolithic table you created in week one. Each table will need a primary key. In the comments for each table. Explain to me the how you came to your primary key choice. List out the superkeys, the candidate keys, and why you chose the primary key that you did.

$(25$ Points$)$ Create a foreign key reference to your tables to make sure that the tables can be joined.

Insert and Query:

$$ Open your dml$.$sql file and insert some data into each table. Add at least $3$ tuples in each table. Make sure that each student has one teacher, a teacher has a classroom.

$$ Open your query.sql file and write some queries on your database. $($At least $3$ queries$)$ Make sure that one of the queries has a join between two of the tables.

$3.$ Now that you have your tables in $3$rd normal form and decomposed.

Write some more inserts to each table. Create the $4$ basic classes. Math, Science, English, and History. Assign a teacher to each of these and make sure that there are at least three students in each class. Make sure each student has a grade in each class they are in$.$

Write a query that shows $($for each student$)$ the first and last name, class name, teacher name, and grade they are receiving for each class. Group this query by the student name and sort each class by the highest grade $($A$)$ to the lowest grade $($F$).$

Create two functions and two procedures:

function should be called addStudent and insert a student into the database. The other function should be called getTeacherId return the teacher id when searching for the teacher's first and last name.

The first procedure will be called getAllStudents and return all students in the students table. The second procedure will be called getTeacherStudents and return all the students that are taught by a specific teacher. You will need to take as arguments the name of the teacher. Make sure to use the function you created in the first part of this question to get the teacher id number inside of this procedure. You will need to use some variables and joins inside of your procedure to make this work!

$4.$ Let's begin our Application Architecture.

In python, create your Business Logic Layer, and Data Access Layer $($DAL$).$ Create two folders inside of your application folder. The folders should be named BLL and DAL. Inside of your DLL and BAL, map the tables that you currently have and create each of the tables as a Class. Add at two methods to your classes. One to read data and another to add data. Make sure all your parameters are included. Your BLL should call the DAL and pass down data to your DAL.

Get your application to connect to the database. Use the admin$\_$user you created in Week $1$ to make