TEMPLE GATE POLYTECHNIC
ABIA STATE.
UGHELLI CAMPUS, DELTA STATE.
FIRST SEMESTER EXAMINATION
COURSE TITLE: DATABASE DESIGN I
COURSE CODE: COM 312 DEPARTMENTS: COMPUTER SCIENCE
TIME ALLOWED: 2 HOURS INSTRUCTIONS: ANSWER FIVE QUESTIONS
All questions carry equal mark (14 each)
QUESTION 1
a) Define the term Data (4marks)
b) What is Data modeling (4marks)
c) Draw a bank database management system (DBMS) (6marks)
QUESTION 2
a) What is a Database (4marks)
b) State five (5) properties of a database (5marks)
c) List five (5) Data can be stored (5marks)
QUESTION 3
a) Explain what you understand by Database Management System (8marks)
b) List five (6) projects a database can be linked to (6marks)
QUESTION 4
a) List and explain four characteristics of a Database (8marks)
b) Draw a typical Network model diagram. (6marks)
QUESTION 5
a) Explain the term Schemas (6marks)
b) List four (4) items to consider during the design process of a database. (4marks)
c) What is hierarchical model (4marks)
QUESTION 6
a) Write short notes on the following
i) Homogeneous distributed database systems
ii) Heterogeneous distributed database systems (8marks)
b) Explain the following
i) Domain
ii) Records (6marks)
QUESTION 7
a) Write short notes on the following
i) Logical data independence
ii) Physical data independence (8marks)
b) Explain four (4) types of keys in database creation (6marks)
MR PAUL APELEOKHA
MARKING SCHEME
QUESTION 1
Data are factual information such as measurements or statistics about objects and concepts.
We use data for discussions or as part of a calculation.
(4marks)
Data modeling is the first step in the process of database design. This step is sometimes
considered to be a high-level and abstract design phase, also referred to as conceptual design.
(4marks)
A bank database management system (DBMS).
(6marks)
QUESTION 2
A database is a shared collection of related data used to support the activities of a particular
organization. A database can be viewed as a repository of data that is defined once and then
accessed by various users.
(4marks)
A database has the following properties:
• It is a representation of some aspect of the real world or a collection of data
elements (facts) representing real- world information.
• A database is logical, coherent and internally consistent.
• A database is designed, built and populated with data for a specific purpose.
• Each data item is stored in a field.
• A combination of fields makes up a table. For example, each field in an employee table
contains data about an individual employee.
(4marks)
Data can be stored in:
• Filing cabinets
• Spreadsheets
• Folders
• Ledgers
• Lists
• Piles of papers on your desk (5marks)
QUESTION 3
A database management system (DBMS) is a collection of programs that enables users to create
and maintain databases and control all access to them. The primary goal of a DBMS is to
provide an environment that is both convenient and efficient for users to retrieve and store
information.
With the database approach, we can have the traditional banking system as shown in Figure
2.3. In this bank example, a DBMS is used by the Personnel Department, the Account
Department and the Loan Department to access the shared corporate database.
(8marks)
Because of the versatility of databases, we find them powering all sorts of projects. A database
can be linked to:
• A website that is capturing registered users
• A client-tracking application for social service organizations
• A medical record system for a health care facility
• Your personal address book in your email client
• A collection of word-processed documents
• A system that issues airline reservations
(6marks)
QUESTION 4
Self-describing nature of a database system
A database system is referred to as self-describing because it not only contains the database itself,
but also metadata which defines and describes the data and relationships between tables in the
database. This information is used by the DBMS software or database users if needed. This
separation of data and information about the data makes a database system totally different
from the traditional file-based system in which the data definition is part of the application
programs.
Insulation between program and data
In the file-based system, the structure of the data files is defined in the application programs so
if a user wants to change the structure of a file, all the programs that access that file might need
to be changed as well.
On the other hand, in the database approach, the data structure is stored in the system
catalogue and not in the pro- grams. Therefore, one change is all that is needed to change the
structure of a file. This insulation between the programs and data is also called program-data
independence.
Support for multiple views of data
A database supports multiple views of data. A view is a subset of the database, which is defined
and dedicated for particular users of the system. Multiple users in the system might have
different views of the system. Each view might contain only the data of interest to a user or
group of users.
Sharing of data and multiuser system
Current database systems are designed for multiple users. That is, they allow many users to
access the same database at the same time. This access is achieved through features called
concurrency control strategies. These strategies ensure that the data accessed are always correct
and that data integrity is maintained.
The design of modern multiuser database systems is a great improvement from those in the
past which restricted usage to one person at a time.
(8marks)
Network model diagram.
(6marks)
QUESTION 5
A schema is an overall description of a database, and it is usually represented by the entity
relationship diagram (ERD). There are many sub-schemas that represent external models and
thus display external views of the data. Below is a list of items to consider during the design
process of a database.
(6marks)
• External schemas: there are multiple
• Multiple sub-schemas: these display multiple external views of the data
• Conceptual schema: there is only one. This schema includes data items, relationships
and constraints, all represented in an ERD.
• Physical schema: there is only one (4marks)
The hierarchical model represents data as a hierarchical tree structure. Each branch of the
hierarchy represents a number of related records. Figure 4.2 shows this schema in hierarchical
model notation.
(4marks)
QUESTION 6
Homogeneous distributed database systems
Homogeneous distributed database systems use the same DBMS software from multiple sites. Data
exchange between these various sites can be handled easily. For example, library information
systems by the same vendor, such as Geac Computer Corporation, use the same DBMS
software which allows easy data exchange between the various Geac library sites.
Heterogeneous distributed database systems
In a heterogeneous distributed database system, different sites might use different DBMS software, but
there is additional common software to support data exchange between these sites. For example,
the various library database systems use the same machine-readable cataloguing (MARC)
format to support library record data exchange.
(8marks)
A domain is the original sets of atomic values used to model data. By atomic value, we mean that
each value in the domain is indivisible as far as the relational model is concerned.
(6marks)
QUESTION 7
Logical data independence
A logical schema is a conceptual design of the database done on paper or a whiteboard, much
like architectural drawings for a house. The ability to change the logical schema, without changing
the external schema or user view, is called logical data independence.
Physical data independence
Physical data independence refers to the immunity of the internal model to changes in the
physical model. The logical schema stays unchanged even though changes are made to file
organization or storage structures, storage devices or indexing strategy.
Physical data independence deals with hiding the details of the storage structure from user
applications. (8marks)
Candidate key
A candidate key is a simple or composite key that is unique and minimal. It is unique
because no two rows in a table may have the same value at any time. It is minimal
because every column is necessary in order to attain uniqueness.
From our COMPANY database example, if the entity is Employee(EID, First Name, Last Name,
SIN, Address, Phone, BirthDate, Salary, DepartmentID), possible candidate keys are:
• EID, SIN
• First Name and Last Name – assuming there is no one else in the company with the same
name
• Last Name and DepartmentID – assuming two people with the same last name don’t
work in the same department
Composite key
A composite key is composed of two or more attributes, but
it must be minimal. Using the example from the candidate
key section, possible composite keys are:
• First Name and Last Name – assuming there is no one else in the company with the same
name
• Last Name and Department ID – assuming two people with the same last name don’t
work in the same department
Primary key
The primary key is a candidate key that is selected by the database designer to be
used as an identifying mechanism for the whole entity set. It must uniquely identify
tuples in a table and not be null. The primary key is indicated in the ER model by
underlining the attribute.
• A candidate key is selected by the designer to uniquely identify tuples in a table. It must not
be null.
• A key is chosen by the database designer to be used as an identifying
mechanism for the whole entity set. This is referred to as the primary key. This
key is indicated by underlining the attribute in the ER model.
In the following example, EID is the primary key:
Employee(EID, First Name, Last Name, SIN, Address, Phone, Birth Date, Salary, DepartmentID)
Secondary key
A secondary key is an attribute used strictly for retrieval purposes (can be composite), for
example: Phone and Last Name.
Alternate key
Alternate keys are all candidate keys not chosen as the primary key.
Foreign key
A foreign key (FK) is an attribute in a table that references the primary key in another table OR it
can be null. Both foreign and primary keys must be of the same data type.
In the COMPANY database example below, DepartmentID is the foreign key:
Employee(EID, First Name, Last Name, SIN, Address, Phone, BirthDate, Salary, DepartmentID)
(6marks)
