Database Management System and Three-Schema Architecture

COVER PAGE

Question 3
(a) Why database management system (DBMS) is much more than just a database.
DBMS is much more than just a database because a DMBS is a software that helps database
users to create and manage databases by providing a systematic way to create, update, retrieve
and manage the data making up the database. Thus the database itself is the collection of tables
made of columns and rows which are related to one another while the DBMS is the software
designed to act as an interface between the database and the users of the database. By using a
DBMS to manage a database, the data in the database can be easily accessible and consistently
managed. A DBMS manages the data in the database, the engine in which the database runs on
and the database schema.
(b) The three-schema architecture has 3 levels;
 Physical level- in this level physical storage of the data making up a database is stored in
physical addresses on storage devices.
 Conceptual level- The conceptual level is used by database designers and describes the
structure of the database. Data models like Entity relationship diagrams are used in this
level.
 External level (user view) - The external level shows the relation of the data with the end
users. In this level different segments of the database are defined based on the groups
of users.
Based on the three-schema architecture described above, the following changes will be done on
each level to accommodate these new requirements.
(i) A new application process requires a new table in the database, and uses none of the
existing tables.
 Physical level- No change is required in the physical level because data physical
storage of the data is handled by the database management system (DBMS).
 Conceptual level- At this level, the data model should be updated to
accommodate the new tables added.
 External level- Changes in the user views are required to group the users who
will be using the new table.
(ii) A new application process is required that uses data already in use by an existing
application process. However, the performance requirements for the new application
require the database to use new hardware so that data retrieval is more efficient.
 Physical level- Because changes in the hardware are required to accommodate
the new changes, then the physical level will be changed by the DBMS to suit
the new hardware
 Conceptual level- No changes are required at this level because the data model
does not change as the new application uses existing data
 External level- Some changes in the user views will have to be done to make it
possible for the new application to access only parts of the data that its
supposed to access

(iii) A new application uses tables already in the database that are currently in use by
different application processes; no existing application process uses all of these tables.
 Physical level- No change is required in the physical level because data physical
storage of the data is handled by the database management system (DBMS).
 Conceptual level- No changes required in the conceptual level because the data
model of the database does not need any changes as the new application will be
using existing tables
 External level- Some changes in the user views will have to be done to make it
possible for the new application to access only parts of the data that it’s
supposed to access
Part 4 Data Modelling
Question 4
(a) List entity types and relationship types;
Entity Types
 Bird ( birdID, commonName, biologicalName, size, colouring, picture,
habitatDescription, familyID)
 Family ( familyID, name, generalCharacteristics )
 Location (locationID, name, description, GPS_Coordinates)
 Observation (observationID, date, time, duration, locationID, notes)
 Observed_birds (observationID, birdID)
Relationship Types
 Bird one-to-one family
 Family one-to-many bird
 Location one-to-one observation
 Observation one-to-one location
 Observation one-to-many optional observed_birds
 Observed_birds one-to-one observation
(b) Based on the diagram