IRU database design part one Geoff Leese September

IRU – database design part one Geoff Leese September 2009 1

The mission statement “High level” purpose of the project n “The purpose of the student records database system is to collect, store, manage and control access to the data that supports the delivery of our HE courses” n 2

The mission objectives Each one identifies a particular task that the database must support n Examples n ä To enter, update and delete data about students ä To perform searches on staff data ä To report on assessment data 3

The Requirements Specification Document (RSD) Often a very formal document – avoids argument later! n Templates exist for their production n Clear, complete, unambiguous set of requirements n Often expressed as “user views” n Based on interviews, observation etc. n 4

RSD example (VERY partial) n The module leader must be able to ä ä 5 Maintain (enter, update delete) details about her module(s) Maintain (enter, update, delete) details about the sessions where her module is delivered Maintain (enter, update, delete) details about the assessments required in his module Report on which students are taking his module and their assessment progress

ERD implied by that view 1 is assessed by Module 1 is taught in Assessment M assesses M belongs to Session M takes M is attended by Student M attends 6 M is taken by

Entities n n An object or concept about which a system needs to hold information is known as an entity type or entity. a person, place, a thing, an object, an event which can be distinctly identified and is of interest. e. g. : A specific student: Fred Smith ä 7 ä Can be concrete like a company Can be abstract like an idea, concept

An entity must satisfy the following criteria: There must be more than one occurrence of the entity n Each occurrence must be uniquely identifiable n There must be data that the organisation wants to hold about the entity n It should be of direct interest to the system n 8

Entity relationship modelling A method of analysing the logical structure of an organisations information. n graphical representation of: n ä what the information is about ä how it relates to other information and business concepts ä how business rules are applied to its use in the system. 9

Concepts and notation Many different notations used for drawing ER-diagrams n SSADM methodology: n ä Logical 10 Data Structures (LDS). – This approach uses four main concepts and symbols: – Entities, – Relationships – Degrees – Optionality

Attribute n The attributes describe the entity ä e. g. Sales Invoice entity may have the following attributes: – Sales invoice number – Customer Number – Sales invoice date ä The detail of the attributes belonging to entities can be recorded as part of the diagram or in a separate textual description. 11

Relationships n n 12 Entities do not remain isolated they are related to other entities. Represents a logical and not physical explanation of a system. The relationships between entities represent business associations or rules and not physical links. Any entities which are related are joined by a line on the diagram. The line is labelled with the name of the relationship in both directions and the relationship is two way

Degree of relationship (1) n n 13 Denotes the number of occurrences of each entity type in the relationship SSADM notation this is shown using ‘crows feet’.

Degree of relationship (2) n n 14 One to one (1: 1) This shows that each occurrence of Entity A is related to only one of entity B and each occurrence of B is related to only one occurrence of A. Example - each student has one set of personal data, and each set of personal data is related to only one student.

Degree of relationship (3) n n 15 One to Many (1: M) This shows that each occurrence of entity A may be related to many occurrences of entity B. Example: each sales invoice may be sent to only one customer but each customer may receive one or more sales invoices. This reflects one of the business rules of the organisation.

Degree of relationship (4) n n n 16 Many to one (M: 1) This shows that many occurrences of Entity A may be related to a single occurrence of Entity B. (It’s 1: M the other way around!) Many to Many (M: N) Relationships which are 1: 1 or M: N are usually converted to 1: M on closer inspection.

Optionality n n n 17 Each relationship can be further annotated to show if it must exist for all occurrences of the participating entity types. If there can be occurrences of one entity that are not related to at least one occurrence of the other entity, then the relationship is optional. shown by the use of a dashed line at the optional end(s) in the SSADM approach.

Part of an ERD Supplier May be sent Purchase order Is sent to So what does it mean? 18

It means………. . n 19 Each supplier may be the supplier for one or more purchase Orders and each Purchase Order must be placed with one supplier.

One to try………. Student 20 Course

Use of ERDs SSADM uses it in the feasibility phase as well as in the investigation of the current system and in the logical design of the proposed system. n RAD approaches concentrate on the proposed system and so use ERDs to model the proposed system alone. n 21

Tips on drawing ERDs n n n 22 Diagrams are easier to follow if you avoid crossing lines where ever possible. For 1: M relationships you should aim to keep the entity at the ‘ 1’ end above and/or to the left of the entity at the ‘M’ end. (Not always possible). At all times the diagram should agree with the users understanding of the system.

Resolving many to many relationships: n The main reasons for this are: ä The M: N relationship hides many masterdetail relationships ä M: N relationships make navigation around the model very difficult or even impossible. ä M: N relationships invariably hide information about the participating relationships or the entities themselves. ä Many DBMS’s (MS-Access included!) can’t implement M: N relationships! 23

How to resolve M: N Take the primary key from the entity at each end of the M: N relationship. n Merge them to form an “interposing” or “joining” entity between the original two. n Determine a primary key for the new entity n Re-examine dependencies n 24

Resolving one to one relationships achieved either by merging the two entities involved n or by replacing the 1: 1 relationship by a I : M relationship which reads one or more, the reason for this is: n ä 1: 1 relationships often obscure an underlying single entity ä There may be a “missing link” entity. 25

Resolving M: N relationships n n n 26 A sales invoice consists of one or more sales invoice lines, and an invoice line is associated with one invoice. A sales invoice line comprises one item of stock, and an item of stock may appear on one or more sales invoice lines. The M: N relationship was hiding the entity invoice line.

Removing redundant relationships n n n 27 a “route map” navigates between any pair of entities, possibly by a number of different routes. One of the aims of drawing a clear diagram should be to include only the minimum number of relationships needed to apply all the business rules relating to the data. Any unnecessary relationships are redundant and will involve a maintenance overhead if implemented in the final system

ERDs in software Access uses the relationship view to show ERDs for the database n On the Tools menu, select Relationships to see an ERD of your database (only works with multiple tables) n Access Help has topics on relationships n 28

ERM Summary n n n 29 Aims to facilitate communication between the database designer and the end user during the requirements analysis. To facilitate such communication, the designer needs adequate communication tools. Entity-Relationship diagrams are such tools that enable the system analyst to display the overall view of the enterprise data structure (the enterprise conceptual schema).

Further Reading Rolland Chapter 2 n Date C. J ((2004); An introduction to Database Systems (8 th edn); Chapter 14 n Ritchie C (1998); Relational Database Principles; chapters 3 and 4 n Avison D. E. and Fitzgerald G. (1995); Information Systems Development. . . ; Section 4. 5 n 30