Entities and Attributes

When a new system is to be designed, the systems analyst will identify the entities that exist within it. An entity is anything that the system stores data about. Examples of entities in a computerised mail order system might be customer, order, invoice, delivery note, stock item, supplier etc. An entity is a clearly identifiable thing that has associated data attached to it.

The data attached to an entity is called its attributes. When the system is implemented then most of the entities will correspond to records and their attributes to fields.

One of the jobs of the system designer is to map the relationships between entities. This helps to ensure that the system will have the necessary data structure and that redundancy will be avoided.

Relationships between entities can be one to many, many to many and one to one. These are best explained by an example. Consider a youth orchestra that has a music library. A musical work consists of a number of pages called parts. Each part contains the music that one member will need to perform the work.

Fig 1 Entities in Music Issue System

Music is posted to members each term for rehearsal and performance The system must produce mail labels at this stage.

The librarian needs to obtain lists of members and the music each one has so that these can be manually checked off when the music is returned. The librarian also needs lists of music parts that have been issued but which are now overdue. This list should show the member's name and telephone number so that they can be contacted and reminded to return the music.

Two data entities in this system are Orchestra Member and Orchestral Work. The attributes of these entities might be as shown in fig 1.

The relationship between the entities is many to many as the entity map in fig 2 shows. One orchestral work is loaned to many members and one member has parts from many orchestral works (one for each of the works being performed that term). In this context many means a number that is potentially more than one.

Fig 2 Entity Map for Original Entities

Many to many relationships should be avoided in database design since they lead to situations where data has to be repeated. In this situation, for one term's loan, either the member's details would have to be repeated for each item borrowed or the music details would have to be repeated for each member borrowing that item.

The use of several lines at either end of the main line indicates the many to many relationship. We can eliminate a many to many relationships by defining one or more new entities that relate to the original entities involved in the many to many relationship. This will involve redefining the attributes of the original entities to remove from them the attributes that now properly belong to the new entity. Note also that a one to one relationship is also normally undesirable. If two entities have a one to one relationship then it would indicate that they should be joined together to form a single entity.

Fig 3 Revised Entity Map

With the music issue system, we create the entity music part. One work is made up of many parts but one part belongs to one work. One member has many parts issued to them but one part is given to one member. The new relationships are shown in Fig 3 and the attributes in Fig 4. Note than in this context many means any number that could be greater than one. The relationship is one member having many loans even if some members actually have only one piece of music on loan. This is because the system must allow for more than one loan (many) per member.

Fig 4 Entities in Revised Music Issue System

When the database is implemented, then Catalogue Number will be the key field for Orchestral Works records and membership Number will be the key for Members records. Parts of a particular work are numbered from one up to the number of parts that make up the work and this is the sequence number of the part. Sequence Number is therefore not a key field for a part and neither is Catalogue Number since each work has many parts. However Sequence Number and Catalogue number together form a combination that uniquely defines a part. This combination can be used as the primary key for a part. This is and example of a primary key being produced by concatenation (joining together).

You might like to test your understanding of entity relationships by adding the following entities to the relationship diagrams.

Music publisher: Publisher from whom the music was purchased. Different publishers supply different musical works

Past Programme: List of works performed in each concert. A concert would normally consist of several musical works and a particular work might be played on several occasions

©LEV