Logical design
Logical design is essentially the process of making the conceptual design work once it has been agreed by the users. Whereas the conceptual design needs to reflect the users' understanding of the world, the logical design needs to provide the detail on how the model can be represented using relational technology.
The starting point for the logical design should be the ER diagram from the conceptual design stage. The outputs from the logical design stage are a new ER diagram that reflects the actual relational implementation, and a data dictionary.
ER diagram
Confusion can arise because there are two ER diagrams involved. Remember that the first reflects the community view of the data and must be comprehensible to the users. The second represents the technical view of the model which takes into account the requirements of the relational platform. This level of detail is usually too difficult for users to understand. Possible causes of confusion for users might be:
- The inclusion of foreign keys
- The existence of link entities introduced to resolve : relationships
- The replacement of generalisation/specialisation relationships with a practical equivalent
The following example is taken from the worked example used all the way through Connolly and Begg. If you are finding it difficult to follow any of the processes discussed here, please go through this example in the book. Each stage of refining the data model is discussed in detail with examples.
When designing a database for a property agency, it is established that PROPERTY and CLIENT are two important entity types. A CLIENT can view a PROPERTY, and when they do, they can leave a comment. The conceptual model might be represented as shown below:
Notice that there is a : relationship between the two entities. Notice, too, that the relationship itself has some attributes associated with it. From what you know of relational structure, you should realise that this is not possible: attributes are only found in tables and relationships are represented by pairs of keys. In the conceptual model, though, this is perfectly valid. The purpose of the conceptual model is to understand the world as the users see it.
During the logical design stage, the challenge is to take the agreed conceptual model and map it onto a set of tables that will give an equivalent description of the subject area. You know a third table is required to resolve the : relationship, and the 'floating' attributes can be included. The logical model would therefore look like this:
Connolly and Begg suggest the following process for logical design:
- Derive relations for the logical model
- Validate relations using normalisation (this is covered in week 6)
- Validate relations against user transactions
- Check integrity constraints
- Review logical model with the user
Data dictionary
The data dictionary is a detailed catalogue of the data requirements for every table and every column that will be used in the database. There is no standard format, but a table like the one below is perfectly adequate:
| Table name | Column name | Contents | Type | Format | Domain | Mandatory | Key | Reference |
|---|---|---|---|---|---|---|---|---|
| PROPERTY | property_no | Unique id | Integer | 09999 | 1-99999 | Y | PK | |
| address | Postal address | Text | Xxxxx | Y | ||||
| type | Type code | Character | X | F, H | Y | |||
| rooms | Number of rooms | Integer | 09 | 1-16 | Y | |||
| rent | Monthly rent | Money | £0999 | 100-2000 | Y | |||
| CLIENT | client_no | Unique id | Integer | 09999 | 1-99999 | Y | PK | |
| first_name | First name | Text | Xxxxx | |||||
| last_name | Last name | Text | Xxxxx | Y | ||||
| telephone | Telephone number | Text | [0-9],+,(,),space | Y | ||||
| email address | Text | |||||||
| VIEWING | view_date | Date of viewing | Date | dd/mm/yyyy | Y | PK | ||
| comment | Notes | Text | ||||||
| client_no | Client reference | Integer | 09999 | 1-99999 | Y | PK/FK | ||
| property_no | Property reference | Integer | 09999 | 1-99999 | Y | PK/FK |
The data dictionary duplicates some of the information on the ER diagram, but this is useful for cross-checking. Any corrections should be applied to both documents.
The data dictionary also provides additional information which is used in the next design stage.
The term data dictionary is also used in another sense which is briefly discussed in week 9.