There are two approaches that database designers commonly use to come up with a design for a relational database. They are used together, even though at first you may think they are completely different approaches that produce different designs for the same system!!
E-R diagrams. The first approach is to produce an E-R diagram of the proposed system. The designer will ask themselves what obvious ‘entities’ exist in a system and how are they related. Entities are identifiable objects in a database about which you would store information. We have already seen lots of examples of entities such as Member, Dog, Breed, Undergraduate and Degree. Each of these entities requires a table to store real-life examples of that entity in (known as ‘records’). Each actual record is stored in a row in the appropriate table. Each record is made up of ‘fields’. A field is a piece of information you keep about an entity. In the Member’s table in the previous section, fields included, Initial, Surname, Title, Sex and Postcode, for example. Database designers often also refer to the ‘attributes’ of a particular record rather than ‘fields’ but they mean the same thing. Notice that fields are the columns in tables. Once the designer has come up with an E-R diagram of logically linked entities, they can then go ahead and build the database.
Normalisation. A second technique the database designer can use is known as ‘normalisation’. This has its roots in mathematical analysis and can produce a very efficient design. It involves identifying all of the possible attributes in a database and then applying a set of rules to them in turn. Each stage in the process of normalisation can result in a ‘better’ design.
E-R diagrams and normalisation together
Normalisation will produce database designs that can be shown mathematically to be the ‘best’ design. By this, we mean a design that minimises the amount of data redundancy. However, it may not necessarily produce the best design in terms of ease of understanding for humans! In practice, the designer will use both techniques together!
The first approach
The second approach
We will start looking at how relational databases are designed by first looking at E-R modelling. We will then look in considerable detail at normalisation.
E-R diagrams and their use in designing relational databases
An E-R diagram is a diagram that database designers use to show the relationships between groups of data (each group being known as an ‘entity’). It gives the designer a very simple yet effective overview of the entities in a system and how they relate to each other.
We will now look at the building blocks of E-R diagrams.