Discussion Point:

How many of the above concepts are recognisable in your experience of using a database?

10.03 The relational database

In the relational database model each item of data is stored in a relation which is a special type of table. The strange choice of name has its origin in a mathematical theory. A relational database is a collection of relational tables.

When a table is created in a relational database it is first given a name and then the attributes are named. In a database design, a table would be given a name with the attribute names isted in brackets after the table name. For example, a database for the theatrical agency may contain the following tab es:

Member(MemberlD, MemberGivenName, MemberFamilyName, BandName, ...)

Band(BandName, AgentID, ...)

113

The logical view of the data in these tables is given in Table 10.02 and Table 10.03. Each attribute is associated with one column in the table and is in effect a column header. The column itself contains attribute values.

MemberlD	Member GivenName	Member FamilyName	Band Name
0005	Xiangfei	Jha	ComputerKidz
0009	Mahesh	Ravuru	ITWizz
0001	Dylan	Stoddart	ComputerKidz
0025	Vandana	Graham	ITWizz

Table 10.02 Logical view of Member table in a relational database

BandName	AgentID
ComputerKidz	01
ITWizz	07

Table 10.03 Logical view of Band table in a relational database

Although some database products do allow a direct view of a table this is not the norm hence the use of the term 'logical view' here. If a user wishes to inspect all of the data in a table a query should be used.

Relation: the special type of table which is used in a relational database

Attribute: a column in a relation that contains values

A row in a relation should be referred to as a tuple but this strict nomenclature is not always used. Often a row is called a 'record' and the attribute values 'fields'. The tuple is the collection of data stored for one 'instance' of the relation. In Table 10.02, each tuple relates to one individual band member. A fundamental principle of a relational database is that a tuple is a set of atomic values; each attribute has one value or no value.

The most important feature of the relational database concept is the primary key. A primary key may be a single attribute or a combination of attributes. Every table must have a primary key and each tuple in the table must have a value for the primary key and that value must be unique. Once a table and its attributes have been defined the next task is to choose the primary key. In some cases there may be more than one attribute for which unique values are guaranteed. In this case, each one is a candidate key and one will be selected as the primary key. More often there is no candidate key and so a primary key has to be created. Table 10.02 shows an example of this with the introduction of the attribute MemberID as the primary key (the primary key is underlined in the logical v•ew).

The primary key ensures 'entity integrity'. The DBMS will not allow an attempt to insert a value for a primary key when that value already exists. Therefore each tup e must be unique. This is one of the features of the relational model that helps to ensure data integrity. The primary key also provides a unique reference to any attribute value that a query is selecting.

Although it is possible for a database to contain stand-alone tables it is usually true that each table will have some relationship with another table. This relationshÞ is implemented by using a foreign key.

KEY TERMS

Primary key: an attribute or a combination of attributes for which there is a value in each tuple and that value is unique

Foreign key: an attribute in one table that refers to the primary key in another table

            The use of a foreign key can be discussed on the basis of the two database tables represented in Table 10.02 and Table 10.03. When the database is being created, the Band table is created first. BandName is chosen as the primary key because unique names for bands can be guaranteed. Then the Member table is created. MemberlD is defined as the primary key and the attribute BandName is identified as a foreign key referencing the primary key in the Band table. Once this relationship between primary and foreign keys has been established, the DBMS will prevent any entry for BandName in the Member table being made if the corresponding value does not exist in the Band table. This provides referential integrity which is another reason why the relational database model helps to ensure data integrity.

Question 10.01

BandName is a primary key for the Band table. Does this mean that as a foreign key in the Member table it must have unique values? Explain your reasoning.

10.04 Entity-relationship modelling

The top-down, stepwise refinement (see Chapter 12, Section 12.01) approach to database design uses an entity-relationship (ER) diagram. This might be initially created and used by a systems analyst before being passed on to the database designer. Otherwise the designer has to create •t. The term 'relationship' (not to be confused with a relation) was introduced earlier in connection with the use of a foreign key. An entity (strictly speaking an entity type) could be a thing, a type of person, an event, a transaction or an organisation. Most importantly, there must be a number of 'instances' of the entity. An entity is something that will become a table in a relational database.

WORKED EXAMPLE 10.01

diagram for the theatrical agency theatrica agency which will be sufficient to model a would need. The starting point for a top-down design is a handle book•ngs for bands. Each band has a number of venue. Each booking might be for one or more bands. 'agency' because there is only the one. You choose Venue. For each of these there will be more than one booking is for a gig at a venue but you ignore this entity will be sufficient to hold the required data aim is not to define too many. You choose the following be, for example, a relationship between Member and will be hand ed through the other relationships that draw a preliminary ER diagram as shown in Fgure 10.02.   Booking   Venue     entity-relationship diagram of the relationships deciding on what are known as the 'cardinalities' of the a single line connecting each pair of entities. This line which might be described as the 'forward' one and as drawn. However, this only becomes apparent at relationship. First we have to choose one of the following of each relation: the relationship between Member and Band. We member of only one Band. (This needs to be confirmed as a that one Band has more than one Member so it has

Creating an entity-relationship Let's consider a scenario for the part of the final database they statement of the requirement: The agency needs a database to members. Each booking is for a Step l: Choose the entities You look for the nouns. You ignore Booking, Band, Member and instance. You are aware that each because you think that the Booking about a gig. Step 2: Identify the relationships This requires experience but the th ree•. Booking with Venue Booking with Band Band with Member. You ignore the fact that there will Venue because you think that this indirectly link them. You can now Member   Band   Figure 10.02 A preliminary Step 3: Decide the cardinalities Now comes the crucial stage of relationships. At present we have actually defines two relationships the 'backward' one on the diagram the final stage of drawing the descriptions for the cardinality •      one-to-one or one-to-many or •      many-to-one or many-to-many or M:M. This can be illustrated by considering argue that one Member is a fact by the agency.) We then argue

115

many. Therefore the relationship between Member and Band is M:l. In its simplest form, this relationship can be drawn as shown in Figure 10.03. Member   Band   Figure 10.03 The M:l relationship between Member and Band This can be given more detail by includ'ng the fact that a member must belong to a Band and a Band must have more than one Member. To reflect this, the relationship can be drawn as shown in Figure 10.04. Member   Band   Figure 10.04 The M:l relationship with more detail At each end of the relationship there are two symbols. One of the symbols shows the minimum cardinality and the other the maximum cardinality. In thisparticular case the minimum and maximum values just happen to be the same. However, using the diagram to document that a Member must be ong to a Band is important, It indicates that when the database is created it must not be possible to create a new entry in the Member table unless there is a valid entry for BandName in that table. For the relationship between Booking and Venue we argue that one Booking is for one Venue (there must be a venue and there cannot be more than one) and that one Venue can be used for many Bookings so the relationship between Booking and Venue is M:l. However, a Venue m•ght exist that has so far never had a booking so the relationship can be drawn as shown in Figure 10.05. Booking   Venue   Figure 10.05 The M:l relationship between Booking and Venue Finally for the relationship between Band and Book•ng we argue that one Booking can be for many Bands and that one Band has many Bookings (hopefully!) so the relationship is M:M. However, a new band might not yet have a booking. Also there might be only one Band for a booking so the relationship can be drawn as shown in Figure 10.06. Band     Booking     Figure 10.06 The M.•M relationship between Band and Booking Step 4: Create the full ER diagram At this stage we should name each relationship. The full ER diagram for the limited scenario that has been considered is as shown in Figure 10.07. Member   Band     Booking   Venue         belongs to is booked for is made at has is for is booked for Figure 10.07 The ER diagram for the theatrical agency's booking database To illustrate how the information should be read from such a diagram we can look at the part shown in Figure 10.08. Despite the fact that there is a many-to-many relationship, a reading of a relationship always considers just one entity to begin the sentence. So, reading forwards and then backwards, we say that:

116

One Band is booked for zero or many Bookings One Booking is for one or many Bands Band     Booking     is booked for is for Figure 10.08 Part of the annotated ER diagram

10.05 A logical entity-relationship model

A fully annotated ER diagram of the type developed in Section 10.04 holds all of the information about the relationships that exist for the data that is to be stored in a system. It can be defined as a conceptual model because it does not relate to any specific way of implementing a system. If the system is to be implemented as a relational database the ER diagram has to be converted to a logical model. To do this we can start with a simplified ER diagram that just identifies cardinalities.

If a relationship is I:M, no further refinement is needed. The relationsh'p shows that the entity at the many end needs to have a foreign key referencing the primary key of the entity at the one end.

If there were a relationship there are options for implementation. However, such relationships are extremely rare and will not be considered further.

The problem relationship is the M:M, where a foreign key cannot be used. A foreign key attribute can only have a single value so it cannot handle the many references required. The solution for the M:M relationship is to create a link entity. For Band and Booking, the logical 117 entity model will contain the link entity shown in Figure 10.09.

Band		Band-Booking		Booking

Figure 10.09 A link entity inserted to resolve a M:M relationship

Extension Question 10.01

Is it possible to annotate these relationships?

w•th the link ent•ty in the model it is now possible to have two foreign keys in the link entity; one referencing the primary key of Band and one referencing the primary key of Booking.

Each entity in the logical ER diagram will become a table in the relational database. It is therefore possible to choose primary keys and foreign keys for the tables. These can be summarised in a key table. Table 10.04 shows sensible choices for the theatrical agency's booking database.

Table name	Primary key	Foreign key
Member	MemberlD	BandName
Band	BandName
Band-Booking	BandName & BookinglD	BandName, BookinglD
Booking	BookinglD
Ven u e	VenueName

Table 10.04 A key table for the agency booking database

The decisions about the primary keys are determined by the uniqueness requirement. The link entity cannot use either BandName or BookinglD alone but the combination of the two in a compound primary key will work.

TASK 10.01

Consider the following scenario. An organisation books cruises for passengers. Each cruise visits a number of ports. Create a conceptual ER diagram and convert it to a logical ER diagram. Create a key table for the database that could be implemented from the design.

10.06 Normalisation

Normalisation is a design technique for constructing a set of table designs from a list of data items. It can also be used to improve on existing table designs.

WORKED EXAMPLE 10.02 theatrical agency consider the document shown in Figure 10.10. This is a theatrical company might use. Theatre Number of band members 5 3 Headlining data sheet (ignoring headings) can be listed as a set of attributes: VenueName, VenueAddress1, VenueAddress2, Date, NumberOfMembers, Headlining)) because we are starting a process of table design. The BandName, NumberOfMembers, Headlining is because they there is a repeating group, the attributes cannot sensibly A table must have single rows and atomic attribute would be to include tuples such as those shown in Table here with the duplication of the bookinglD, venue   Venue Addressl Venue Address2 Date Band Name Number Of Members Headlining Theatre Camside CAI 23.06.2016 Computer Kidz 5   Theatre Camside CAI 23.06.2016 ITWizz 3   unnormalised table Normalising data for the To illustrate the technique let's booking data sheet that the Booking data sheet: 2016/023 Venue: Cambridge International Camside CAI Booking data: 23.06.2016 Bands booked ComputerKidz ITWizz Figure 10.10 Example booking The data items on this sheet (BookinglD, (BandName, The list is put inside brackets extra set of brackets around represent a repeating group. If be put into one relational table. values so the only possibility 10.05. There is now data redundancy data and the date. Booking Venue Name 2016/023 Cambridge International 2016/023 Cambridge International Table 10.05 Data stored in an

118