In this class we will learn about Data Modelling and Database Design.
We will look at different kinds of Data Models and learn how to model the entire world.

Slide 1
The Scope is what we see around us - for example, Students with a Teacher in a Classroom.
Slide 2


The 'Things' are the School, the Classrooms, the Teachers, the Students,
Subjects and Classes, but excluding, for example, Books.
Slide 3 Here we define the Business Rules that our Database must comply with.
For example -
R.1 In a School there can be many Classrooms.
R.2 In a Classroom, there can be many Students.
R.3 Each Student can register for many Classes.
R.4 A Subject can be taught in many Classes.
R.5 Each Teacher can teach many Classes.
Slide 4 From the Business Rules we can create a conceptual Data Model that looks like this -

This Model is very useful for discussions with all interested parties.
Slide 5


We can identify things that are small in number by a Name or Code.
This applies to the School and Classrooms and perhaps Teachers.
For other things, like Classes or Students, we usually generate numbers, such as ABC123.
Then, of course, we keep a table to translate each number to a specific name.
Slide 6
Here we show a Logical Model that has the Identifiers.
We can see that we have an Entity or 'Thing of Interest' in our Conceptual Model
and also we show how the Identifiers appear in a Logical Data Model.
Because of the detail, we used a commercial software Data Modelling package,
which we can then use to generate the Physical Database.

The Boxes are called Entities and the Lines show the Relationships between the Entities.
Therefore the Diagram is called an Entity-Relationship Diagram (or ERD).

One of the beautiful things about an ERD is that it tells a story.

In this case, the story goes something like this :-
1. A School can have a number of Classrooms.
2. Each Classroom can hold a number of Classes
3. Each Class is attended by Students, dedicated to a specific Subject and taught by a Teacher.

The PK in each Box marks the Primary Key, which is how we identify a record in the Table when
it becomes a Table in a physical Database.
For example, we can identify a School by its name, and a Classroom will uually have a unique number.
Each Student will usually have a unique number.
In theory, we could identify a Student by their name but it is too cumbersomed and error-prone so we
generate a number and always use that.

Subjects are relatively few in number so we can use a Code which looks like an abbreviation of the Subject.
For example 'Maths' for Mathematics'.


Here we show just the Identifiers for each Entity or Table.

Slide 6
Our Logical Model has more details and is a big step to the physical Database.
Because of the detail, we use a commercial software Data Modelling package,
which we can then use to generate the Database.


Here we show all the Attributes as well as the Identifiers.

Slide 7
Finally, we create the Cloud Service using, for example, Oracle's Apex.
This allows anybody to use our Database in the UK or around the world.
They can monitor attendance, print off Attendance Reports and so on.
Slide 7
Our Approach is to identify Subject Areas and treat the Student Platform as our Enterprise Data Model.
Step 8)
Here we show our Student Platform and a number of Subject Areas which is our starting point.

Slide 7
When we decide to expand our Student Registration Data Model, 
we can choose our Subject Areas from these Data Models that we have created previously  
Separate Data Models have previously been designed for each of these Suject Areas :- 
•Assignments 
•Attendance 
•Athletes and Teams 
•Behavior Monitoring 
•Class Scheduling 
•Exams 
•Registration (with Parents and Guardians) 
•Student Loans 
•Transcripts 
Step 9)
When we decide to expand our Student Registration Data Model, then this Conceptual Model from our High Level Data Model for Students could be our starting-point ...

Slide 7

Slide 7

Slide 7

a) Now let's consider the Question of 'How can we have a complete solution to every modelling requirement ?'
The solution is to go up as many levels as required until we get to 'the top' and then come down again.

Let's remind ourselves about our starting-point which is our General Approach ...


Slide 7
Now let's look at the most general solution we can think of.
We consider the Question of 'How can we have a complete solution to every modelling requirement ?' 
The solution is to go up as many levels as required until we get to 'the top' and then come down again.

This is a very general solution which reflects my life and my interests.
It can be applied by anybody, anywhere and adapted to suit their situation.
The next step is to go through each Subject Area Model and expand them and apply them to your particular situation.
Click here to see the subject Area Models that are available on my Web Site.


Slide 7
So far, we have looked at Conceptual Models, Logical Models and Physical Models.

Now we are going to look at Dimensional Models which are a very special kind of Model especially for gathering statistics and figures. They are called Dimensional because they feature Dimensions as their distinctive characteristic. The most common Dimension is a Time Period. For example if we wanted to say how many Students we have this year, that would be a number for the Dimension of a year. We show an example on the right.


Slide 7
The three other most common Dimensions are Region, Subject and Demographics.
Region is a geograpic Dimension, for example London or Surrey.
Subject is what we study, such as Data Modelling and Database design.
Demographics would be characteristics, for example Age.


Slide 7
This Logical Model shows four Tables of Reference Data, which are the Dimensions.
The four lines joining these Tables to the central Logical Dimensional Model Table are called Relationshhips.

They tell us important things about the Relationships, such as the fact that every piece of data must have a Time-Period. 


Slide 7
What kind of Data Models have we studied ?

Conceptual Models are  the highesta level and are very useful for people who want to understand what we are doing,
but are not interested in the detail.
Logical Models are more useful for people like me who want to know the details of the data that we are talking about.
For example, 'How many letters are there in the name of a Subject ?' 

Physical Models are used to create the Database, so they are important for defining things like

Finally, Dimensional Models, as we have juat learned, define the Dimensions that we can use to analyse our data 
and produce Reports.
They are important because many institutions have to produce Reports to meet the requirements of the Government 
or governing bodies.  

If you would like more information, you can email me at info@barryw.org.
 

 


Here we look at the way we can create a Data Model for the real-life situation of Feeding-Time and the ZOO

Real Life Situation

Real Life Situation
Step-by-Step Commentary
Step 1 : Getting Started

We can see a staff member feeding the animals
(in this case a group of Llamas from South America)

When we plan our Data Model, we realise that what brings
all the participants is the Event of "Feeding the Animals".

Therefore our Approach is to think firstly about the Event
and then how the people and animals are related to
each other in the context of Feeding-Time.
Real Life Situation

Real Life Situation
Step-by-Step Commentary

Step 2 : Our first Model consists simply of the Event Entity.

Event
Real Life Situation

Real Life Situation
Step-by-Step Commentary

Step 3 : Now we add the Animals Entity with a line between Animals and the Event to show they are related.


Animals and Events
Real Life Situation

Real Life Situation
Step-by-Step Commentary

Step 4 : We can see a staff member feeding the animals so we add a Staff Entity with a line to the Event to show their Relationship.
Animals, Events and Staff
Real Life Situation

Real Life Situation
Step-by-Step Commentary

Step 5 : Now we add Food with a line to the Event Entity.
Animals, Events, Staff and Food
Real Life Situation

Real Life Situation
Step-by-Step Commentary

Step 6 : Review our Data Model
Now is a good time to review our Data Model. First we realise that there will usually be more than one Animal participating in a Feeding-Time Event.
So we change the relationships to show that.
Food
Real Life Situation

Real Life Situation
Step-by-Step Commentary

Step 7 : Food in an Event
We have assumed that there will be only one type of Food provided at the Feeding-Time Event.
However, it is more reslistic to create a Data Model which
we can use in a situation where there is more than one type of food provided.
So we add an Entity called 'Food in an Event'.
It is very clear that there is only one member of Staff so
that part of our Model is correct.
Food at an Event
Real Life Situation

Real Life Situation
Step-by-Step Commentary

Step 8 : An Entity-Relationship Diagram (ERD)
The Model that we have created is a 'Conceptual Model'
which is very good for talking to business Users.
But for talking to technical people, such as developers,
it is useful to have an ERD for the basis of communication.

We have added some Attributes in each Entity or Table
to provide a foundation for future detailed work.
Data Model


Here we show our Self-Service approach to Agile Enterprise Data Modelling based on what is called a Hub-and-Spoke Architecture.
We have drafted a top-level Education Data Platform and have identified 8 Subject Area Models
that provide important details and can in turn be expanded to have Subject Area Models of their own.

Agile Data Model

Conceptual Data Model for Agile Education
Subject Area Data Models are available here :-
  1. Behavior Monitoring
  2. Class Scheduling
  3. Enterprise Data Model
  4. Online Exams
  5. Student Activities
  6. Student Assessments
  7. Student Assignments
  8. Student Registration


© Database Answers Ltd. 2017