IT Systems Analysis and Design

1. IT Systems Analysis and Design Entity Relationship Diagrams BTEC National Extended Diploma

2. Aims • Gain some understanding into Entity Relationship Diagrams (ERD) and what they are used for. • Attempt to understand the decomposed process • Attempt to create an ERD diagram BTEC National Extended Diploma

3. Entity Relationship Diagrams • ERD • An ERD represents the entities and the relationships that exists between them. • An ERD diagram is created to establish what tables within a database are required and what foreign keys are needed BTEC National Extended Diploma

4. Entity Relationship Diagrams • ERD – Entities • Real world things that are part of the system under investigation. • Examples include Products, Customers, Orders. BTEC National Extended Diploma

5. 1 M M M 1 1 Entity Relationship Diagrams • ERD – Relationship • Relationships illustrate how two entities share information in the database structure. • Relationships exists between each of the entities, examples are shown below: BTEC National Extended Diploma

6. Products Employee Order Entity Relationship Diagrams M 1 M M involves Makes A many to many relationship as shown above is not allowed because it will create duplicate data, and repeating groups. For more information use the link below: http://www.cms.livjm.ac.uk/johnwillitts/RelDBDes/13Norm/SuppRepeatingGrp/repeating_groups.htm BTEC National Extended Diploma

7. Entity Relationship Diagrams Order table This is the result of a many to many relationship, as you can see there is repeating groups of data (A1, A2, A3), the OrderID primary key has lost it’s uniqueness and there is loads of redundancy i.e. the order date 29/03/2009 is stored more than once. BTEC National Extended Diploma

8. Order Products Order Employee Customer Products Entity Relationship Diagrams 1 M M 1 M 1 Makes Consists Included M Can Make 1 To resolve this many to many relationship we have to add another table which is a decomposed table of order and products, called Order Products. BTEC National Extended Diploma

9. Entity Relationship Diagrams Product Order Products Employee Customer Order table By using the decomposed table it reduced redundancy and removed repeating groups from the Order table, this results in a better functioning database. BTEC National Extended Diploma

10. Entity Relationship Diagrams • ERD – Attribute • An attribute is a specification that defines a property of an entity. An attribute of an entity usually consists of a name and a value for example: These are all attributes, they have a name and a value. BTEC National Extended Diploma

11. Conclusion • Gained some understanding into Entity Relationship Diagrams (ERD) and what they are used for. • Attempted to understand the decomposed process • Attempted to create an ERD diagram BTEC National Extended Diploma