University Database System

A university database system maintains a set of tables to marks, retrieve and manipulate student marks.

These tables reside in a centrally or locally located server. A sample of the tables may look like as follows:

Mark

Student ID Subject Session Assignment 1 Assignment 2 Assignment 3 Final Exam
1001 ITC100 201530 43 10 40 20
1001 ITC100 201630 60 50 70 70
1002 ITC100 201530 90 100 99 90
1002 ITC200 201530 100 90 95 90
1001 ITC200 201660 80 87 100 99
... ... ... ... ... ...

Student

Student ID Name
1001 Wallace
1002 Grommit
... ...

The DBMS above is very simple - we assume that all subjects have three assignments and an exam and when calculating the final mark, we assume that all subjects use the same weighting. However, this simple model is sufficient for this assignment.

The Student ID in the student table is unique, and the combination of Student ID + Subject + Session in Mark table is also unique. This implies that a student can take the same subject multiple times but not in the same session. Poor Wallace has to repeat ITC100, but luckily Grommit was able to support him in his study in the next offering. Wallace finally passed and did very well on other subjects. Good on you Wallace!

Write a GUI-based JAVA program that would perform the following tasks.

• Create Tables: Create two tables to store the above information. Please choose meaningful table names, column names and appropriate data types for the columns.
• Insert Student records: The program should allow users to insert a student record. The Student ID must be generated automatically, and the program prompts users for the students name.
• Insert Mark records: Users can only insert a mark record if the student exists in the student table. The program should also display the students name and seek confirmation from users before inserting the record and it must do so in a user friendly way.
• Display:
  • Simple display. Display the student ID, name, marks of all four assessments and the final mark of all students in the database ordered by student ID.
  • Score display. Display all the information in a simple display along with the final mark, which is a calculated field. The final mark= 10%(Assignment 1) + 20%(Assignment 2) + 20%(Assignment 3) + 50%(Final exam).
• Search: The user of your program should be able to display the marks (all three assignments and exam) by entering the students name. the program should display the student ID, student name, marks of all assignments and exam.
• Update: The user of your program should be able to update any non-key fields of a particular record. You have to think about which are the best inputs that can be used to retrieve the records to be updated.