3.14. Review: CRUD Check

Figure 3.14.1: The CRUD table for the Piccadilly system. It cross-references the entities and relationships with the event-responses.

First, compare your CRUD table against this one. Yours should be substantially the same. However, the purpose of the exercise is to find missing events and thus confirm the completeness of the essential model. Our table has raised some questions about the model’s completeness, and we want to discuss them. Before we do, let’s see how the table works.

Find the entry for the entity ADVERTISING AGENCY. The table indicates that event 9 creates an instance of this entity. Your event list shows that event 9 is New agency wants to do business. The event-response process model shows that a new instance of the entity ADVERTISING AGENCY is created when the NEW AGENCY data flow arrives from one of the advertising agencies.

The CRUD table also shows that events 1, 8, 10, 12, 13, and 15 all have reason to reference instances of the ADVERTISING AGENCY entity. You can find out the reason by looking at the corresponding event-response models for each of these events. This means that the system both creates and references the entity.

Note that the table does not show an event that updates an ADVERTISING AGENCY, so it raises a question about the need to change any of the attributes of this entity. Let’s use the data dictionary definition for ADVERTISING AGENCY to help answer the question:

Advertising Agency = Agency Name + Agency Address + Agency Phone Number

It seems reasonable to have some provision for an agency to change its address or phone number or even its name. Thus, we see that the CRUD table has exposed an event that has not yet been included in our analysis.

You’ll need to update the specification to reflect the missing requirement:

1. Add the data flow AGENCY DETAILS CHANGE to the context diagram.

2. Define AGENCY DETAILS CHANGE in the data dictionary:

Agency Details Change = Agency Name
+ (New Agency Name)
+ (New Agency Address)
+ (New Agency Phone)

Strictly speaking, this definition allows a flow that changes nothing. However, when faced with the alternative lengthy definition that lists each of the possible combinations of the three fields, we find this is a better, less error-prone alternative. Also, the risk of misunderstanding is very low because even if someone did input a data flow that changes nothing, it will not harm the system’s data.

1. Add event 19 Agency changes details to the event list.

2. Build event-response process and data models for this event.

3. Add an entry to the CRUD table to show that ADVERTISING AGENCY is now updated by the new event 19.

According to the CRUD table, there is no event that deletes instances of the ADVERTISING AGENCY entity. Is there ever any circumstance under which Piccadilly stops doing business with an agency and deletes all reference to the agency? If the answer is yes, then you will need to add another event to your list, just as you did for the agency details change. If the answer is no, then put NOT APPLICABLE in the appropriate column in the table.

Note that not all data are deleted. This is an essential model, and deleting data to make room in the database is not a consideration. Data are only deleted when the business policy requires that you must expunge all reference to the data.

Work through the rest of the entities to ensure that all the events are captured.

This definitely seems odd. Surely, Piccadilly would need to reference the relationship when an agency pays an invoice. Now look at the context diagram. Shouldn’t we have a data flow called something like AGENCY PAYMENT? If our system is responsible for sending out invoices, should it also be responsible for accepting payments? Or are payments part of another system? If the users decide that payments are part of this system, you’ll need to add another event to the list, model the event, and update the appropriate data definitions.

The CRUD table above reveals some flaws in our context. For example, the ALLOCATING relationship between COMMERCIAL COPY and COMMERCIAL SPOT is created and deleted, but never referenced. So it appears that the Piccadilly system remembers which commercial was played in which break, but never uses that information. Either there is no need to remember it, or we have missed a requirement. Perhaps the information about which copy was transmitted should appear on the invoice. This is something you should take up with the users.

From the table, we can see that BREAKCHART DAY is never created. We went back through the events to find out why. Event 16 Time to finalize new programme schedule is the most likely candidate to create this entity. Look at the mini specification for the process ISSUE PROGRAMME SCHEDULE. A few lines down, it says,

For each BREAKCHART DAY within the next quarter

Yet this process is the one that sets up the breakchart and its programmes for the coming quarter. We should add a line before this to say,

Create an instance of BREAKCHART DAY for every day of the quarter

and then

For each BREAKCHART DAY

We then update the CRUD table to show that event 16 creates the entity BREAKCHART DAY, and we update the event-response data model.

Another entity, COMMERCIAL BREAK, is never created. The problem again lies with the mini specification for event 16. When the schedule is being finalized, the broadcasting rules are used to determine where the breaks in each programme are put. It is this process that establishes the breaks along with the CONTAINING relationship. The specification has failed to fully describe the process.

You were not expected to know how breaks are created. None of the text has mentioned it. However, the CRUD check revealed that something was wrong. Your task as an analyst is to find out what went wrong.

The FILMING relationship is created, but not referenced. The system remembers which production companies produced which commercial copy, but it doesn’t seem interested in this information. It may be remembered for the benefit of another system, or this system may be missing an event. Back to the users you go. While there, you can also ask about the SUPPLYING relationship between PROGRAMME SUPPLIER and PROGRAMME. For perhaps the same reasons, it is not referenced either. Whatever the reasons, the errors can be corrected before we start to implement the system.

We have not examined the entities and relationships to see if they are updated or deleted. Nor do we intend to. For the purposes of this book, the check you ran is sufficient for you to understand the process. We feel that the time invested in examining each of the attributes to determine if it can be updated or deleted would be wasted in the context of this book. No such dispensation applies for your real projects.

There is one last detail that you need to add to your table. When an event-response process model is made up of more than one process, it is useful to put the process number, rather than the event number, in the CRUD table. For instance, the process model for event 1 Agency wants to run a campaign contains three processes. One of those processes, 1.2 CREATE SUGGESTED CAMPAIGN, creates an instance of ADVERTISING CAMPAIGN. There are two reasons for this mind-bending detail. First, as input to the task of file design, it provides precise information about exactly which processes access which data. Second, if you are planning to implement a system in an object-oriented environment (more about this in Chapter 2.15 Object-Oriented Viewpoint), this detail provides input to the task of object partitioning.

The correlation of the data to the individual event-responses is largely a clerical task. As such, it is a perfect candidate for automation and for use of a CASE tool. Now that you understand the technique, let the CASE tool do it for you. If you don’t have a CASE tool or if your tool doesn’t have this facility, you’ll have to do it manually. It is not such a huge task. We used a spreadsheet for the CRUD table in this book. We have also seen CRUD tables written on whiteboards, in notebooks, and most things in between.

When the elements of the outgoing data flows match the stored attributes, it usually indicates a reference action. Again, look to the event-response process models and mini specs to confirm.

There is a section on CRUD checking in Chapter 2.11 Event-Response Models if you wish to review this topic. Pay careful attention to the discussion of the event-response data model.

Also, study the Piccadilly event-response models in Chapter 3.12, and note their use of stored data. Keep the CRUD table beside you, and ensure that you can correlate each of the table’s accesses with the event-response models.