Category: Advanced Database

For this module we are going to create a database for a car repairs garage.

We are going to set up some tables and a form in this section to demonstrate the principles.

[the_ad id=”12355″]
[the_ad id=”12356″]

First, create a new database in LibreOffice Base called ‘Car Garage’.

Then select tables and click create table in design view.

Add the fields shown, setting the Car Reg Number field as the primary key by right-clicking on the grey area to the left of the field and selecting primary key.

Save the table, giving it the name ‘Car and owner details’.

Open the table and enter some data.

Notice that the account settled field, which was set to ‘Boolean Yes/No’ data type has a checkbox.

Create another table with the fields shown, setting ‘Staff Number’ as the primary key.

Populate with similar data to that shown below.

Name the table ‘Mechanics’ and save the data.

Create one more table in design view and add the fields to the left, setting ‘Job No’ as the primary key and selecting ‘Autovalue’ so it automatically creates unique job numbers.

For the ‘Date’ field, in the properties box below, click the … symbol next the Format example box.

This will open a new dialogue box.

Here you can select from a selection of default formats for entering the date records.

These input formats are known as input masks. We will look at these in more detail later on in this chapter.

For now, select the top format from the list.

Save the table as ‘Jobs’ and close it.

Open the table ‘Jobs’ from the main window and enter data.

Structured Query Language (SQL) is used to manage data within a database.

It uses relational algebra to create commands that retrieve or maintain the data.

Some examples of commands are CREATE – to create tables, RENAME – to rename tables, SELECT – to select data records, INSERT – to insert data records and DELETE – to delete data records.

[the_ad id=”12355″]
[the_ad id=”12356″]

To see examples of SQL code, open the database ‘Company Employees’ (created in module 1 or available for download).

Right click on one of the queries, and select ‘Edit in SQL mode’

This brings up a new window displaying the SQL code for that query, with the commands highlighted in blue text.

The main use for SQL is in querying databases to retrieve information.

All queries created in LibreOffice Base use SQL, even ones created in design mode.

The average user won’t need to use SQL code, it’s mainly used when dealing with highly complex queries or to write entire systems.

It’s useful to know about it, but you don’t need deep understanding for this module.

Logical Design – must be carefully designed to incorporate all the data and applications needed, including possible future changes and developments. Define the output(s) required, then work backwards to include all the elements needed.

[the_ad id=”12355″] [the_ad id=”12356″]

Database Creation – set up tables, relationships, forms for inputting data, queries and reports. This needs someone with good technical understanding of databases.

Data Entry – data can be inputted using the forms created in the last step. This doesn’t require technical knowledge if done manually, but can be a long process. Sometimes data can be imported from other sources.

Data Maintenance – this is an important step to maintain integrity of the data. For a large database, a database administrator will be needed to add new data and delete obsolete data.

Information Retrieval – information can be retrieved via queries, forms and reports. Depending on the applications of the database, users may be restricted to using the queries and reports that are provided or they may be able to create their own. Larger organizations may hire database managers to use SQL for complex functions.

Databases are systems used for the storage, maintenance and retrieval of data.

In the modern ‘internet age’, databases are used widely to power ‘dynamic websites’, meaning prices, product details, mailing lists and other large groups of data can be easily updated and searched.

[the_ad id=”12355″]
[the_ad id=”12356″]

Website content management and customer relationship managements (CRM) systems use databases to improve services and marketing.

As well as commerce and service provision businesses, databases are also frequently used in other industries such as manufacturing, to facilitate logistics, inventory, distribution and invoicing. These are called enterprise resource planning databases.

For example, a large e-commerce website that sells a range of products will probably use:

– A dynamic website powered by a relational database that contains prices, product details and specification, reviews, etc. Data can be filtered and retrieved easily using queries that can be built-in to the website’s User Interface (UI).

– A website content management system which uses a simplified database interface so data can also be updated and changed easily by a site administrator without much technical knowledge.

– A customer relationship management system that tracks metrics such as number of visitors, time spent on site, purchases made, etc. This data can be retrieved and reported to provide insights for the business owners on how effective sales and marketing efforts are, for instance.

Hierarchical databases are designed using a ‘tree structure’, that has groups of parent/child relationships. They are fast to create and easy to use, but limited and inflexible as they contain lots of one-to-many relationships.

Relational databases consist of a set of related tables. They are flexible as they can be rearranged in various ways without having to reorganise the tables. They are fairly easy to create, maintain and make data retrieval simple.

Object-oriented databases use object-oriented programming languages. Each object belongs to a class. These types of database are limited as they don’t all support Structured Query Language (SQL), thus making data retrieval difficult.