Full Circle Magazine FR

Databases are used to store information about objects or data. In the previous tutorial, we mapped out how our books database would look. We designed tables for our data, and defined relationships between those tables. Now, we will put our planning into action by actually creating the database file, adding the tables, and creating the relationships. Creating the Database File As I mentioned before, Base is not a database file but an interface for accessing and manipulating a database file. Although it is possible to connect to many different database types, we will use the default HSQL database for our books database. To start the database wizard, select Database from the LibreOffice Home screen or File > New > Database. The first screen of the database wizard lets us choose whether we want to open an existing database or create a new one. Select Create a New Database, and click Next.

Les bases de données sont utilisées pour stocker des informations sur des objets ou des données. Dans le manuel précédent, nous avons défini comment serait notre base de données de livres. Nous avons conçu nos tables pour les données et défini les relations entre ces tables. Maintenant, nous allons réaliser ce que nous avons prévu en créer réellement le fichier de base de données, en ajoutant les tables, et en créant les relations.

Création du fichier de base de données

Comme je l'ai mentionné précédemment, Base n'est pas un fichier de base de données, mais une interface pour accéder et manipuler un fichier de base de données. Bien qu'il soit possible de se connecter à de nombreux types de bases de données différentes, nous allons utiliser la base de données par défaut en HSQL pour notre base de données de livres.

Pour lancer l'assistant de base de données, sélectionnez Database à partir de l'écran d'accueil LibreOffice ou Fichier> Nouveau> Base de données. Le premier écran de l'assistant de base de données nous permet de choisir si nous voulons ouvrir une base de données existante ou en créer une nouvelle. Sélectionnez Créer une nouvelle base de données, puis cliquez sur Suivant.

The second screen of the wizard asks us whether we want to register the database and what we want to do once the database has been created. Registering a database in LibreOffice makes it available in all our documents. We won't need this for our database, so select No – do not register the database. Check Open the Database for Editing, and click Finish. LibreOffice will open a file dialog to define a location and name for the database. I simply named the file: books Once you have a name and location for the database file, the main Base screen opens. Down the left side, you have the different pieces which can make up a database file. The top right gives you access to the different actions you can take for each part, and the lower right shows the objects already created.

Le deuxième écran de l'assistant nous demande si nous voulons enregistrer la base de données et ce que nous voulons faire une fois que la base de données a été créée. L'enregistrement d'une base de données dans LibreOffice la rend disponible dans tous nos documents. Nous n'avons pas besoin de cela pour notre base de données, vous devez sélectionner Non - ne pas enregistrer la base de données. Cochez Ouvrir la base de données pour l'édition, puis cliquez sur Terminer. LibreOffice va ouvrir une boîte de dialogue pour définir un emplacement et un nom pour la base de données. J'ai simplement nommé le fichier: books

Une fois que vous avez un nom et un emplacement pour le fichier de base de données, l'écran principal de Base s'ouvre. Sur le côté gauche, vous avez les différents éléments qui peuvent constituer un fichier de base de données. En haut à droite, vous avez l'accès aux différentes actions que vous pouvez faire pour chaque partie, et en bas à droite il y a les objets déjà créés.

Field Types Before we create our first table, we need to discuss some of the common database field types. When you select a type for a field, you are presented with many options for the type. Many of the types are identical, and are there for compatibility reasons. The most common types are: Integer – a whole number, eg. 123 VarChar – a variable length string of characters. You will define the maximum length for the VarChar. Date – a date value, of course, eg. 10-15-2012 (the exact format is location specific) Time – a time value, such as 09:15:25 decimal – a real number including the whole (integer) number and its fractional part, eg. 123.25 (the part separator is location specific) For our purposes, we will use Integer and VarChar.

Creating the Tables Base has three different ways to create tables: through a table wizard, through design view, and by SQL statements. The table wizard is good only to create specific types of tables by picking from a list of predefined field names. The SQL method requires you to know and understand the SQL language and is beyond the scope of this article. The design view is usually the best choice, and presents you with a list you fill in to create your table. We will use the design view to create our tables for this project. We will start with the Books table. Select Tables from the Database pane on the left. In the Tasks pane, click on Create Table in Design View… to open the Design View dialog. Across the top you have labels for each of the elements of a field: Field Name, Field Type, and Description. The Description is optional but is useful for making notes about how a field is used. At the bottom, we see the Field Properties. This section will change according to the type of field we select.

In the first field, enter the name BookID. From the dropdown box in Field Type, select Integer. Adding a description is up to you. Under the field properties, change AutoValue to Yes. This will place a key icon in the box beside the field record showing it is the primary (or key) index. In the second row, type Title for the name. Give this one a type VarChar. Again, a description is up to you. In the field properties, leave the length at 100, the default for VarChar. The third field is Published with a type of VarChar. Change the length in the field properties to 12. I chose VarChar rather than date because we just want the year, and if the publishing date of a book is unknown, I can enter just “Unknown”. Click on the save icon, and Base prompts you for a table name. Enter Books. Our tables for Authors and Media are created in much the same way. For Authors, create two fields: AuthorID, integer (AutoValue Yes); and Name, VarChar (length 50). For Media, MediaID, integer (AutoValue Yes); and Type, VarChar (length 25).

Our two foreign key tables need a little different treatment. In BooksAuthors, create two integer fields named BookID and AuthorID. Click on the icon box beside the first record. Holding down the Shift key, click in the icon box for the second. At this point, you should have both records selected. Right-click the icon box and select Primary Key from the context menu. This creates a combination key. The two values together create the primary key, which uniquely identifies each record in the table. For the BooksMedia table, create two integer fields named BookID and MediaID. Select both fields, right-click, and select Primary Key. Create Relationships Once we have all our tables defined, we can create the relationships that bind everything together. We will create relationships between our three main tables and our foreign key tables. The direction in which you drag the fields is important, so pay close attention to how you do it.

To start the Relation Design dialog, go to Tools > Relationships. You are presented with a list of tables. Select a table and click Add to add the table to the Relation Design. Add the tables in the following order to make it easy: Authors, BooksAuthors, Books, BooksMedia, Media. Once all the tables are added, select Close. Drag the BookID field in Books to BookID in BooksAuthors. A Relation dialog pops up. Under Update option, pick Update cascade and OK. This will cause the field to update when the Books table updates. Drag the AuthorID in Authors to AuthorID in BooksAuthors. Select Update cascade in the Relation dialog. Next, drag the BookID in Books to BookID in BooksMedia. Select Update cascade. Finally, drag MediaID in Media to MediaID in BooksMedia. Select Update cascade. Your relation design should look something like the one pictured below. With our tables and relationships created, we are ready to begin work on creating forms for data input. In our next How-To, we will create the forms for data entry. Everything will come together to create a usable data entry system.