Quick links: Task 1, Task 2, Task 3, Task 4.

Purpose

The purpose of this assignment is to give you hands-on experience with designing, constructing and using a database for a real-world-like domain. You will see all aspects of database creation, from understanding the domain to using the final database from external applications.

Assignment submission and deadlines

To pass the programming assignment, you must pass all five tasks described on this page. You will do the assignment in groups of two. The first things you need to do is to form groups, register in Fire and request a PostgreSQL account.

You must submit your solutions through the Fire reporting system, where you can also see deadlines for each task.

You must submit your group's solutions to each task by the given deadline. After submission, your assignment will be graded ("pass" or "reject") and you will receive comments on your solution (for tasks 1, 2, and 3). If your submission is rejected, you are allowed to refine your solution and re-submit it.

To pass the final part of the assignment, you must demonstrate your system to one of the teachers. Your files for task 4 must be uploaded to the Fire system after you have demonstrated your system, and before the task 4 deadline.

Regarding cheating

The assignments are carried out in groups of two. You are not allowed to show any part of your code to anyone outside your group, other than teachers. You are not allowed to look at or submit code written by anyone else than you.

Both members of the group must be actively participating in each of the tasks.

Introduction

In this assignment you will design and construct a database, together with a front end application, that handles university students and courses. You will do this in four distinct tasks:

Construction: create (a prototype of) the database and explore it with queries.
Design: take a step back and redesign the database using more systematic approaches.
Usage: define more constraints and triggers to maintain the database.
Interface: write a wrapper program to permit database access without explicit use of SQL.

All of the tasks are related to each other. They deal with the same database domain, and subsequent tasks build on earlier ones to varying degrees.

For each task you will hand in and get feedback on your results. Since errors in one task may propagate to the next one, it is wise to hand in your solutions early to get more chances for feedback. If there is time, teachers can also accept your solution directly in lab sessions.

Be sure to read through the full description of the assignment before you start since the requirements we place on the system must influence your initial design as well.

Domain description

The domain that you will model in this assignment is that of courses and students at a university. Note that the described domain is not identical to Chalmers or GU.

The university for which you are building this system is organized into departments, such as the Dept. of Computing Science (CS), and educational programs for students, such as the Computer Science and Engineering program (CSEP). Programs are hosted by departments, but several departments may collaborate on a program, which is the case with CSEP that is co-hosted by the CS department and the Department of Computer Engineering (CE). Department names and abbreviations are unique, as are program names but not necessarily abbreviations.

Each program is further divided into branches, for example CSEP has branches Computer Languages, Algorithms, Software Engineering etc. Note that branch names are unique within a given program, but not necessarily across several programs. For instance, both CSEP and a program in Automation Technology could have a branch called Interaction Design. For each program, there are mandatory courses. For each branch, there are additional mandatory courses that the students taking that branch must read. Branches also name a set of recommended courses from which all students taking that branch must read a certain amount to fulfill the requirements of graduation (see below for additional requirements).

A student always belongs to a program. Students must choose a single branch within that program, and fulfill its graduation requirements, in order to graduate. Typically students choose which branch to take in their fourth year, which means that students who are in the early parts of their studies may not yet belong to any branch.

Each course is given by a department (e.g. CS gives the Databases course). Each course has a unique six character course code. All courses may be read by students from any program. Some courses may be mandatory for certain program, but not for others. Students get academic credits for passing courses, the exact number may vary between courses (but all students get the same number of credits for the same course). Some, but not all, courses have a restriction on the number of students that may take the course at the same time. Courses can be classified as being mathematical courses, research courses or seminar courses. Not all courses need to be classified, and some courses may have more than one classification. The university will occasionally introduce new classifications, so the database needs to allow this. Some courses have prerequisites, i.e. other courses that must be passed before a student is allowed to register to it.

Students need to register for courses in order to read them. To be allowed to register, the student must first fulfill all prerequisites for the course. It should not be possible for a student to register to a course unless the prerequisite courses are already passed. It should not be possible for a student to register for a course which they have already passed.

If a course becomes full, subsequent registering students are put on a waiting list. If one of the previously registered students decides to drop out, such that there is an open slot on the course, that slot is given to the student who has waited the longest. When the course is finished, all students are graded on a scale of 'U', '3', '4', '5'. Getting a 'U' means the student has not passed the course, while the other grades denote various degrees of success.

A study administrator can override both course prerequisite requirements and size restrictions and add a student directly as registered to a course. (Note: you will not implement any front end application for study administrators, only for students. The database must still be able to handle this situation.)

For a student to graduate there are a number of requirements they must first fulfill. They must have passed (have at least grade 3) in all mandatory courses of the educational program they belong to, as well as the mandatory courses of the particular branch that they must have chosen. Also they must have passed at least 10 credits worth of courses among the recommended courses for the branch. Furthermore they need to have read and passed (at least) 20 credits worth of courses classified as mathematical courses, 10 credits worth of courses classified as research courses, and at least one seminar course. Mandatory and recommended courses that are also classified in some way are counted just like any other course, so if one of the mandatory courses of a program is also a seminar course, students of that program will not be required to read any more seminar courses.

System Specification

You will design and implement a database for the above domain, and a front end application intended for students of the university. Through the application they should be able to see their own information, register to, and unregister from courses.

Formally, your application should have the following modes:

Info: Given a student's national identification number, the system should provide:
- the name of the student, the student's national identification number (10 digits) and their university issued login name/ID (something similar to how the CID works for Chalmers students).
- the program and branch (if any) that the student is following.
- the courses that the student has read (including failed courses), along with the grade.
- the courses that the student is registered to and waiting for (and their queue position if waiting).
- whether or not the student fulfills the requirements for graduation.
Register: Given a student id number and a course code, the system should try to register the student for that course. If the course is full, the student should be placed in the waiting list for that course. If the student has already passed the course, or is already registered, or does not meet the prerequisites for the course, the registration should fail. The system should notify the student of the outcome of the attempted registration.
Unregister: Given a student id number and a course code, the system should unregister the student from that course. If there are students waiting to be registered, and there is now room on the course, the one first in line should be registered for the course. The system should acknowledge the removed registration for the student. If the student is not registered to the course when trying to unregister, the system should notify them.

Task 1: Constructing the database

Note: The last view you need to create for this assignment is more time consuming than the other parts.

Your task is to construct a first version the database by implementing the database schema in a database engine (PostgreSQL). The schema for this can be found here: schema_task1.pdf .

Note that the schema is somewhat incomplete, but that will be fixed in part two of the assignment. You should implement the schema with CREATE TABLE statements. Remember to:

use exactly the given table and attribute names
express the same primary key and foreign key constraints
use sensible types (types are not expressed in the abstract schema).
- Hint: For the position attribute, you may either use a timestamp, an auto-increasing (unique) number or the absolute position (there are advantages and disadvantages to each).
disallow NULL values

Thus you should create all tables, marking key and foreign key constraints in the process, and you should also insert basic checks that ensure that only valid data can be inserted in the database. Examples of invalid data would be the grade '6', or a course with a negative capacity.

When you have created the tables, you should fill the tables with example data. Ordinarily, this is a time-consuming but important part of the development of a database. Having data in the database is crucial in order to properly verify that it behaves the way that you expect it to. The tables should be filled with enough data so that it is possible to test that your application can handle the various operations specified above. Just inserting tons of data is of no use if the data still doesn't test all parts of the database.

In this course however, we have decided to just give you a set of insert that should work (possibly with slight adjustments): inserts.sql (download the file, ignore the Canvas preview). Note the various corner cases covered by the tests:

A handful of students, at least one of which fulfills the requirements for graduation and a couple that do not for different reasons.
A number of courses that test all of the various aspects a course. This includes classifications, mandatory, recommended etc.
Two branches with the same name on different programs, each with at least one registered student and at least one mandatory course.
At least three waiting students for two different (full) courses.
At least one student that has not chosen a branch.
A student that has not taken any courses.
A student that has only failing grades.
...

Following the system specification, create these views:

View: BasicInformation(idnr, name, login, program, branch) For all students, their national identification number, name, login, their program and the branch (if any). The branch column is the only column in any of the views that is allowed to contain NULL.
View: FinishedCourses(student, course, grade, credits) For all students, all finished courses, along with their codes, grades (grade 'U', '3', '4' or '5') and number of credits. The type of the grade should be a character type, e.g. CHAR(1).
View: PassedCourses(student, course, credits) For all students, all passed courses, i.e. courses finished with a grade other than 'U', and the number of credits for those courses. This view is intended as a helper view towards later views (and for task 4), and will not be directly used by your application.
View: Registrations(student, course, status) All registered and waiting students for all courses, along with their waiting status ('registered' or 'waiting').
View: UnreadMandatory(student, course) For all students, the mandatory courses (branch and program) they have not yet passed. This view is intended as a helper view towards the PathToGraduation view, and will not be directly used by your application.
View: PathToGraduation(student, totalCredits, mandatoryLeft, mathCredits, researchCredits, seminarCourses, qualified) For all students, their path to graduation, i.e. a view with columns for
- student: the student's national identification number.
- totalCredits: the number of credits they have taken.
- mandatoryLeft: the number of courses that are mandatory for a branch or a program they have yet to read.
- mathCredits: the number of credits they have taken in courses that are classified as math courses.
- researchCredits: the number of credits they have taken in courses that are classified as research courses.
- seminarCourses: the number of seminar courses they have read.
- qualified: whether or not they qualify for graduation. The SQL type of this field should be BOOLEAN (i.e. TRUE or FALSE).

Hint1: For the last view, make a query for the data of each column and when they all work, put them in a WITH clause and use a chain of (left) outer joins to combine them.

Hint2: Use COALESCE to replace null values with 0 (e.g. COALESCE(totalCredits,0) AS totalCredits. Also, keep in mind that comparing null values with anything gives UNKNOWN!

Hint3: A query containing student/classification/credit with a row for each classification of each course every student has passed may be useful.

Make sure that your views use the right names of columns! Use AS to name a column.

Testing: The file runtests.sql automatically runs the files for this assignment. The file output.txt contains the expected output of doing this. Run the file (with \i runtests.sql in psql) and make sure your output matches the example (assuming you use the provided insert.sql file) and that you get no errors (order of rows may possibly vary). This is not a perfect guarantee that your code works as it should, but it does find several common errors.

Deliverables: For task 1, you should submit the following files through Fire:

tables.sql: your SQL code for creating the tables.
insert.sql: OPTIONAL, your SQL code containing the insert statements for the data IF IT DIFFERS FROM THE FILE YOU WERE GIVEN. Also include a short comment next to every change you made to the file.
views.sql: your SQL code for creating the listed views.
explanation.txt OPTIONAL, this file is only required if you did not get all the tests provided to work exactly as intended, and should contain a short description of the problem. Submissions that do not contain this file and fail to pass the tests will be summarily rejected without comments.

Note that SQL code should be in plain text format. Make sure that PostgreSQL can execute your files on an empty database before you hand them in. Do not have the "delete everything"-statement in any of the files you submit.

Automatic grading: Fire will test submitted solutions to catch some common errors, if your submission fails the tests it is likely to get rejected. Even if your solution passes the tests, a grader will manually check your code for mistakes.

IMPORTANT! Note that Fire doesn't use the same inserts you have in your inserts.sql file, but a more complete (and therefore harder to pass) set.

Here are some hints about the tests run by Fire:

* Some student doesn't belong to any branch
* Some student hasn't taken the mandatory courses on his/her branch
* Some student hasn't taken the mandatory courses on his/her program
* Some student doesn't has enough math credits
* Some student doesn't has research credits
* Some student hasn't taken any seminar
* Some student hasn't enough credits from the recommended courses of his/her branch
* Some student is qualified

Task 2: Database design

The task in this lab is to take a look back at the database implemented in Lab 1 and check how we should have done it. More precisely, we will

apply our design skills to the domain description:
- build an E-R model and derive a schema from it by the E-R to schema translation
- analyse functional dependencies (FD) and derive a schema by normalization
compare the previously given schema (schema_task1.pdf ) with the ones obtained by these methods and construct a final schema for the whole database (and translate it into SQL).

There are many interesting outcomes that may result from this study:

we may notice that E-R and FD give the same schema as the original schema (but then we have done something wrong!)
find differences between E-R, FD and schema_task1.pdf that help complete each of them.
find a very different design that is still justifiable.

E-R model

First create an E-R diagram that correctly models the domain described in the domain description above.

You can use any tool you like for this task, as long as you hand in your solution as an image in one of the formats .png, .jpg, .gif or .pdf and as a schema (.txt with primary keys marked like _this_, or a .pdf like schema_task1.pdf ). We recommend you use Dia (Links to an external site.) for ER-diagrams.

You must in both cases use the translations specified in the lectures, and mark the keys and references properly.

Hint: if your diagram does not contain at least one weak entity, at least one ISA relationship, and at least one many-to-at-most-one relationship, you have probably done something wrong.

Functional dependencies

Your second task is to formulate all functional dependencies in the domain and derive a valid schema from them. This should be done mostly independently of the E-R design and of the schema in schema_task1.pdf .

Identify the functional dependencies that you expect should hold for the domain:
1. Collect all attributes (with unique names, e.g. sname for student names and cname for course name etc.) for the domain and put them into one table, Domain(....). You may look in the schema from your ER model for this.
2. By reading the Domain Description, identify all functional dependencies in Domain(....). Do not just extract them from your ER-model! Here is a non-exhaustive list of things you may want to consider when searching for functional dependencies:
  - department names and abbreviations
  - study program names and abbreviations
  - branch names
  - student names and identification numbers
  - student programs and branches
  - course names and codes
  - course classifications and prerequisites
  - course waiting lists (more than one FD involving position)
  - Use the dependencies to find keys and normal forms:
    1. Find all possible keys of Domain(....), as well as all BCNF violations.
    2. Decompose Domain(....) to distinct tables by using the BCNF normalization algorithm.
    3. Carefully mark primary keys and references in the resulting schemas.

Compare and discuss:
1. Compare the BCNF schema with your E-R schema, asking in particular: are their constraints that only E-R or only FD can reveal?
2. Are there any constraints that neither approach guarantee? (Hint: Are you sure a student can only attend one program?).
3. As a result of this, build a "perfect schema", based on the ER-schema but including unique constraints and at least one additional/modified reference constraint based on the functional dependencies.
4. Compare this with the original schema in schema_task1.pdf . If you made clever design choices, most of it should be similar but with additional constraints and relations.
5. Translate the updated schema into SQL Tables and add required inserts etc. If you find that you will have to make significant changes to existing inserts/views, perhaps your design is not yet perfect.

Hint: the Query Converter (http://www.grammaticalframework.org/qconv (Links to an external site.)) can automate much of the analysis, as soon as you have identified the functional dependencies yourself. It can get slow when the schema is very large, but you can then just reload the page to kill the process.

Hint: The final tables should contain at least four additional relations compared to the schema in Task 1, and at least 3 UNIQUE constraints (one is only required for technical reasons to enable a constraint that prevents students from choosing a branch in the wrong program).

Deliverables: For Task 2, you should submit the following files through Fire

ER.png: your E-R diagram as a .png, .jpg, .gif or .pdf file
ER-schema.txt: the database schema derived from your E-R diagram, as a text file or a PDF (no Microsoft Word, no HTML!)
FD.txt: your Domain(...) schema together with the functional dependencies you have found (you can omit dependencies that can be derived from the ones you mention)
FD-schema.txt: the decomposed schemas using the BCNF algorithm
final-schema.txt: your final database schema
tables.sql, views.sql, inserts.sql: Your modified database code.

Criteria of acceptance:

ER.png: the E-R diagram must be syntactically correct and a reasonable model for the domain (and you cannot do this without weak entities and ISA entities!)
ER-schema.txt: the ER-schema must be correctly derived from the diagram
FD.txt: you should have found all and only the reasonable functional dependencies in the domain (derived ones can be omitted)
FD-schema.txt: the decompositions should be correct (this is partly automatic if you use Query Converter, but you should make sure to mark the keys and references)
final-schema.txt: Your final schema that models the domain as closely as possible (some things will simply not be possible to do in schemas, like checking that there are no circular requirements for courses or that a student can not register for a course they have already passed).
*.sql: The current executable code for your database, including all the changes made from this design task. Should contain no DROPS or other debug-code, just the code for creating the tables, views, and test data - all expanded/modified to work with the new design.

Task 3: Triggers

When your tables and views are implemented in Task 2, the next task is to create two triggers to handle some key issues in registration and unregistration. Here is a piece of code to get you started on the first trigger:

Postgresql trigger example (Links to an external site.)

But first, you should define one more view that can be used by your triggers, and your application in Task 4:

View CourseQueuePositions(course,student,place): For all students who are in the queue for a course, the course code, the student's identification number, and the student's current place in the queue (the student who is first in a queue will have place "1" in that queue, etc.). This view is trivial if you store the positions directly in the database, but not if you store e.g. registration timestamps.

When a student tries to register for a course, it is possible that the course is already full, in which case the student should be put in the waiting list for that course. When a student unregisters, it might be that there is now room for some student who is in the waiting list, and who should then be registered for the course instead. Such things are typically handled via triggers. You should write two triggers:

When a student tries to register for a course that is full, that student is added to the waiting list for the course. Be sure to check that the student may actually register for the course before adding to either list, if it may not you should raise an error (use RAISE EXCEPTION (Links to an external site.)). Hint: There are several requirements for registration stated in the domain description, and some implicit ones like that a student can not be both waiting and registered for the same course at the same time.
When a student unregisters from a course if the student was properly registered and not only on the waiting list, the first student (if any) in the waiting list should be registered for the course instead. Note: this should only be done if there is actually room on the course (the course might have been over-full due to an administrator overriding the restriction and adding students directly).

You need to write the triggers on the view Registrations instead of on the tables themselves (the view was built in task 1 above). (One reason for this is that we "pretend" that you only have the privileges listed under Task 4, which means you cannot insert data into, or delete data from, the underlying tables directly. But even if we lift this restriction, there is another reason for not defining these triggers on the underlying tables - can you figure out why?)

Hint1: Write your triggers incrementally, first make a registration trigger that just always give an error and test that it works, then make it give an error only if the student is already registered (or waiting) and otherwise register it, then make it give a different error if the student is missing prerequisites, then make it put the student in a waiting list if the course is full, and so on.

Hint2: One way to check if a course is full: Count the number of courses with that code and more students than its seat capacity. The count will be 1 or 0 (0 meaning either it has no limit or the limit is not exceeded).

Testing Make a file tests.sql with inserts and deletes that test all cases of your triggers. Write a short comment (line starting with --) over each insert/delete that states what is tested the expected outcome (change/error).

Hint 1: Your tests need to test every way a registration could fail, and the outcomes of successfull registrations/unregistrations. The latter includes:

Registered to unlimited course.
Registered to limited course.
Waiting for limited course.
Unregistered from unlimited course.
Unregistered from limited course without waiting list.
Unregistered from limited course with waiting list.
Unregiestered from overfull course with waiting list.
Have a limited course with at least three people on the waiting list. Make sure that a person from the middle of the waiting list can be unregistered, and that people after them are moved to the correct positions.

Hint 2: Look at the list at the end of Task 4 for some ideas on what to test.

Deliverables. For task 3, you should hand in the following files:

setup.sql: SQL code that sets up your database for testing the triggers. This will normally be the concatenation of files tables.sql, insert.sql and views.sql from Task 2. We must be able to test your triggers by executing the files setup.sql and triggers.sql, in that order.
triggers.sql: SQL code for creating the two triggers and the CourseQueuePositions view.
tests.sql The testing code for your triggers.

Make sure that PostgreSQL can execute your files before you hand them in.

Task 4: Front end application

The last part of this assignment is to write an application that students can use to communicate with your database. This application should be a Java program that uses JDBC to connect to the PostgreSQL database to request and insert the proper data (if you really want to you can use other languages than Java, but assistance from teachers will be limited).

You can perform these tasks in any order, but the order below is suggested based on when material is presented in the lectures.

Get familiar with the infrastructure

Download the files PortalConnection.java, TestPortal.java and PortalServer.java . PortalConnection is the class where you will write your solution to this task. You will also edit TestPortal, but only to test that your solution works. PortalServer will not be edited but used to run your solution as a web server. You also need to download the latest Postgres JDBC driver from here: https://jdbc.postgresql.org/download.html (Links to an external site.). At the time of writing, what you are looking for is a file called postgresql-42.2.10.jar.

If you downloaded the files correctly and either imported them into an Eclipse project or used whichever other way of compiling them you prefer, it should be possible to compile and run TestPortal, with one of three possible outcomes:

1. It says you are missing the Postgres driver, and you need to add the .jar file you downloaded to your classpath. Google something like "add jar to runtime classpath eclipse", or whichever IDE you are using. Ask in a lab session if you can not get it working.

2. You get some other exception, most likely an SQLException. You will most likely need to edit the database connection information in the top part of PortalConnection to match the connection you are using, the default is to connect to a database called portal, running on the same machine as the java program with the user postgres and password postgres (note that in a production setting, using the superuser postgres is a bad idea, also don't put passwords that you wish to keep secret in your source files).

3. You are able to step through the whole program by pressing enter but it doesn't really do anything yet because the methods in PortalConnection are just placeholders.

When you are able to achieve outcome 3 above, proceed with the lab. If you want to, running PortalServer and connecting trough your browser (see below) should also work at this point.

Register students

Start by changing the register method in PortalConnection so it uses the Connection object conn to register the student for the course indicated by the method parameters. You should use a try-with-resource to create the PreparedStatement used and catch any error.

The method should return a string on one of these forms depending on the outcome:

{"success":true}

{"success":false, "error":"<error message goes here>"}

The error messages you provide do not need to be user friendly at all, but they are not allowed to contain newline characters, double quotes or other characters that will interfere with a JSON string value. You can use the provided getError method to turn any SQLException you catch into a valid error message.

Testing: Run a few registrations in TestPortal and check in psql if the correct changes were made. Run a few invalid registrations and check that you get error messages.

Unregistering students

Next you do the same thing for the unregister method, making attempt to unregister a student. Give an error string of the same format as before, particularly you should give an error if the student is not registered/waiting or the course does not exist (basically if 0 rows are deleted).

Testing: Run a few unregistrations in TestPortal and check in psql if the correct changes were made. Run a few invalid unregistrations and check that you get error messages.

Getting student information

For this part of the assignment you will need to know JSON and JSONSchema. The getInfo method takes a student ID and returns a lot of information about that student (the basic information, taken and registered/waiting courses, and everything from PathToGraduation). The information should be a JSON documents that validates against the schema found in information_schema.json.

You can construct this JSON document in any way you want as long as it validates against the schema, but there are three basic approaches:

1. Using the Postgres facilities to construct the JSON document. It is possible to get all the information in a single gigantic query by using the functions json_build_object, json_agg and perhaps a bit of COALESCE+json_build_array. You can write and test the query in a .sql file and psql (as in earlier labs) and then just make it into a Java string after it works (there are tools online for doing this automatically).

2. Using a JSON library like org.json to build the JSON document using java language features. Basically run a few queries, loop through the result and construct JSON objects, arrays, and values using the library of your choice. This might require a bit of reading up on the library and more knowledge of Java than the other approaches.

3. Use Java string manipulation to build the JSON document. Write a few queries to fetch all the data, loop through the results and use the + operator to stitch them together into a syntactically valid JSON document that matches the provided schema. The downside is that the code will probably look horrible (can be mitigated by adding some helper methods e.g. to convert a Java-string to a JSON string with double quotes) and you will make lots of tedious JSON syntax mistakes. The upsides are that it requires very little Java/SQL knowledge and you will learn the syntax of JSON very well while doing this.

It is possible to combine these approaches, e.g. using a few queries to build JSON objects and arrays in Postgres and then stitching them together with some string manipulations in Java into the complete document.

Testing: During development you will want to continuously run information requests in TestPortal and checking that the output is valid JSON (you can use tools like JSON lint (https://jsonlint.com/ (Links to an external site.)) for this or a Java JSON library that can parse JSON. At some point you should check that the output can be validated against the given schema (google "json schema validator" to find a few online tools for this).

Running the webserver

Compiling and running PortalServer.java will start a web server on your computer using port 8080 unless you change it (80 is the default port for HTTP). This means you can connect to it by navigating your browser to http://localhost:8080/ on the computer that is running the server. If it works you should arrive at a beautiful login screen where you can enter a student id number to proceed to the portal. If there are lots of errors in your code, the server might not work as intended and you will have to go back and figure out what's wrong.

It is not absolutely necessary to get the web server running, but if you don't you should at least explain what went wrong.

Note: After selecting a student, if you change the URL in your browser from e.g.
http://localhost:8080/run?student=1111111111

to

http://localhost:8080/info?student=1111111111

You can see how the JSON is transmitted from your web server to your web browser.

SQL Injection

In this part of the task, you are going to do something highly unusual: Deliberately introduce an SQL injection vulnerability into your code. Particularly, it should be possible to delete ALL registrations for all students by typing in a sneaky course code.

First try to figure out the course code you should use yourselves, if you can not then look in the lecture slides about SQL injection.

Demonstration

Your solution to this task needs to be demonstrated in a lab session. For the demonstration both members of the group need to attend.

Prepare by:

1. Making a script in TestPortal.java that contains the following with "pauses" between all steps:

List info for a student.
Register the student for an unrestricted course, and show that they end up registered (show info again).
Register the same student for the same course again, and show that you get an error response.
Unregister the student from the course, and then unregister again from the same course. Show that the student is no longer registered and the second unregistration gives an error response.
Register the student for a course that they don't have the prerequisites for, and show that an error is generated.
Unregister a student from a restricted course that they are registered to, and which has at least two students in the queue. Register again to the same course and show that the student gets the correct (last) position in the waiting list.
Unregister and re-register the same student for the same restricted course, and show that the student is first removed and then ends up in the same position as before (last).
Unregister a student from an overfull course, i.e. one with more students registered than there are places on the course (you need to set this situation up in the database directly). Show that no student was moved from the queue to being registered as a result.
Unregister with the SQL injection you introduced, causing all (or almost all?) registrations to disappear.

2. Writing the name of both group members and your Fire group number on a piece of paper (which you will hand to a teacher upon successful demonstration).

3. Sign up to demonstrate and make sure you have a psql connection open to the database you use (ready to reset it with a .sql script), an editor with your Java source code, an editor with all your .sql source code, and a browser window with tabs for checking a few different students through the web server (if you got it working).

4. After you demonstrate, ask the teacher you demonstrate to for their name.

Hand in after demonstration

Submit your TestPortal.java and PortalConnection.java through Fire (Task 4), write the name of the person you demonstrated to in the comment for the submission.