Into Databases

[lala56]

Part I

The term database refers to a specific set of data, which can be accessed and manipulated through a database management system (DBMS). The databases themselves can be object-oriented, relational or hierarchical and there is a wide variety to choose from. Some DBMS are available only for specific operating systems. They can be small or large and they come at a price or for free (open source). In the following I will describe the basics of a relational database management system (RDBMS).

The conceptual description of your business is a data model. In your model you have entities with attributes. If you were to sell shoes one of your entities is shoe and attributes are price, size, colour etc. You assign to each attribute a domain, which is a specific data type. If we take the attribute price the data type “decimal number†would not be sufficient, so we specify the domain, which is currency. This makes sure the decimal number has only two decimal places and cannot have any negative values.

In your data model it is important to analyse what kind of relationship there is in between the entities. If your shoe business is big enough to have several stores you could have another entity called “storeâ€, which should be connected to the shoe entity. To do so it is important to analyse the number of participants in this relationship (cardinality) as there are four general options, which are one to one, one to many, many to one, many to many. Let us say a certain pair of shoes is just sold in one store. You all will agree this is a one to one relationship. But if the same pair is sold in various stores, then this is certainly a one to many relationship. I am sure you will also agree there might be more than one pair of shoes, which is sold in a certain department (many to one) and it even could be that there are 5 different pair of shoes, which are sold in 3 different stores (many to many). Let us also consider the direction of the relationship (directionality). If the relationship flows in both ways it is bidirectional, otherwise a unidirectional. As the one to many and many to one bidirectional relationship is one and the same thing this gives you seven options to choose from.

To simplify the relationship issue you can use entity relationship diagrams (ERD), which will help you to get a clearer understanding of the existing relationships. Those use symbols as entities and arrows to show the direction(s) of the relationship. They also show how many participants are taken part in any given relationship. If you were interested in the use of them just enter “entity relationship diagrams†into Google and you will come up with a wealth of information. For a great definition of ERD please relate to "http://en.wikipedia.org/wiki/Entity-relationship_model".

This is all very theoretical, but it will save time and effort later on if done with the necessary commitment. Once you have your data model established you start your data schema, which is the physical design of your database. Part II will explain how to design the actual database and should follow in due course

 

[temi]

Nice post Lala, rep added

 

[lala56]

Thank you Temi,

Sounds great (lala is just really nice). This encourages me even more to write some more articles...

lala

 

[temi]

Yes, do you ever doubt that Lala is a really nice woman? LOL

 

[lala56]

Part II

Cheers, Temi. You made my day

And here it comes:

Part II

According to your data model you will work on your data schema now, which is the design of your database. A database consists of a number of tables. Each table represents one entity. It has columns and rows. The columns stand for the various attributes of the given entity. The rows are called records. One record is a unique set of attributes, representing one particular instance of the given entity. If you want to go totally scientific into the subject you can call one set of attributes a tuple.

In our shoe example the entity shoe had the attributes price, size, colour. Let us also assume that we only have one model, which is unisex. I know it is very unlikely to have just one model if you want to be successful in the shoe industry, but this assumption makes the explaining easier. Our sizes start at 37 and go up to 45. We have two different colours and our prices increase by £1 per shoe size. That means that we would come up with 18 records (9 sizes multiplied by 2 colours), where two of them would always have the same price (same size, but different colour).

Now this all is very nice, but don’t you agree it might get a bit messy. It would be beneficial to find a unique identifier for each record, so we know for sure, about which one we are talking. A primary key is exactly what we are looking for. One or two “normal” attribute(s) can serve as the primary key. In our given shoe example we could take the size and colour as our primary key. There is no shoe with same size and colour twice. But as our requirements might change (we might even consider the possibility of having more than one model) this would not be a good idea. It would be better to have a designated unique identifier. When this one is set up via your Database Management System (DBMS), it won’t be possible to use the same primary key more than once, which makes sure each record is uniquely referenced.

So, each record in our shoe table is unique. Now we produce a table called “store”, which has the columns name (which is the primary key), street, city, postcode, telephone number and manager. It makes more sense to have this kind of information separate from our shoe table. As we have three stores we have three records. Would it not be nice to have in our shoe table a reference to show where customers could buy a certain pair of shoes (combination size/colour)? Normally you might get a shoe size/colour combination in any branch of a shoe business, but our stores are different. We have each combination just once;-)

So, how would we do this? We use a foreign key, which is essentially the primary key of the records in the table we want to reference. So, our foreign key in the shoe table is actually the primary key of the store table (which is the column “name”). Confused? Just let it settle for a bit. It makes perfect sense. The foreign key in the referencing table is the primary key of the referenced table.

We are almost done with the second part. I just want to mention one more concept, which fits nicely into this section. It is the referential integrity, which relates to the consistency of the relationship between tables. This means we cannot use a foreign key, which does not exist as valid primary key in the table we reference. Also, if we change or delete any records in the referenced table we have to make sure all the appropriate records in the table with the foreign key change accordingly. This can be achieved by a cascading update or cascading delete. If you are interested in finding out more about this concept you will find plenty online available.

To continue with our database research, part III will follow shortly…