Databases (HT2013)

Programming Assignment

Purpose

The purpose of this assignment is for you to get hands-on experience with designing, constructing and using a database for a real-world 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 four tasks described on this page.

You must submit your solutions through the Fire reporting system. Read the separate notes on registering in the Fire system, submitting assignment tasks and requesting your group's ORACLE account.

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 within one week.

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.

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:
  1. Designing the database schema
  2. Constructing the database (I)
  3. Constructing the database (II)
  4. Writing a front end application

Each task is based on the previous one, so it is one single assignment that you will do. It will not be possible to only do the fourth task for instance, since you will then have no database on which to build your application.

For each task you will hand in and get feedback on your results. You cannot continue with a later task before the previous one(s) are approved, so it is wise to hand in your solutions early to get more chances for feedback.

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. So that the task is not too large and unspecified, you will here get a description of the domain that restricts the problem somewhat. 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 study programmes for students, such as the Computer Science and Engineering programme (CSEP). Programmes are hosted by departments, but several departments may collaborate on a programme, which is the case with CSEP that is co-hosted by the CS department and the Department of Computer Engineering (CE). Department names and programme names are unique.

Each study programme is further divided into branches, for example CSEP has branches Computer Languages, Algorithms, Software Engineering etc. A student always belongs to a programme and a branch. Note that branch names are unique within a given program, but not necessarily across several programs. For instance, both CSEP and a programme in Automation Technology could have a branch called Interaction Design. For each study programme, 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 fulfil the requirements of graduation, see below.

Courses are given by a department (e.g. CS gives the Databases course), and may be read by students reading any study programme. Some courses may be mandatory for certain programmes, but not so for others. Some, but not all, courses have a restriction on the number of students that may take the course at the same time. Each course gives a certain number of credits. For simplicity we assume all students get the same amount of credits for a given course, regardless of which study programme or branch they belong to. 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. Some courses have prerequisites, i.e. other courses that must be read before a student is allowed to register for the course at hand.

Students need to register for courses in order to read them. To be allowed to register, the student must first fulfil all prerequisites for the course. It should not be possible 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, and if one of the previously registered students decides to drop out, that slot on the course 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 student 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 student 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 she must first fulfil. She must have passed (have at least grade 3) in all mandatory courses of the study programme she belongs to, as well as the mandatory courses of the particular branch. Also she must have passed at least 10 credits worth of courses among the mandatory and recommended courses for the branch. Furthermore she needs 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 one seminar course. Mandatory and recommended courses that are also classified in some way are counted just like any other course. As an example, if one of the mandatory courses of a programme is also a seminar course, students of that programme 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 system should have the following modes:

Task 1

Your first task is to design the database that your application will use. The goal of this task is to reach a correct database schema that could later be implemented in Oracle.
  1. You are to create an E-R diagram that correctly models the domain described above. Hint: if your diagram does not contain (at least) one weak entity, (at least) one ISA relationship, you have done something wrong. 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. The tool Dia is available on the school computer system, has a mode for ER diagrams, and can export diagrams to image files, but using any other tool is also OK.
  2. When your E-R diagram is complete, you should translate it, using the (mostly) mechanical translation rules, into a database schema consisting of a set of relations, complete with column names, keys and references. (You can indicate key attributes in a plain text file by putting an underscore before a key attribute's name.)
  3. For each relation in your design, state any functional dependencies that you expect should hold. Here is a non-exhaustive list of things you may want to consider when searching for functional dependencies:

For Task 1, you should hand in

The last three (schema.txt, fd.txt, discussion1.txt) could instead be given as a single file if you like. No .doc files!

We recommend that you check your solution against these common errors in Task 1 before submitting your work.

Deadline: Tuesday 2013-11-12 (before 23:59)

Task 2

When your design of the database has been approved, the next task is to construct the database by implementing the database schema in a relational database management system (Oracle).

  1. You should create all tables, marking key and foreign key constraints in the process, and you should also insert 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 that takes a negative number of students.

  2. When you have created the tables, you should fill the tables with example data. This can be time-consuming, but it is an 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. You should fill the tables 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. You should motivate your choice of data by stating what parts of your database are tested.
    Important: When you insert data in the database, do it by writing the insert statements in a file that can then be executed. This way you won't have to re-do all the work if there is something that you need to change with the table.
  3. Since you know exactly what information your application will need from the database, in what forms, it is a good idea to write views that provide that information in a simple form. In a real setting, we would even ensure using privileges that the application cannot work with anything but these views. Unfortunately we cannot let you test working with privileges on the Oracle system we use, but we will still expect your application to adhere to the privileges we list.

    Following the system specification, create these views:

For Task 2, you should hand in

Note that SQL code should be in plain text format. Make sure that iSQLPlus can execute your files before you hand them in. Test this by clearing out your database (e.g. using the instructions for deleting everything) and then running your SQL files.

Deadline: Tuesday 2013-11-26 (before 23:59)

Task 3

When your tables and views are implemented correctly, the next task is to create two triggers to handle some key issues in registration and unregistration. But first, you should define one more view that can be used by your triggers, and your application in Task 4:

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 that:

  1. 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. (Reminder: the domain description states that it should not be possible for a student to register for a course which they have already passed.)
  2. when a student unregisters from a course (and was previously registered and not only in the waiting list), the first student (if any) in the waiting list should be registered for the course instead.

You need to write the triggers on the view Registrations instead of on the tables themselves. (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?)

Prepare test cases (in SQL) for testing both insertion and deletion to check that the triggers work. Make sure your test suite covers all the points listed under Task 4, that we will check the complete application for.

Some useful links:

For Task 3, you should hand in

Note that SQL code should be in plain text format. Make sure that iSQLPlus can execute your files before you hand them in.

Deadline: Friday 2013-12-06 (before 23:59)

Task 4

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 Oracle database to request and insert the proper data.

To your help when writing your application we provide you with a stub file that contains the code for connecting to Oracle on the local system. It also contains hooks for the three operating modes of the application, and this is where you should insert your code. The idea is that you should not need to focus so much on the pure Java parts of the application, but rather get straight down to business with the database-interfacing code.

The stub file, StudentPortal.java, looks like this:


import java.sql.*; // JDBC stuff.
import java.io.*;  // Reading user input.

public class StudentPortal
{
	/* This is the driving engine of the program. It parses the
	 * command-line arguments and calls the appropriate methods in
	 * the other classes.
	 *
	 * You should edit this file in two ways:
	 * 	1) 	Insert your database username and password (no @medic1!)
	 *		in the proper places.
	 *	2)	Implement the three functions getInformation, registerStudent
	 *		and unregisterStudent.
	 */
	public static void main(String[] args)
	{
		if (args.length == 1) {
			try {
				DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
				String url = "jdbc:oracle:thin:@tycho.ita.chalmers.se:1521/kingu.ita.chalmers.se";
				String userName = ""; // Your username goes here!
				String password = ""; // Your password goes here!
				Connection conn = DriverManager.getConnection(url,userName,password);

				String student = args[0]; // This is the identifier for the student.
				BufferedReader input = new BufferedReader(new InputStreamReader(System.in));
				System.out.println("Welcome!");
				while(true) {
					System.out.println("Please choose a mode of operation:");
					System.out.print("? > ");
					String mode = input.readLine();
					if ((new String("information")).startsWith(mode.toLowerCase())) {
						/* Information mode */
						getInformation(conn, student);
					} else if ((new String("register")).startsWith(mode.toLowerCase())) {
						/* Register student mode */
						System.out.print("Register for what course? > ");
						String course = input.readLine();
						registerStudent(conn, student, course);
					} else if ((new String("unregister")).startsWith(mode.toLowerCase())) {
						/* Unregister student mode */
						System.out.print("Unregister from what course? > ");
						String course = input.readLine();
						unregisterStudent(conn, student, course);
					} else if ((new String("quit")).startsWith(mode.toLowerCase())) {
						System.out.println("Goodbye!");
						break;
					} else {
						System.out.println("Unknown argument, please choose either " +
									 "information, register, unregister or quit!");
						continue;
					}
				}
				conn.close();
			} catch (SQLException e) {
				System.err.println(e);
				System.exit(2);
			} catch (IOException e) {
				System.err.println(e);
				System.exit(2);
			}
		} else {
			System.err.println("Wrong number of arguments");
			System.exit(3);
		}
	}

	static void getInformation(Connection conn, String student)
	{
		// Your implementation here
	}

	static void registerStudent(Connection conn, String student, String course)
	{
		// Your implementation here
	}

	static void unregisterStudent(Connection conn, String student, String course)
	{
		// Your implementation here
	}
}

The intended behaviour of the program is that you use if from the command line, giving some student identification number as an argument (what exactly that is depends on your design). This corresponds to the student "logging on" to the portal. Once logged on, the student can choose one of the three modes "Information", "Register" or "Unregister". If the first is chosen, all information for that student should be printed. Exactly what information must be printed is given by the system requirements specified above. If one of the latter modes are chosen, the student will be prompted for a course to register to or unregister from, and the application should perform the requested operation and print the result (success, failure).

The stub file above can be compiled and run as it is, only nothing will happen in any of the modes. Your task is thus to fill in the actual logic of these three tasks.

Running your application could look like this:


$> java StudentPortal 123456-7890
Welcome!
Please choose a mode of operation:
? > Information
Information for student 123456-7890
-------------------------------------
Name: Emilia Emilsson
Line: Information Technology (IT)
Branch: Systems Development

Read courses (name (code), credits: grade):
 Set Theory (MAT050), 5p: 5
 Functional Programming (TDA450), 10p: 5
 Object-Oriented Systems Development (TDA590), 10p:  4
  
Registered courses (name (code), credits: status):
 Databases (TDA356), 5p: registered
 Algorithms (TIN090), 5p: waiting as nr 3

Seminar courses taken: 0
Math credits taken: 5
Research credits taken: 0
Total credits taken: 25
Fulfils the requirements for graduation: no
-------------------------------------

Please choose a mode of operation:
? > Register
Register to what course? > TDA350
You are now successfully registered to course TDA350 Cryptography!

Please choose a mode of operation:
? > Register
Register to what course? > TDA381
Course TDA381 Concurrent Programming is full, you are put in the 
waiting list as number 2. 

Please choose a mode of operation:
? > Quit
Goodbye!
$>

Note that the formatting here is only a suggestion, you may choose to format your output differently as long as you give the proper information back to the user.

To get access to the jdbc drivers from your application, you should import the following into your java CLASSPATH:
/chalmers/sw/sup64/oracle_client-11.2.0.3/product/11.2.0/client_1/inventory/Scripts/ext/jlib/ojdbc5.jar

Your student application should behave as if it has only the following privileges:

We will check your submitted code to ensure that you adhere to these privileges, even though we cannot get the system to enforce them automatically.

For Task 4 you should hand in

However, the fourth task will not be corrected through the submission system, even though you should still submit your source code. Instead you should come to one of the supervision sessions and demonstrate your running application, and we will accept or reject it on the spot (pending the check of the submitted code for authority violations). As you probably realize, if all of you wait until the last Friday we will quite simply not have time for everyone, so come early!

Here is a list of what we will test your application for:

  1. Listing information
    1. List information for a student.
    2. List information for a student who fulfils the requirements for graduation. This is done to test your PathToGraduation view.
  2. Registering and unregistering from courses
    1. Register a student for an unrestricted course, and show that they end up registered.
    2. Register the same student for the same course again, and show that the program doesn't crash, and that the student gets an error message.
    3. Unregister the student from the course.
    4. Unregister the student again from the same course and show that it doesn't crash your program. (As stated earlier, the application does not need to notify the user that this student was not registered, but it's nice if it does. Consider how this could be done.)
    5. Register a student for a course that they don't have the prerequisites for, and show that the registration doesn't go through.
  3. Course queues
    1. Register the student for a restricted course that is already full, and show that they are placed on the waiting list.
    2. Unregister the student and show that they were removed from the waiting list.
    3. Register a student who is already registered to a full course, and show that this student is not also added to the waiting list for the course.
    4. Unregister a student who is registered to a restricted course with at least two people on the waiting list, and show that the right student from the waiting list ends up registered to the course.
Ensure that the data you have put into your system can handle all these cases. Please prepare before you ask us to check your application, so that running through these cases will be smooth. Prepare a list of instructions (with student identifiers, course codes, etc.) that you will use in the demonstration. When demonstrating your application in one window, it is useful if you also have a browser window with iSQL*Plus open, so that you can show the state of your tables and views during the demonstration.

Deadline: Friday 2013-12-13, at the practical session

Other useful information

Oracle Links

JDBC links


Last Modified: 21 November 2013 by Graham Kemp