– Part III
Chapter 16 Methodology – Conceptual Database Design
Review Questions
16.1 Describe the purpose of a design methodology.
A structured approach that uses procedures, techniques, tools, and documentation aids, to support
16.2 Describe the main phases involved database design.
Conceptual database design to build the conceptual representation of the database, which
16.3 Identify important factors in the success of logical database design.
Work interactively with the users as much as possible
Follow a structured methodology throughout the data modeling process
Employ a data-driven approach
16.4 Discuss the important role played by users in the process of database design.
system. In particular, the user should clarify any ambiguities in the specification that describes the
– Part III
4
16.5 Describe the main objective of conceptual database design.
16.6 Identify the main steps associated with conceptual database design.
16.7 How would you identify entity and relationship types from a u
specification?
16.8
the attributes with entity or relationship types?
In a similar wa
requirements specification. The attributes can be identified where the noun or noun phrase is a
property, quality, identifier, or characteristic of one of these entities or relationships. By far the
– Part III
16.9 Describe the purpose of specialization/generalization of entity types, and discuss why this is
an optional step in conceptual database design.
16.10 How would you check a data model for redundancy? Give an example to illustrate your
answer.
(1) Re-examine one-to-one (1:1) relationships
In the identification of entities, we may have identified two entities that represent the same
(2) Remove redundant relationships
A relationship is redundant if the same information can be obtained via other relationships.
(3) Consider time dimension
16.11 Discuss why you would want to validate a conceptual data model and describe two
approaches to validating a conceptual model.
We validate a conceptual data model to check the model to ensure that the model supports the
transactions required by this view. Using the model, we attempt to perform the operations
manually. If we can resolve all transactions in this way, we have checked that the conceptual
Two possible approaches to ensuring that the local conceptual data model supports the
required transactions are:
– Part III
6
16.12 Identify and describe the purpose of the documentation generated during conceptual database
design.
Exercises
The DreamHome case study
16.13 Create a local conceptual data model for the Branch user views of DreamHome documented
in Appendix A. Compare your ER diagram with Figure 13.8 and justify any differences found.
Student should create an ER diagram similar to Figure 13.8. Some acceptable differences may
be:
Specialization of Staff into Supervisor and Manager may not have been identified (in
16.14 Show that all the query transactions for the Branch view of DreamHome listed in Appendix A
are supported by your local conceptual data model.
The University Accommodation Office case study
16.15
study documented in Appendix B.1.
7
16.16 Create a local conceptual data model for the one user view. State any assumptions necessary
to support your design. Check that the local conceptual data model supports the required
transactions.
The EasyDrive School of Motoring case study
16.17
documented in Appendix B.2.
Requests
1..1
1..1
8
16.18 Create a local conceptual data model for the one user view. State any assumptions necessary
to support your design. Check that the local conceptual data model supports the required
transactions.
1..1
Sits
Runs
– Part III
9
The Wellmeadows Hospital case study
16.19 Identify user views for the Medical Director and Charge Nurse in the Wellmeadows Hospital
case study described in Appendix B.3.
16.20
Medical Director
The Director is responsible for the overall management of the hospital and must maintain control
over the use of resources (including staff, beds, and supplies) in the provision of cost-effective
treatment for all patients.
1. The hospital is composed of many wards. Each ward is managed by a Charge Nurse.
3. The hospital maintains a central stock of surgical (e.g. syringe, bandages) and non-
surgical (e.g. plastic bags, aprons). The details of surgical and non-surgical supplies
4. The details of the suppliers of the surgical, non-surgical and pharmaceutical items are
10
5. Patients are normally referred to the hospital for treatment by their local doctor. The
6. The details of patients referred to the hospital includes the patient number, name (first
7. When a patient is referred by his/her doctor to attend the hospital, the patient is given an
appointment and is examined by a consultant. The details of the appointment are stored
examination room (e.g. Room E112).
8. The details of outpatients are stored. The information stored includes the patient details
as stated earlier (see 6) and the date and time of the appointment at the outpatient clinic.
Charge Nurse
The Charge Nurse has overall responsibility for the management of a single ward. The Charge
Nurse is allocated a budget to run the ward and must ensure that all resources (staff, beds and
supplies) are used effectively in the care of patients.
The Charge Nurse and other senior medical staff are responsible for the allocation of beds to
patients on the waiting list.
1. The information to be held on each ward includes the details of staff allocated to each
2. The information stored on each patient on the waiting list includes the patient number,
3. When a patient enters the ward they are allocated a bed with a unique bed number. Each
11
4. Staff are allocated to work in wards, as required. The Charge Nurse of each ward is
5. When required the Charge Nurse may obtain surgical, non-surgical and pharmaceutical
16.21 Create local conceptual data models for each of the user views. State any assumptions
necessary to support your design.
The student should create local conceptual data models using the requirements specification for
– Part III
12
Attends
Is
GoesTo
Doctor
Clinic
Has
1..1
1..1