A relational database has to be developed as a set of coordinated tables designed to satisfy specific requirements. There are different ways to achieve this with many methods, (methodologies) specifying a series of tasks. These tasks mirror the general principles of any computer system development. There is no strict sequence to the following principles:

Establishing requirements - involves consultation with, and agreement among, users as to what persistent data they want, expressed as a statement of data requirements. A statement of the persistent data that needs to be maintained in order to satisfy the needs of the variety of users in the organisation.

Data Analysis - starts with the statement of data requirements to produce a conceptual data model - a formal representation of what data the database should contain, in terms independent of how it may be realised. The conceptual data model is used in database design activities. It is about the shared, conceptual representation of what is communicated between people during database development - the data in a database.

Database Design - takes the conceptual data model to produce a specification of a logical schema. The specification of all the tables in the database and their constraints. Choices are made as to the most appropriate tables.

Implementation - is the construction of a database according to the specification of the logical schema, which requires a specification of the storage schema. For relational databases the storage schema will be limited to choosing indexes to satisfy performance requirements.

For this course the method used to express data concepts in the statement of requirements is Entity-Relationship modelling (E-R modelling).

Entity - represents a thing that has meaning in a given context and about which there is a need to record data. A conceptual data model is not about an individual entity - it is about entity types, and will cover the common properties of a collection of entities.

Where there are several entities of the same type - each one is seen as an instance of the entity with its own unique identifier attribute. The entity type may also have other attributes.

Attribute - is a component of an entity type that represemts a single property of entities of that type. They should be named accordingly e.g. StudentId for a students identifying number.

Relationship - is an association between entities that has meaning in a given context and needs to be recorded.

The degree of a relationship can be:

• one to one
• one to many
• many to many

A participation condition can be optional if entities of that type do not need to be involved in the occurrence.

A participation condition can be mandatory if entities of that type must be involved in the occurrence. when the participation condition is mandatory it is a constraint.

Constraint - many aspects of constraints can be represented in a diagram. Others are simply written into the conceptual model.

Assumptions - any aspect of the model that may not be otherwise explicitly represented.

Entity types

Ward (WardNo, WardName)
Patient (PatientId, PatientName)
Nurse (StaffNo, NurseName)
Doctor (StaffNo, DoctorName, Position, Specialism)
Team (TeamCode, TelephoneNo)
Treatment (StaffNo, PatientId, StartDate, Reason)
Drug (DrugCode, DrugName)
Prescription (PrescriptionNo, Quantity, DailyDosage)

Constraints

A doctor responsible for a patient must have a position of consultant.

A doctor heading a team must have a position of consultant.

A doctor providing treatment for a patient must be from the same team as 
the consultant who is responsible for the patient.

A consultant belongs to a team via the HeadedBy relationship whereas other 
doctors belong to a team via the ConsistsOf relationship.

Two nurses involved in an occurrence of the Supervises relationship must be 
assigned to the same ward.

The attribute Position (of doctor) only has a value of Consultant, Registrar 
or House Officer.

The Specialism attribute (of doctor) only has a value if the value for the 
Position attribute is Consultant.

Assumptions

Only the details of a patient'd current stay in hospital are recorded (i.e. only
as an in-patient).

Comments, suggestions, ideas to
Stuart Banner