Data Modelling Fundamentals

Data is everywhere – take your best friend for example, he/she has a gender, a height, set interests, an age, a sexuality etc. To make a model of a database, first find out what’s important.

Take a music album for example, what is important?

The Title, The Artist, The Year of Release, Number of albums sold, Producer.
In order to put data into a database, it needs a structure, some kind of a model. To put data into a database it needs to go through a process called – “Purpose of Normalisation” This makes the data consistent, clean and ensures a proper structure. Consistent e.g. all the same units of measurement. Normal Form  You can have more than 6 normal forms in theory. First normal form Only “one of each” in each column of a database   – Database requires a primary key In the table above, it is the Student ID.  The ID is unique to every student, whereas age and exam score can sometimes overlap. Second Normal Form Remove redundancy (repetition) of data in rows To handle repeated data Students are the entity – the entity is described by the ID, exam score, age and town attributes. We could make another entity, called Town. The student entity would then be give a Town ID – which would be the ‘foreign key’ The foreign key is one of many side The primary key is one the one side. So one student can only be in one town. But one town can have many students. A “crow’s foot” notation represents the many side: (please ignore the contents of the cells shown in the screenshot below)

or by Chen notation (again, ignore the data, it’s not the same as the Excel Table)

The many students – to one class relationship is called cardinality in Chen’s diagram/organisation.

Using the Excel Table from above:

We could give Chester a code of 1, and Wrexham a code of 2. So that table would then look like this:

 

We would then have another table, that told us which code, represented which town

Important types of data

INT – integer – whole numbers eg. 42
DECIMAL – floating number (decimal numbers) – 42,1
DATETIME – Date and tone in one – 12.24.2013
VARCHAR – String(text) – Niels .  Used when filling in forms for example, an address. It can be varied, but usually has a limit of how many characters it can contain. Postcode on the other had, would probably be CHAR and would have a set, predetermined number of characters.
See w3 Schools for more information
Relationships
One-to-many – one town can have many students
Many to many – many students can live in many towns
One-to-one – consider if these should be replaced by an attribute

Modelling to Design
Need to create foreign keys on the many-side
Enter datatypes
Many-to-many relations resolved by an intermediate table which acts as a key for the data

The intermediate table gives each many-to-many scenario it’s own ID:

 

MySQL Workbench
Can be downloaded from here

You can then create a database
Click “create New EER Model”

Click “Add diagram” near the top

Click on the table icon:

 

Then click on the to the right in the workspace
Give the table a name of “Students” by double clicking on the table icon
Click under the Column header to enter a column of ID

Make sure Datatype is INT, keep PK ticked for Primary Key, NN is ticked for Not Null (has to have an entry) and tick AI for auto-increment

Click below to add Score, Age and Town

You can also make another table for Town, and give each town an ID and name.
A third table would then have to be created for the foreign keys.
Click the tab at the bottom that is called “Foreign Keys”
Give the Foreign key a name, such as Towns_Students_Town

 

References