Database Case Study Report TIMES UNIVERSITY (TU) currently uses a paper‐based manual process for managing applications for special consideration (SC), the process for students to request special consideration of an assessment deadline, or arrangement of alternative assessments.

The university is planning to automate the process, and as a database designer, you are going to design the back-end database for the system. The current process is detailed below:

To apply for special consideration (SC) of an assessment, students download the special consideration form from the TU Website and then fill out the sections allocated for the student, which are then sent to the Learning Facilitator (LF) along with the supporting documents via email. There are 6 sections allocated for the student, they are:

• Section 1: Personal details
• Section 2: Modified assessment arrangement requested
• Section 3: Reason for application
• Section 4: Supporting documentation
• Section 5: Assessment or examination affected
• Section 6: Declaration

Sections 2 and 4 have a number of options for the students to tick depending on their circumstances.

Likewise, there is also a section that is required to be filled by the LF, and alternatively in some cases to be filled by the Program Director.

To get a good understanding of the data requirements, please check the SC form currently used by the university. However, you should note that the form is not a full reflection of the data requirements of the new system the university is planning to implement.

SC form acceptance works by the following process:

when the LF receives the SC form, they are responsible for checking the form for completeness and deciding if the student is eligible for an SC.

If the student is eligible for SC, the LF approves the form by filling in the details allocated to the LF, which includes the new submission date with signature.

If declined, the LF would add reasons.  The form is then sent to the Academic Services Officer (ASO) team who is dedicated to processing these applications in a timely manner. They work on different campuses, and there is at least one dedicated ASO for each campus.

Final processing of the form, which includes checking the form for completeness and supporting documents is carried out by the ASO, who then notifies the outcome to the student and LF via email.

Finally, a request is sent to the Blackboard (BB) team to adjust the due date of the assignment accordingly in BB for that student.

Typically, the form is processed on the same day, and the student is informed of the outcome within 1-2 days if all the supporting documents are available.

The LF is responsible for ensuring that the application is complete with supporting documents before sending them to the ASO team. If not, the ASOs would not be able to process the application. Forms with extension periods longer than 10 days would require the Program Director’s (PD)/Course Coordinator’s approval in addition to the LF’s approval.

The data in the forms are recorded in an Excel spreadsheet shared on a network drive.

When the new system is designed, the university also wishes to make the application process more flexible to allow multiple SC requests to be submitted at the same time in one form, instead of the current process where the student is required to fill out separate applications for each subject.

Likewise, students can also apply for SC for multiple assignments of the same subject in a single application. For example, in some circumstances, a student may want to apply for an SC for all the assessments of one or more subjects. To implement this new feature, instead of one authorized person (LFs/PD) approving the application, one or more approvers may require approving the requests. For example, if the student applied for SC for assignments of two different subjects, then each request would be approved by the relevant LF delivering that subject.    The SC approval process is one of the processes that get periodically audited by the quality assurance team. At any time, the team could request to provide reports of the SC approval process.  The reports include, but is not limited to, the following statistics:

• The number of applications received each semester, by course and vertical.
• The total number of applications that do not conform to the SC policy, and the LF who approved them.
• The number of SC forms approved come after a particular assessment due date. • The total number of students by gender, campus, and mode (F2F/online).
• The total number of forms rejected, and likewise, the total number accepted.
• The average number of days taken to process the form

Based on the information provided in the case study, create a functioning database using MySQL that can be queried to meet the requirements of the business.

4. The database case study report should include the following elements:
5. a) ER diagram, relational model and associated business rules and assumptions
6. b) SQL commands to:

• Create tables and Insert sample data rows into each table
• SQL queries to provide business insights. At least one of each of the following is required:
• SELECT query with a condition
• GROUP BY query
• JOIN query o NESTED query for each query explain its business value i.e. what business question does the result answer? Does it save money/more efficient process?

1. c) At least two visualizations with an explanation of its business value. For each visualization explain its business value. E.g., does the visualization highlight savings in time or money?

Referencing It is essential that you use appropriate APA style for citing and referencing research.