Data Modelling Introduction special thanks to Janet Francis

Data Modelling Introduction (special thanks to Janet Francis for this presentation) CREATE THE DIFFERENCE

Aims • Introduce Data Modelling • Introduce the concept of business rules • Introduce the concept of relational databases and associated rules (Codd’s laws) • Introduce the Entity Relationship Diagram CREATE THE DIFFERENCE Slide 2 of 20

Data Modelling • There are many types of data model • We will be using the Entity Relationship Model • The model provides an entity relationship diagram (ERD) which can easily be translated into a number of related tables for a relational database. • The underlying concept of storing data in a series of related tables allows for more flexible information retrieval. CREATE THE DIFFERENCE

The Entity Relationship Diagram • Comprises – Entities – Relationships CREATE THE DIFFERENCE

Entities • Entities are represented as a box Supplier • Entities are things about which data needs to be stored. • They differ from the external entities in the process model in that they do not interact with the system - instead, the data they store is used by the system. CREATE THE DIFFERENCE

Attributes • An attribute describes an entity • Eg. Attributes for the entity “Product” might be: – Type – Colour – Size – Shape CREATE THE DIFFERENCE

Attributes, Entities and the Database The Entity name will become the table name The attributes will be the column names Attributes will have a type associated with them eg. Text, Character, Integer, Date This type determines how they are stored and the operations which can be performed CREATE THE DIFFERENCE

Relationships • An entity cannot exist in the system without being related to another entity. • The relationship between two entities is determined by a set of business rules and/or assumptions • Relationships are drawn on the ERD as a line. • Relationships can be described in three ways: – Multiplicity (also known as Cardinality, Degree, Order) – Optionality (Whether participation in the relationship is optional or not) – Name CREATE THE DIFFERENCE

Multiplicity (Cardinality) • The relationship can be defined as – 1: many – 1: 1 – Many to Many CREATE THE DIFFERENCE

Optionality • The relationship can be defined as – 0: many – 0: 1 – 0 to Many CREATE THE DIFFERENCE

Name • A relationship must be named. The relationship name will contain a verb. • Naming is important because between two entities, there might be more than one relationship. • Eg The Entities Staff and Module – One member of staff leads the module – Members of staff teach the module CREATE THE DIFFERENCE

An example • Business Rules – A supplier supplies at least one and possibly many products – Each product has only one supplier supplies Is supplied Supplier Multiplicity 1: many No Optionality CREATE THE DIFFERENCE Product

Another example • Business Rule – A person may give one or more contact telephone numbers supplies Person Is supplied Contact telephone No. Multiplicity 1: many Optionality on one side – the number (if given) will belong to someone but not everyone will choose to give a contact number. CREATE THE DIFFERENCE

Laws of the relational model • Edgar Codd devised the Relational Model based on mathematical concepts. • Mathematical concepts are defined by rules (laws). • The Relational Model has 12 rules http: //en. wikipedia. org/wiki/Codd%27 s_12_rules • However, not all the rules are in common use in popular RDBMS’s • We will pay particular attention to rule 2 and rule 10. CREATE THE DIFFERENCE

Rule 2: Aim - Entity Integrity • Ensuring entity integrity is the aim of one of the basic rules which is applied to relational databases – Ensuring entity integrity means ensuring that there are no duplicate records within the table • To make sure that Entity Integrity is preserved, each entity must have a special attribute which uniquely identifies it. – the field that identifies each record within the table is known as a primary key – the primary key MUST • Be unique • Have a value (I. e. not be null) CREATE THE DIFFERENCE Slide 15 of 20

Rule 10: Aim - Referential Integrity • Ensuring referential integrity means ensuring that – data is consistent between related tables • enforced by the existence of a foreign key (which is related to the primary key in the related table) • The foreign key MUST contain a value which is present in the primary table. • Integrity constraints are used to enforce this. CREATE THE DIFFERENCE