We are learning ...


So that we can ...




A database ...


Databases can be ...





A database is actually a collection of objects. Some of the objects hold data, some define its structure, some aid sorting and searching the data and some determine how it is displayed.



A data model is a method of describing the data, its structure, the way it is interrelated and the constraints that apply to its organisation. A conceptual data model is a representation of the data requirements of an organisation constructed in a way that is independent of any software used to construct the database.


Data models are generally constructed based on rules first proposed by Peter Chen in 1976, where the data model views the world as distinct entities, described by attributes and relationships between them ...



Data models are represented using Entity Relationship Diagrams, or ERDs.



Entity occurrences

Any entities you identify in a complex system like this are always singular, i.e. 'Pupil', not 'Pupils'. You will see this written incorrectly in many places online and in textbooks (shame). The entity describes the characteristics of one instance of the entity - a so called entity occurrence.


Importantly, there has to be a way of uniquely identifying each entity occurrence, in this case by the pupils name. In practice, something more complicated is often used, like an ID or Code (which would allow the pupil to change his / her name without compromising the 'uniqueness' of the entity occurrence).

More on relationships

There are three main types of relationship that can exist between entities ...


... plus all three relationships can be optional (zero or zero or many) or mandatory (one or one or many). Since the relationships are binary (i.e. have two sides), that gives us 36 different combinations. The relationships are shown using special arrows, one side of which are shown in the diagram.


The entities themselves are shown using boxes with at least the entity name and often with the attributes as well, though we'll look at that in a second. Each binary relationship can be 'read' from Left To Right (L2R) or Right To Left (R2L). Consider the following examples before attempting the task which follows.




More about attributes

Data about each entity occurrence is stored as values in attributes within the entity itself. There are three 'types' of attribute which we use to describe both the entity occurrence and define the relationship between each one.



You choose a primary key based on any unique characteristic of the entity or, if one doesn't exist, you make one up. Primary keys can be single attributes or combinations of more than one attribute - whatever is simpler to work with. No matter what, however ...

The Primary Key must uniquely identify each entity occurrence and cannot be repeated!

Primary key attributes are often underlined whereas foreign key attributes are italicised however, I'm likely to simple add the suffix [PK] for the primary key and [FK] for the foreign key, if that's OK?


What practical significance does this have? Read through the following examples, one at a time ...






Resolving the many to many relationship problem

As we have just seen, many to many relationships are bad because they are difficult to implement without breaking the rule of the Primary Key - that it should be unique.

I suppose that, in the example we looked at, we could have a combined primary key of

TeacherID

and

PupilID

in both entities which would allow one entity occurrence for every teacher - pupil partnership but that would also mean that each entity occurrence would have a copy of the teacher forename and surname and the pupil forename and surname which introduces a whole raft of problems around data consistency and unproductive maintenance (see later).


To fix this, we introduce an associative entity which sits between the two other entities and holds details of a single occurrence of the relationship between the two entities. For instance ...


Here, the associative entity,

Taught

, holds each instance of a Teacher teaching a pupil and, through the inclusion of a separated

TaughtID

, allows a teacher to teach a pupil multiple times (maybe in different classes). This model also allows an extra non-key attribute,

Notes

, to be used to keep notes on each Taught entity occurrence, such as which class they were in or how they are progressing, for instance.



Investigating complex relationships

Since life is complex, real life data models tend to be complex as well, often involving many entities and complex relationships between them. Before we move on, it's time to have a look at some complex relationships courtesy of Barry Williams!





At about the same time as Peter Chen proposed the Entity Relationship Model, a computer scientist working for IBM called Edgar F. Codd invented a practical realisation of this theory when he designed the first the Relational Data Base Management System or RDBMS.

Both the Entity Relationship model and the RDBMS originally served to separate the data from the processes which managed it.

A simple, flat data structure

Let's consider a simple example of data storage - a customer list. A tabular representation of the customers is correctly called a relation (but often a table) and is a realisation of the customer entity which we have already met (so many italics).


In order for us to be able to refer to the relation, we must give it a unique name within the relation. Each record (more accurately called a tuple) must have a unique value which can be used to identify it also which we refer to as the primary key. We can not define the structure of our relation using standardised table notation as follows if we call the relation customer.


This relation name refers to the entity in the data model. Notice that the table name is emboldened and the primary key is underlined. This structure definition is the heading and the data in the table is the body. Remember that a tuple is a row of data in the table (similar to a record) and an attribute can be represented as a named column in the table.



Note : if you do much work with Access databases, you will often use Leszynski / Reddick table notation to represent the names and structures of the database objects. Even though I used to do this when I worked with Access, I've chosen not to name the objects in this fashion to reduce (!) the complexity of the topic. However, feel free to prefix your relation names if you wish!