Data Modelling

1
Data Modelling
2
Introduction

• Historical development with most organisations
• Small-scale origins
• Individual computers bought for particular
  applications
• Typically batch-processed billing systems and
  payroll
• Gradual development of systems for other
  departments
• Systems developed independently
• Many files created, often with different data
  structures to suit different programs and even
  different operating systems

3
Problems with traditional approach

• Data redundancy
• data duplicated in many different files.
• Data inconsistency
• same data held in different files has to be
  updated in each separate file when it changes.
• Program-data dependence
• Every computer program has to specify exactly
  what data fields constitute a record in the file
  being processed. Changes in data structure result
  in changes to programs
• Lack of flexibility
• Difficult and time-consuming to assemble the data
  from the various files and write new programs to
  produce the required non-routine reports.
• Data was not shareable
• If one department had data that was required by
  another department, it was awkward to obtain it.

4
The Database approach

• The concept of a database was born out of a need
  to solve the problems caused by the flat-file
  systems
• Database
• A collection of non-redundant data shareable
  between different applications
• Company data to be centralised in a common
  resource for access by any application
• Although solving problems of redundancy and
  inconsistency, new problems were created
• Unproductive maintenance programs were still
  dependent on data structure, so changes required
  by one department would require all programs
  using that data structure to be changed.
• Lack of security all data could potentially be
  accessed by all applications

5
Database Management System

• A DBMS is a layer of software inserted between
  the data files and the applications
• This software goes some way to solving the
  remaining problems
• Two essential features are
• Program-data independence
• User / application does not need to be aware of
  the data storage structure
• User access restrictions
• Each user is limited to applicable data

6
The conceptual data model

• From the statement of data requirements a
  conceptual data model is produced.
• This describes how the data elements in the
  database are to be grouped.
• Three terms are used to describe the data
  requirements
• An entity is a thing of interest to an
  organisation about which data is to be
  heldExamples of entities include Customer,
  Employee, Stock Item, Supplier.
• An attribute is a property or characteristic of
  an entityExamples of attributes associated with
  a Customer include Customer ID, Surname,
  Initials, Title, Address, Credit Limit.
• A relationship is a link or association between
  entitiesAn example is the link between Dentist
  and Patient one dentist has many patients, but
  each patient only has one dentist.

7
Types of relationship

• There are only three different degrees of
  relationship between two attributes. A
  relationship may be
• One-to-one e.g. the relationship between Husband
  and Wife, or between Householder and Main
  Residence
• One-to-many e.g. the relationship between Mother
  and Children, between Customer and Order, between
  Borrower and Library Book
• Many-to-many e.g. the relationship between
  Student and Course, between Stock Item and
  Supplier, between Film and Film Star

8
Entity-relationship diagrams

• An entity-relationship diagram is a diagrammatic
  way of representing the relationships between the
  entities in a database.

9
A hospital in-patient system

• A hospital is organised into a number of wards.
• Each ward has a ward number and a name recorded,
  along with a number of beds in that ward.
• Each ward is staffed by nurses.
• Nurses have their staff number and name recorded,
  and are assigned to a single ward.
• Each patient in the hospital has a patient
  identification number, and their name, address
  and date of birth are recorded.
• Each patient is under the care of a single
  consultant and is assigned to a single ward.
• Each consultant is responsible for a number of
  patients.
• Consultants have their staff number, name and
  specialism recorded.

10
Exercise on In-patient system

• State 4 entities for the system giving a suitable
  identifier for each entity.
• Draw an entity-relationship diagram to show the
  relationships between the entities.