Data Analysis, Design, and Implementation: Dominican College Database

This project details the design and implementation of a relational database system for Dominican College. It includes a critical comparison of different data models and schemas, justifying the choice of a relational model. The project analyzes the benefits and limitations of various database technologies and explores different approaches to database design, including ER diagrams and normalization techniques (1NF, 2NF, 3NF). The database structure is built using appropriate database software, with tables designed and populated with sample data. The conceptual data model (CDM) is presented, explaining entities, associations, attributes, and identifiers. The project aims to provide a robust and normalized database solution to meet the specific requirements of Dominican College, ensuring data integrity and efficient data management. Desklib provides access to similar projects and solved assignments for students.

Data Analysis and Design
1

Task 1
A.C.1.1 Critically compare different data models and Schemas
Choose the right data model, or opt for the most relevant data management strategy.
Data has always been of great importance to businesses. However, the advent of the web has literally
exploded their volume - as the methods to treat them - so that the role of the database becomes
eminently strategic. Added to this is the emergence in recent years of new database architectures that
make their choice even more complex. Each database system has its own characteristics, which reflect
how designers have chosen to understand the different issues related to data administration. Each of
these decisions results from compromises whose primary objective is to focus on what matters most.
For example, should data consistency prevail over the availability of the database? Should we favour
vertical scalability of an instance or, on the contrary, give priority to horizontal scalability?
The easiest way to choose the right model is to determine the value or importance of the relationships
between your application's data. A reflection that calls for other questions:
- How many relationships are there in the data?
- What is the complexity of these relationships? In other words, how many types of relationships are
there? Do these relationships have properties or annotations?
- How often does the application query these relationships during a user request?
Depending on the answer to each of these questions, we will be able to judge how important relations
will be within a dataset. If your data has a multitude of relationship points, their value will probably be
higher. Nevertheless, these answers can be subjective. By looking at the complexity of the relationship -
and its pecuniary value for the business - it is possible to choose the data management model more
appropriately.
.
2

1.2 Critically discuss the benefits and limitations of different database technologies
A poor choice of the data model can also affect the development of software and applications, as it
would create unnecessary complications for developers. But the main difficulty is due to the complexity
of the code required for the data access layer.
In terms of software maintenance costs, the choice of the wrong model can add to the "technical debt"
contracted for everything to work properly and come to disturb the work of developers, who may be
forced to develop alternative solutions or program more code to make everything turn. Ultimately, this
can result in overruns of budget or time - not to mention the frustration generated or the need to
designate culprits.
For example, one might decide to use a relational database to integrate all the customer data into a
"360 client" application. Although nothing prohibits it technically, it would involve a long reflection on
data modelling. Not to mention that one would probably have to develop more code to execute any
recommendation request, however simple.
With a graph database, maintenance of business issues can be done separately from the application. We
can then compile a complete profile of each customer in which are listed his last purchases, the
products he has viewed, the recommendations he has written, the movements made on his shopping
cart and the items he has saved on his wish list. This data can then be used to identify trends and who
buys which products. A graph-oriented database will, therefore, be easier to maintain over time, but
also more likely to generate value for the company.
3

1.3 Analyze different approaches to database design
The use of more complex data models is to be avoided when it is not fully justified. If we save a set of
operation histories in a graph-oriented database, using a query language like Gremlin will be difficult and
overly complicated. The simpler databases offer more readability for their performance and often result
in lower costs. In addition, the mapping between the logical modelling of data and their physical
representation within the system is clearer. In more sophisticated models, such as relational or graph-
based databases, query execution is often performed by query optimizers and a multitude of index
structures. This causes a multiplication of data paths and configuration options while complicating the
execution of queries. If we do not have the use of the advanced relationship-building capabilities that
are so important to these data models, you'll be paying for these features for nothing, in terms of
performance or effort on the part of the professionals responsible for their deployment.
Find the best configuration for an entire application or service
It is even more important to choose the right type of database for your application or use case that any
mistake in this area can be extremely expensive. However, most applications will create data that can be
used in various ways by the company. This can lead to situations in which several data models must be
used depending on the use of the data. This would be particularly the case for an application that would
use data from connected devices and then analyze them to identify trends and estimate their value.
In the long run, the amount of data generated by connected devices tends to grow in size as they send
updates back to the central servers at regular intervals. Also, if your business has thousands of devices
that update their status every quarter of an hour, you'll need a solid time series database. To analyze
this dataset, however, depending on the method chosen for data mining, we may need a different
configuration in parallel.
Depending on the value that can be generated by exploiting the data, it may be necessary to use
different data models for the same service or application. This, therefore, assumes that the data is
moved or transferred as a stream to its final destination. However, each data model - and each data
management platform that accompanies it - will have to be supported, starting with the production
systems.
In the near future, this kind of "multimodel" databases will become more important. Gartner predicts
that “by 2017, all major operational database management systems will integrate multiple data models,
4

relational and NoSQL, within a single platform, “according to a research note by Nick Heudecker, Merv
Adrian and Etisham Zaidi. The objective, in this case, is to facilitate support for production systems,
while still choosing the most appropriate data model for the application.
Going forward, the value of each data model must be carefully considered from the start. Any wrong
choice in this area can add unnecessary costs and software development costs. However, as applications
increase their data consumption and incorporate production deployments whose architecture is based
on the value generated by the data, the need for the concurrent use of multiple data models will also
increase. Developers will have to learn to consider data processing in a variety of ways. Fortunately, they
will be able to rely on multimodel data management tools to do this.
5

TASK 2:
2.1 Design a relational database system to meet given requirements provided in the given case study.
Here, I have designed the database structure to develop the database for Dominican college. The
Dominican college is located in the UK in a different location so, it required to develop the database. To
develop the database there are five steps to follow them. The first step is to design the database by
designing the ER and normalization. These two designing process is done here:
Figure 1 ER design to develop the Dominican database
Normalisation:
The database should be normalized and it does not have any of the duplicate key or anomalies in the
Dominican college. To solve this problem, introduce a normalization method which is presented here:
1. First Normalisation: In normalization, the duplicate key and anomalies are removed
6

Figure 2 I normalisation
2. Second Normalisation
Figure 3 2 normal Form
3. Third Normalisation
7

Figure 4 3 Normalisation
8

When we directly build the tables of a database in a database management software (Oracle, SQL
Server, DB2, Access, MySQL, PostGre), we are exposed to two types of problem:
a. We do not always know in which table to place certain columns (for example, the delivery
address is put on the table of the customers or in the table of the orders?);
b. We have difficulty in predicting the intermediate junction tables, for example, the table of
interpretations which is indispensable between the tables of the films and the table of the
actors).
It is, therefore, necessary to resort to a preliminary design stage. The techniques presented here are
part of the Merise methodology (Method of Study and Computer Realization for Enterprise Systems)
developed in France in 1978 (Tardieu et al.), which allows, in particular, to design an information system
in a standardized and methodical way. The purpose of this course material is to introduce the entity-
associations schema section "Conceptual Data Model (CDM)", the relational schema (sections "Data
Logic Model (LDM)" and "Physical Data Model (PDM)" ) "), And explain the translation between the two
(sections" Translation of a CDM into an MLDR "and" Retro-design "). The construction of the entity-
association schema can be done by studying the functional dependencies ("Functional dependencies"
section) and taking into account a number of unavoidable conceptual extensions ("Complements"
section).
Unfortunately, the following are not discussed here: constraints, treatments, relational language and
project management. For all these important notions, as related to the design of information systems,
the reader is directed to (Akoka & Comyn-Wattiau), (Matheron) and (Nanci et al.). Object modelling is
also not part of the tools presented in this document.
9

2.2 Build a relational database system for the database design in A.C 2.1 using an appropriate
database software application.
To build database, tables and firm should be developed and forms should be developed to create the
user interface.
Relationship Diagram
Figure 5 Relationship Diagram
Tables design view:
Figure 6 administrator Table
10

Figure 7 Branch Name
Figure 8 Course Program design view
Figure 9 Lecturer table design view
Figure 10 Manager Table
11

Figure 11 Student Design view table
Figure 12 Unit design view
Datasheet view
The tables are designed to store the data in the Dominican college database:
Here, I have present the datasheet view of the tables with stored data
Figure 13 unit table
12