Remarkable University is implementing a new student grading system. The scenario is the same as Assignment 1.
You will need to implement the database, create users and grant privileges to the users, perform SQL injection testing, and develop backup strategies for the database.
You may work in a group of up to 3 members, and ONE member of each group needs to submit on [email protected]:
- A pdf report detailing your work including (but not limited to):
- Explanations of your database design choices, and screenshots of your database structure and data
- Descriptions of user privileges (using access matrix) with explanations, and screenshots of the SQL commands you used
- Descriptions of your SQL injection test (in steps) with screenshots and explanations of your observations
- Descriptions of your backup strategies with clear justifications
- An Implementation History as follows
- An ER diagram (pdf) for the database design
- An SQL script including all the commands for database implementation, user creation, and privilege granting
You will receive up to 1% for the presentation of your report (structure, grammar, …).
PART A. Database Implementation (8%)
1. Database Design (4%)
Use the sample data provided in the Appendix and design the database using ER diagrams.
The sample data do not reflect the table structure in the database and you will need to revise the structure of the tables. You need to describe the improvement you made and explain your design choices.
The sample data are not complete either. For example, they do not describe the teaching and managing relationships: academic staff teach courses and admin staff manage courses and enrolments. You need to create those data if required. In particular,
- An academic staff may teach many courses
- A course must be taught by one or more academic staff
- Courses and enrollments must be managed by one or more admin staff
- A student can enroll in one or more courses
- A grade must correspond to an enrollment
- A student may have multiple enrollments in the same course
You also need to present the new tables after your improvement and data completion.
2.SQL Implementation (4%)
Create the tables based on your ERD and insert the provided data using a SQL script, including proper datatypes and integrity constraints.
PART B. Users and Privileges (9%)
1. Users (2%)
Create a sufficient number of users (at least six) to demonstrate different sets of privileges. For example, at least two students, two academic staff with different privileges and at least two admin staff in charge of courses and enrolment respectively.
2.Assign Table-level Privileges (4%)
Assign privileges to each user. Explain why such roles need to have the access on certain tables. There are some pre-defined rules:
- Staff should have restricted access according to their roles/positions. For example, admin staff managing enrollment can modify only enrollment information, and admin staff managing courses can only modify course information
- Academic staff and students can see information about courses but cannot edit
- Academic staff can see the names and genders of the students but not their birthdays or phone
3.Create Views and Related Privileges (3%)
Create at least two views with one of them including a join of two or more tables. Explain your choice and explain the importance of the views. Assign the views to appropriate users.
- Students should only see the grades that belong to themselves (and read only).
- Academic staff can only see the enrolment of the courses they
- Academic staff can only modify grades of the course they
PART C. SQL Injection Test (3%)
You will use the Web interface we provide to access the database you developed. In particular, you will try to modify the tables with SQL injection.
- Go to codeanywhere.com and open the editor. Start the container that hosts your
- Download the index.html file from the course website and save it under the container. It provides the Web form interface for entering the student
- Do the same for the main.php file. It is a PHP script used to connect to the database. Note that it makes the following assumptions:
- It connects to the database as the root user whose password is
- The database name is “Assignment2”.
- The table name is “student” and the column names and order are: student_id, first_name, last_name, DOB, sex,
If your database has different setting, you need to make appropriate changes to the main.php file. You should not make other changes to the file.
- Run the project by clicking on
- You will enter some input into the form and try to modify the database (e.g., delete the student table).
- Explain what you are trying to input and what are the expected
If you receive the following error message, then your SQL injection is NOT successful. You will have to keep trying.
PART D. Database Backup Strategy (4%)
Suppose the DBA wants to also use a cloud-based storage service to conduct and store regular backups. Define a backup and retention strategy:
- Why is a backup strategy important?
- Which portions of the database should be backed up, and how often?
- For each portion that is backed up, how many backups should be retained before the newest backup overwrites the oldest?
Implement backup and recovery (export and import). Provide the .sql file from the export, drop the database, and provide screenshot after the recovery.
PART E. Advanced Data Management (for 7623ICT students only, 5%)
MySQL is a popular choice for information storage and processing, but many other information storage and processing methods exist. Pick any three of the following
- Cloud-hosted databases
- NoSQL databases
- Hadoop distributed file system databases
- Graph databases
and answer the following questions with respect to your three chosen systems:
- What are the benefits and drawbacks of each system?
- For which kinds of data would you choose each system?
What security features are available for each system?
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:dmxyzl003
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: [email protected] 微信:itcsdx