The tracks or songs on CDs are the main reason for buying them in the first place and they are a key part of the music database system. We have delayed including a table for the tracks because, by the rules of relational database design, they cannot be included in the CDs table. One reason for this is that there is a variable number of tracks on CDs and we could not decide in advance how many to include in our design of the CDs table; tracks would form a repeating group in the CDs table and this must be avoided.
In order to store details of tracks, therefore, we have to set up a new table and then link each track in this new table to its 'parent' record in the CDs table. What we need to do is set up a link between the primary key in the CDs table and an identical field in the Tracks table.
Set up a new table for TrackDetails as follows:
| Field Name | Data Type | Field Properties |
| TrackID | AutoNumber | Long Integer |
| TrackTitle | Text | Size=100 |
| CDID | Text | Size=20 |
| Author | Text | Size=30 |
| DateRecorded | Date/Time | Short date |
| TrackNumber | Number | Integer |
| Duration | Text | Size=10 |
Set the primary key to TrackID. The TrackTitle by itself is not enough to distinguish between tracks as two or more could easily have the same title.
The fields that will link each CD in the CDs table to its tracks in the Tracks table are SerialNumber and CDID, which contain the same data. In the Tracks table this field is known as a foreign key, that is a primary key from one table included in another table. The primary key-foreign key mechanism is the way in which tables are related. For each record or row in the CDs table with its own primary key there will be one or more related records or rows in the Tracks table: the relationship is one-to-many, which is a fairly common occurrence in data.
We could use the Relationships facility in Access to link the two tables but first we should review our work so far and see if it stands up to the complicated real world of music. We are trying to capture that world and reduce it to a 'model' in a database, a difficult process that can be fraught with errors and compromises.
We must make each record in the TrackDetails table unique in relation to the CDs table. The same track may appear on more than one CD, tracks are often recycled into Greatest Hits collections and there may be live versions and 'alternate takes' on 'anthology' style releases; there may be three or four 'remixed' versions of the same track on some CDs. A track record, however, can only belong to one CD otherwise it would be part of a many-to-many relationship, something we have to avoid. We cannot have a situation where there are many CDs that can own many tracks; each CD can own many tracks and a track can only belong to one CD.
After further thought on the nature of CDs and tracks it turns out that the analysis so far is not entirely adequate. Take the case of a song such as 'Help' by The Beatles (old-time I know, but it's just an example, the same principles will apply to any song and artist).
The song 'Help' appears on at least three CDs, by the Beatles, 'Help', 'The Beatles 1962-66' and '1'. In order to link the song Help to the three CDs the song will have to appear three times in the Tracks table with a different CD serial number in the CD field each time. It is not possible to include more than one CD serial number in the Tracks table because this would violate First Normal Form (repeating groups).
We could include the CD serial number (the foreign key) in the primary key for the Tracks so that each CD could have its own unique copy of the song. This will not work properly because, by Second Normal Form, all the fields in the table should be dependent on this composite key but, for example, the author of the song is not dependent on the serial number of the CD. In this approach, if there is more than one occurrence of a song in the Tracks table then any mistake in the data (e.g. 'Beetles') would have to be corrected or deleted more than once (this is the inconsistency problem).
The solution to this problem is to create a middle table to link the CD and the Track:
CD -------includes-------> Issue <--------recorded_on-------Track
Now the entities are CD, Issue and Track. Each CD includes many Issues and a Track can appear in many Issues. Thus the CD 'Help' can include the Issue of the song 'Help', as can the CDs 'The Beatles 1962-66' and '1'. Similarly, each track can appear in many Issues without violating the rules of normalisation and database design. The Issue table need only include the CD Serial Number and the Track ID, the two forming a composite primary key that uniquely identifies each occurrence of a song on a CD; with no additional fields there are no problems with partial dependencies (second normal form) or non-key dependencies (third normal form).
This is the art of data modelling. We must now amend our design to include the
middle table. Create another table called Issues with two fields as follows:
| Field Name | Data Type | Field Properties |
| TrackID | Text | Size=100 |
| CDID | Text | Size=20 |
Select both fields and set them as the primary key. We can now add the details of some tracks and their CDs. One problem with this table will be finding the data, neither of which is easy to remember, names are much easier to deal with.
Relationships between tables are like relationships between people. Two tables may have a one-to-one relationship, like man and wife, a one-to-many relationship, as in polygamous societies, one husband with many wives, or many-to-many, where many people are friendly with many others.
The next step is to relate tables through their common fields - the tables have been designed to allow this. A relationship is used for extracting information from related tables.
We want to be able to list one or more CDs and the tracks on each one. To do this we have to link the CDs table to the Tracks table and then the TrackDetails table to Tracks.
To set up a relationship close all database objects to display the database control panel. Click on the relationships button in the toolbar (third from the right).
The toolbar changes to Relationships and the display shows an empty relationships window.
To add tables to the Relationships window click on the Add button (+) and choose from the list displayed.
Add the CDs, the Issues and the TrackDetails tables with the Add button and then click the Close button.
The task now is to create the relationships by dragging from one field to another in the field list boxes.
Click on 'SerialNumber' in the CDs table and drag the mouse to the CDID field in the Labels table. You are now shown the Edit Relationships dialogue:
Click the box marked 'Enforce Referential Integrity' and then OK. This
the Relationships window will show the following display:
Note that the symbols showing the order of the relationship, one-to-many is only shown if you choose to enforce referential integrity.
Referential integrity refers to the situation where a record in the 'many' side of a relationship is obliged to have a parent on the 'one' side'.
Enforcing referential integrity in this case will ensure that tracks in the TrackDetails table can only exist if there is a parent record in the CDs table: a track has to belong to a valid CD before it can be added to the TrackDetails table.
Think carefully if this is what you want. Could a track exist without a CD? In the online world this is quite likely as music is increasingly distributed across the internet. Also, music collectors know that record companies keep trial and rejected recordings 'in the vaults', but these might one day be placed in the table (they may be available on 'bootlegs'). On the other hand you may want to restrict your table to tracks that exist on actual CDs so that buyers can locate their favourite songs and buy the CDs where they can be found.
In another example we might create a table of football clubs and another table of footballers. We might want to make it a rule of this information system that football players could only exist within it if they were attached to a club that was a member of the database; to achieve this we would enforce referential integrity. With referential integrity enforced it would not be possible to add a player to a club that did not exist in the database, we would have to add the club first and then the player.
When referential integrity is enforced two other options are available. 'Cascade Update Related Fields' means that any change in a parent field will be passed on to fields in related tables. Thus if the name of a football club is changed then the relevant field in the Players table would also be changed. 'Cascade Delete Related Records' means that if a parent record is deleted then all child records will also be deleted so if a club, say Mansfield Town, is deleted then all its players are deleted as well as they no longer meet the rule of having a valid club. These would be drastic steps in our music database but if that is what we want...
In the case of CDs and Tracks we would probably, on balance, prefer not to enforce referential integrity so that we do not have to remove tracks if a CD is deleted. To remove referential integrity double click the black line linking the two tables and remove the tick from the Enforce Referential Integrity box.
One of the main reasons for linking tables with relationships is so that we can get at information in related tables. In particular, we would like to know what tracks are to be found on what CDs so that, for example, we can find a CD in a shop (or online) that includes a particular track we like. This takes us back to the Query tab in the database control panel.
Double click the Create query in Design view option and add the CDs, Issues and TrackDetails tables to the query. We can now choose any field from the three tables, in any order, and specify search and sort criteria as before. For example:
In data view:
We can now create reports and web output based on this query.
Access does not create relationships for lookups, it uses SQL instead so if we want to introduce information from the Labels and MusicType tables we will have to add them to the Relationships table by hand. To do this open the Relationships display as before and click the Add ('+') button to add them to the display.
Drag the LabelName field in the Labels table to the Label field in the CDs table.
Access can see the type of relationship in these two cases and displays this as 'One-To-Many'. The symbols show that for each occurrence of Label in the CDs table there is just one possible choice from many in the Labels table. Similarly there is just one possible choice from many for the Category field.
We can now define further queries that make use of this additional related information.
Once again, it may be some help now to read the notes on database design.