BraviaResearch.com

You are here: Home Resources Importance of Data Management

Importance of Data Management

Importance of Data Management - 4.0 out of 5 based on 1 review
4.5 1 1 1 1 1 1 1 1 1 1 Rating 4.50 (1 Vote)

Executive summary

A database is an important tool for data management in any institution, government, organization, or corporation. The Nottingham University Business School has a big population and requires the use of a database to manage student activities and performances. Institutions with a higher number of students and staff members like the Nottingham University Business School need efficient databases for efficient retrieval of query results and posting of information for storage.

This report gives the procedure used in the designing of the Nottingham University Business School database. This database will mainly help in storage of students’ records like degree course, module, tutor, corresponding academic divisions. The system will also help in quick retrieval of this information and analysis of the performances of different groups of students. This report explains the process for all the above functionalities and is implemented using SQL.  

Introduction

A database is an important storage tool and helps institutions, organizations, governments, and corporations to store large volumes of data. It helps to save on physical storage space in offices and is very efficient for use. In addition to this, a database helps to retrieve specific information for analysis purposes.The University of Nottingham is one of the best universities in the country and the world. It has a number of campuses in the region as well as in other countries. The Nottingham University Business School is located in Jubilee Campus. It has helped many students in attaining their professional dreams and desires in business. Over the years, the school has expanded its intake of students and increased the number of modules offered. This has necessitated the need for an efficient database and will mainly be used for storage and retrieval of student information and performance.  

The presence of a database in the school will have very many positive impacts to the institution. The first benefit of the database is to increase on the storage space in the offices, which can then be used for other purposes. In addition to this, it will make data storage, manipulation, and retrieval very easy for the working staff. This will increase the efficiency of the facility, which will enable for more admissions and additional modules. The database will mainly target students’, tutors’, and modules’ information, which will be stored in a relational database. This database will have easy to use interfaces and will be very efficient in performance for better performance.

Reports

Alphabetical list of the final mark of each student for a single module

This question requires outputting one module’s overall mark while showing students’ first and last names in alphabetical order. To show the process the Accounting Information Systems module is used to output all the students’ marks. The first step is to create a table (AIS_CW), which outputs the student’s ID, marks, first and last names. The next step is to select the students’ IDs from the table students. The coursework title for AIS from submitted_by is then selected to be able to select the students’ marks for the AIS coursework. After that, multiply the marks by the weighting of the coursework to be output in to a new column labelled total cw mark. The same method was used to create table “AIS_EX” which showed the weighted exam marks, students IDs, and their names.

To output the overall students marks, inner join the two tables “AIS_CW” and “AIS_EX” where the exam marks and the coursework will be added to each other to create the final mark for students and the students’ names and ids were brought in through both tables. To ensure that it is in alphabetical order, sort them by family names and freeze the column.

2.1.0 Query and results

SQL statements

Table AIS_CW:

SELECT Students. Student_ID, submitted_by.Coursework_Title, Mark*Coursework AS total_cw_mark, Students.First_Name, Students.Last_Name

FROM submitted_by, [Module], Students

WHERE (((Students.Student_ID)=submitted_by.Student_ID) And ((submitted_by.Coursework_Title)="Accounting Information Systems 2000 essay") And ((Module.Module_Code)="N12131"));

Table AIS_EX:

SELECT Students.Student_ID, Sat_by.Exam_Code, Mark*Exam AS total_exam_mark, Students.First_Name, Students.Last_Name

FROM Sat_by, [Module], Students

WHERE (((Sat_by.Exam_Code)="N12131-E1") And ((Module.Module_Code)="N12131") And ((Students.Student_ID)=Sat_by.Student_ID));

Table QA:

SELECT QA_CW.total_cw_mark+QA_EX.total_exam_mark AS Final_Mark_of_N12131, QA_CW.Student_ID, QA_CW.First_Name, QA_CW.Last_Name

FROM QA_CW INNER JOIN QA_EX ON (QA_CW.Student_ID)=QA_EX.Student_ID;


2.1.1 Interpretation of results

In order to show the execution process for this question, the Accounting Information System is used as the module. The first thing in this case is to select the coursework marks from the table submitted_by, then multiplied the marks by the weight of the coursework which is 0.2 to create  table (AIS_CW) to show student ids, first and last names, and their marks. The same operation was used to extract weighted exam marks from the table sat_by and helped to create table (AIS_EX). The two tables (AIS_CW and AIS_EX ) are then inner joined and added more information, such as the students’ first and last name  and  the module code to create table (QA). The last process is to sort the resultant table in alphabetical order and freeze the column to be sure it will remain unchanged

Summary statistics for all modules

This question is complicated and involves selecting every module for each student and for all the students. It is important to have in mind all the exams and all the coursework done for every student. There is also need to note the weightings for every module and track the exams and coursework for every module. This is because some modules have an exam as the only assessment while others require coursework only.

2.2.0 Query and results

SQL statements Table ALL_EX:

SELECT MODULE_CODE, STUDENT_ID, WEIGHTING*MARK AS EX_MARK

FROM EXAM, SAT_BY

WHERE EXAM.EXAM_CODE=SAT_BY.EXAM_CODE;

Table ALL_CW:

SELECT COURSEWORK.MODULE_CODE, SUBMITTED_BY.STUDENT_ID, WEIGHTING*MARK AS CW_MARK

FROM COURSEWORK, SUBMITTED_BY

WHERE (((COURSEWORK.COURSEWORK_TITLE)=[SUBMITTED_BY].[COURSEWORK_TITLE]));

Table CW_EX:

SELECT [ALL_CW].STUDENT_ID, [ALL_CW].MODULE_CODE, CW_MARK+EX_MARK AS MARK

FROM ALL_CW, ALL_EX

WHERE ALL_CW.MODULE_CODE=[ALL_EX].MODULE_CODE And ALL_CW.STUDENT_ID=[ALL_EX].STUDENT_ID;

Table FINAL_MARK:

SELECT  ALL_CW.STUDENT_ID, ALL_CW.MODULE_CODE,CW_MARK+EX_MARK AS MARK

FROM ALL_CW, ALL_EX

WHERE ALL_CW.MODULE_CODE=ALL_EX.MODULE_CODE

AND ALL_CW.STUDENT_ID=ALL_EX.STUDENT_ID

UNION ALL

SELECT ALL_CW.STUDENT_ID, ALL_CW.MODULE_CODE, ALL_CW.CW_MARK AS TOTAL_MARK

FROM ALL_CW LEFT JOIN CW_EX ON (ALL_CW.MODULE_CODE=CW_EX.MODULE_CODE) AND (ALL_CW.STUDENT_ID=CW_EX.STUDENT_ID)

WHERE CW_EX.STUDENT_ID IS NULL

AND CW_EX.MODULE_CODE IS NULL

UNION ALL SELECT ALL_EX.STUDENT_ID, ALL_EX.MODULE_CODE, ALL_EX.EX_MARK AS TOTAL_MARK

FROM ALL_EX LEFT JOIN CW_EX ON (ALL_EX.STUDENT_ID=CW_EX.STUDENT_ID) AND (ALL_EX.MODULE_CODE=CW_EX.MODULE_CODE)

WHERE CW_EX.STUDENT_ID IS NULL

AND CW_EX.MODULE_CODE IS NULL;

2.2.1 Interpretation of results

In order to extract and produce the required statistics, the relationship between exam and coursework had to be determined. There were 15 students, 5 for each year; each student took 8 modules a year, which makes up 240 modules taken in total. Among these 240 modules, there were 120 requiring coursework and exams, 115 exams only and 5 coursework only. Select each part of these and form ALL_CW, ALL_EX and CW_EX. The graph below further explains the concept: For the results of the operation, use a left join and union to put them together as table FINAL_MARK, with these processed data we can use SQL functions and get the standard deviation, average, max, and min. This can be retrieved through a query named MODULE_STATISTICS. The final step includes adding module convenor name and the division code.

Is the average mark for level 2 modules lower or higher than level 3?

The data required for this question is half processed in question B and will not require a lot of processing. In question B, the average marks for every module are given in table QB, but the information concerning the level is not included. This part links them together.


 

2.3.0 Query and results

SQL statements Table QB:

SELECT MODULE.MODULE_CODE, MODULE.MODULE_CONVENOR_ID, FIRST_NAME, LAST_NAME, MODULE.DIVISION_CODE, AVG_MARK, STDEV_MARK, MAX_MARK, MIN_MARK

FROM [MODULE], MODULE_STATISTICS, CONVENOR

WHERE MODULE.MODULE_CODE=MODULE_STATISTICS.MODULE_CODE

AND MODULE.MODULE_CONVENOR_ID=CONVENOR.MODULE_CONVENOR_ID;

Table LEVEL_MARK:

SELECT MODULE_LEVEL, AVG_MARK

FROM QB, [MODULE]

WHERE [QB].MODULE_CODE=MODULE.MODULE_CODE AND MODULE_LEVEL>1;

Table QC:

SELECT MODULE_LEVEL, AVG(AVG_MARK) AS LEVEL_MARK

FROM LEVEL_MARK

GROUP BY MODULE_LEVEL;

2.3.1 Interpretation of results

Since table QB did not include the level of every module, select the level column from table Module where the level >1 to create a LEVEL_MARK, which includes average mark of the module for level 2 and level 3. The next step is to average both level 2 and level 3 to see which level is higher (QC). From the results of the average mark for level 2 and level 3, level 3 is seen to be relatively harder.   

  1. Table of performance for the 2010 cohort students at the end of their degree

This question requires producing a table showing the performance of the 2010 cohort of students at the end of their degree. The table should also show their average, second and third year marks, and their final overall mark.

2.4.0 Query and results

                        SQL statements

Table YR3_STUDENTS:

SELECT STUDENT_ID

FROM STUDENTS

WHERE YEAR=3;

Table FINAL_MARK:

SELECT  ALL_CW.STUDENT_ID, ALL_CW.MODULE_CODE,CW_MARK+EX_MARK AS MARK

FROM ALL_CW, ALL_EX

WHERE ALL_CW.MODULE_CODE=ALL_EX.MODULE_CODE

AND ALL_CW.STUDENT_ID=ALL_EX.STUDENT_ID

UNION ALL

SELECT ALL_CW.STUDENT_ID, ALL_CW.MODULE_CODE, ALL_CW.CW_MARK AS TOTAL_MARK

FROM ALL_CW LEFT JOIN CW_EX ON (ALL_CW.MODULE_CODE=CW_EX.MODULE_CODE) AND (ALL_CW.STUDENT_ID=CW_EX.STUDENT_ID)

WHERE CW_EX.STUDENT_ID IS NULL

AND CW_EX.MODULE_CODE IS NULL

UNION ALL SELECT ALL_EX.STUDENT_ID, ALL_EX.MODULE_CODE, ALL_EX.EX_MARK AS TOTAL_MARK

FROM ALL_EX LEFT JOIN CW_EX ON (ALL_EX.STUDENT_ID=CW_EX.STUDENT_ID) AND (ALL_EX.MODULE_CODE=CW_EX.MODULE_CODE)

WHERE CW_EX.STUDENT_ID IS NULL

AND CW_EX.MODULE_CODE IS NULL;

Table YR3_STUDENT_MARK:

SELECT [FINAL_MARK].STUDENT_ID, MODULE_CODE, MARK

FROM FINAL_MARK, YR3_STUDENTS

WHERE YR3_STUDENTS.STUDENTS.STUDENT_ID=[FINAL_MARK].STUDENT_ID;

Table YR3_LEVEL:

SELECT STUDENT_ID, [YR3_STUDENT_MARK].MODULE_CODE, MODULE_LEVEL, [YR3_STUDENT_MARK].MARK

FROM YR3_STUDENT_MARK, [MODULE]

WHERE MODULE.MODULE_CODE=[YR3_STUDENT_MARK].MODULE_CODE;

Table QD:

SELECT T3.STUDENT_ID, SECOND_YEAR_MARK, THIRD_YEAR_MARK, OVERALL_MARK

FROM (SELECT T1.STUDENT_ID, SECOND_YEAR_MARK, THIRD_YEAR_MARK

FROM

(SELECT STUDENT_ID, AVG(MARK) AS SECOND_YEAR_MARK FROM YR3_LEVEL WHERE MODULE_LEVEL=2 GROUP BY STUDENT_ID) AS T1,

(SELECT STUDENT_ID, AVG(MARK) AS THIRD_YEAR_MARK FROM YR3_LEVEL WHERE MODULE_LEVEL=3 GROUP BY STUDENT_ID) AS T2

WHERE T1.STUDENT_ID=T2.STUDENT_ID) AS T3, (SELECT STUDENT_ID, AVG(MARK) AS OVERALL_MARK FROM YR3_LEVEL GROUP BY STUDENT_ID)  AS T4

WHERE T4.STUDENT_ID=T3.STUDENT_ID;

2.4.1 Interpretation of results

The first step is to select all year 3 students from STUDENTS as YR3_STUDENTS, and then added marks from FINAL_MARK to create YR3_STUDENT_MARK. To show the levels of the modules they took, select level from module to create YR3_LEVEL. The final step is to use the subselect statement to merge year2, year3, and overall marks in one table (QD).

  1. Do female students perform better than males?

This question uses the information and tables created in the previous sections to execute the querry for results. As such it does not require many lengthy procedures to retrieve the result of the query. The first step is to select the final marks based on gender(E1), and analyse the results in terms of standard deviation, average maxmium, and minimum(QE).

2.5.0 Query and results

SQL statements Table E1:

SELECT [FINAL_MARK].STUDENT_ID, GENDER, MARK

FROM FINAL_MARK, STUDENTS

WHERE [FINAL_MARK].STUDENT_ID=STUDENTS.STUDENT_ID;

Table QE:

SELECT GENDER, AVG(MARK) AS [AVG], STDEV(MARK) AS StDev, MAX(MARK) AS [MAX], MIN(MARK) AS [MIN]

FROM E1

GROUP BY GENDER;

2.5.1 Interpretation of results

For better explanation and interpretation of the results the results of the querry were tabulated and graphed in a column chart as shown below. The answer to the question is that female students did not perform better than males, but, males did slightly better.

  1. Is there a relationship between group size and performance?

The first step is to count the number of members in each group. After this, join both FM group and DDI group as F1. The next step is to obtain the group marks from submitted_by, to create F2, and combine these two as table QF.

2.6.0 Query and results

SQL statements

Table F1:

SELECT GROUP_NUMBER,COUNT(GROUP_NUMBER) AS GROUP_SIZE

FROM DDI_GROUPS

GROUP BY GROUP_NUMBER

UNION ALL SELECT GROUP_NUMBER,COUNT(GROUP_NUMBER) AS GROUP_SIZE

FROM FM_GROUPS

GROUP BY GROUP_NUMBER;

Table F2:

SELECT GROUP_NUMBER, AVG(MARK) AS CW_MARK

FROM SUBMITTED_BY

WHERE GROUP_NUMBER IS NOT NULL

GROUP BY GROUP_NUMBER;

Table QF:

SELECT F1.GROUP_NUMBER, GROUP_SIZE, CW_MARK

FROM F1, F2

WHERE F1.GROUP_NUMBER=F2.GROUP_NUMBER;

2.6.1 Interpretation of results

For better analysis and interpretation of the relationship between group size and performance, the following line charts give a better view of the results. From the charts, it is clear that only FM shows that  the smaller the group, the better the marks. From this comparison and the fact that there are only 2 groups, it can be concluded that there is no relationship between group size and preformance. 

  1. Does first year performance predict final years performance?

This question requires more analysis and skills other than reading from data or charts and may require hypothesis testing. The first step is to select all year 3 student’ first year, and final year marks from YR3_LEVEL, and name the results as table QG. After this step, hypothesis testing is done to see if first year performance predicts final year performance.

To do this, there is need to construct a table that shows the average marks and 3 more columns in terms of D,  and D-, alongside a bar chart. D is the difference between first year and final year and  is the mean of D. In this case, there is need to assume that both first and final year marks are normally distributed.

2.7.0 Query and results

SQL statements

Table YR3_LEVEL:

SELECT YR3_STUDENT_MARK.STUDENT_ID, YR3_STUDENT_MARK.MODULE_CODE, MODULE.MODULE_LEVEL, YR3_STUDENT_MARK.MARK

FROM YR3_STUDENT_MARK, [MODULE]

WHERE (((MODULE.MODULE_CODE)=[YR3_STUDENT_MARK].[MODULE_CODE]));

Table QG:

SELECT T1.STUDENT_ID, FIRST_YEAR_MARK, THIRD_YEAR_MARK

FROM (SELECT STUDENT_ID, AVG(MARK) AS FIRST_YEAR_MARK FROM YR3_LEVEL WHERE MODULE_LEVEL=1 GROUP BY STUDENT_ID)  AS T1, (SELECT STUDENT_ID, AVG(MARK) AS THIRD_YEAR_MARK FROM YR3_LEVEL WHERE MODULE_LEVEL=3 GROUP BY STUDENT_ID)  AS T2

WHERE T1.STUDENT_ID=T2.STUDENT_ID;

2.7.1 Interpretation of results

For better interpretation of the results, the tables and graphs given below were used for analysis. Calculations were also used to see the relationship between fist years and last year performance.

STUDENT_ID

FIRST_YEAR_MARK

THIRD_YEAR_MARK

D

D-D¯

(D-D¯)2

4160559

62.5

53.175

9.325

7.31

53.4361

4170023

50.05

58.725

-8.675

-10.69

114.2761

4187645

69.1375

64.475

4.6625

2.6475

7.009256

4197865

60.3

51.475

8.825

6.81

46.3761

4197890

58.5625

62.625

-4.0625

-6.0775

36.93601

SUM

   

2.015

 

258.0336

Assumptions:                both year 1 average and year 3 average marks are normally distributed

Hypotheses:    H0:           μD = 0, where μDy1 – μy3           H1: μD 0

Test:                                   2-tail

Distribution:                  t, using matched-sample test as distributions are dependent.                    

Degrees of freedom:      N-1 = 16 – 1 = 15

Significance level:              5%

Critical value:                    ±2.131                                                                

Decision rule:                 reject HO if sample value > 2.131 or < -2.131                  

Sample value:                    sample t =  where SE() = s/√N

                                       =2.015                             

       s = 8.0317

                                       SE() = s/√N = 8.0317/√8 = 2.834

                                 sample t = 2.015/2.834 = 0.7586          

From the calculations above, sample value 0.75864 is in between critical values (±2.131). Due to this, do not reject H0 at the 5% significance level. This means that the data is consistent with the hypothesis and shows that the mean mark per student in first year is the same with the mark in the final year at the 5% significance level. From the analysis of the chart and the results of the hypothesis test, it is evident that first year mark can be used to predict final year performance. However, with limited data, this method is not efficient and the results could be unreliable. This method is only reliable with large samples of data and gives precise results.

  1. Are exams harder than coursework?

The first step in this exercise is to extract the marks from sat_by and submitted_by, and then use functions like average, standard deviation, maxmium and minimum to statistically analyse the data.

2.8.0 Query and results

SQL statements

Table QH:

SELECT AVG(SAT_BY.MARK) AS EX_AVG, AVG(SUBMITTED_BY.MARK) AS CW_AVG, STDEV(SAT_BY.MARK) AS EX_StDev, STDEV(SUBMITTED_BY.MARK) AS CW_StDev, MAX(SAT_BY.MARK) AS EX_MAX, MAX(SUBMITTED_BY.MARK) AS CW_MAX, MIN(SAT_BY.MARK) AS EX_MIN, MIN(SUBMITTED_BY.MARK) AS CW_MIN

FROM SAT_BY, SUBMITTED_BY;

2.8.1 Interpretation of results

For better analysis and interpretation of the difference between the two types of assesment, a column chart is used to show the performane in the two methods. From the chart, it is clear that coursework is characterized by higher average mark, maximum and minimum mark, than exam. However, the exam statistics are slightly higher in standard deviation than assessment. From this, it is clear that exam is relatively harder than coursework.

3.0 Conclusion

In conclusion, it is evident that the implementation of this database at the institution will be of great benefit to both the management and the students. It will help the institution to have a better competitive edge over its competitors. It will enable the institution to have a personal approach towards the students and will enable for more intake of students. Through use of selective queries, the database will help the management to notice areas of weakness, which will improve its performance. Students will also be able to monitor their performance over time and take the necessary action to correct their mistakes for better performance in future. The database will also reduce the running cost of the institution as the information manipulation and retrieval process will be easier and can be performed by few employees compared to the manual system. A database is, therefore, an important project and needs to be implemented at the school for better performance and efficiency.

References

Harrington, J. L., 2009. Relational database design and implementation: clearly explained. Boston: Elsevier.

Newsletter

Make sure you dont miss interesting happenings by joining our newsletter program.

Contact us

Talk to us today. Use the contact provided below

  • Hot line: +1-3155576175

Connect with us

We're on Social Networks. Follow us & get in touch.
You are here: Home Resources Importance of Data Management