Proposed Exercises

Below is a set of exercises proposed to practice the knowledge acquired in this unit. They are optional, but it is recommended to complete them to better assimilate the concepts. They will help you prepare the unit’s practical assignment.

1. PostgreSQL

By your own way, install and test a connection to PostgreSQL DBMS. Take screenshots of each step and create a presentation explaining if you had any difficult point.

Recommended work is:

1. Install a docker container with postgreSQL. PostgreSQL works in 5432 port. Create a redirection from 2345 from your machine to the docker container.
2. Install DBeaver software (an open source program to allow to connect to almost all DBMS). Create a connection to the postgres container.
3. Create a Java program to connect to your container, adding the corresponding driver.

2. SQLite - Login

SQLite is a special DBMS, because it isn't a DBMS. SQLite is an embedded database, because all the database (data and structure) is stored inside an unique file. This file can be stored in our projects, and it's the reason because it is called embedded: the database is inside our package or our Android App, etc. It's the ideal way to store a few information inside our little apps.

Warning

The main drawback of sql is security, because it does not offer a login system. You have to protect the database file with system security.

Do this steps taking screenshots of every important point:

1. Install SQLite, version 3. You can install SQLite from linux repositories or unzipping a bundle of programs in Windows systems. You need to add these programs's folder to the system path in order to run from anywhere.
2. Create an empty database. Syntax is sqlite3 newDB.sqlite. Once the database is created, the sqlite prompt appears, and you are ready to work. Special commands in sqlite starts with point (.). You can list them with .help.
3. Create a new table, running for example:

   create table student(
       dni varchar(9) primary key,
       name varchar(50),
       age int);
   

4. Insert a few rows: insert into student values(,,)
5. Select the rows you have inserted.
6. Exit from sqlite (.quit)
7. Create an script file with a new table and a few insert, and store in the same folder than the database you are working (for example teachers).
8. Enter again in the database, and test .shell ls, .read, .tables and .schema commands.

3. Login Process

Almost all applications (web, mobiles or desktop) make a login task before it grant access to the resources. Let's go to do a login and register windows.

In Aules you will get an empty project who show this window when you run it:

{width=60%}

User will now enter its information, and it could happen that the USER were wrong, and then:

{width=90%}

or it could happen that the PASS were wrong, and then:

{width=90%}

or both are well, and the app says:

{width=90%}

If the user press Alta nuevo usuario, the data stored in the boxes is inserted on database.

You have on the platform a partially implemented classes. Complete the TO-DO sections.

3.1. A SQLite database

Create an empty database in SQLite, with a unique table USERS with username and password. The password must be a character string of length 40:

create table users (
    username char(20),
    password varchar(40) not null, 
    primary key (username)
);

When we store passwords in files and databases, saving them encrypted is mandatory. You have a Xifrar.java class, that allows you to encrypt passwords.

4. Ask for update

Create a Java program to :

1. Select an entire table from a database by your own (table and database).
2. The program will show row by row, and ask the user what he wants to do:
3. Edit (e): the program ask the column (number) and the value to update continously. When 0 is entered the program update the row and then, follow showing the rows
4. Delete (d): the program makes the classical question, Are you sure you want to delete (y/n)? and do what the user says.
5. When all the rows will be shown, the program ask for adding a row, and then ask for all the columns values, and save it.