DELTA CENTRAL COLLEGE OF MANAGEMENT AND
SCIENCES (DECCOMS)
IN AFFILIATION WITH
TEMPLE GATE POLYTECHNIC
ABA, ABIA STATE.
COURSE TITLE: DATABASE DESIGN 1
COURSE CODE: COM 312 DEPARTMENTS: COMPUTER SCIENCE
TIME ALLOWED: 2 HOURS INSTRUCTIONS: ANSWER FIVE QUESTIONS
QUESTION 1
1a. what is data? (3 marks)
1b. List five places that data can be stored (5
marks)
1c. Define a Database Management System (6 marks)
QUESTION 2
2a. Explain five characteristics and benefits of a database (10 marks)
2b. What is a High-level Conceptual Data Models (4 marks)
QUESTION 3
3a. Define a schema (4 marks)
3b. List the four types of schemas (4 marks)
3c. Data independence refers to the immunity of user applications to changes made in the definition
and organization of data. Explain logical data independence and physical data independence
(6 marks)
QUESTION 4
4a. There are four main distribution systems for database systems and these, in turn, can be used
to classify the DBMS. Elaborate on these classifications based on database distribution;
Centralized system
Distributed database system
Homogeneous distributed database systems
Heterogeneous distributed database systems (12 marks)
4b. What is a domain (2 marks)
QUESTION 5
5a. Highlight two properties of a table (2 marks)
5b. Define an entity (2 marks)
5c. List four characteristics of entities (4 marks)
5d. Explain two types of attributes (6 marks)
QUESTION 6
6a. Describe entity integrity and referential integrity (6 marks)
6b. What are enterprise constraints and business rules (6 marks)
6c When does an insertion anomaly occur (2 marks)
QUESTION 7
7a. Explain database development process (2 marks)
7b. Discuss the following terms
Analysis
Logical design
Realizing the design
Implementation (12 marks)
MARKING SCHEME
1a. what is data (3 marks)
Data are factual information such as measurements or statistics about objects and concepts. We
use data for discussions or as part of a calculation. Data can be a person, a place, an event, an
action or any one of a number of things. A single fact is an element of data, or a data element.
If data are information and information is what we are in the business of working with, you can
start to see where you might be storing it.
1b. List five places that data can be stored (5
marks)
Data can be stored in:
• Filing cabinets
• Spreadsheets
• Folders
• Ledgers
• Lists
• Piles of papers on your desk
1c. Define a Database Management System (6 marks)
Database Management System
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.
Figure 2.3. A bank database management system (DBMS).
Managing information means taking care of it so that it works for us and is useful for the tasks
we perform. By using a DBMS, the information we collect and add to its database is no longer
subject to accidental disorganization. It becomes more accessible and integrated with the rest of
our work. Managing information using a database allows us to become strategic users of the data
we have.
2a. Explain five characteristics and benefits of a database (10 marks)
CHARACTERISTICS AND BENEFITS OF A DATABASE
Restriction of unauthorized access
Not all users of a database system will have the same accessing privileges. For example, one user
might have read-only access (i.e., the ability to read a file but not make changes), while another
might have read and write privileges, which is the ability to both read and modify a file. For this
reason, a database management system should provide a security subsystem to create and control
different types of user accounts and restrict unauthorized access.
Data independence
Another advantage of a database management system is how it allows for data independence. In
other words, the system data descriptions or data describing data (metadata) are separated from
the application programs. This is possible because changes to the data structure are handled by
the database management system and are not embedded in the program itself.
Transaction processing
A database management system must include concurrency control subsystems. This feature
ensures that data remains consistent and valid during transaction processing even if several users
update the same information.
Provision for multiple views of data
By its very nature, a DBMS permits many users to have access to its database either individually
or simultaneously. It is not important for users to be aware of how and where the data they
access is stored
Backup and recovery facilities
Backup and recovery are methods that allow you to protect your data from loss. The database
system provides a separate process, from that of a network backup, for backing up and
recovering data. If a hard drive fails and the database stored on the hard drive is not accessible,
the only way to recover the database is from a backup.
If a computer system fails in the middle of a complex update process, the recovery subsystem is
responsible for making sure that the database is restored to its original state. These are two more
benefits of a database management system.
2b. What is a High-level Conceptual Data Models (4
marks)
High-level Conceptual Data Models
High-level conceptual data models provide concepts for presenting data in ways that are close to
the way people perceive data. A typical example is the entity relationship model, which uses
main concepts like entities, attributes and relation- ships. An entity represents a real-world object
such as an employee or a project. The entity has attributes that represent properties such as an
employee’s name, address and birth date. A relationship represents an association among
entities; for example, an employee works on many projects. A relationship exists between the
employee and each project.
3a. Define a schema (4 marks)
CHAPTER THREE
Schemas
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.
3b. List the four types of schemas (4 marks)
• 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
3c. Data independence refers to the immunity of user applications to changes made in the definition
and organization of data. Explain logical data independence and physical data independence
(6 marks)
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. For example, the addition or
removal of new entities, attributes or relationships to this conceptual schema should be possible
without having to change existing external schemas or rewrite existing application programs.
In other words, changes to the logical schema (e.g., alterations to the structure of the database
like adding a column or other tables) should not affect the function of the application (external
views).
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. The applications should not be involved with these issues, since there is no
difference in the operation carried out against the data.
4a. There are four main distribution systems for database systems and these, in turn, can be used
to classify the DBMS. Elaborate on these classifications based on database distribution;
Centralized system
Distributed database system
Homogeneous distributed database systems
Heterogeneous distributed database systems (12 marks)
Classification Based on Database Distribution
There are four main distribution systems for database systems and these, in turn, can be used to
classify the DBMS.
Centralized systems
With a centralized database system, the DBMS and database are stored at a single site that is used by
several other systems too. This is illustrated in Figure 6.1.
In the early 1980s, many Canadian libraries used the GEAC 8000 to convert their manual card
catalogues to machine- readable centralized catalogue systems. Each book catalogue had a
barcode field similar to those on supermarket products.
Distributed database system
In a distributed database system, the actual database and the DBMS software are distributed from
various sites that are connected by a computer network, as shown in Figure 6.2.
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.
4b. What is a domain (2 marks)
Domain
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.
5a. Highlight two properties of a table (2 marks)
Properties of a Table
• A table has a name that is distinct from all other tables in the database.
• There are no duplicate rows; each row is distinct.
• Entries in columns are atomic. The table does not contain repeating groups or multi-valued
attributes.
• Entries from columns are from the same domain based on their
data type including: number (numeric, integer, float, small
int,…)
character (string) date logical (true or false)
• Operations combining different data types are disallowed.
• Each attribute has a distinct name.
• The sequence of columns is insignificant.
• The sequence of rows is insignificant.
5b. Define an entity (2 marks)
Entity, Entity Set and Entity Type
An entity is an object in the real world with an independent existence that can be differentiated
from other objects. An entity might be
• An object with physical existence (e.g., a lecturer, a student, a car)
• An object with conceptual existence (e.g., a course, a job, a position)
5c. List four characteristics of entities (4 marks)
Characteristic Entities
Characteristic entities provide more information about another table. These entities have the
following characteristics:
• They represent multi-valued attributes.
• They describe other entities.
• They typically have a one to many relationships.
• The foreign key is used to further identify the characterized table.
• Options for primary key are as follows:
1. Use a composite of foreign key plus a qualifying column
2. Create a new simple primary key. In the COMPANY database, these might include:
* Employee (EID, Name, Address, Age, Salary) – EID is the simple primary key.
* Employee Phone (EID, Phone) – EID is part of a composite primary key. Here, EID is
also a foreign key.
5d. Explain two types of attributes (6 marks)
Types of Attributes
There are a few types of attributes you need to be familiar with. Some of these are to be left as is,
but some need to be adjusted to facilitate representation in the relational model. This first section
will discuss the types of attributes. Later on we will discuss fixing the attributes to fit correctly
into the relational model.
Simple attributes
Simple attributes are those drawn from the atomic value domains; they are also called single-valued
attributes. In the COM- PANY database, an example of this would be: Name = {John} ; Age =
{23}
Composite attributes
Composite attributes are those that consist of a hierarchy of attributes. Using our database example,
and shown in Figure 8.3, Address may consist of Number, Street and Suburb. So this would be
written as → Address = {59 + ‘Meek Street’ + ‘Kingsford’}
Figure 8.3. An example of composite attributes.
Multi-valued attributes
Multi-valued attributes are attributes that have a set of values for each entity. An example of a multi-
valued attribute from the COMPANY database, as seen in Figure 8.4, are the degrees of an
employee: BSc, MIT, PhD.
Figure 8.4. Example of a multi-valued attribute.
Derived attributes
Derived attributes are attributes that contain values calculated from other attributes. An example
of this can be seen in Figure 8.5. Age can be derived from the attribute Birth date. In this
situation, Birth date is called a stored attribute, which is physically saved to the database.
6a. Describe entity integrity and referential integrity (6 marks)
There are several kinds of integrity constraints, described below.
Entity integrity
To ensure entity integrity, it is required that every table has a primary key. Neither the PK nor
any part of it can contain null values. This is because null values for the primary key mean we
cannot identify some rows. For example, in the EMPLOYEE table, Phone cannot be a primary
key since some people may not have a telephone.
Referential integrity
Referential integrity requires that a foreign key must have a matching primary key or it must be
null. This constraint is specified between two tables (parent and child); it maintains the
correspondence between rows in these tables. It means the reference from a row in one table to
another table must be valid.
6b. What are enterprise constraints and business rules (6 marks)
Enterprise Constraints
Enterprise constraints – sometimes referred to as semantic constraints – are additional rules
specified by users or data- base administrators and can be based on multiple tables.
Here are some examples.
• A class can have a maximum of 30 students.
• A teacher can teach a maximum of four classes per semester.
• An employee cannot take part in more than five projects.
• The salary of an employee cannot exceed the salary of the employee’s manager.
Business Rules
Business rules are obtained from users when gathering requirements. The requirements-gathering
process is very important, and its results should be verified by the user before the database design
is built. If the business rules are incorrect, the design will be incorrect, and ultimately the
application built will not function as expected by the users.
Some examples of business rules are:
• A teacher can teach many students.
• A class can have a maximum of 35 students.
• A course can be taught many times, but by only one instructor.
• Not all teachers teach classes.
6c When does an insertion anomaly occur (2 marks)
Insertion Anomaly
An insertion anomaly occurs when you are inserting inconsistent information into a table. When
we insert a new record, such as account no. A-306 in Figure 10.2, we need to check that the
branch data is consistent with existing rows.
7a. Explain database development process (2 marks)
Database Development Process
A core aspect of software engineering is the subdivision of the development process into a series
of phases, or steps, each of which focuses on one aspect of the development. The collection of
these steps is sometimes referred to as the software development life cycle (SDLC). The software
product moves through this life cycle (sometimes repeatedly as it is refined or redeveloped) until it is
finally retired from use. Ideally, each phase in the life cycle can be checked for correctness
before moving on to the next phase.
7b. Discuss the following terms
Analysis
Logical design
Realizing the design
Implementation (12 marks)
Analysis
Data analysis begins with the statement of data requirements and then produces a conceptual data
model. The aim of analysis is to obtain a detailed description of the data that will suit user
requirements so that both high and low level properties of data and their use are dealt with. These
include properties such as the possible range of values that can be permitted for attributes (e.g.,
in the school database example, the student course code, course title and credit points).
The conceptual data model provides a shared, formal representation of what is being
communicated between clients and developers during database development – it is focused on
the data in a database, irrespective of the eventual use of that data in user processes or
implementation of the data in specific computer environments.
Logical Design
Database design starts with a conceptual data model and produces a specification of a logical
schema; this will determine the specific type of database system (network, relational, object-
oriented) that is required. The relational representation is still independent of any specific
DBMS; it is another conceptual data model.
We can use a relational representation of the conceptual data model as input to the logical
design process. The output of this stage is a detailed relational specification, the logical schema,
of all the tables and constraints needed to satisfy the description of the data in the conceptual
data model. It is during this design activity that choices are made as to which tables are most
appropriate for representing the data in a database. These choices must take into account various
design criteria including, for example, flexibility for change, control of duplication and how
best to represent the constraints. It is the tables defined by the logical schema that determine
what data are stored and how they may be manipulated in the database.
Implementation
Implementation involves the construction of a database according to the specification of a logical
schema. This will include the specification of an appropriate storage schema, security
enforcement, and external schema and so on. Implementation is heavily influenced by the choice
of available DBMSs, database tools and operating environment. There are additional tasks
beyond simply creating a database schema and implementing the constraints – data must be
entered into the tables, issues relating to the users and user processes need to be addressed, and
the management activities associated with wider aspects of corporate data management need to
be supported. In keeping with the DBMS approach, we want as many of these concerns as
possible to be addressed within the DBMS. We look at some of these concerns briefly now.
In practice, implementation of the logical schema in a given DBMS requires a very detailed
knowledge of the specific features and facilities that the DBMS has to offer. In an ideal world,
and in keeping with good software engineering practice, the first stage of implementation would
involve matching the design requirements with the best available implementing tools and then
using those tools for the implementation. In database terms, this might involve choosing vendor
products with DBMS and SQL variants most suited to the database we need to implement.
However, we don’t live in an ideal world and more often than not, hardware choice and decisions
regarding the DBMS will have been made well in advance of consideration of the database
design. Consequently, implementation can involve additional flexing of the design to overcome
any software or hardware limitations.
Realizing the Design
After the logical design has been created, we need our database to be created according to the
definitions we have produced. For an implementation with a relational DBMS, this will
probably involve the use of SQL to create tables and constraints that satisfy the logical schema
description and the choice of appropriate storage schema (if the DBMS permits that level of
control). One way to achieve this is to write the appropriate SQL DDL statements into a file
that can be executed by a DBMS so that there is an independent record, a text file, of the SQL
statements defining the database. Another method is to work interactively using a database tool
like SQL Server Management Studio or Microsoft Access. Whatever mechanism is used to
implement the logical schema, the result is that a database, with tables and constraints, is
defined but will contain no data for the user processes.