Further Links Contact Us Search Help

Syllabus | Exams | Assessment | Resources | Site reviews | Careers

Information Processes and Technology

Home > Information Processes and Technology > Information Systems and Databases > Database Modelling

Database modelling

Database modelling
Entities, instances, attributes, relationships
Cardinality
Converting the ERD to a relational database
Creating a data dictionary
Creating a database
Practice exercise

Database modelling

Database modeling is an important part of the database design process. It provides a structured, systematic approach that supports the development of well-structured and high performance databases. If databases are well designed potential problems associated with data redundancy and poor performance due to ill-structured links between tables can be avoided or minimised.

Database modeling is utilised as just part of the design process of database systems. It is undertaken once the user needs have been identified and before and implementation is undertaken, that is before any consideration is given to the choice of DBMS or hardware. This paper focuses on the conceptual modeling stage of the database design process (in particular the creation of entity-relationship diagrams (ERDs)), and also implementing the design (ERD) using a relational database. Development of ER diagrams is a skill that can be developed through experience. Skilled database modelers are highly valued people within system development project-teams.

Figure 1 shows a sample entity relationship diagram that represents the relationships between patients admitted to a hospital and the doctors who treat them. The important entities in this situation are the patients and doctors. The diagram indicates that a doctor may admit many patients to the hospital, and just one doctor admits each patient. A doctor may treat many patients in the hospital, and a patient may be treated by a number of different doctors while they are in the hospital. Details of the treatment (date, time and results) are recorded for each treatment.

Figure 1: A sample entity relationship diagram that represents the relationships between patients admitted to a hospital and the doctors who treat them.

Let us now systematically look at each of the components that make up an ERD and how they are used.

Entities

An entity is a 'thing' of interest to an organisation about which data is to be stored. Example entities for a school database system could include students, teachers, classrooms, etc. Output from systems (e.g. reports) and individual objects (like the principal of a school) are not valid entities in an ERD. (Hint: when reading a description of a situation to be represented using an ERD entities can often be identified by paying attention to nouns).

Entities are represented in an ERD using a rectangle that is labeled by writing the name of the entity in the center in upper-case letters. In Figure 1 there are two entities they are DOCTOR and PATIENT.

Instances

An occurrence of an entity is called an instance. A database table ultimately holds each instance of an entity as records, or rows in a table. An example of an instance of the entity DOCTOR could be Dr. Peter Benjamin and examples of a instances for PATIENT could be Mrs. Helen Bowles or Mr. Paul Pappos.

Attributes

Characteristics of entities are termed 'attributes' of an entity. Attributes of a STUDENT may be name, date of birth, address, phone number, year advisor etc.

Attributes of entities are represented in an ERD using an ellipse that has the name of an attribute in lower-case (again a convention) letters inside. Each ellipse is connected to the relevant entity using a straight line. In Figure 1 the attributes of a DOCTOR are the Doctor_ID and Specialty. The attributes of a patient are Patient_Name and Patient_Number.

Key Attribute

Each entity must have a unique identifier, called a key attribute. The key attribute must be unique for the life of the entity and must be original for each instance. Key attributes are shown in ERD by underlining the name of the appropriate attribute. In Figure 1 Doctor_ID is the key attribute of the entity DOCTOR and Patient_Number is the key attribute of the entity PATIENT. When an identifier is made up of two or more attributes it is called a composite key.

Relationships

A relationship in an ERD describes the associations between entities that are of interest to the organisation and users. Relationships are shown in an ERD by a diamond with a description of the relationship written (in lower case letters) in the diamond. Lines connect the diamond to the related entities. In Figure 1 there are two relationships, 'admits' and 'treats'. (Hint: when reading a description of a situation to be represented using an ERD relationships can often be identified by paying attention to verbs).

The number of entities in a relationship identifies the 'degree' of a relationship. The degree of a relationship can be unary, binary or ternary. Examples of particular types of relationships are shown below in Figures 2, 3 and 4.

Binary Relationships (involving two entities)

A binary relationship describes a relationship that exists between two entities. Binary relationships are the type most frequently found in ERDs. See Figure 2 for an example of a binary relationship that describes the relationship between employees in an organisation and their assignment to projects. Both of the relationships in Figure 1 are binary.

Figure 2: An example of binary relationship

Unary Relationships (involving one entity)
A unary relationship describes a relationship that exists involving just one entity, or the relationship between instances within the same entity. An example is the description of the marriage relationship between people. The notation used for this unary relationship is shown in Figure 3. There are no unary relaionships in Figure 1.

Figure 3: An example of a unary relationship

Ternary (involving three entities)
A ternary relationship describes the relationship between three entities. It is often difficult to find a verb that describes the relationship between three entities so a name that relates the three entities can be used. See Figure 4 for en example of a ternary relationship that shows the relationship between students and teachers at a school in the context of the subjects in which students are enrolled. There are no ternary relationships in Figure 1.

Relationships beyond ternary relationships are not common, but do sometimes occur. There is no specific name to describe the relationship between more than three entities.

Figure 4: An example of a ternary relationship

Cardinality

Cardinality in ERDs expresses the specific number, or range of numbers, of entities involved in a relationship. Cardinality is usually describes as 'one-to-one' (1:1), 'one-to-many' (1:M) or many-to-many' relationships (M: N). Cardinality is indicated on an ERD using a numeral 1 or the letters M or N on the relevant relationship lines. For a relationship involving a 'many' an 'M' or 'N' includes a range of numbers from one to infinity. A relationship that involves a '1' includes a range of only one.

Figure 1 shows both a one-to-many (admits) relationship and a many-to many relationship (treats). Figure 5 shows the part of Figure 1 that forms the 'admits' relationship. When describing the 'admits' relationship it would be said that one doctor admits a patient to the hospital, but a doctor can admit one or more patients.

Figure 5: The 'admits' relationship from Figure 1

Figure 6 shows the part of figure 1 that forms the 'treats' relationship. When describing the 'treats' relationship it would be true to say that a patient can be treated by one, or a number of doctors (many) while in hospital, and that doctors treat one or many patients in the hospital.

Figure 6: The 'treats' relationship from Figure 1

Converting the ERD to a Relational Database

Once an ERD has been designed it is necessary to then map the design onto a database model that can be implemented using a DBMS (a database management system). ERDs are mostly mapped onto the relational database model. A relational model is based on relations (or tables) that are linked together. An example of a table, or relation is provided in Table 1. The table is made up of columns that are called fields, and rows that are called records. Each column has a field name at the top.

Product_ID	Product_name	In_stock	Cost_price	Retail_price
001	Box of 10 coloured pencils	15	$3.65	$5.65
002	Box of 10 red pens	23	$12.95	$16.93
003	Box of 100 rubber bands	32	$0.78	$1.64
004	Stapler	8	$8.97	$15.50

Table 1: An example of a relation

A special notation is used to describe relations that save us the need to draw up the table whenever you want to refer to it. The relational notation for the table in Figure 5 would be:

PRODUCT(Product_ID, Product_Name, In_Stock, Cost_Price, Retail_Price)

Where PRODUCT is the name of the relation and the field names are listed inside parentheses. When converting ERDs to a relational model we shall use this notation.

Steps in converting an ERD to a relational model.

There are some steps that can be followed to convert and ERD to a relational model, which can then be implemented using a relational DBMS.

Step 1
Each entity in the ERD becomes a relation, with the attributes of the relation becoming fields. For the ERD in Figure 1 the two relations PATIENT and DOCTOR, shown in Figure 7 would become the relations:

PATIENT(Patient_Number, Patient_Name)

DOCTOR (Doctor_ID, Specialty)

Figure 7: Entities PATIENT and DOCTOR from Figure 1 that become relations

The key attributes in the ERD become primary keys in the relations that are shown using underlining. A primary key in a relation uniquely identifies each record in the relation.

Step 2
Many-to-many relationships require the creation of a new relation that allows the two entities involved in the relationship to be linked. The primary key of the new relations is the combined keys from the two participating entities, forming a composite primary key. Together the two parts of the composite key produce a unique value for each record. Individually the primary keys from the two participating entities are also each a 'foreign key'. A foreign key is a field that provides a link to a primary key in another relation. A foreign key does not require that all values are unique, unlike a primary key.

Any attributes of the relationship become fields in the new relation. In many cases there are no attributes of the relationship and the linking relation contains just the two fields that from the composite primary key. For the ERD in Figure 1 there is one many-to-many relationship, 'treats' (see also Figure 6), which becomes the following relation:

TREATS (Patient_Number, Doctor_ID, Date, Time, Results)

A foreign key can be shown using italics, or in handwriting using a dotted or wavy underline. It is important to show a foreign key is different to a primary key.

Step 3
One-to-many relationships require a link to be created in a special way using the key attributes. This is done be putting the key attribute from the 'one' side of the relationship into the 'many' side of the relationship as a 'foreign key'. For the ERD in Figure 1 there is one one-to-many relationship, that is 'admits' (see also Figure 5). The 'one' side of the relationship is DOCTOR and the 'many' side of the relationship is PATIENT. That means that the key attribute in the relation for DOCTOR becomes a foreign key in the relation for PATIENT, meaning that the relation PATIENT now becomes:

PATIENT(Patient_Number, Patient_Name, Doctor_ID)

Final Relational Model for Figure 1
There are three relations that need to be implemented to create the database for the system described in Figure 1. The three final relations are:

PATIENT(Patient_Number, Patient_Name, Doctor_ID)

DOCTOR (Doctor_ID, Specialty)

TREATS (Patient_Number, Doctor_ID, Date, Time, Results)

Creating a data dictionary

In order to create the implementation of the database using a DBMS the next step is to create a data dictionary for the identified entities.

Entity (Table) Name	Attribute (Field) Name	Data Type	Data Format	Field Size	Description	Example
PATIENT	Patient_Number	Number	####	4	Unique identifier for each patient	0001
	Patient_Name	Text		45	The given and family names of the patient	Sylvia Citizen
	Doctor_ID	Number	###	3	Unique identifier for doctors	001
DOCTOR	Doctor_ID	Number	###	3	Unique identifier for doctors	001
	Speciality	Text		15	Special skills of the doctor	Immunology
TREATS	Patient_Number	Number	####	4	Unique identifier for each patient	0001
	Doctor_ID	Number	###	3	Unique identifier for doctors	001
	Date	Date	DD/MM/YY	8	The date of a patient treatment	16/02/2001
	Time	Time	HH:MM:SS	8	The time at which a patient received treatment	12:15:00
	Result	Text		255	Description of the treatment results/outcome	Patient suffering from chronic lower back pain for over 2 years. Treatment: increase tranverse abdominus strength via physiotherapy and exercises

Creating the database

Once the data dictionary has been defined the design can be entered into a DBMS by defining the database tables and the links between them. The data dictionary and the definition of tables and links are known as metadata - that is, data about the data in the database. Figure 8 shows the table structures and the links between them when implemented in Microsoft Access.

Figure 8: A 'Relationships Screen' from Microsoft Access showing table structures and links for the Patient/Doctor Example

After defining the tables and relationships data can be entered into the tables and the database used for the purposes for which it was designed.

Further reading

McFadden F. R., Hoffer J. A. and Prescott M. B. (1999) Modern Database Management (5^th Ed). USA: Addison Wesley. (Chapter 3 and Chapter 6 pp 218-231)

Rob C. and Coronel C. (2000) Database Systems: Design, Implementation and Management. USA: Course Technology (Chapter 4)

Practice exercise - Scenario

Stationary Supplies is a company that sells stationary products to customers. Stationary Supplies allows their account customers to place orders over the telephone and delivers the required products to the customers' premises. This is possible for account customers because the company keeps information about these customers in a database, including an identifer, the customer name, phone number and delivery address.

When an order is phoned through by a customer the company records order details, and in-particular the date of the order. Stationary Supplies keeps a database of information about their products, and when orders are received a record of the quantity of a product included in the order is recorded. An order may contain a number of different products.

Task:
Create an entity relationship diagram to represent the above scenario, and then convert your ERD to a relational model. You could also implement your design using a relational DBMS (like Microsoft Access)

Suggested solution - Entity relationship diagram

Relational model for the above ERD

ACCOUNT CUSTOMER (Customer_Number, C_Name,C_Phone, Delivery_Address)

ORDER (Order_Number, Order_Date, Customer_Number)

PRODUCTS(Product_ID, P_Name, P_Description, Retail_Price)

ORDER-INCLUDES-PRODUCT (Order_Number, Product_ID, Quantity)