Data Analysis and Design: A Comprehensive Guide to Relational Database Systems

This comprehensive guide explores the fundamentals of data analysis and design, focusing on relational database systems. It delves into various data models, their benefits and limitations, and provides a step-by-step approach to designing, implementing, and testing a relational database system. The guide also covers essential database tools and techniques, query language implementation, and user documentation creation. Through practical examples and case studies, it equips readers with the knowledge and skills to effectively manage and analyze data using relational database systems.

Data Analysis and Design
1 | P a g e

2 | P a g e

Task 1.1 critically compare different data models
In the case of a large database, multiple users must be able to manipulate the information it
contains quickly and at any time. In addition, large companies tend to accumulate many
independent files with linked files or even overlapping data. As part of a data analysis, it is necessary
that data from multiple files can be linked. This is why different types of databases have been
developed to meet these requirements: text-oriented, hierarchical, network, relational, object-
oriented.
Hierarchical database
Hierarchical databases are among the oldest databases. Within this category, records are organized
in a tree structure. Each level of records stems from a set of smaller categories.
Network database
Network databases are also among the oldest. Rather than providing unique links between different
sets of data at various levels, network databases create multiple links between sets by placing links,
or pointers, on one set of records or another. The speed and versatility of network databases have
led to a massive adoption of this type of database within companies or in the field of e-commerce.
Text-oriented database
A text-oriented database, or flat file database, is in the form of a file (table) in .txt or .ini format. A
flat file is a text file or a file that combines text with a binary file. In general, in these databases, each
line has only one record. Most PC databases are text-oriented databases.
SQL database (relational)
Relational databases were invented in 1970 by IBM's EF Codd. These are tabular documents in which
data is defined in order to be accessible and to be reorganized in different ways. Relational
databases consist of a set of tables. Within these tables, the data is categorized. Each table has at
3 | P a g e

least one column corresponding to a category. Each column contains a number of data
corresponding to this category. The standard API for relational databases is the Structured Query
Language (SQL). Relational databases are easily extensible, and new categories of data can be added
after the creation of the original database without having to modify all existing applications.
4 | P a g e

Task 1.2 critically discuss the benefits and limitations of different database technologies
Computer databases are used in a large number of companies to store, organize and analyze data. A
database is a collection of information organized in order to be easily searchable, manageable and
up-to-date. In a database, data is organized in rows, columns, and tables. They are indexed so that
you can easily find the information you are looking for using computer software. Whenever new
information is added, the data is updated, and eventually deleted. They are responsible for creating,
updating or deleting data. They also search the data they contain at the user's request, and launch
applications from the data.
Databases are used by many companies in all industries. They are used by airlines to manage
reservations. They are used for production management. For medical records in hospitals, or for
legal registrations in insurance companies. The largest databases are generally used by government
agencies, large corporations or universities. To access the databases, a DBMS (database
management system) is used, namely a software of the DBMS or RDBMS type. A DBMS is a database
management system. It allows defining, manipulating, retrieving and managing the data stored
within the database. The DBMS retrieves information from the database at the request of the user,
based on the requests made. To make a request, the user can enter a keyword, or perform a sort
command. The power of a DBMS is its ability to define new relationships from basic relationships
given by arrays to respond to queries. Typically, the user enters a series of characters, and the
computer searches for the corresponding sequences to provide the user with the source materials in
which these characters appear. For example, a user can search for all records containing a field
linked to a person with the Dupont family name. The term Database is increasingly used as an
abbreviation for Database Management System. There are many different DBMSs. Some are small
systems that can be launched on a personal computer; others are huge systems requiring a
mainframe. DBMS was invented in the 1960s to support hierarchical databases. The first systems
were organized sequentially (alphabetically, numerically, or chronologically). It was not until the
advent of direct access storage devices to access data randomly through indexes. Some of the best
5 | P a g e

known DBMSs include the IBM Information Management System and the CA Integrated Database
Management System. An RDBMS is a relational database management system. This type of software
was developed in the 70s based on the relational model. Still today, it remains the most popular way
to manage a database. The most known RDBMS are Microsoft SQL Server, Oracle Database, IBM DB2
and MySQL.
6 | P a g e

Task 1.3 analyze different approaches to database design
There are few approaches towards the database development. First is the bottom-up approach.
Relationships are mathematical beings. They are subject to certain structural constraints and their
purpose is to be manipulated, combined, using relational algebra or relational calculus (which is an
application of calculating predicates). Having a preference for calculating predicates, Ted Codd
reasoned logician. We will see during the study of the first normal form, that in 1969, he placed
himself within the framework of the logic of the second order, judging the following year that the
logic of the first order was sufficient to manipulate relationships.
a) The adequacy of relational calculus (and consequently of relational algebra) to first-order logic
resulted in a strong constraint, leading to the normalization of relations in what is commonly called
the first form. Normal (1NF), according to which a relationship cannot be a value for an attribute of
another relationship: for example, invoice lines of an invoice cannot be values of an Invoice Line
attribute of a relationship Invoice.
Certainly, with systems like IMS / DL1, by construction (hierarchical model requires), invoice lines are
nested in invoices, commitments on invoice lines are nested in invoice lines, etc. But IMS / DL1 does
not allow to manipulate sets using an algebra or a calculation, we are at a lower level where we treat
only one record at a time and, under these conditions, there is obviously no constraint on how to
structure the data.
b) About other normal forms
The so-called second normal form, third normal form and normal form of Boyce-Codd are the
elements of a theory, first developed by Codd in 1970, then completed by Raymond Boyce (too early
disappeared in 1974). Seven or eight years after Codd started it, mathematicians like Jorma Rissanen
and Ronald Fagin took over to complete the theory of normalization, which was done in 1979 with
the provision of the fourth and fifth normal forms ( and sixth, twenty years later).
7 | P a g e

Respecting these normal forms has the effect (among other things) of ridding the relations of
redundancies that are not only unnecessary and causes obesity, but above all, they generate errors
with regard to the rules of management. enterprise data during update operations (say INSERT,
UPDATE, DELETE). These redundancies are most often the consequence of clumsy or even non-
existent upstream conceptual modelling or the bad fruit of an untimely "denormalization
c) Observations concerning conceptual modelling.
When graphically representing data: Conceptual Data Models (CDM) of the Merise method, and
more generally entity/relationship diagrams (or even class diagrams), there is a lot of verification
work for each type of association. (Still referred to as a type-association or type-relationship)
between entity types, consisting of ensuring that each of the properties cannot be verified on a
subset of the collection of the standard relationship. The relation-type in question has nothing to do
with the relation of the Relational Model, it is about the association (relationship) existing between
entities-types. This verification work - also called the normalization leads to the expulsion of a
property from a standard association to a standard entity (or vice versa). This has to do with what
Codd calls standardization in the second normal form (2NF), which has a much broader scope
because it concerns all the relvars composing a relational database. The 2NF is also much more
formal about its statement.
Standardization plays a crucial role in the quality of the architecture of the database, which must be
structurally valid and able to evolve, first by using a synthetic, top- down (so the help of the Merise
method, for example, which is also valid for class diagrams, and secondly, a rigorous verification,
involving an analytical, bottom-up approach, for which we rely on the theory of standardization: The
architecture of the database thus comes from a mixed approach where the art of yoyo is practiced,
intelligently alternating the two approaches.
d) Consideration of temporal data:
8 | P a g e

Bottom-up approach as developed by Date & Codd, have enriched the relational theory, deepening
the field of temporal databases with extreme precision, and providing us with the techniques to
launch us on the basis of theoretical work (case of TSQL2 which was proposed in time to be
integrated to the SQL / 2 standard). Thus it is proposed that the bottom-up approach which involves
normalization is the best approach.
9 | P a g e

Task 2.1 Design a relational database system to meet a given requirements provided in the given
case study.
The database of Dominican college is designed to complete the first step of designing. The second
step is to develop the database with selecting a tool. To develop this database, I have selected the
MS access tool which is easily available in the market with low cost. To design the database, the ER
tool is used to define the entities and attributes which is found or design according to the given
scenario of the Dominican College.
Figure 1 ER diagram
After designing the ER diagram, I have started to normalise the data with follow all the steps of the
normalisation:
10 | P a g e

1 Normalisation:
Figure 2 1NF
11 | P a g e

12 | P a g e

Data Model
A model is often represented using a graphical formalism to describe the data (or more precisely the
data types) and the relationships between the data. There are three levels of modelling for the
databases:
The conceptual model:
It makes it possible to describe the real according to an ontological approach, without taking into
account the technical constraints.
The logic model:
It makes it possible to describe a solution, by taking a general computer orientation (a type of DBMS
typically), but independently of the precise choice of implementation.
The physical model:
It corresponds to the technical choices, in terms of DBMS chosen and its implementation
(programming, optimization, etc.).
13 | P a g e

Example: Example of Conceptual Modeling Formalism
The Entity-Association model has been the most widely used in database design. The UML model,
which is becoming widespread for computer design, is based on an object-oriented approach.
Logical Modeling Formalism
The relational model is the dominant model. The relational-object model (an adaptation of the
relational and object models to the DBMS framework) is currently in full growth. The "pure" object
model remains mostly at the experimental and research stage. Older models (hierarchical, network,
etc.) are hardly used today.
Data Schema
A schema is used to describe the structure of a database, describing all the data types of the
database. The occurrence of a database consists of all the data corresponding to the types of the
schema of the database.
There are three levels of schema abstraction:
The conceptual level:
It allows to describe real-world entities and associations. This is the overall schema of the database,
it offers a canonical view.
The conceptual level corresponds to the conceptual model.
The external level:
It makes it possible to describe the entities and associations of the real world, but views of a
particular user or group of users (we also speak of "view" for an external schema). It is a restriction
of the conceptual scheme oriented towards a specific use. There are usually several external
schemas for the same conceptual schema. The external level corresponds to a subset of the
conceptual model restricted to the points of view of certain users.
The internal level:
It corresponds to the physical implementation of the entities and associations in the files of the
database.
14 | P a g e

Task 2.2 Build a relational database system for the database design in A.C 2.1 using an appropriate
database software application.
In this section I have start the second process of development. To develop the database, the
selected tool is MS Access:
To store the data or information in the database, I have developed tables. With the help of tables,
the data is stored in the database. Here I have shown the tables design and datasheet view:
Figure 3 Academic manager database
Table 2: Admin
15 | P a g e

Table 3:
Table 4: Course
16 | P a g e

Table 5: Lecturer
17 | P a g e

The Application at hand is for Dominican College. A briefcase description was given to this
organization. It may be worthwhile to further highlight that there are various entities in this project.
For one we have the stock controller, who would replenish stocks of products sold. The Stock
Controller checks the stock levels in the product file and places supplier orders for the products that
require restocking. Dominican College uses a number of suppliers, but each product is supplied by
one supplier. Orders are made up for each supplier showing the products and quantity required.
Details of supplier orders are sent to the Malcolm Mooney the Accounts Manager. The supplier
sends the ordered products with a delivery note which the Stock Controller checks and then updates
the product file with the quantities delivered. The suppliers send invoices for the delivered products
and the Stock Controller authorizes payment. Management would like Dominican College to have an
online presence. As a result, she requires a new system that will support the business processes
described above but also provides facilities for customers to place orders online via a new website
that reflects the principles of the company. Sandra Sterling also believes that this will provide
enhanced marketing opportunities and that Dominican College should consider the use of social
media to develop customer relations.
Form the above discussion it is now clear that there can be two approaches which can be used to
develop this system. (Bentley,1997).
18 | P a g e

Task 2.3 Apply a range of database tools and techniques to enhance the user interface to meet the
given case study requirements.
In this section, I have shown the user interface of the database, how they create the database, how
they use the database etc.
This image shows the creation of database by the users with selecting valid option
This given image presents the structure of the database where user select the table to show the
data and select query to search the specific data from the Dominican college.
19 | P a g e

Academic Generate form
Admin form
20 | P a g e

Campus Generated Form
Course Generated Form
Lecturer Generated Form
21 | P a g e

Student Generated Form
22 | P a g e

Unit Generated Form
23 | P a g e

Task 3.1 Explain the benefits of using manipulation and query tools in a relational database systems
per the requirements are given in the case study.
In the current system, the best suited is the Rapid Application methodology. We have discussed the
case and we understand using manipulation and query tools in this methodology. There are different
views of each user. So we require an iterative development in this we integrate the use case of each
user. The iteration is a repetition of a sequence of instructions or of a part of a program, for a fixed
number of times in advance, or for as long as a defined condition is not fulfilled, in order to resume
processing on different data.
1-
2-
24 | P a g e

3-
25 | P a g e

4-
5-
6-
26 | P a g e

Benefits of Manipulation Language
1. Using DML Queries, data stored in the database can be retrieved easily.
2. Using DML Queries, specific information can be extracted easily.
3. Using DML Queries, report can be extracted and can be filter either, weekly, monthly or yearly.
Benefits of SQL
1. It helps to retrieve large amount of information easily.
2. It can features such as grouping and aggregate functions that helps to extract specific information.
3. SQL Queries can be modified as per the user requirements.
27 | P a g e

Task 3.2 Implement a query language into the relational database system that has been
implemented in task 2.2
1-
2-
28 | P a g e

3-
4-
29 | P a g e

5-
6-
30 | P a g e

The main purpose of a computer system is to satisfy the customer's needs. The development process
will, therefore, be accessed by the user.
Use cases illustrate these needs.
They detect and then describe the functional requirements (from the user's point of view), and
together they constitute the use case model that dictates the full functionality of the system.
The goal of a unified process is to control the complexity of IT projects by reducing risk.
Query tools are a set of generic principles adapted to the specificities of the projects. Tools address
the following concerns:
WHO is involved in the project?
WHAT, what happened during the project?
How should it be done?
WHEN is performed each deliverable?
Archaeology: the Architecture of SQL
As per the archaeology magazine and the recommendations thereof, the tool has to manage the
development process by two axes. It may be highlighted here that Here we are using the following
processes to understand:
a. The vertical axis represents the main sequences of activities, which includes activities
according to their nature. This dimension reflects the static aspect of the process that is expressed in
terms of components, processes, activities, sequences, artefacts and workers.
b. The horizontal axis represents time and shows the progress of the process of the life cycle;
this dimension accounts for the dynamic aspect of the process which is expressed in terms of cycles,
phases, iterations and milestones.
31 | P a g e

Task 3.3 Critically evaluate how meaningful data has been extracted through the use of query tools
for the given case study.
1-
32 | P a g e

2-
33 | P a g e

3-
34 | P a g e

4-
35 | P a g e

5-
6-
36 | P a g e

Tool repeats a series of cycles that revolve around 4 phases
a. Analyses of needs
37 | P a g e

b. development
c. construction
d. transition
To effectively conduct such a cycle, developers need all representations of the software product.
Use case model is the analysis model: detailing the use cases and conducting a first distribution of
the behaviour
Use case model is followed by a design model for finishing the static structure of the system in the
form of subsystems, classes and interfaces.
 An implementation model: integrating components
 A deployment model: defining the physical nodes of computers
 A test model: describing the test cases checking the use cases
 A representation of the architecture
The expression of needs as the name indicates allows to define the different needs:
 identify the main needs and provide a list of their duties
 identify functional requirements (from the perspective of the user) that lead to the
development of use cases models
 apprehend the non-functional requirements (technical) and deliver a list of demands.
The use case model presents the system from the point of view of the user and represents in the
form of use cases and actor, the needs of the client. The objective of the analysis is to gain an
understanding of the needs and requirements of the client. This involves delivering specifications to
enable the design of the solution to be chosen.
An analysis model provides a complete specification of the needs arising from use cases and
structures them in a form that facilitates understanding (scenarios), preparation (definition of the
architecture), modification and maintenance of the future system. It is written in the language of
developers and can be considered as a first draft of the design model.
Design and Implementation of the chosen model
The design provides an in-depth understanding of constraints related to the programming language,
component usage, and operating system. It determines the main interfaces and transcribes them
using a common notation. It is a starting point for implementation:
 It decomposes the implementation work into a subsystem
 It creates a transparent abstraction of the implementation
Task 4.1 Critically review and test the implemented relational database system
1-
38 | P a g e

2-
3-
39 | P a g e

4-
40 | P a g e

5-
6-
41 | P a g e

Implementation is the result of design to implement the system in the form of components, that is,
source code, scripts, binaries, executables, and other elements of the same type. The main
objectives of the implementation are to plan the integrations of the components for each iteration
and to produce the classes and the subsystems in the form of source codes.
The tests allow verifying the results of the implementation by testing the implementation. To carry
out these tests, it is necessary to plan them for each iteration, to implement them by creating test
cases, to carry out these tests and to take into account the results of each one. (Kerzner, 1997).
42 | P a g e

Task 4.2 Create documentation to support the implementation and testing of the relational database
system that has been implemented in Task 2.2
Table –
Relationship Diagram
Tables
Figure 4 Academic manager database
Table 2: Admin
43 | P a g e

Table 3:
Table 4: Course
44 | P a g e

Table 5: Lecturer
45 | P a g e

46 | P a g e

Academic Generate form
Admin form
47 | P a g e

Campus Generated Form
Course Generated Form
48 | P a g e

Lecturer Generated Form
Student Generated Form
49 | P a g e

Unit Generated Form
50 | P a g e

Task 4.3 Create user documentation for the developed relational database system in task 2.2
This is one of the best-suited approaches for designing the Dominican College. One of the important
aspects of this framework is the Needs analysis which gives a view of the project as a finished
product. If this phase is done correctly, there are good chances of this effort to succeed. If the
outcome of the project is to develop the system, that the chances are that this system will meet the
client requirements completely.
This phase focuses on the main needs (from the point of view of the user), the general architecture
of the system, major risks, delays and costs. The project is in place. The Analyst while conducting the
needs analysis, tends to answers the following questions:
o What will the system do? In relation to the main users, what services will it render?
o What will be the general (target) architecture of this system
o What will be the deadlines, the costs, the resources, the means to deploy?
(Batrol and Matin, 1992). (Megginson, 1991).
Academic Generate form
Admin form
51 | P a g e

Campus Generated Form
Course Generated Form
52 | P a g e

Lecturer Generated Form
Student Generated Form
53 | P a g e

Unit Generated Form
54 | P a g e

1-
2-
55 | P a g e

3-
4-
56 | P a g e

5-
6-
57 | P a g e

Report
58 | P a g e

59 | P a g e

60 | P a g e

Task 4.4 Explain how verification and validation has been addressed in the developed relational
database system.
The development takes over the elements of the needs analysis phase and specifies them in order to
arrive at a detailed specification of the solution to be implemented. The development makes it
possible to specify most cases of use, to design the architecture of the system and especially to
determine the reference architecture. At the end of this phase, project managers must be able to
predict the activities and estimate the resources needed to complete the project.
The tasks to be carried out in the elaboration phase are as follows:
o Create a reference architecture
o Identify the risks, those that are likely to upset the plan, cost and schedule
o Defining the levels of quality to be achieved
o Formulating use cases to cover functional requirements and planning the
construction phase
o Develop an offer addressing issues of timing, personnel and budget
Construction is the time when the product is built. The reference architecture is transformed into a
complete product. The product contains all the use cases that the project managers, in agreement
with the users have decided to develop for this version. The product is in beta version. A group of
users tries the product and detects the anomalies and defects. This phase involves activities such as
training for customer users, implementation of a support service and correction of anomalies.
61 | P a g e

62 | P a g e

Task 4.5 Explain how control mechanisms have been used in the developed relational database
system.
In the Domician College Database, Access control have been implemented that will provide the
access control to the implemented relational database. Different Tables have been created so that
each member has his own table to store the relevant data.
Control Mechanism is implemented that allows the followings actions:
Provide Academic Manager to assign various courses to all students.
 By allowing any student to list the available courses and their subsequent fees.
 By allowing the admin to assign course to the students
 Control Mechanism system helps the manager to course and the student that are enrolled
in.
While Implementing the Control Mechanism in the database system different levels are developed
to provide special access to the members of the College. Validation are provided in the database so
that incorrect values can be avoided. Moreover it also helps to provide authentication feature to the
members such that error message is shown to the user whenever he/she enter a wrong or incorrect
password.
In database various control mechanism have been used. All these mechanism provide role-based
control system.
63 | P a g e

References
Avison D and Fitzgerald G – Information Systems Development: Methodologies, Techniques and
Tools (McGraw Hill Higher Publishing Company, 2006) ISBN 0077114175
Batrol, K.M. and Matin, D.C. (1992). Management, McGraw-Hill.
Bentley, C. (1997). Prince: A Practical Handbook (Computer weekly Professional Series) 2nd Edition –
Paperback.
Chao L – Database Development and Management (CRC Press, 2006) ISBN 0849392381
Connolly T and Be.g.g C – Database Systems: A Practical Approach to Design, Implementation and
Management (Addison Wesley, 2004) ISBN 0321210255
Doucette, M. (1997). Microsoft Project for Dummies. Book & Disk Edition -Paperback - 384 pages -
Published
Grude, K. V.,Tor Hague, T. and Anderson E.S. (1997). Goal Directed Project Management 2nd Edition
– Paperback
Kerzner, H.(1997). Project Management: A Systems Approach to Planning, Scheduling and
Controlling.
Kliem, R.L., Ludin, I. S. and Robertson K. L. (1997). Project Management Methodology: A Practical
Guide for the Next Millennium
Megginson L.C. (1991). Management, Concepts and Applications, Harper Collins
Meredith, J.R. and Mantel S.J.Jr. (1995). Project Management, J. Wiley & Sons
Publishers Inc.
64 | P a g e