- Category: Resources
- Published: 03 September 2014
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.
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.
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
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"));
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));
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;
- Next >>