| |
|
|
| |
|
1) Tutorial on Data Modelling for Young People :
In this Tutorial, we follow two young visitors to Windsor Castle in England.
An iPhone Version is being created.
Our visitors are Dimple, a 10-year old girl, who likes sightseeing and icecream
and Toby, Dimple's 12-year-old brother, who likes sightseeing and databases.
[Dimple] : Toby, It's great being in London which is so exciting and buzzing.
[Toby] : I'm glad you like it, Dimple. What would you like to do today ?
[Dimple] : Toby, we have seen Buckingham Palace, where the Queen of England lives, and now I'd like to visit Windsor Castle, because it's one of the most popular tourist attractions in England, and it's easy to get to from London.
[Toby] : OK. Let's go ...
|
|
2) WINDSOR CASTLE
[Dimple] Wow, Toby, there are so many Banks, Cafes, Pubs, Restaurants, Shops, Wine Bars and Hospitals !!!
There is even a Castle, as well as a Royal Park with Deer.
[Toby] : Yes, let's start thinking about our Data Model.
The other thing that we see when we look around is people - lots of people.
|
|
3) STARTING OUR DATA MODEL :
[Dimple] : How do we get started ?
[Toby] : Well, we know that we have People and Places.
The simplest start is to call all these places Establishments.
Then we simply have different kinds of Establishments.
And we have people - local people, visitors, people working here, people here on business and so on.
[Dimple] : Hmmm - so how do we translate what we know to help us get started with our Data Model !!!
|
|
4) IDENTIFIERS AND PRIMARY KEYS :
[Dimple] : Toby, I am one of these People so how I am I going to identify myself from everybody else.
[Toby] : We will give everybody a Unique Identifier and every Establishment its own unique Identifier.
When we use these we call them Primary Keys, and show them in the diagram with a PK on the left-hand side.
[Dimple] : That sounds good, Toby, but I don't know what it means.
[Toby] : Well, Dimple, let's look at how we use these Identifiers ...
|
|
5) RELATIONSHIPS AND FOREIGN KEYS :
[Toby] : Dimple, now we can add some interesting details because we know that one person can visit many Establishments.
We also know that one Establishment is visited by many Visitors.
Then we call this a Many-to-Many Relationship between People and Establishments.
To make it easier for you to understand I have expanded the Many-to-Many Relationship into two different things, which are called One-to-Many Relationships.
[Dimple] : So Toby, is that like saying that One Person can make Many Visits to many Establishments ?
[Toby] : Yes, Dimple - that's great - and we can also say that One Establishments can have Visits from many People.
At this point, we can show how all these boxes are related, and that is a very big step, because it takes us to the idea of 'Relationships'.
And now we can see how useful our Identifiers can be because we can include the Person and Establishment Identifiers in our Visits table. Then the Person_ID field becomes a link to a record for a Person in the Person Table.
This link is what is called a Foreign Key and we can see it's shown with 'FK' on the left-hand side.
|
|
6) PRODUCTS AND PRODUCT TYPES :
[Dimple] : Toby, when we go into a shop we want to buy something.
And there are thousands and thousands of possibilities.
How do we deal with all that in our little Data Model ?
[Toby] : Well Dimple, it's really quite easy. It's like all our Modelling where we look for simple patterns that cover many situations.
[Dimple] : Hmm - I don't know what that means. Maybe if you showed me I might understand it.
[Toby] : OK.
Everything that we buy is called a Product, and all we have to do is simply define the type of each Product - such as a Coffee, Muffin or a Newspaper.
Then we draw a little box called Products and say that every Product has a Type.
In other words, there is a Relationship between the Products and Product_Types boxes.
The lines are called Relationships and they are very important in Data Modelling.
We are now creating an Entity-Relationship Diagram or "ERD".
|
|
7) PRODUCTS, TYPES AND PRODUCT HIERARCHIES:
[Dimple] : Toby, when we look closely at the Menu Board to try to decide what to order we can see lots of possibilities
But after a while we can see a pattern that helps us decide.
How do we deal with all that in our little Data Model ?
[Toby] : Well Dimple, it's really quite easy.
We define something called a Hierarchy.
Hierarchies are very common and simply mean any situation where there are Parents, Children, Gand-children and so on.
If we look at the Starbucks Menu Board on the right-hand side we can see a simple example of 'Expresso' and under it a number of different drinks.
My favourite is Caramel Macchiatto.
So in this case, the top-level of our hierarchy is a Product Category called Expresso, and the next level down is a Product called Caramel Macchiatto.
[Dimple] : OK. That sounds OK.
[Toby] : Finally, we show this Herarchy by a dotted line that in the top-right hand corner in the Entity called 'Ref_Types_of_Products'.
This is formally called a 'Reflexive' relationship and is informally called 'Rabbits-Ears'
|
|
8) TYPES OF PEOPLE :
[Dimple] : Toby, that looks OK.
I guess we can deal with Types of People the same way, can we ?
[Toby] : Yes, Dimple, and Types of Establishments as well.
[Dimple] : OK, that sounds sensible. And do they use these Identifiers in a Database ?
[Toby] : Yes, and what is even better is that the Database will automatically generate a new unique Identifier for you and your Visits and Purchases in case you want to get a refund later.
|
|
9) TYPES OF PEOPLE AND ESTABLISHMENTS:
[Dimple] : Toby, that looks OK.
I guess we can deal with Types of Establishments the same way, can we ?
[Toby] : Yes, Dimple.
[Dimple] : OK, that sounds sensible. And do they use these Identifiers in a Database ?
[Toby] : Yes, and what is even better is that the Database will automatically generate a new unique Identifier for you and your Visits and Purchases in case we want to keep track of things, like maybe you want to get a refund later
so we need to get your details from the Database.
[Toby] : Before we move on, let's talk about Establishments.
One special thing about Windsor is that it has a Castle where the Queen lives and a very large Royal Park, where she keeps Deer.
But when we think about these things, we find that we can simply fit into our definition of Establishments.
|
|
10) VISITS AND PURCHASES :
[Dimple] : Toby, with so many People, Establishments and Purchases how do they keep track of everything ?
[Toby] : Well, Dimple, by this time, everything has its own Identifier that is used wherever they need to keep track.
[Dimple] : OK, that sounds sensible. And do they use these Identifiers in a Database ?
[Toby] : Yes, and what is even better is that the Database will automatically generate a new unique Identifier for you and your Visits and Purchases in case you want to get a refund later.
|
|
11) PEOPLE AND STAFF:
[Toby] : Dimple, let's take a closer look at the different types of people we can find in Windsor.
[Dimple] : OK, Toby. I hope I don't have to think too much because I might get a headache ?
[Toby] : No, Dimple, I will do the thinking and talking and all you have to do is nod your head when you understand.
[Dimple] : OK, Toby. I promise to do that.
[Toby] : We already said that we have Local People and Tourists.
Some of these Local People are shoppers and some of them will be working in the shops.
We will call the workers 'Staff' and we know more about them than we do about the Visitors.
For example, we will probably know the Gender of everybody just by looking at them.
For Staff, we will usually also know their Data of Birth and their Home Address.
Therefore, although they are all People, we know more about Staff than we do about Visitors.
In Data Modelling we have a very powerful approach that we call Inheritance that we can use here.
If we want to describe this in English, we would say that Staff inherit the People_Type_Code and Gender from the parent Entity of People, and in addition, they have a Date of Birth and Home Address.
Does that make sense, Dimple ?
[Dimple] : I think so, Toby.
Is it like saying that we inherit having two arms and two legs from our Parents because they have two arms and two legs, but that we have also have things that are just us ?
[Toby] : Yes, Dimple - that's great - let's take a break and do some shopping !!!
[Dimple] : I like the sound of that, Toby. Can I have an icecream ?
|
|
12) STAFF, ESTABLISHMENTS AND DERIVED FIELDS :
[Dimple] : Toby, how do we specify that Staff must work in some Establishment ?
[Toby] : Dimple, that's a very good question.
Fortunately, the answer is very easy.
We add a One-to-Many Relationship between the Staff and Establishment Entities
In English, we would say that every member of Staff must work in one Establishemnt and every Establishment can employ many members of Staff.
In the diagram, we show this with a Foreign Key by the establishment_id field in the Staff Entity.
So if we look closely at the Staff Entity, we will see 'FK' by the establishment_id field.
[Dimple] : OK, that sounds good, and I can see how the Identifiers are very important.
[Toby] : I am glad to hear it, Dimple.
There is one more thing I have to say.
We are learning Data Modelling and one important thing about Data Modelling is that it has to follow a set of Rules.
These Rules help us to produce good Data Models and so they they are very important.
One of the Rules is that we cannot include any bits of data that can be derived from any other bits of data.
For example, we usually want to know how many people work in a Shop or Cafe.
Therefore we include a Staff Count field with the Establishment.
But when to comes to finding the value that goes in here, we will count the records in the Staff Table for each Establishment.
Therefore, it's a derived Field and we call it a name that starts with 'derived_' to make things clear.
Does that sound sensible, Dimple ?
[Dimple] : I suppose so, Toby.
But I've got a headache, can we go for an icecream now ?
|
|
13) REFERENCE DATA :
[Toby] : Dimple, you can see that I am using a Gender Table and People Types Table.
I have given them both names that begin with 'Ref_' to make it clear that they are Reference Data.
This means that the values don't change much and I can use them to define what the valid values can be.
This is a technique that professional Data Modellers but we don't need to worry about it today.
[Dimple] : I'm glad to hear it, Toby !!!
Although it isn't difficult to understand and it seems like a good idea.
[Toby] : In our small example, we have only four kinds altogether -
Gender, Types of Establishment, People and Products.
|
|
14) BRINGING IT ALL TOGETHER :
[Toby] : Dimple, if we bring together everything we have talked about, we will see that we have quite a good Data Model that any professional would be proud of.
[Dimple] : OK, Toby. Do you think I will understand it ?
[Toby] : Let me help you by making a list of the Business Rules for our Model :-
People can be either Staff or Visitors.
People can make Visits to Establishments
Products can be Coffee, Souvenirs and so on.
Staff have a Date of Birth and a Home Address.
People can make Visits to Establishments.
During a Visit they can purchase Products.
[Toby] : OK, Dimple - we have a very nice Data Model and now we can take the break I promised you.
[Dimple] : That's great, Toby - can I have an icecream ?
Sure, but before we do I should say something about PF, which appears in the Staff Table.
It's unusual and it's called PF because it means a field which is a Primary Key in the Staff Table and a Foreign Key to the People Table.
[Dimple] : Hmmm, I've got a headache, Toby - can we please go an get an icecream ?
[Toby] : OK, Dimple. You've been a very good girl and you deserve a break.
|
|
15) ICECREAM :
[Toby] : Dimple, I 've got some wonderful news for you.
[Dimple] : I'm glad to hear it, Toby - what is it ?
[Toby] : I have found your favourite icecream here in Windsor, which is Baskin-Robbins ;)
[Dimple] : Toby, are you teasing me ?
[Toby] : No, Dimple - look, there it is across the road from Windsor Castle !!
[Dimple] : Wow - that's great, so I can have my favourite Butter Pecan icecream.
|
|
16) PLEASE EMAIL ME
I hope you have found this Tutorial fun and useful.
I would be very pleased if you could
email me with your questions or suggestions.
In this Tutorial we have covered the following Topics :-
- Hierarchies
- Identifiers
- Inheritance
- One-to-Many Relationships
- Many-to-Many Relationships
- Primary Keys and Foreign Keys
- Reference Data
Barry Williams
Principal Consultant
Database Answers Ltd.
London, England
|
|
|
|
