4.1 Data modelling, increasing the detail

4.1.1 Optionality and cardinality

Consider an example based on the manufacture of various products. We will call the producer of these products G&P, which is a hypothetical manufacturing company that produces various products relating to what we might classify as Home Care products…things like toothpaste, detergent, shampoo, soap, beauty cream, hairspray etc. Another company, known as Ainsburies LTD, is a separately owned retail company and it sells the products it purchases from G&P (of course it might also purchase products from other manufacturers) directly to customers through its supermarket outlets.

We are designing a data model, for Ainsburies LTD. This is the scope of our data model. The model must handle the recording of product deliveries. A Product_Delivery entity can be thought of as relating to different products, which have the generic entity name Product. From the point of view of the Ainsuries LTD, what has to happen is that the Products need to arrive at the store. When they arrive, they will, in reality, need to be stored inside the supermarkets warehouse. However, for the purpose of our example we will not cover this storage aspect. We only cover everything up until their arrival at the store.

To begin, we make a number of observations, with the help of the client, as follows:

1. Each delivery (we will refer to in-schema as Product_Delivery) will be associated with potentially many items made by G&P, but not necessarily every product (likewise, named ‘Product’ in the schema) made by G&P is part of such a delivery.

2. Each Product might be associated with many deliveries. This makes sense – once a store aquires a given product, the ultimate aim is to sell it and thus run the stock down, resulting in the triggering of another order, which needs to be delivered.

3. Each physical delivery must arrive at a given location (likewise, named ‘Location’ in the schema).

Therefore, we have three entities (Product_Delivery, Product and Location), and, to begin with four potential relationships:

Product_Delivery--Product.

Product--Product_Delivery.

Product_Delivery--Location; and

Location--Product_Delivery.

It is, however, helpful to define the boundaries of relationships and we would like to be more explicit about the Optionality and Cardinality of relationships, before going on to introduce a more detailed, higher-resolution database design in tabular form.

General Definitions:
Optionality: The minimum number of entities that can be associated with another entity.

Cardinality: The maximum number of entities that can be associated with another entity.

Therefore, look at Table 1. We said, previously, that a Product_Delivery can contain any number of Product items, but not necessarily every product is part of a delivery. We might also realize that a delivery from G&P must contain at least one Product (a delivery which contains nothing is not a delivery of anything at all!).

Therefore, when thinking about the relationship, Product_Delivery--Product, we say that a Product_Delivery may have 0 instance of any given Product or it may have any number of Products. Thus, the optionality is ‘0’ and the cardinality is ‘*’ (meaning “any”). On the other hand, a Product might not belong to a Product_Delivery (optionality ‘0’), but if it does, then it only belongs to a specific, singular (cardinality ‘1’) Product_Delivery. Similarly, a product delivery may not be made to a given location (optionality ‘0’), if the location is not a retail outlet, for example, but product deliveries are made to many locations (*). Finally, a Location might not receive a product delivery, (optionality ‘0’), but numerous (*) will receive deliveries.

Relationship	Optionality	Cardinality
Product_Delivery--Product	0	*
Product--Product_Delivery	0	*
Product_Delivery --Location	0	*
Location-- Product_Delivery	0	*

Table 1: Optionality and Cardinality

Figure 1: Entities and Their Relationships

Typically, the bi-directionality (the two-way property) of relationships is captured diagrammatically on a single line, to help keep diagrams neat, while at the same time allowing them to express the information required. Therefore, by way of illustration, we present a complete diagram in Figure 1. Note: the notation used here is a kind of alternative to that introduced in Week 3 (see section TDO). We do not elaborate on the differences here, but for further reference on alternative notations available, please refer to ch 3 in Ritchie (Ritchie,2002).

4.2 More detailed tabular diagrams of entities

As we have suggested previously, in week 3, good databases usually follow a well-defined process of design. Due to the relatively limited scope of this document, we will follow these principles, but by focusing on a small part of what might otherwise be a much larger database design.

In Figure 1, we introduced some simple entities and their relationships. This was in order to illustrate the ‘boundaries’ of the relationships, using a slightly different notation than previously used. We will expand on this example, here, by defining the entities more fully, which requires a tabular representation of the entities in the diagram. Through the process of the design, we end up with more entities (or even fewer) as we iterate, consequently realizing that previous choices may be unnecessarily complicated or too limited.

Therefore, let us reconsider the boxed entities in Figure 1: Product_Delivery, Product, Location. Let us consider in a little more depth what these entities consist of in the real world, by providing natural language descriptions of them:

Product_Delivery: physically, this can be thought of a specific trip undertaken by a vehicle of a given type, which must make the delivery (of various goods) to the store (destination) from the given location of the factory (origin). This delivery must be received within a time window – i.e., it is no good if it arrives at the store too early as there might not be enough room in the warehouse to accept the stock, but too late might result in lost sales due to lack of stock. From the point of view of a retailer, then, it is useful to monitor the performance of the supplier in this light. A time window will be defined, generally, by a start time and an end time. To distinguish the ‘expected’ time window from the ‘actual’ time window, we postfix the relevant variables with appropriate letters – ‘e’ (expected) and ‘a’ (actual). Other relevant information is the capacity of the delivery. Physically, this depends on the actual vehicle used for the delivery task. Vehicles have a maximum weight that they can carry – this is the weight beyond which adding any more weight could be dangerous and/or damage the vehicle. Similarly, if the products being shipped are quite light, then it is likely that the other capacity limitation comes into play – this is the actual volume of the truck, which cannot be exceeded for obvious reasons.

The Product: we will keep the description of the product quite simple. Of course there are all kinds of complexities relating to a given product (branding, color, shape, etc), which do not concern us in terms of the current scope. Really, for the purpose of moving and storing a product, we need to know what kind of product it is, but then only the dimensions (weight, volume) of a given unit. Products might arrive, not as an individual item to be placed immediately on a supermarket shelf, but as a box containing several individual items and we might need to think about how we quantify the number of units etc.

The location, indicates that the store (and the factory), might be represented by a location, rather than a full address. The reason a delivery application might have the address/location etc., stored as separate entity is because it makes sense from the point of view of easily accessing geo-coordinates for routing. Here, then, we are assuming that the company owns its own fleet of vehicles, which need to be routed, but perhaps by use of a third-party routing API that accepts a (Latitude, Longitude) geo-coordinate. Furthermore, while is important to store the postal address, Latitude, Longitude might not usually be considered as logically acceptable attributes of a postal address.

In Figure 2, we present the corresponding design in diagrammatic form. Again, we have used yet another alternative representation for the relationships. We won’t explain these here, but we do encourage you to explore the various recommended textbooks to discover the meaning of all these different kinds of representations. The reason we have used many kinds of representation in the notes is such that you are aware that they exist, and different textbooks (and employers) will use different combinations. Try to become familiar with them all.

Figure 2: Simple 'logistics' database

4.3 Creating tables

4.3.1 Identifying rows within a table with ‘primary keys (PK)’

4.4 Creating relationships between tables

4.4.1 Many-to-many example

Consider the following example about the delivery of products from a given manufacturer to a destination retail store. Goods, physically, will be loaded onto a vehicle that has a maximum weight and volume (capacity). The vehicle will be expected at the store at a given time, although the actual time will need to be recorded; these features are captured by the <Product_Delivery> table whose Delivery_ID is a generated integer and primary key. The <Product> is described as above, again, with a generated integer, <Product_ID>, and the primary key. Importantly, for a many-to-many relationship a link-table must be created. Please see Table 4 The link table allows many products to be associated with products deliveries and many product deliveries to be associated with products.

Figure 3: Many to many example with link-table

4.4.2 One to many example rows in other tables with foreign keys

4.4.3 Modelling time-dependent data in tables

An often-important characteristic of products is of course their price. This is not captured in the above example, so let us consider a different logistics context to the one presented...

Let us imagine that freight companies are increasingly under pressure to monitor their costs, and their GHG emissions. This can be done in several ways, but let us assume an ‘internet of things’ application. It is well-known, which we take as given, within in the freight sector that driver behavior can affect the uptake of fuel. For example, large accelerations can result in the burning of additional fuel, increasing costs and GHG emissions. Therefore, a device has been created by ‘ANP’, an analytics company, to sit on-board vehicles and monitor accelerations. Data is streamed to a remote server stored in a database, which also belongs to an Analytics company ‘ANP’. Freight companies (e.g., ‘G&P’ and ‘Marks’) outsource analysis to ‘ANP’.

Figure 5: Database model for vehicle accelerations

How might ‘ANP’ represent this data as a database? An example is provided in Figure 5. Notice the relationship here: Vehicle may have many Accelerations. We might imagine a situation where, once an acceleration goes above some threshold, it is reported to the database (as in the example provided), such that time sampling is non-uniform in frequency. This is relevant to the current situation, where the two companies are interested in identifying drivers who over-accelerate, such that training can then be provided. We might however, just as easily imagine a situation where the accelerations are required at even frequencies in time, regardless of the values. This is often the requirement in the field of time series analysis.

Again, this situation concerning the dynamic capture of data is achieved using a table, where each row enters a value for the specific time, here per-vehicle-acceleration. This is a common technique for time dependent data modelling, as other examples in the literature demonstrate (e.g., see Ritchie ch 4).

4.5 Physical Design

4.5.1 Data types

4.5.2 Identity using automatic sequencing

# This is very straightforward to create a database Populations
CREATE DATABASE Populations;
# Create a table named People inside the Populations database
#
CREATE TABLE Populations.People
(
ID int NOT NULL AUTO_INCREMENT,
Name varchar(255) NOT NULL,
PRIMARY KEY (ID)
);
# Load the names John, Jeff, Sarah, James into the People table
# that now exists in the Populations database
#
INSERT INTO Populations.People (name) VALUES('John'),('Jeff'),('Sarah'),('James');

SELECT * FROM Populations.People;

4.5.3 Indexing

select Name from Populations.People WHERE ID = 3;

select Name from Populations.People WHERE Name = 'Sarah';

4.6 Summary