Analysis of Data Models and Relational Database System Design

This report provides a comprehensive analysis of different data models, including hierarchical, network, text-oriented, and relational databases, critically comparing their benefits and limitations in various applications. It delves into database technologies such as DBMS and RDBMS, discussing their evolution and usage in managing data. The report also explores different approaches to database design, emphasizing the bottom-up approach involving normalization to eliminate redundancies and ensure data integrity. Furthermore, it includes the design of a relational database system for Dominican College, detailing the entities, attributes, and relationships using an ER diagram and normalization techniques implemented with MS Access. The report also touches on the three levels of modelling for databases: conceptual, logic and physical. Desklib offers students access to this report and a wealth of study tools.

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