ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 1 of 14
ACIT 3910 Database Administration and
Management
Assignment #2
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 2 of 14
Introduction:
For this assignment you will be creating a new database in MySQL. The database will be a small school
database with students, teachers and the courses they attend. We will create views for commonly used
queries and we will use users, roles and privileges to ensure students can log in, but only see their
information (and not the private information of other students or instructors). We will also use triggers
and stored procedures to enforce data integrity.
This assignment uses many of concepts already learned in the following Labs: Users and Roles; Party
Database; Views and Triggers.
Note that while this simple database is attempting to replicate some real-world applications, it is far
from being a fully complete or an optimized database design and implementation.
Objectives Overview:
Create a school database that will meet the following requirements:
1. Create a database called school.
2. Create the following tables in your database:
a. A student table called student.
The student table will contain a list of all students who have attended the school.
b. An instructor table called instructor.
The instructor table contains a list of all the instructors at the school.
c. A course table called course.
The course table contains
d. A table to store which instructor(s) are teaching which course(s) called
course_instructor.
This table is a many-to-many link between course and instructor, showing a list of all the
instructors teaching the course. In the case of some courses, they may have more than
one instructor, and instructors often teach more than one course.
e. A table for enrollment called course_student.
This table is a many-to-many link between course and student, showing which students
are enrolled in which classes.
f. A grades tables called grade.
This table stores a list of all the final grades for students who have completed their
courses.
3. Populate the tables with some sample data so that the queries and views will return some
meaningful data.
4. Create views for the following queries:
a. A view called my_grades for students to see their grades.
b. A view called student_schedule for students to see this course schedules.
c. A view called instructor_schedule for instructors to see which courses they are
teaching.
5. For each of the students and instructors create MySQL users for them so they can login to
MySQL and query and/or modify their information.
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 3 of 14
6. Create roles for the MySQL users:
a. A student role for students called student.
b. An instructor role for instructors called instructor.
7. Grant the instructor role the ability to SELECT, INSERT, UPDATE, DELETE on all tables within
the school database. Grant the instructor role the ability to SELECT on the
instructor_schedule view.
8. Grant the student role the ability to SELECT on the student_schedule and my_grades
views.
9. Grant all instructors the instructor role.
10. Grant all students the student role.
11. Set the default roles to all (to activate the roles) for all the students and instructors.
12. Enforce that the grade_percent is between 0.0% and 100.0% for the grade table.
13. Reverse engineer the database to produce an ER diagram showing all the tables and foreign
keys.
Database Details:
Create the following tables as detailed:
All tables will be using the InnoDB table storage engine.
student
Field Type Null Key Extra
student_id int(11) NO Primary auto_increment
first_name varchar(45) NO
last_name varchar(45) NO
date_of_birth date NO
instructor
Field Type Null Key Extra
instructor_id int(11) NO Primary auto_increment
first_name varchar(45) NO
last_name varchar(45) NO
date_of_birth date NO
course
Field Type Null Key Extra
course_id int(11) NO Primary auto_increment
course_code varchar(10) NO
start_date date YES
end_date date YES
description varchar(100) YES
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 4 of 14
course_instructor
Field Type Null Key Extra
course_instructor_id int(11) NO Primary auto_increment
frn_course_id int(11) NO Foreign
frn_instructor_id int(11) NO Foreign
course_student
Field Type Null Key Extra
course_student_id int(11) NO Primary auto_increment
frn_course_id int(11) NO Foreign
frn_student_id int(11) NO Foreign
grade
Field Type Null Key Extra
grade_id int(11) NO Primary auto_increment
frn_course_id int(11) NO Foreign
frn_student_id int(11) NO Foreign
grade_percent decimal(5,2) NO
Enforce the following foreign keys:
• course_instructor Table:
o frn_course_id to the course_id column in the course table
o frn_instructor_id to the instructor_id column in the instructor table
• course_student Table:
o frn_course_id to the course_id column in the course table
o frn_student_id to the student_id column in the student table
• grade Table:
o frn_course_id to the course_id column in the course table
o frn_student_id to the student_id column in the student table
Set each of the foreign keys to RESTRICT on Update and RESTRICT on Delete.
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 5 of 14
Here is an overview of the tables, columns and foreign keys:
Populate Tables with Data:
IMPORTANT: Capitalize the first letter of each student’s first name and each instructor’s first
name.
Example: Evangelica not evangelica.
student
student_id first_name last_name date_of_birth
1 Ally Brannon 1997-05-06
2 Evangelica Cruz 1998-04-21
3 Ema Kimberly 2000-01-18
4 Zed Notaro 1999-10-01
5 Tulio Steffen 1998-08-08
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 6 of 14
instructor
instructor_id first_name last_name date_of_birth
1 Collyn Kistner 1985-07-24
2 Linnea Nosek 1975-12-30
3 Jayson Phelps 1976-12-17
4 Duston Bunschoten 1981-08-11
5 Chen Esparza 1982-02-02
course
course_id course_code start_date end_date description
1 BIO 111 2018-01-05 2018-04-15 Introduction to Animal Biology
2 BIO 112 2018-01-05 2018-04-15 Introduction to Plant Biology
3 CHEM 121 2018-01-05 2018-04-15 Organic Chemistry
4 MATH 152 2018-01-05 2018-04-15 Calculus 1
5 MATH 155 2018-01-05 2018-04-15 Calculus 2
course_instructor
course_instructor_id frn_course_id frn_instructor_id
course_student
course_student_id frn_course_id frn_student_id
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 7 of 14
grade
grade_id frn_course_id frn_student_id grade_percent
Users, Roles and Privileges:
Each student and instructor must have a MySQL login so they can query their information.
This will mean they will be able to login to MySQL using the command:
• For students: mysql -u <student.first_name> -p
• For instructors: mysql -u <instructor.first_name> -p
For example: mysql -u Evangelica -p
Allow all users the ability to log in from any host (@‘%‘).
For simplicity sake, give all users (students and instructors) a password of ‘password’.
IMPORTANT: Capitalize the first letter of each student’s name and each instructor’s name.
Example: Evangelica not evangelica.
All students will be given the student role.
All instructors will be given the instructor role.
Give the instructor role SELECT, INSERT, UPDATE, DELETE privileges on all tables in the school
database.
Give the instructor role SELECT privileges on the instructor_schedule view in the school
database.
Give the student role SELECT privileges on the student_schedule and my_grades views in the
school database.
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 8 of 14
Views:
We want students to be able to see their own grades but we don’t want them to see other students’
grades. There is a function built-in to MySQL that can retrieve the name of the currently logged in user.
It is called USER(). Calling this will return the user and the host of the current user. Example:
When logged in as Evangelica by running: mysql -u Evangelica -p
SELECT USER();
Returns:
[email protected]’localhost’;
We can strip off the host portion of the USER() function to return just the user portion using the
substring_index() function using the ‘@‘ symbol to remove the host portion, like this:
SELECT SUBSTRING_INDEX(USER(), "@",1);
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 9 of 14
Create a view for the students’ grades called my_grades that has the following columns:
• Student’s first name
• Student’s last name
• The course code of the course taken
• The grade percent
Restrict the output to only the rows that belong the currently logged in student.
Example 1 – Evangelica:
Example 2 – Tulio:
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 10 of 14
Create a view for the students’ schedule called student_schedule that has the following columns:
• Student’s first name
• Student’s last name
• The course code of the course taken
• The course start date
• The course end date
Again, restrict the output to only the rows that belong the currently logged in student.
Example 1:
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 11 of 14
Create a view for the instructors’ schedule called instructor_schedule that has the following
columns:
• Instructor’s first name
• Instructor’s last name
• The course code of the course taken
• The course start date
• The course end date
Again, restrict the output to only the rows that belong the currently logged in instructor.
Example 1:
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 12 of 14
Data enforcement:
You must ensure that the grade_percent column in the grade table never contains a negative
grade percent (grade of < 0%) nor a grade of > 100%. Use triggers and stored procedures to enforce the
proper grade values.
If an attempt the add or change the grade_percent to a negative value, abort the operation with
the following error:
Check constraint on grade.grade_percent failed – grade_percent
<insert_grade_percent_here> must be greater than 0%.
(Note replace <insert_grade_percent_here> with grade that was negative.)
Example:
Check constraint on grade.grade_percent failed – grade_percent -10.2
must be greater than 0%.
If an attempt the add or change the grade_percent to a value greater than 100%, abort the operation
with the following error:
Check constraint on grade.grade_percent failed – grade_percent
<insert_grade_percent_here> must not be larger than 100%.
(Note replace <insert_grade_percent_here> with grade that was too large.)
Example:
Check constraint on grade.grade_percent failed – grade_percent 125
must not be larger than 100%.
Running the MySQLDump:
You will need to run the following command and submit the mysqldump file School.sql as part of
your submission:
mysqldump -u root -p school --routines > "School.sql"
The start of your output should look like this:
-- MySQL dump 10.13 Distrib 8.0.11, for Win64 (x86_64)
--
-- Host: localhost Database: school
-- ------------------------------------------------------
...
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 13 of 14
ER Diagram:
Using MySQL Workbench, reverse engineer the school database to produce an ER Diagram.
Expand the Index section of each table to show the primary and foreign keys on each table.
Take a moment to rearrange the tables so that the foreign key lines are not crossing each other and to
clean up the diagram.
Submit your screenshot:
Running the Verify Script:
You will need to run the following command and submit the file School_Verify_Results.txt as
part of your submission:
mysql -u root -p -t < "Assign #2 Verify.sql" >
"School_Verify_Results.txt"
IMPORTANT! Make sure you include the -t option when running the above command.
The start of your output should look like this:
--------------- --------------- -------------- ------------------ ---------------- --------------
| GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
--------------- --------------- -------------- ------------------ ---------------- --------------
| ‘student‘@‘%‘ | def | school | my_grades | SELECT | NO |
... ...
ACIT 3910 Database Administration and Management
Assignment #2
Patrick Guichon March 9, 2019 14 of 14
Marking Guide:
Criteria: Marks:
Create a database called ‘school’ 1 mark
Create the following tables:
• student
• instructor
• course
• course_instructor
• course_student
• grade
All tables must have the correct columns, data types, auto_increments, default
values and foreign keys as required.
5 marks /table
30 marks total
Reverse engineer the database using MySQL Workbench to produce an ER Diagram 5 marks
Tables are populated with the correct data. 1 mark /table
5 marks total
Create the following views:
• my_grades
• student_schedule
• instructor_schedule
Ensure that the views return the expected results. Views must show data only about
the currently logged in user and not any other users.
3 marks /view
9 marks total
Create the student and instructor users, roles and assign the correct privileges to
them. Ensure the roles are active for each user:
• Create users
• Create roles
• Grant Privileges
• Assign user to roles
• Activate roles for each user
Enforce that when a grade is added or changed, it must be within the range of 0.00%
to 100.00%.
5 marks
Total: 65 marks
Submission Requirements:
Submission: File name:
ER Diagram Screenshot School_ERD.jpg
MySQLDump File of the school database School.sql
Run the verify script and submit the results School_Verify_Results.txt
因为专业，所以值得信赖。如有需要，请加QQ：99515681 或邮箱：[email protected]
微信：codehelp