DITA session 3

DITA session 3
Databases

In this session we talked about databases and SQL.
The first point looked at was how before databases were commonplace peoples information needs were hindered in a company by different departments all having their own information stored in their own way so if another department needed the information it would be difficult to get hold of and possibly in an incompatible format. This led to redundancy in information and inconsistency in peoples data.

A database allows data to be stored in a central place and allows users access to it (via a database management system) from many locations this resolves the incompatibility and redundancy issues.

This is a good way of dealing with data when you own the information as you can structure the database to suit the needs of the users and can fairly easily create search systems.

It however is not suitable if the data is not your own as the information is often unstructured and heterogeneous.

We briefly looked at Entity Relationship Modelling which is a little beyond the scope of this course but basically it allows you to describe the content of a database at a design level and is something that is done before any database is created and looks at the relationships between the things (entities) that will be in the database.

We then progressed onto SQL and how it is a language that allows communication between a user and the database management system to query what is in the database and allow housekeeping of the information.

A database is a collection of 2 dimensional data tables with rows and columns and the complicated part is what you do with the tables.

We then looked at the relationships between the entities within a database and how there can be 3 different kinds of these relationships the one to one where only one entity can be in one other, eg a painting can only be in one gallery, many to one where many items can appear in one entity, eg many paintings in one gallery, or many to many where lots of entities could be in lots of other entities, eg many painters in different galleries.

An entity is basically any thing and can have lots of attributes infinite in fact but a database should only collect relevant information for example a personnel database would need to know your name, address and phone number but possibly not your hair colour or shoe size.

When an entity has more than one attribute problems occur so you need to have more a table for each database table using the art gallery example from class we would have one table containing artworks which has the artist and date painted in them and another table for the galleries which could have the address, city and country in each of the attributes would be in a uniquely identified id so the two tables can relate to each other for example if the Mona Lisa is ID1 and it is in gallery 3 (where 3 is in the galleries table) then it can select the information from the right line in the gallery table to assign the correct gallery.

We then looked at how you query a database and a few of the keywords that have to be used.
Select colums
From tables
Where something is true

For example again using the gallery model
my sql> select name, country from galleries where country = “uk”;

The “” marks indicate you are looking for text within not a column.

Another example would be
my sql> select title
> from artworks
> where date > 1800;

You can split the query over many lines as the query only stops with a semi colon and in this case we have used a > to mean less than.

We then went into the lab to work with a real database to try and ascertain information about different aspects of the database.

This brought together the relatively simple theory and showed how complicated it can get when working with a real database the software is very particular and everything has to be done in a certain way it is case sensitive and everything has to be the correct terminology so for example typing Gallery when the table is galleries will respond with an error. It also showed how you have to understand the nature and layout of the tables before you can begin to query the database as if you don’t know what table an entity is in then it is impossible to stumble across the information you need. It is very precise and there is no way to work it out as you go you have to know the layout well.

To do this you can first type
show tables; which will show you the different tables in the database
desc authors; where authors is a table it will show the details of that table

This is vitally important as without this information the rest of the exploration is impossible.

We then had to develop queries using all that we had learnt to ascertain different pieces of information from the database.