Welcome to our 'Cradle to the Grave' Tutorial   Back to the Home Page 1) INTRODUCTION : This Tutorial is for beginners to Data Modelling. Topics covered in this Tutorial include :- Creating Entities Primary Keys Foreign Keys 1-to-Many Relationships Hierarchies Inheritance Reference Data I have used the Dezign Data Modelling Tool to create the Data Models in this Tutorial. I chose Datanamic because it is powerful, flexible and affordable. At the end of this Tutorial, we will have designed a number of Logical Data Models, which are commonly referred as Entity-Relationship Diagrams, or 'ERD' On July 4th. I added Conceptual Models to make things easier to understand. I created these in colour in Microsoft Word and their purpose is to be more 'user friendly'. If you are completely new to Data modelling, I recommend you go through this Tutorial and just look at these coloured Conceptual Models until you understand what is going on. Then look at the Logical Data Models and see how they compare. I hope you find this Tutorial interesting and helpful. Please email me and let me know. 2) FROM THE CRADLE Topics covered include :- Entities Primary Keys The approach I have chosen for this Tutorial is to cover the Life span of a typical human being, from the cradle to the grave. This allows me to trace the increasing complexity in life and match it to an increasing complexity in Logical Data Models. The first task is to decide on the scope of our Data Model. My Approach has three Steps :- Establish the Scope of the Data Model Identify the 'Things of Interest' that are within the Scope, These will be called Entities. Determine the Relationships between them. DECIDING THE SCOPE OF OUR DATA MODEL I have decided that the Scope is 'From the Cradle to the Grave'. This will include childhood, teenage years, becoming a student, getting a job, getting married, getting sick, and finally dying Therefore, anything outside this scope are not 'Things of Interest'. 3) ME AND MOMMY Topics covered include :- Entities Foreign Keys Primary Keys One-to-Many Relationships How to create an Entity in Dezign 1. Right-click on a blank area in the diagram 2. From the drop-down list, choose Insert and Entity 3. Check the 'PK' box for the Primary Key attribute, which will usually be the first one on the Entity. 4. Click on Close to save the results. 4) ME, MOMMY AND MY MEALS : Topics covered include :- Entities Foreign Keys Primary Keys One-to-Many Relationships Many-to-Many Relationships Reference Data At this Stage, we show only the Entities with no Relationships and minimum Attributes. and specify only the Primary Key and one 'details' field that will be replaced later on. The Primary Key field(s) should always be first. You will notice that the first field in the Customers_version2 Table is the Customer_id. It has a 'PK' symbol beside it, which indicates that it is the Primary Key for the Table. The Primary Key is very important and is the way that we can recognise each individual record in the Table. Creating a Primary Key in Dezign 1. Right-click on the Entity 2. Choose Attributes 3. Check the 'PK' box for the Primary Key attribute, which will usually be the first one on the Entity. 4. Click on Close to save the results.               5) ME AND THE PLAYGROUP : Topics covered include :- Entities Foreign Keys Primary Keys One-to-Many Relationships Many-to-Many Relationships Here we have added the Relationships between the Entities. When this Primary Key is used in another Table, it is referred to as a'Foreign Key'. We can see a good example in this diagram, where the customer_id appears in the Customers_Payment_Methods Table as a Foreign Key. This is shown with an 'FK' symbol beside it Mandatory Key Fields A Foreign Key is usually mandatory, in other words, a value for a customer_id in the Customers_Payment_Methods Table must correspond to an actual value of the customer_id in the Customers_Version_1 Table. This is shown in the diagram by the short straight line at the end of the dotted line close to the Customers Table. Foreign Keys in Dezign 1. Foreign Keys are created automatically when you make a Relationship between two Entities. 2. I recommend that you move the field up in the Entity so that it takes it place alphabetically among the Key fields. To do this, right-click on the Entity choose the Attributes option, then click on the up or down arrow on the right-hand side. 6) I GO TO CHURCH SUNDAY SCHOOL : Topics covered include :- Entities Foreign Keys Primary Keys One-to-Many Relationships Many-to-Many Relationships Addresses One-to-Many Relationships A Customer can have more than one Payment_Method, for example, American Express or Cash. In other words, we would say that the Relationship is optional at the Customers_Payment_Methods Table end. This is shown by the symbol that has three small lines at that end of the Relationship dotted line, which is referred to as Crow's Feet. Optional Key Fields Not every Customer will have a Payment_Method. In general, they would but we need to allow for situations where Customers change their minds and don't buy anything. In other words, we would say that the Relationship is optional at the Customers_Payment_Methods Table end. This is shown by the little 'O' at that end of the Relationship dotted line.               7) I MOVE INTO STUDENT ACCOMMODATION : Topics covered include :- Entities Foreign Keys Primary Keys One-to-Many Relationships Many-to-Many Relationships Reference Data At this Stage, we add Reference Data. This diagram shows how the hierarchies of Products and Product Types that we have just discussed are shown in our Entity-Relationship Diagram. Rabbits Ears You will notice that the table called 'Product_Types_v1' has a dotted line coming out on the right-hand side and going back in again on the top-right corner. Data Analysts call this a Reflexive Relationship, or informally, simply 'Rabbits Ears'. In plain English, we would say that the Table is joined to itself and it means that a record in this Table can be related to another record in the Table. This approach is how we handle the situation where each Product can be in a hierarchy and related to another Product. For example, a Product called Panini could be in a Product Sub-Category called 'Miscelleneous Sandwiches' which could be a higher Product Category called 'Cold Food', which itself could be in a higher Product Super-Category called simply 'Food'. Next time you go into Starbucks, take a look at the borad behind the counter and try to decide how you design the Products area of the Data Model. You should pay special attention to the little 'zeros' at each end of the dotted line. These are how we implement the fact that the 'Parent Product Type Code' is optional, because the highest level will not have a Parent. 8) STUDENT ASSESSMENTS !!! : Topics covered include :- Entities Foreign Keys Primary Keys One-to-Many Relationships Many-to-Many Relationships Addresses Food and Drink are specific examples of the more general Thing called a Product. They inherit some common attributes from the Product, and also have some of their own. For example, Food can contain Nuts but Drink do not contain nuts, but both have a Product Name. The unusual symbol in the middle of the diagram, composed of a circle with two small lines underneath it is how Inheritance is shown using the particular Data Modelling Tool that I am using,which is called Dezign. Inheritance is a very important topic when you are creating a Data Model. In plain English, we would say that Inheritance occurs where a Parent-Child relationship exists between Things of Interest (or Entities). You can ask the simple 'Is-a' question - in this case, if we ask 'Is a Book a Product' then clearly the answer is 'Yes' so we think there is an Inheritance relationship between them. In the example of Inheritance shown in this diagram, we can see that all Products have Names and Descriptions. Therefore, Books, Food and Drink will inherit these characteristics from the parent Product. However, each type of Product will have specific characteristics that it does not share with other types of Products. For example, Books have ISBNs and Authors, but Food and Drink do not. One of the important things in your Data Model is to be sure you have identified all the Inheritance relationships. However, from many years of experince as a DBA, I should point out that relationship is often blurred in a real physical Database because it can be clumsy to implement. I sometimes find myself showing Inheritance in a Logical Data Model which then disappears when I design the Physical Database, which is what ultimately becomes the Database.               9) I JOIN FACEBOOK : Topics covered include :- Entities Foreign Keys Primary Keys One-to-Many Relationships Many-to-Many Relationships This diagram shows Address Types, which are an example of Reference Data. This kind of data has the following characteristics :- it doesn't change very much it has a relatively small number of values, usually less than a few dozen and never more than a few hundred. Therefore we can show it with a Code as a Primary Key. Data in Reference Data Tables can be used to populate drop-down lists for Users to select from. In this way, it is used to ensure that all new data is valid. Standards In the Address Table, you will see a field called 'iso_country_codes'. iso stands for the 'International Standards Organisation'. Where possible, it's always good to use national or international standards. Customer Addresses This is a general and flexible approach to handling Addresses in our Data Model. We have a separate Address Table, which allows us to have more than one Address for any Customer very easily. This design also has some other benefits :- We can accomodate more than one person at the same Address. We need to do this because different members of a family may sign-up separately with Amazon. With a separate table of Addresses, we can easily use commercial software to validate our Addresses. To find this kind of software, simply Google for "Address Validation Software". I have used QAS with great success in the past. With this approach, we can always be sure that we have 100% good Address data in our Database. 10) I JOIN THE SWIMMING CLUB : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Reference Data (eg Address Types and Payment Methods)               11) I GET A TICKET FROM A TRAFFIC COP : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Reference Data, eg Document Types and Record Status 12) I GET MARRIED : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Reference Data This Model was created using a different Data Modelling Tool, called ERWin from Computer Associates. It shows that if you are familiar with the underlying principles that you will be able to understand and ERD.               13) I BECOME A BASEBALL UMPIRE : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Reference Data, eg Division Types 14) I GO TO HOSPITAL : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Reference Data               15) TO THE GRAVE : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Reference Data 16) EVENTS IN MY LIFE : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Reference Data               17) EVENTS IN MY WORK : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Hierarchies (eg Organisations) Inheritance (from Information_Catalogue to Customer_Data, Invoices, Contracts and Orders. Reference Data eg Status_Codes 18) SUMMARY OF TOPICS IN THIS TUTORIAL : Topics covered :- Primary Keys Foreign Keys One-to-Many Relationships Many-to-Many Relationships Hierarchies Inheritance Reference Data If you would like to see other Topics included, please let me know. Tutorial.        19) PLEASE EMAIL ME I hope you have found this Tutorial interesting and useful. Please email me with your questions or suggestions so I can improve it. If you want to try the Dezign Data Modelling Tool, you can download it from the Datanamic Web Site. Good luck with creating your first Data Model !!! Barry Williams Principal Consultant Database Answers Ltd. London, England © DatabaseAnswers.org 2015