Revision as of 15:34, 14 January 2016

Practice classes

Information about the practice classes can be found on your practice class teacher's homepage

• Margit Aarna and Risto Heinsar http://blue.pri.ee/ttu

3. Homework

Database homework

• There has been given a PostgreSQL database, which consists of a simple student information system data.
• To take the task, you first have to register with your student code. This is necessary for creating an user for you and an user specific database, which has been filled with sample structure and data. You will have rights to insert, modify and delete rows in your database and feel free to do so.

Registration

• Connection parameters:

Host: ewis.pld.ttu.ee
Database: <student code>
Port: 5432
User: <student code>
Password: <password you chose during registration>

• For managing the database, you can use some of postgre database clients, like:
  • PgAdmin III (Windows, Linux, Mac OS)
  • MyWebSQL (web client)
  • phpPgAdmin (web client)
• Data structure has been given with the image

• If there is a need to reset the initial database, you can use the following SQL statements for that - Link
• Homework should be commited to your GIT repository

Tasks

Create a text based user interface program, that would have the following functionality:

1. Adding a student. User will be asked: first name, last name, date of birth. Program has to generate unique student code for each student. (2p)
2. Modifying and deleting a student. (2p)
3. Adding, modifying and deleting a course. (2p)
4. Giving grade for a student for a specific course. When inserting a grade, the average grade should also be updated taking account the credit points. (2p)
5. Searching students based on their weighted average grade. For example, if we want to search students for ex-matriculation, we could give in condition avg < 1.

Program has to communicate with the given database and use it for reading and storing data.

Helping materials

• PostgreSQL documentation
• SQL cheatsheet

C sample

Sample - Creates a connection with a database and queries 10 first rows.

The easiest way to compile your program is using TUT's lab computers, where PostgreSQL is previously installed and the library can be easily included. [mini.li.ttu.ee/~priit/IAY0340.2013/ssh_setup/putty_guide.html Guide for connecting to lab computers over SSH]

It is of course possible to install the postgresql c libraries also on your own computer independent of your OS, but this can be time consuming on the first try, as the correct library directories have to be linked. For more information you can visit http://www.postgresql.org/docs/9.1/static/libpq.html and for downloading the libraries with postgresql itself: http://www.postgresql.org/download/ .