OnlineStore Relational

Relational database schema

Remote access to the MariaDB server of the course

To connect to the MariaDB server from anywhere with our own machine we will have to take into account the following steps:

• Download and install a MariaDB /MySQL access client application suitable for your machine's operating system. For Windows we recommend HeidiSQL, DBeaver for Mac, but you can find many others in the market.

  • Download HeidiSQL (DBeaver)here

• Configure the access parameters to the practice server:

  • Network type: MySQL (TCP/IP)

  • Hostname/IP: bbdd.dlsi.ua.es

  • Port: 3306

  • Username/password: The ones assigned to you by the FBDweb application to access your MariaDB database.

Access from outside the network of the University of Alicante

If you try to log in from outside the University you will probably get an access error due to a security check. In that case click here to request access and in less than a minute you should be able to enter. This permission lasts approximately 24 hours.

Importing backup copies

Backup copies include the creation of tables and the insertion of rows in such a way that you get an exact copy of the database used in FBD practices.

We assume that you have a MariaDB/MySQL server running on localhost, and a client that allows access to it. Normally, the client configuration will be similar to the one shown above, but changing the host to 127.0.0.1, and the user credentials to those set on your local server —root, if a non-admin user has not been specified.

It is important that the database server is configured according to the requirements of the subject, especially in terms of group by (aggregations) behavior. To do this, run on the client connected to your server:

SET @@GLOBAL.sql_mode=CONCAT(@@SQL_MODE,',ONLY_FULL_GROUP_BY,ANSI');

SET @@@SESSION.sql_mode=@@GLOBAL.sql_mode;

The links contain a text file (.sql) with UTF-8 character encoding. Depending on the operating system and the MariaDB server and client, you may have to convert to another character encoding if you want to keep accents and other regional characters - notepad allows you to save as in different encodings.

Download the OS202526.sql, the dump of the Online Store database. You can also download EjemploTodo20190727.sql, the database dump of the Ejemplo database used in the lessons.

Creation of the database

Option A

1. From HeidiSQL, or any other client, connected with a user with permissions on your MariaDB/MySQL (localhost:3306) to create databases and tables.

2. "File/Execute SQL file...". Or open the file and execute.

Option B

1. 1. By command line, the mysql executable needs to be able to be invoked from the path where the terminal is opened.

   2. Extract tol.sql from the archive and place it in the same path.

   3. Execute mysql -u xxx -pyyy (xxx is a user with permissions to create databases and tables, password yyy is assumed).

   4. Execute \. ToLtodo2022.sql

Important note: the file is UTF-8 encoded. Depending on the operating system and client used it may be necessary to translate it to another encoding to read correctly the characters of our language. In MS Windows this can be done easily with Notepad.