You are given the following scenario and set of requirements and are required to 1) construct a spreadsheet based application from these requirements, 2) derive a set of relational schemas and construct entity relationship models and 3) translate these models into a database application. You are then required to produce a report that describes and demonstrates each of these tasks.

Learning Outcomes to be assessed

1. Construct a spreadsheet based application from a given set of requirements.

2. Create a logical and physical entity relationship model from a given set of requirements.

3. Construct a database based application from a logical and physical entity relationship model.

Details of the task

A fictional University needs to report on various aspects of their institutional systems, using both spreadsheets and databases. Your task involves implementing a spreadsheet application to report on student attendance/results, modelling their library system, implementing the library system as a database and then producing a professional report that details these activities.

Part 1

Firstly, you are asked to produce a spreadsheet that models student attendance/results over a semester. The data you are reporting on corresponds to a single semester, which contains 11 teaching sessions (i.e. 11 weeks). If a session has been attended, it is marked with a 1. If a session hasn't been attended, it is marked with a 0. The marks for three assignments are also reported in columns R - T.

Download the 4104COMP_Coursework 1Ref_part 1.xlsx document from Canvas and use this document to complete this spreadsheet so that attendance and marks data can be tracked and reported. The spreadsheet will need to include the following functionality:
1. Using appropriate formulas/functions, calculate the following:
a. In cell B102, calculate the total number of students in the class
b. In row 102, calculate the percentage of students that attended each weekly session
c. In column P, calculate the total number of sessions each student has attended over the course of 11 weeks
d. In column Q, using your calculations from c), calculate the percentage of attendance for each student
e. In column U, using the data from AS1 - AS3 (column R - T), calculate the overall average that each student has obtained
f. Complete the Statistics Table, at the bottom of the sheet, by calculating the average, standard deviation, median, minimum and maximum (rows 103 - 107) for AS1, AS2, AS3 and the Overall Average columns

2. Apply Conditional Formatting rules to satisfy the following conditions:
a. All instances of non-attendance during weeks 1 - 11 should be red
b. All instances of attendance during weeks 1 - 11 should be green

c. All instances in columns AS1 - AS3 whereby a student has failed an individual assignment (i.e. mark is below 40) should be red
d. All instances whereby a student has achieved an overall average of less than 40 should be grey

3. Plot the following two graphs:
a. A scatter plot of the Attendance % against the Overall Average
b. A column chart to depict the Attendance per week (E102 - O102)
Both graphs should be suitably formatted and include appropriate axis titles and main title

4. The entire spreadsheet should also be suitably formatted.

Part 2

Secondly, the University library needs to keep track of the books that have been loaned to students and record their details in a database. You can assume that:
• A book can be associated with numerous loans (obviously not at the same time).
• Each book has a numeric identifier (ISBN), title, lead author, publication year, and associated subject.
• The subject records such data as an ID and name.
• Students are assigned to loans. It is sufficient to record the student's ID, name, address, phone number and email.
• A student can make numerous loans.
• Each loan also has an associated ID, book ISBN and STUDENT ID, as well as the start/end date and late fees of the loan.

Your task includes:

1. Using the information and raw sample data in Appendix A, and the un-normalised relational schema (ONF) in Appendix B, derive a set of relational schemas that are (where appropriate) in 3NF. State any assumptions/justifications for the design decisions that you make.

2. Using your normalised data from 2.1), produce a Conceptual Entity Relationship Diagram
(ERD), using Crow's Foot Notation, which illustrates appropriate entities and relationships.

3. Using your Conceptual ERD from 2.2) transform this into a Logical ERD, using Crow's Foot Notation, which illustrates appropriate entities, attributes, relationships and primary/foreign keys.

4. Using your Logical ERD from 2.3, transform this into a Physical ERD, using Crow's Foot Notation, which specifies appropriate table names, column names, relationships, primary/foreign keys, data types, length and nullability.

5. Using your Physical ERD from 2.4 as the basis for your implementation, implement the database for the University library system using Microsoft Access. The database should be populated with the raw sample data from Appendix A.

6. Once the core functionality of the database has been developed, you should attempt to extend the database by investigating and incorporating additional functionality to enhance your application, such as:

Report
You must then produce a professional report that documents your development activity for parts 1 and 2. Your report must contain the following:

1. A description of the implementation process that was undertaken to complete the spreadsheet from part 1. This includes:
a. A copy of the completed spreadsheet
b. Documentation of the formulas/functions that you have used to complete part 1.1
c. Documentation of the Conditional Formatting that has been applied (for part 1.2)
d. Both graphs from part 1.3

2. Documentation of the process that was undertaken to complete part 2. This includes:
a. Relational schemas from part 2.1
b. Conceptual Entity Relationship Diagram from part 2.2
c. Logical Entity Relationship Diagram from part 2.3
d. Physical Entity Relationship Diagram from part 2.4
e. Documentation of the implementation process that was undertaken to implement the database in Microsoft Access (from part 2.5)

Your report should be suitably formatted/presented, structured logically and be free from grammar and spelling errors. To prepare your report, you are advised to use a word processor (such as Microsoft Word or OpenOffice/LibreOffice) that supports spelling and grammar checking.

Attachment:- Data Modelling Scenario Referral.rar