Database Design For Student Grading System Essay.

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:

1. A pdf report detailing your work including (but not limited to):

1) Explanations of your database design choices, and screenshots of your database structure and data

2) Descriptions of user privileges (using access matrix) with explanations, and screenshots of the SQL commands you used

3) Descriptions of your SQL injection test (in steps) with screenshots and explanations of your observations

4) Descriptions of your backup strategies with clear justifications

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

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 adminstaff managing courses can only modify course information
• Academic staff and students can see information about courses but cannot edit it.
• Academic staff can see the names and genders of the students but not their birthdays or phone numbers.

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 teach.
• Academic staff can only modify grades of the course they teach.

The student grading system had a lot of repetition of data and it had to be normalized for it to be efficient and  save on storage space.

Normalization of the tables to 3NF enables the data to be efficiently queried through joins of the parent tables.

Some tables had to be introduced and a foreign key constraint put on the child tables to maintain data integrity.

Some of the new tables include:

1. Position Table, This table keeps the information about the various positions that the academic staff can hold. An academic staff can hold one position at any given time. I.e. Professor, Asst. Professor, Lecturer etc.
2. Duty Table, This table keeps information about the various duties admin staff should undertake, they include: course duty and enrollment duties.
3. Campus Table, This table keeps information about all the Campuses available for the students.
4. Roles Table, This table stores information about the role of the user interacting with the Grading System.

These tables are base tables (Parent tables) that have to be populated with data first, so that they can be referenced in the child tables that depend on them for data.

Creation of these 11 tables can be created through a data definition Language (DDL) Script, attached is the script for the assignment2 Schema.

Running this script recreates the Assignment2 Schema.

Below are some screenshots of the data including their structure:

1.3 The student table with sample data added.

1.3 The enrollment table with sample data included. (student, campus and course data are referenced by foreign keys)

1.4 The grades table, references the enrollment table and accords each enrollment record with a grad

1.5The users table, this depicts all the user of the system and what roles they have within the system.

This table will define the privileges accorded to the users and what they have access to within the grading system.

1.6 The roles table, table depicting the various roles within the system. Role 1 being the system DBA.

Descriptions of user privileges (using access matrix) with explanations, and screenshots of the SQL commands used.

The student grading system has a variety of users that have different roles and ways of interacting with the system.

The types of users in this system include:

Database Developer: This user has access to all the functions of the database and can alter the structure of the database.

Roles include:

• Altering the schema structure i.e. create, drop, alter the tables, views, indexes, triggers used by all other database users.

Database administrator: This user has access to administrative privileges and usually has no limits within the schema operations.

Roles:

• creating users
• Granting read, update and delete privileges
• Access to insert, update, delete records fro any table
• Backup data
• Shutdown and startup databases.

Database user:

This mainly refers to the application that resides on top of the schema and interacts with the tables and views created.

Roles:

• Select, Insert, Update, Delete information in database

Application users: These users interact with the application and have set role and privileges on certain tables and views within the schema. In this case we have three users Academic staff, Admin staff and Students.

Admin Staff:

• Update information on student table cases where edits arise.
• Update enrollment table with academic staff to teach the courses.

Application Administrator:

Roles:

• Create and delete users
• Add Roles and assign privileges to these roles.
• Insert and update data in tables i.e. Campus, role, position, duty tables

Creating Users

Student User.

Descriptions of your SQL injection test (in steps) with screenshots and explanations of your observations

• Register to codeanywhere.com and open the editor
• Create a new container called nerd
• Upload the main.php and index.html file
• Upload successful
• Edit the main.php
• Edit connection parameters and run the file.
• Allow popups
• An html form with entry fields appears.
• Enter the relevant data and press OK

User Privileges and Access Matrix

The form displays the connection was successful and shows the data that’s sent.

• Evidence of the data in the database

Backups are necessary to avoid loss of data in the event of a disaster that might cause alteration or loss of data from the database. Backups can be used to restore that data and continuation of database operations.³

A backup and retention strategy is a formulation by the dba on how often the backups are to be done and where that data is to be stored.

The frequency of the backups depends on the rate of how data is accessed and modified it can range from hourly for production databases to daily or weekly for much slower databases.

The backed up data can be retained for a set period of time or till the next backup is done.

The other factor that is considered is the type of storage where the backup is to be kept. This can be on local storage (primary) which provides for instantaneous restoration. It can be archived for long term persistency in secondary storage devices (local or remote storage locations) or in distributed storage. Online (cloud) backups are on the rise and becoming increasingly popular due to the instant availability of data and access to data even after catastrophic events. Online backups are only restricted by bandwidth.

Which Portions of the database should be backed up and how often?

High traffic tables like the enrollment table grade table can be backed up once every day to maintain consistency of the data in case of data failure. The backups can be kept for 10 days till another backup is done.

Low traffic tables that are rarely accessed can be backed up weekly using additive backups and a full backup (archive) done every year (52 weeks.

Implement backup and recovery (export and import). Provide the .sql file from the export, drop the database, and provide screenshot after the recovery.

Restored database structure and data.

MySQL

Cloud Hosted Databases

NoSQL databases

Advantages

Ease of Use:  MySQL is very easy to use, this is due to a factors like a large community following since it has been an industry standard. The software is easy to install, the interface intuitive to use and a host of third party tools to enable interaction with the database.

Support: Although it has a gentle learning curve MySQL users will generally run into problems or issues and they will always find massive support in online forums from other users, this is due to its popularity among web developers.

SQL Injection Testing

Price: MySQL is fairly priced, this refers to the non-open source version, because you can get a version of MySQL for free or bundled with other software, but for fairly large systems access to support will cost a fair price compared to other solutions.

Disadvantages

Scalability: MySQL does not work well for large enterprises due to issues in scalability. Its inability to handle concurrent and massive request brings it to a stand. Thus scalability in MySQL is a determining factor while creating large systems.

Functionality: MySQL databases lack in many features that require add-ons and third party applications to recreate that functionality, some of the issues include fault tolerance, data warehousing and reports on performance.

Support: MySQL being once open-source was acquired by Oracle that have halted the development of the software significantly compared to its earlier days.

Used for: small websites, small web apps.

Advantages

Ease of setup: Depending on the type of cloud storage product the cloud solutions providers offer, a major advantage is the time saved to get the system running, its coupled by factors like; no – installations required, no hardware and software maintenance involved.

Performance: Cloud based software run on high end servers that guarantee up-time and impressive speeds to access data on your schema. This coupled with performance tuning of the database gives optimum performance to the end users.

Accessibility: Cloud services enable the  users to access their information from anywhere as compared to standalone servers, this coupled with high scalability makes them ideal for a number of uses but work well in real-time data gathering. 

Disadvantages

Scalability: cloud solutions are not ideal for very large enterprises in the long term as compared to hosting your own servers. It’s a fine balance between the package or price offered by the service provider and the size of the database. For very large users it may be cheaper to buy, set-up and hire administrators and incur a high one – off cost and enjoy low maintenance cost therein as compared to high periodical invoices that increase as storage capacity increases.

Connectivity: Although the service provider provides the service at high bandwidths and 24 – hr. up-time this can be limited by the users bandwidth or lack of thereof.

Security and compliance: Due to security reasons like corrupting other users’ data, security protocols in cloud based services can cause bottlenecks since all data and any apps installed have to scrutinized for threats.

Backup and Recovery

Contracts: Due to the nature of the business model, contracts or plans have to be entered with service providers that can cause loss of capital when better services are offered and providers have to be changed. Scalability issues can bring unpredicted costs that were not visible when initially entering the contract.

Used for: Mobile app data storage, web apps, website hosting.

Advantages

Administration: NoSQL databases require little hands-on maintenance as compared to relational counterparts that require fully trained DBAs just to get them up and keep the running. NoSQL databases include auto-repair capabilities, data distribution, performance and tuning, simplified data models making them easier to use and maintain.

Cost: NoSQL can easily be deployed in cheap commodity clusters, as transaction and volume increases this in turn makes cost of storage less where you can process and store more data.

Scalability: NoSQL databases were made for transparent expansion, designed for use with low-cost commodity hardware makes them uniquely advantageous where scalability is needed as not much cost goes to adding a new stack.

Disadvantages

Support:  since NoSQL database is a new technology it hasn’t been taken up by many users and this in turn means there is not much in terms of community support as compared to relational databases that were in used more than 20 years ago.

Diversification: Being a relatively new technology there are many types of NoSQL systems and not much uniformity among them this brings a lot of variables like performance , flexibility , scalability  and complexity while choosing a vendor.

Used for: Distributed systems.

MySQL:

The basic security feature that MySQL offers is grants and privileges this ensures that only specific users have access to tables they are allowed interaction with. It deters unauthorized access of other information stored within the database.

Connection encryption, through the use of the SSL library MySQL is able to provide encryption to both client and server through the use of private keys and certificates.

MySQL provides users with database logs that can be analyzed to determine if there has been any malicious activity and for analysis in case an irregularity is discovered.

MySQL can also ban remote systems from access after several unsuccessful attempts (set by admin) to connect. The banned client will receive a “Host is blocked” error message and then disconnected from any further access. This helps in avoiding DoS (Denial of Service) attacks.

Intrusion Detection System, cloud storage providers have IDS systems that can detect, track and record all types of intrusion attempts. This coupled with updates in the security rules for their firewalls can counter threats and ban malicious IP Addresses.

Firewalls, these can be perimeter firewalls that check each network packet data to ensure data integrity before access to the database and also includes internal firewalls for the applications and database to block any malicious information between the application/database and the user.

Data encryption; some sensitive information can be encrypted then stored, this maintains that information although stolen cannot be used by the parties unless they have decryption keys.

Source of users: NoSQL databases can determine which users have access to the database, they can verify connecting clients from whitelisted IPs and let them perform permitted operations.

Authentication, authentication systems through usernames and passwords (basic auth) this determines the actual identity and role of users.

Logs, this is enabled for auditing, a native audit enables one to track access and operations performed on the database.

Data on NoSQL databases can be encrypted on network and on disk (protection of data at rest) to prevent unauthorized access and use.