Dominican College Data Analysis and Design Project - SQL Database

This project report details a comprehensive data analysis and design assignment, covering various aspects of database management. It begins by comparing different data models and database schemas, discussing their respective benefits and limitations, with a focus on relational, hierarchical, and network models. The project then delves into the practical design of a relational database system, using SQL to meet specific requirements for a case study involving a course management system. It includes building the database, enhancing the user interface, and implementing query languages. The report further explores the benefits of using manipulation and query tools, evaluating data extraction effectiveness. The final sections focus on testing, reviewing, and documenting the implemented database system, including user documentation, data validity verification, and control mechanisms. The report provides a complete analysis of the project from design to implementation and testing.

Data Analysis and Design

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
TASK 1............................................................................................................................................1
A.C.1.1 Compare different data models and Scheme............................................................1
A.C.1.2 Discuss the benefits and limitations of different database technologies...................7
A.C. 1.3 Analyse different approaches to database design.....................................................8
TASK 2..........................................................................................................................................12
A.C.2.1 Design a relational database system to meet a given requirements provided in the
given case study....................................................................................................................12
A.C.2.2- Build a relational database system for the database design Domicile College System
using SQL database software...............................................................................................18
A.C.2.3 Apply database tools and techniques to enhance the user interface......................19
TASK 3..........................................................................................................................................22
A.C. 3.1 Benefits of using manipulation and query tools in a relational database system..22
A.C. 3.2 Implement a query language into the relational database system that has been
implement.............................................................................................................................24
A.C. 3.3 Evaluate how meaningful data has been extracted with the use of query tools......26
TASK 4..........................................................................................................................................26
A.C. 4.1 Testing and reviewing the implemented relational database system.....................26
A.C. 4.2 Supporting documentation for database testing and implementation....................27
A.C. 4.3 User documentation for course management database of Dominican college......28
A.C. 4.4 Data validity and verification.................................................................................29
A.C. 4.5 Control mechanisms in database system................................................................30
CONCLUSION..............................................................................................................................30
REFERENCES..............................................................................................................................31
INTRODUCTION...........................................................................................................................3
TASK 1............................................................................................................................................3
A.C.1.1 Compare different data models and Scheme............................................................3
A.C.1.2 Discuss the benefits and limitations of different database technologies...................7
A.C.1.3 Analyze different approaches to database design....................................................9
TASK 2..........................................................................................................................................12

A.C.2.1 Design a relational database system to meet a given requirements provided in the
given case study....................................................................................................................12
A.C.2.2- Build a relational database system for the database design Domicile College System
using SQL database software ..............................................................................................18
A.C.2.3 Apply database tools and techniques to enhance the user interface......................19
TASK 3..........................................................................................................................................22
A.C. 3.1 Benefits of using manipulation and query tools in a relational database system .22
A.C. 3.2 Implement a query language into the relational database system that has been
implement.............................................................................................................................24
A.C. 3.3 Evaluate how meaningful data has been extracted with the use of query tools...26
TASK 4..........................................................................................................................................26
A.C. 4.1 Testing and reviewing the implemented relational database system ....................26
A.C. 4.2 Supporting documentation for database testing and implementation....................27
A.C. 4.3 User documentation for course management database of Dominican college .....28
A.C. 4.4 Data validity and verification.................................................................................29
A.C. 4.5 Control mechanisms in database system ...............................................................30
CONCLUSION..............................................................................................................................30
REFERENCES .............................................................................................................................31

INTRODUCTION
Data analysis is a process of modelling data, inspecting, transforming and cleansing with
objective to determine the important information which helpful for decision marking. Data
mining is part of data analysis, it mainly focuses on discovering, and modelling data etc. it also
covers data analysis for business intelligence. Marks and Spencer is a leading organization use
different database technologies for managing their data such as customer details, product
information and company information.
This report will discuss about different data model and what are the benefits of different
database technologies. It also analyses various strategies and approaches for database design.
This assignment will discuss about relational database to identify the requirement of techniques
and tools for designing to meet the need of case study. Further, this report will discuss about the
query language tools which is helpful in relational database. Afterwards, it also implements the
test in rational database system to create documentations for maintaining the database in proper
manner (Lewis, 2015). This report describes validation and verification techniques in relational
database system.
TASK 1
A.C.1.1 Compare different data models and Scheme
Data model Database schema
Stage Data model is basically used
for conceptual system
modeling.
Database schema is a process
which is useful for system
implementation.
Purpose Data model is design database
management system.
Data schema is applicable in
database system to
implementation.
Level of details Data model is using low and
medium key attributes.
Data schema is applicable in
high data and it also defines
the specific relations between
objects.
Tool Data model uses CASE and It uses database development
1

diagram tools which helps for
design purpose.
and management tools.
Form Data model is using graphical
UML diagram to represent the
entities and attributes.
Database form in structure
form like tables, columns and
rows.
Comparison between data models:
Hierarchical Model Network data model Relational data
model
Data structure  Hierarchical
data model
follow one to
one and many
to many
relationships.
 This model is
based on child
relationship.
 Network data
model follow
many to many
relationships.
 This model is
record many
parents and
many children.
 Relational data
model follows
one to one and
many to many
relationships.
 This model is
based on data
structure.
Data manipulation  This model has
no independent
stand-alone
query.
 Retrieve
algorithm is
very complex.
 Retrieve
algorithm is
very complex.
 Retrieve
algorithm is
very complex.
Data integrity  Hierarchical
model doesn't
insert any data
related the
child and
 This model is
not suffers for
inserting
 It allows
inserting the
values on table.
 It can be free to
update
2

doesn't have
parents
values.
 It is freely
updated data
and
informationsinf
ormation’s,
delete and free
to edits.
anomalies due
to
normalisation
process.
Data model is an abstract model that is helpful for designing a logical database. It shows that
how the entities and data are connected with each other. They are processed for storing data in
the form of database management system. Data model can be divided into three ways: -
 Network Data Model
 Hierarchical Data model
 Relational data model
There are important data model which is helpful for storing the information and data in
proper manner. Marks and Spencer is a leading organization and they are also using database
management system to secure their customer information and company details in proper way.
Hierarchical Data model- Hierarchical database model is organized in the form of tree
structure. This data model has one parents and many children (Kratochwill, 2015). Nodes are
connected to each other in the form of tree.connected to each other in the form of tree.
3

Illustration 1: Hierarchical data model
(Source: Hierarchical data model, 2013)
Network Data Model- A network database model allows the multiple record to link with the
same file. This data model is designed as flexible way to represent objects and their specific
relationship. In this model, all the entities are managed in the form of graph. This is complex
structure and nor flexible to identify the model. Network model has many parents and many
children.
4
Illustration 2: Network data model structure
(Source: (Network model, 2013)

Relational Data model- The relational data model is type of database management to
arrange data in the form of table and structure. This model is providing the declarative method
for identifying the specific queries and data. Relational data model is most popular database
management system that has relationship with another database (Baek, Meroni and Manzini,
2015). Marks and Spencer is using this model to manage the complex data in proper ways.
Relational model is developed for reducing the complexity in the database management system.
The database management has many to many relationships with the entities.
Database Schema- The database schema is database management system that is
described in formal language. Database schema is structure that shows the logical view of whole
database therefore, how data can organize and create relationship between two tables. Database
defines the relationship between entities.
5
Illustration 3: Relational database model
(Source: (Relational data model, 2013)

Physical schema- This schema is main part of database management system because it
stores the actual data in different forms such as files and indices etc. It can be represented that
how the data stored in the secondary database (Taylor, 2017).
Logical schema- This schema is defining all the logical constraints which is applied in
the data storage. According to this schema, the data store in form of views, tables, and integrity
constraints.
Data dictionary language- Data dictionary language store information in the form of
structure such as type of data and size. It also defines relationship between databases. The
6
Illustration 4: Database Schema
(Source (:: Data base system, 2017)

company's authorized person can easily change the database environment with the help of DDL
(data description language).
Data manipulation language- Data manipulation language is useful for performing
different operations such as creations, insertion, organising, retrieve and deletion in database.
Mark and Spencer is using this language to delete some data in the database management system.
Data independence- it is major advantages of relational database because it can be split
the data in the form of table like horizontal and vertical form.
A.C.1.2 Discuss the benefits and limitations of different database technologies
Database technologies are important part for Marks and Spencer that is managed the data
effectively and efficiently. This organization is using different tools and platform for arranging
the information. Firstly, Database management system is an effective process that is
manufactured by Microsoft. This database concept is useful for both business organizations and
individuals (Xia and Wishart, 2016). For managing huge amount of database which is useful for
Microsoft SQL server. It is unique platform for creating report, charts, tables and matrices etc.
For creating database, which is not required for complex programs in database management
system: thus, any person has knowledge about SQL query language because it is powerful and
effective database management. There are different technologies such as MS Access, Oracle and
SQL query language etc.
Benefits of MS Access technology-
 It is easy to install and applied.
 MS Access technology helps for completely integrate with the applications such as .net,
php and Java etc.
 MS Access store 2 GB data. It provides multi user support
Limitation of MS Access technology-
 It is harder for converted into the SQL server database. Foreign keys and their
relationships removed will corporates.
 MS Access cannot be updates and deletes operations in database.
 MS Access use temporary file and this file is restricted to maximum 2 GB for all
database.
7

 It cannot optimise in proper ways.
Benefits of Oracle-
 Oracle is helpful for supporting the large database in proper ways. Oracle is secure
process because it performs different tasks at time of transaction and other user never see
to transaction details and information. It will provide security in database management
system.
 This technology is using triggers and cursors for creating database system
 Limitation of Oracle- Oracle is helpful only for large application and which is applicable
only for large organization and business. It is very expensive.
Benefits of SQL-
 SQL technology is helpful for managing the relational database system.
 It is easy for people to lean and understand
 It is portable language and run on any platform such as laptops, mobiles phones and
 Mainframe.
 It provides the security for database SQL language is supported the object oriented programming language and also integrated
with high level programming language like Java and Java database connectivity.
Limitation of SQL-
 There is not SQL profiler.
 SQL server utilised maximum memory
 The maximum size of relational database is 10 GB.
A.C.1.3 Analyse different approaches to database design
Database design approaches are required for creating an effective database system. Marks
and Spencer uses design approach for managing, controlling the database system. It is useful for
sharing the common goals and objective. There are different types of approaches discuss in given
below:
 Top down approach
 Bottom Up approach
 Centralised approach
8

 Decentralized approach
Top down approach: Top down approach moves from top to bottom and analyse the
entire system. The system is needed for identifying the generic need and requirement. Further, it
moves from downwards towards the requirement of users. This is the best approach to firstly
analyse the details understanding of system and their operations and functionality. It also checks
the database and their values.
Bottom Up approach- This approach is opposite to top down approach because it starts
from bottom to up. In marks and Spencer, the system Analyst has responsibilities to check screen
and report. It moves downwards to check all the data that are stored in database. First of all,
bottom up approach create interface between database management systems effectively and
efficiently. (Sandryhaila and Moura, 2014).
Centralised Approach- This approach and model is useful when there are less number of
variable in Marks and Spencer. It is constructing the simple and secure database. In marks and
Spencer, database administrator is determining the system need and design database schema,
data constraints etc. it also verifies the developed database design for achieving the goals and
objective of marks and Spencer.
Decentralized Approach- This approach is useful for complex database structure. This
database is helpful for large organization because it identifies the need and requirement of
customer by operational sites. According to marks and Spencer, they have database engineer to
design the views, constraints, validations and requirement etc. (Kalinin, Sumpter and Archibald,
2015). This design is useful for marks and Spencer to achieve their goals and objectives.
Entity relationship diagram- Entity relationship diagram is show the relationship between entities
set in database management system.
Entity-Relationship Diagram-
Entity- An entity is object that store some information and it is represented in the form of
rectangle shape.
9