Systems Design Techniques
| Learner Guidance If you want further information, the school of computing has developed a Computer Based Learning package supporting the Logical Data Modelling technique. This package will eventually be available through the web but for now, locate the G:\Library\CBL folder (using 'my computer' ) and double click on the Butnmenu.app icon. |
The objectives of this section are to provide definitions for the terms Logical Data Model (LDM) and Logical Data Structure (LDS), and to explain the constituent parts of a Logical Data Structure, and finally to introduce a step by step process for constructing Logical Data Models.
What is a Logical Data Model?
A Logical Data Model (LDM) is a representation of the data used by a system. It shows how the data is logically grouped and the relationships between these groupings as defined by the business requirements of the system. slide 3
The LDM comprises:-
- A diagram called a Logical Data Structure (LDS). NB LDS is simply the SSADM terminology for a Data Model or Entity-Relationship Model).
- Associated documentation of entities and relationships.
What do Logical Data Structures Consist of?
Logical Data Structures consist of of entity types and relationship types:-
An entity type is a logical grouping of data which is relevant to the application in question. The entity type must be relevant, e.g. an information system specifically for the Learning Resources Centre (LRC) would not hold information about lecturers cars, since this is not relevant. slide 5
An entity type is an identifiable object, classification, concept, activity, event or thing concerning the application. The thing must be identifiable since if it cannot be identified no information can be recorded about it in a database, e.g. a chair within the library isn’t identifiable so wouldn’t be an entity, however the classification chair type possibly would be an entity. slide 6
An entity is an occurrence of an entity type. The terms entity type and entity are often used interchangeably, the context usually defines what is actually meant. There must be the possibility of an entity type having more than one occurrence. A common mistake is to include one ‘super’ entity type in the LDS representing the company , the garage the library or the surgery or whatever. For example in a LDS for the University of Glamorgan’s LRC, LRC is not an entity type since there is only one occurrence. If however a LDS is being developed for all the college libraries in Wales then LRC is an entity type because there is the possibility of more than one occurrence.
Entity types are represented as soft rectangles containing the name of the entity. Naming of entities is critical, especially when groups of people are working together. An agreed definition of what is actually meant by an entity name will avoid a lot of confusion and pointless discussion. slide 7
A relationship type (again the terms relationship and relationship type are used interchangeably) is a relevant business association between two entity types (or in other words a relationship is a relevant business association between one entity of one type and 0, 1 or many occurrences of another entity type). There may be more than one relationship type between a two entity types. slide 8
In a completed LDS all relationships are 1:M. 1:1 relationships are either removed (by combining the entities) or are represented as 1:M relationships with the entity created first being the owner. M:M relationships are broken down into two 1:M relationships with the 2 original entities owning a ‘link’ entity. Link entities should not be regarded as in any way inferior to any other entity, link entities can take part in relationships both as owners and members. In practice link entities very often end up as the linchpin of the system. slide 9
The resolution of M:M relationships often causes problems. The following explanation is crucial to Logical Data Modelling. You should bear in mind that relationships are implemented using a system of foreign keys, i.e. the primary key of one entity is held as linking item in another entity. Consider an application in which details of order and parts are held. Looking from order to part we find a 1:M relationship (one order can contain multiple parts), looking from part to order we find a 1:M relationship (one part can appear on many orders). In fact we have identified a M:M relationship. If we try to hold the order number as a foreign key in the parts table, remembering that within relational system columns cannot contain multiple values, we will end up holding the details of each part once for each order which contains that part (what happens if we want to change a part description or the quantity in stock?). If we try to hold the part number as a foreign key in the orders table we will end up holding the details of each order once for each part that appears on the order. To avoid this wasteful duplication a new entity type called order-line is created. slide 10
The entity type part will hold the description and quantity in stock once for each part. The entity type order will hold the date and payment method once for each order. The order-line entity will contain order number as a foreign key to identify the order, the part number as a foreign key to identify the part concerned and the quantity ordered.
| Tutor/Learner Guidance Inexperienced E-R modellers frequently identify M:M relationships incorrectly. The argument appears to run as follows: there are many customers and there are many orders, therefore there must be a M:M relationship. The problem lies in thinking only about the entities involved and not the relationship between them. The following slides may illustrate the incorrect and correct representations more clearly. |
A relationship is drawn as a line with a so called crows foot at the many or member end of the relationship. As a general rule your relationship lines should point down the page.
| Tutor/Learner Guidance Another common problem concerns the resolution of M:M relationships, when the 'link' entity is introduced the new relationship lines are drawn such that the 'crows feet' stay attached to the original entities. The following slides may help to illustrate the incorrect and correct method of resolution more clearly. |
Relationships may be recursive, i.e. an entity can be related to other entities of the same type. Recursive relationships can be 1:M (e.g. a tree structure like an organisation chart) or M:M (e.g. a bill of materials structure). slide 11
M:M recursive relationships are broken down into two 1:M relationships and a link entity in the same way as non-recursive M:M relationships. slide 12
Relationships can be mutually exclusive with other relationships. In other words an occurrence of entity type A may be owned by either an occurrence of entity type B or an occurrence of entity type C. Consider an application in which there is an entity type called Course_Type, e.g. SSADM Version 4 and an entity type called Course_Run, e.g. SSADM Version 4 at the Marriot Hotel Cardiff on July 19th 1994. If there are places available on a particular Course_Run then a booking may be created owned by that Course_Run. If there aren’t any places available on any Course_Runs, then a booking can be owned by the Course_Type indicating that the booking is on a waiting list. When a place becomes available the booking can be disconnected from the Course_Type and connected to the appropriate Course_Run. slide 14
Alternatively an occurrence of entity type A can either own occurrences of entity type B or occurrences of entity type C. Consider an application which needs to trace who has bought particular parts and who as supplied particular parts. An Organisation entity could either own many Supplier_Of_Part entities or many Purchaser_Of_Part entities. Mutual exclusion is shown using an arc across the relationship lines.slide 15
Each end of each relationship must be optional or mandatory. If a relationship end is optional (shown by using a broken line) the entity at that end of the relationship can exist without taking part in the relationship. If a relationship end is mandatory (shown using a solid line) the entity at that end of the relationship must take part in the relationship.
This gives rise to four types of 1:M relationship:-
- Owner Optional - Member Mandatory;
Consider an application in which a Customer can own many Orders. A Customer entity is allowed to exist without having placed any Orders (e.g. a potential customer), but an order must have been placed buy a customer. slide 17 - Owner Mandatory - Member Mandatory;
Consider an application in which an Order consists of many Order Lines. In this case an Order must have at least one Order_Line (an order consisting of 0 order lines is nonsensical) and an Order Line must be owned by an Order. slide 18 - Owner Optional - Member Optional;
Consider an application in some Employees negotiate Orders with Suppliers, but Orders can be received direct from Customers. In this case an Employee (who isn’t a salesperson) can exist without negotiating any Orders and an Order isn’t necessarily owned by an Employee. slide 19 - Owner Mandatory - Member Optional;
Consider an application where some Employees are paid extras via a privately negotiated Commission Plan. In this case a Commission_Plan would not exist unless there was at least one employee being paid via that Commission Plan. An Employee does not have to be on a Commission_Plan. slide 20
| Tutor/Learner Guidance This is an area which can be very confusing for inexperienced practitioners. Owner Optional Member Mandatory Relationships are the most common and should be represented as shown in slide 17. However they are often drawn as shown in slide 20. The source of this confusion lies in the following way of interpreting- 'There must be customers therefore the relationship at the customer (owner) end must be mandatory, there may be orders therefore the relationship at the order (member) end must be optional'. In other words the confusion is caused by thinking about the entities and not the relationships. The following is the correct interpretation 'Customers may exist without having placed any orders (e.g. a person who owns a credit card but has never used it) therefore the relationship at the customer end must be optional, For an order to exist it must have been placed by one (and only one) customer, therefore the relationship at the order end must be mandatory. |
Relationships are named at both ends, the names chosen should be such that meaningful sentences can be constructed describing the nature of the relationship using the entity names and the relationship names.
The Relationship Between Logical Data Structures and Data Flow Diagrams
Since LDSs and DFDs are different views of the same thing you would expect there to be some commonality between them. The obvious area is data stores and entities. Each entity type in the LDS has to be represented in a data store somewhere. This may be a one to one mapping, e.g. the customer entity type will map one to one on to the customers data store, or a many to one mapping, e.g. the order and order_line entity types will be held in one data store called orders.
How are Logical Data Structures Created?
The following steps may be helpful but there really are no hard and fast rules. As the analysis and design exercise proceeds the LDS will evolve and many re-drafts may be necessary as the analysts understanding of the application improves:-
- Identify an initial list of entities
- Using an Entity/Relationship cross reference identify the initial relationships
- Create a first draft LDS
- Validate the LDS against the identified requirements
- Identify any new entities/relationships required
- Rationalise the LDS by combining removing entities/relationships
- Re-Draft the LDS
- Identify and place the required attributes, ensuring that each entity has the appropriate primary and foreign keys
- Ensure that the structure is in third normal
Tutorial Sheet: Data Modelling
Objectives
The most important skill I want you to develop is that of Entity-Relationship Modelling, you should already be familiar with the skills involved from your level 1 modules. From each of the following descriptions draw an Entity-Relationship Model using whatever diagramming conventions you are used to, your diagrams should indicate mandatory (obligatory) and optional (non-obligatory) relationships. Remember that except in the most rudimentary scenarios, there is very often no single correct answer, a lot depends on the assumptions you make.
Question 1
A large company wishes to develop a database system to hold details of the orders placed by its customers and the parts and quantities contained on those orders. Each order must be for one or more parts and a part can appear on many orders.
Question 2
The company has realised that in many cases 'CUSTOMER' refers not to a private individual, but to an organisation that has any number of premises (including 0!). Therefore the company needs to record, for each customer / organisation, details of their premises and the associated Unitary Authority and Postcode.
Question 3
For marketing purposes the company needs to record details of the contacts it has with its customer organisations. Contacts may be linked to specific customer premises or directly to the organisation. It is now possible to link orders received not simply to an organisation but in some cases to a specific premises and a specific contact within the organisation
Question 4
The company has now decided to start a simple personnel database which records details of the job-type and section of each employee. In addition to the current job-type and section the database should hold details of promotions and/or transfers.
Question 5
The Company is broken down into divisions, divisions are broken down into departments and departments are broken down into sections. Some employees of the company do not work for sections, employees can work directly for departments, divisions or the company itself. The personnel database has to be extended to cope with these more complex situations.
Question 6
The sales and personnel databases have to be linked as follows. An order may be negotiated by a salesperson (who is obviously an employee of the company) but may be received directly from the customer over the phone. Order details are entered into the system by an operator (who is also an employee of the company).
Question 7
In addition to holding details of finished parts, the company now wishes to record details of the make up of these parts. Each part may consist of many other parts. Each part may contribute to many other parts
Tutorial Sheet: Learning Resources Centre Case Study
Objectives
The only way to improve your skills in the development of LDS, DFDs and ELHs is to practice the skills on non-trivial applications and receive feedback from your peers and from myself. Remember there is no right answer but lots of wrong answers, make any appropriate assumptions but ensure that you document them.
Scenario
The Learning Resources Centre at the University of Glamorgan (LRC) services the requirements of students, staff and external members who are allowed to reserve books and take books out on loan.
The following activities are currently supported by manual procedures;
- Reserving a book involves filling in a form and passing to LRC staff. The member will then be notified by mail when a copy of the book reserved comes in. Taking a book out on loan simply involves taking the book and your library card to the issue/return desk where the information is recorded. Returns involve handing the books back to the desk and paying any fines due.
- If books are overdue the LRC sends a reminder to the member concerned, the member will be warned that they cannot take any more books out of the LRC until they returned the overdue book and paid any fines due.
- The LRC stocks many book titles and for each book title there may be many copies. Copies may be on loan or on the shelves (either on short loan, 1 week loan or 3 week loan). The system maintains details of all book titles and copies.
- Information is held on book copies for six months following the physical removal of the copy. Information on book titles is removed when the information regarding the last copy of that book has been removed.
Task 1
Produce an initial list of entity types.
Task 2
Using an Entity/Relationship cross reference, identify the initial relationships.
Task 3
Create a first draft LDS
Scenario (Continued)
The LRC also requires the following activities to be computerised:-
At the start of each academic year the LRC sends a request for titles to the academic staff of the University. These requirements are analysed by LRC staff and purchase orders created which are sent to appropriate suppliers. Suppliers deliver the goods and subsequently invoice the LRC.
The library also has rooms which can be booked and various facilities which can be borrowed.
| Tutor/Learner Guidance This part of the exercise is meant to simulate the real nature of systems analysis and design where new requirements are identified as the project progresses. |
Task 4
Validate the LDS against the identified requirements. Identify which processes are catered for and which not.
Task 5
Identify any new entities/relationships required and add them to the LDS.
Task 6
Rationalise the LDS by combining/removing entities/relationships
Task 7
Redraft the LDS
Task 8
Identify and place the required attributes, ensuring that each entity has a primary key and appropriate foreign keys and that the structures are in 3NF.
Task 9
Cross Validate the DFD set against the LDS. Remember that each entity type in the LDS has to appear as a data store or part of a data store in the DFDs. Remember also that each data store has to have at least one input flow and at least one output flow. This task may involve redrafting either or both of the DFDs set and the LDS. For example an entity type which doesn’t appear in a data store could indicate a missing data store and/or process. As a further example a data store which does not support an entity could indicate a missing entity type.
| Learner Guidance If you want further information, the school of computing has developed a Computer Based Learning package supporting the Entity Life History technique. This package will eventually be available through the web but for now, locate the G:\Library\CBL\ELH folder (using 'my computer' ) and double click on the ELH1.app icon. |
The objectives of this section are to provide definitions of the terms Entity/Event Model and Entity Life History, describe the ELH constructs,and introduce a step by step process for developing Entity Life Histories.
What is an Entity/Event Model?
An Entity/Event Model (EEM) defines the business events which affect a system, it defines the effects which these events have on the systems entities and it defines the order in which these events take place. In outline business events trigger processes which in turn affect entities. An EEM consists of:-
- A set of Entity Life Histories (ELH) together with supporting documentation slide 23
- A set of Effect Correspondence diagrams (ECD) together with supporting documentation
ELHs show the sequence of business events from the point of view of each entity, i.e. an individual ELH is drawn for each entity type showing the business events which initially create an occurrence of the entity type, then update and finally delete an occurrence of the entity type. NB ELHs do not contain read only, i.e. enquiry or report production activities.
ECDs show the effects of business events from the events point of view, i.e. each business event has its own ECD. NB In this course because of time constraints we will only be looking at ELHs.
What do Entity Life Histories Consist of?
Events/Effects
An event can be defined as something which triggers a process to create, update or delete an occurrence of one or more entity types. An event is not a process, it is the stimulus which causes a process to be invoked. There are three common types of events:-
- External Events; Occur when transactions arrive from the outside world.
- Internal Events; Occur when predefined conditions within the data have been met.
- Time-based Events; Occur at preset intervals.
An example of an external event is an order arriving from a customer, this event would trigger the process which checks whether the order is from a new customer, checks if the required goods are in stock and finally creates one order entity and a number of order line entities. This single event may also involve the creation of a new customer, the update of a customers outstanding balance and the downdate of quantity in stock.
What is an Effect?
The change within a single entity, i.e. creation, update or delete caused by an event is called an effect slide 27
An ELH shows the sequence of effects which business events cause on a particular entity type. These effects can be in straightforward sequence, or they may involve iteration, selection and parallelism.
How are Entity Life Histories Drawn?
ELHs are drawn using the structured programming/design constructs of sequence, selection and iteration with the addition of parallel horizontal lines to indicate parallel effects, i.e. effects in which the sequence cannot be pre-determined. A complete example of an ELH follows: slides 29 - 33
What Steps are involved in the Creation of Entity Life Histories?
- Establish a list of entities. The list of entities is taken from the LDS starting with those entities which are lowest in the hierarchy, i.e. those which do not take part in any relationships as owner and ending with those highest in the hierarchy, i.e. those which do not take part in any relationships as members.
- Establish a list of events. External events can be drawn up by listing all the data flows which come in from external entities. Internal events and time-based events are more difficult but it helps if you review each attribute of each entity and ask yourself whether it can be updated and if so under what circumstances.
- Create an Entity/Event Matrix. This is a grid with the list of events across the top and the list of entities down the side.. The events are taken one at a time and the grid filled in by identifying the effect the event has on each entity, i.e. Create, Update or Delete.
- Validate the grid chart by (a) checking that each event actually does something (if an event doesn’t create, update or delete any entities is it really an event or perhaps an entity has been overlooked) and (b) checking that each entity has at least one creation event and at least one deletion event.
- Cross validate the events with the DFDs to ensure that each event is catered for in processes. (this may involve re-drafting the DFDs).
- Draw the first draft ELHs starting with the entity at the top of the list and working down. To create a first draft draw a box at the top containing the entity type. Then look across the list of events, if there is only one creation event draw a box below and to the left of the entity type box and fill in the name of the event, if there is more than one creation event draw them all in using the structured programming selection construct. Draw in a main life cycle box. Look across the list of events for deletion events, again if there is only one draw a box to the right of the main life cycle box. Finally the difficult part, decide on how the update events should be drawn in below the main life cycle box, using the appropriate selections, iterations and parallel lines.
How do ELHs relate to Logical Data Structures and Data Flow Diagrams?
Each entity type in the LDS will have an associated ELH, each event has to be supported by a process or processes in the DFD. Entity/Event modelling in addition to providing a useful system viewpoint in their own right can be used to check the consistency, accuracy and completeness of the LDS and DFDs.
As can be seen analysis is not simply a case of drawing the LDS, drawing the DFDs and then drawing the ELHs, frequently the analyst will have to change tack and move from LDS to DFD to ELH, modifying and re-drafting as understanding improves.
At the end of the analysis and logical design stages the analysis/design team should have three separate but linked models which have been cross validated and which together give a complete picture of the system in question.
Tutorial Sheet
Entity Life Histories
Objectives
To continue the process of modelling the LRC system and to explain how ELHs can be used to validate the DFDs and the LDS.
Task 1
Establish a list of entities.
Task 2
Establish a list of events.
Task 3
Create an Entity/Event Matrix.
Task 4
Validate the matrix.
Task 5
Validate the DFD set by ensuring there is a process to cater for each event.
Task 6
Select two or three entities and draw their first draft ELHs
Task 7
Carry out a final consistency check on your DFDs, ELHs and the LDS and re-draft if necessary.
Entity Life Histories (Additional Notes)
To draw entity life histories 2 things are required;
An Entity/Event Cross Reference Matrix. produced by identifying a list of entities, a list of events and identifying how each event affects each entity, i.e by creation, deletion or update. The process of validating this matrix is described in chapter 7.
Entity List Loan Member Reservation Resource Copy Member Type Resource Title | This is the order in which the ELHs will be drawn. The reason for this bottom to top approach is as follows; the temptation when drawing an ELH for an entity like Member or Customer is to include everything that can ever happen to the member. This is not the purpose of ELHs. ELHs only show the events which either create, delete or update the entity |
A list of attributes for each entity.
The order described above is suitable for drawing ELHs but not for designing tables, when designing tables the reverse order is more suitable. The reason being that it is difficult to work out the attributes for an entity like ‘Loan’ without knowing the attributes of ‘Member’.
| Attribute List
Resource Title Resource-Title-Number (Primary Key) ISBN-Number Title Author Publisher Member Type Member-Type-Number (Primary Key) Member-Type-Description Maximum-Concurrent-Loans Member Member-Number (Primary Key) Member-Type-Number (Foreign Key) Name Address Home-Telephone Work-Telephone Current-Loans Reservation Reservation-Number (Primary Key) Member-Number (Foreign Key) Resource-Title-Number (Foreign Key) Date-of-Reservation Resource Copy Resource-Copy-Number (Primary Key) Resource-Title-Number (Foreign Key) Loan Loan-Number (Primary Key) Resource-Copy-Number (Foreign Key) Member-Number (Foreign Key) Loan-Date Expected-Return-Date Actual-Return-Date Fine-Due Amount-Paid |
Task 1
Review the LDS and Level 1 DFD from previous chapters and the first draft ELHs produced in the latest tutorial sheet.
Task 2
Review each of the attributes described above and establish how they affect your 1st draft ELHs.
Task 3
Redraft your ELHs as appropriate.
No comments:
Post a Comment