联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp

您当前位置:首页 >> Database作业Database作业

日期:2019-10-30 10:17

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:

Evangelica@’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


版权所有:留学生编程辅导网 2018 All Rights Reserved 联系方式:QQ:99515681 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。