Data Analysis and Design: Data Models, Schemas, and Database Design

This report provides a comprehensive overview of data analysis and design, focusing on data models, database technologies, and relational database systems. It begins by comparing different data models and schemas, including relational and hierarchical models, and discusses the benefits and limitations of various database technologies like DBMS and RDBMS. The report analyzes different approaches to database design, such as top-down, bottom-up, centralized, and decentralized approaches. It then delves into designing and implementing relational database systems, covering logical and physical database design, ERD diagrams, and DFDs. The report also explores techniques to enhance the user interface and explains the benefits of using manipulation and query tools, such as SQL, in a relational database system. This document is available on Desklib, a platform offering a wide range of study tools and resources for students.

UNIT 33 DATA ANALYSIS AND DESIGN
Introduction:
Data analysis and design: this is the method of organizing the data. These data types and
architectures are to implemented in a database management system. In order to define the data
modelling methods are applied to the data.
Managing of a big amount of data and that is maybe structured or may be in unstructured is the
primary key of the information system. Data models are defining the structure data for storage
purpose in database management systems such as hierarchical model, or relational model. and
they can not be defined as unstructured data, for example, word processing, a digital signal such
as audio and video, and pictures also. the design of data model can be logical if it can be in form
of physical.
Data model:
The term data model refers, that how to manage the structure data it shows the method in which
data is to be organized. Data model show the structure of data in particular domain. And there is
every data mode is different. A data model expresses the classes of entities, that contain the
information about the company, attribute of such information, and relationship among them.
Data structure
The model that define the data in a particular or wey that is called data model. In this data
structure, various components are available classes, entity and attribute al these contain the
information about the organization (McKay, 2018).

LO1
Understand data models and database technologies
1.1 Critically compare different data models and schemas
The logical design of the database is represented by the data model. Here few model are given.
Relational model: this type of database doesn’t have any problems with multiple to one and one
to multiple. In this type of model, data is managed in tabular forms and that are related to one
another that is known as a relational model.
Figure 1: Relation model Source (Chegg, 2018)

Hierarchical model: it is the simplest way of arranging the database. In this type of model, each
node has it a parent or root node but it has single or various children that are known as child
nodes. Also, it can be said that data is to be arranged in the form of the tree structure (Varela,
2016).
1.2 Critically discusses the benefits and limitations of different database technologies:
DBMS:
Generally, a collection of records is known as database management, and it is to design for
managing the data and records.
Basically, there is four type of database system are defining here:
Object-oriented database management
Network database management
Hierarchical database management
Relational database management
In point1.1 I have discussed Hierarchical database management and Relational database
management. Here I will discuss the limitations and benefits of the different type of database
management system.
Figure 2: Hierarchical model Source (Cs, 2018).

Problem with traditional database management system:
The even traditional database model is suitable for simple or easy structures; in a simple
structure, it can easily detect the problem if each and every child has only single parents. But
when a large amount of data would have to manage then it is too difficult to manage the data
through traditional management. For an example, if the database that includes parents and their
children in the same organization then it is to be difficult to describe that parents those have
single child both are worked here or not. In database language it is called many to one or many-
to-many if there are various children then the traditional model cannot describe them effectively.
Solving this database problem by using the relational database:
RDBMS:
 A relational database doesn’t have this problem, because it permits to apply the one
record at any number of tables. A child record is managed in the table as a child of
relationship and the similar record is managed in the table of children that are attending
the company.” by preventing the duplication of the records hence same information could
be used in various ways, Without changing the record (Panwar, 2018)
Figure 3: RDBMS Sources (Panwar, 2018)

Data redundancy: if database is not perfect for any design that contains redundancies which are
not well for any designer. In traditional DBMS data redundancy is present but in relational
database system, there is no redundancy present.
Data independence: a database system has a big amount of data such as metadata is stored in
DBMS for retrieving the data easily. And also it would be required to sometimes it changes. But
whole data is dependent then it would be a tedious and difficult job.
Logical Data Independence: How can be data is managed inside the database, such type of information is
stored by logical data. It is the type of mechanism in which data liberalize as an actual data that is
present on disk. I want to make changes in the table then it cannot be changed.
Physical Data Independence: generally the entire scheme is logical, and all the data is stored in
the form of the bit. Physical data independence means it is the strength of changing the physical
data without affecting the logical data (tutorialspoint, 2018).
Normalization: in traditional DBMS normalization is not present but in relational DBMS
normalization is present.
Data integrity: it means that how much data is accurate consistency, when I making the
database it is essential to maintain the data integrity.
Transaction processing: it is used by the small organizations for dealing with little data and it
supports single users. And RDBMS can deal with a large amount of data.
Storage: Basically DBMS has stored the data in a navigational form or in hierarchical form. And
in RDBMS data is be stored in tables in which identifier is known as a primary key.
Concurrency control: it is the essential part of DBMS this is a mechanism for correcting the
data when more than one data transaction that accesses the similar data that is executed
concurrently according to time.

1.3 analyse different approaches to database design:
Top-down approaches: it starts from the basic idea that what is essential for the system, and
turn to specific details. This process includes the reorganization process of different entities.
Figure 4 Top-down Approach Sources (Owlgen, 2018)
Bottom-up approach: it is the exact opposite of top-down model at begins from the more
special detail and turn down to normal detail. And also in this model reorganization of attributes
are before the entities
Figure 5: bottom-up approach Sources (Owlgen, 2018)

Centralized approach: this approach is beneficial in small organisations, where few entities are
available and interaction between the attributes and entities is simple. Sometimes this structure is
followed in large organizations where easy database applications are available. Generally, it has
an only one database admin, or less design group, that design whole database for the company.
Figure 6 centralize approach Sources (Hardik, 2018)
Decentralized approach: this approach is used when an organization has large no. of entities
and relationship is difficult to exist among them. The carefully selected designer team is
deployed to achieve this job (Rochio, 2016)
Figure 7 decentralize approach Source (Coronel, 2018).

LO2: be able to design and implement relational database systems
2.1 design a relational database system to meet a given requirement
Logical database design: logical modelling contain the information about the business process
and its entities and about its operational units. The logical database is based on the business need
not on the database requirement. Even the need of business is related to need of a database, and it
is used to maintain the database.
Physical database design: it includes the actual need of database according to the requirement
of business. Physical modelling means conversing of business or logical model into relational
database model (Developer, 2018).
ERD Diagram
Figure 8:ERD diagram Source (Smartdraw, 2018).

DFD (data flow diagram)
Figure 9: DFD Source (Smartdraw, 2018).

Primary key:
The primary key can be an attribute or group of attributes that is specify any entity. The primary
key is the unique address of entity and it is essential to each entity. For example name and age of
students as shown in table.
Foreign key: it is also an n attribute that makes a relationship through identifying their root
entity and it also provide a technique of managing the integrity of database. Each and every
relationship supported by a foreign key. Here table is shown in respect to course id and student
id.
Source (Chaitanya, 2015).

2.2 build a relational database system based on a prepared design:

2.3 Apply a range of database tools and techniques to enhance the user interface:
Easy to use: relational database model is easy to use as compare to traditional database model
because it uses the table for storing the data as shown above.
The functionality of UI: here some function for user interface
Here some technique for enhancing the user interface which is following:
1. Consistent design: for easily usable of the database the information’s must be in the form of
consistent fashion if you want to achieve a level of familiarity between your users.
2. Clarity: if a user is not understood or he is not able to understand your database, then ensure
that there are no ambiguities over the way in term of visual hierarchy and content.
3. Maintainability: if UI has a ability of updating and installing itself then the user can change it
and update without occurring any conflict. In other words, it must be flexible (Argondesign,
2018).
Reliability: A reliable DBMS is the DBMS in which user request can be continue processed or it
can be said that the system of undelaying called reliable system.
Consistency: consistency means only valid data will be entered to the database when the
transaction is going on if the database consistency rules are violated then the whole database
rolled back and it will restore in its original states.
Performance: performance is important phenomena in regarding database, it just depends on
demand and supply, and user demand the information from the DBMS and DBMS send the
supply on their request the rate of sending demand of information on the user requesting is as
known as the performance of the database.
Button-based design
The attention that interface element clicked various times in a day by customer or users so it is
necessary to mind them properly to button-based design.

LO3: Be able to use manipulation and querying tools
3.1 explain the benefits of using manipulation and query tools in a relational database system
Features of SQL:
It offers the standardization function text which helps in, data cleaning, reducing volume, an also
in data merging.
And it can categorize the information, through ad hoc taxonomies, along with parting algorithm.
In various categories, data can be assigned.
And it has an ability to store the images, records books with a great length. And also have an
auxiliary system for file management and algorithm of data compression.
And also it has the ability to process the data from a remote area on your local system. For fast
retrieval, it optimizes through cache system.
DBA tools are also present here for supplication of users that is also known as fine-tuning
optimization algorithms for the query; it permits the joining of hush and row to column
switching.
And another feature is real-time transaction can be processed in this system and has an inbuilt
function of computations at different levels.
And also it has extra features of erasing the data of old record automatically and it keeps an only
historical record that is not less than 12 month (Vincent, 2013).
Benefits of query tools in a relational database system:
Query tools:
For retrieving the information basically, query mechanism is used from a database that contains a
little command that allows to the user for manipulating the data in a database management.
Various DBMS use the SQL, in a standard level of query design.

DML: this is also referred to as data manipulation language, it is the type of computer language
that has various commands for manipulating three data. It includes inserting and retrieving of
data in a data table or deleting the data from the table. It assembles through simple English
language and increases effective and efficient user interaction with the DBMS. The fundamental
command of DML is select, insert, delete, update, into, and various function describes blow.
 Select: for retrieving the row from the table select command is used. The syntax that is
used this command is mostly used command in database
management system (Coursehero, 2018).
3.2 implement a query language into the relational database

3.3 Critically evaluate how meaningful data has been extracted through the use of query
tools
Features of query tool: the query tools have many features that make our database productive
and meaningful.
Simple interface:
A lot of efforts have been invested in developing query tool to make it simple interface bu which
it becomes fast and simple. That can access all type of data in our database.
 For analysis of table data, it provides the simple and easy interface.
 You can be done it with minimum clicking and by minimum typing
 It provides a specific function that makes it easy and faster as compare to another tool
Complex environment:
 Query tool can work with the big amount of database and tables also it contains the
mainframes. Advanced query tool runs very fast even if there are the complex
environment with millions of table and thousands of rows.
 Also, it provides comprehensive facilities
Data types:
 Advanced query tool can handle the various data types and other difficult situation that
cannot be handled by normal ODBC based tools.
 And it displays the binary column either in ASCII or in hexadecimal mode.
 A large amount of column can be displayed and updated by this.
Updating data:
 Advanced query tools can update your data unusually and mass.
 And it provides a safe updating mode that blocks the accidentally updating and also
prevents the deleting of rows (Querytool, 2018).

LO4
Be able to test and document relational database systems
4.1 Critically review and test a relational database system
Testing methods for database system: There are various techniques for testing the database
which is given here.
1. Verifying of the equipment and databases
 Name of the database
 Various devices such as log devices, dumb devices and data devices.
 And ensuring that there is enough space for data allocation.
2. Checking of tables and column:
 Checking the names of a table in the database
 And the name of the column for every table
 Type of column
 Checking of null value
3. Indexes and keys:
 Checking the primary key for every table
 Identifying foreign key of every table
4. Testing through stored procedure methods:
 Checking of stored procedure name
 Name of the parameters and types of the parameters.
 Output: it should be necessary to check the records.
 Return value: check the return values of stored procedure
5. Trigger test: in this teat following test is performed by the tester.
 Ensure that name of the trigger is correct
 Validation of triggering updating
 The record should be updated with valid data.
 For covering the triggering error a record should be updated with invalid data.
 Rolling back transaction must be ensured at the time of failure.

Black box testing:
In this method, software is used in which implementation of internal architecture items are being
tested and it is not aware to the tester. Basically, it is suitable for the higher level plan of testing.
White box testing:
It also a software method but in this technique, internal implementation of design architecture is
known to the tester. Basically, it is used for a lower level plan of testing.
Acceptance testing:
Generally this testing is used to ensure the that software that is used for database management
has met specification and given requirement. The basic object of the test is to emulating the
database accordance to the business requirement (Tutorialspoint, 2018).
Figure 10: Acceptance testing Source (Tutorialspoint, 2018).

4.3 create documentation to support the implementation and testing of a relational
database system
Documentation: documentation is very important for a developer and in database management
system; it is just as a guide for a work. First I prepared a document for this project it helps me for
all the application on which I working. And it is helpful for the user and another developer which
are working on similar project. Basically, two type of documentation are prepared
1. User documentation: user documentation is provided to users or it can be said that it is the
guide for the user that how to use the given system. Generally, user documents are in the form of
a booklet that contains all about the company information.
The format for user document:
 Contents table
 Overview of system
 Short technical details
 And the brief detail of hardware and software
 Meaning of technical term.
 Index
Technical documentation:
These documents carry the information provided by developer or manufacturer to the public.
And it refers to different documents along with their related information that is used for a various
purpose such as
 product definition
 and product specification
 features of design
 Ability of design
 functions
 Liability and correct use of the product (Graubner, 2018).

4.4 explain how verification and validation has been addressed
The term verification and validation generally used in different type of two analyses
Verification: this term means a design which I made is correct or not, it contains all the
activities that provide high quality for the design such as analysis, testing, and inspection etc.
Validation: this term means building of right design, in other words, I can say that the design
that I am going to be made is meeting the costumers need or not. It is a subjective process; how
this design addressed to the real world need. These both terms can be easily understood by
following
Figure (Steve, 2010)
Figure 11 varification and validation Source (steve, 2010)

Format check:
It shows the format of your data for example for showing the date of birth date are showing in a
specific format such as (dd/mm/yyyy ) that mean date must be entered in this format.
Figure 12 format check
Range check: it ensured that is entered by the user is within range or not. For example, the date
must be entered between (1 July 2006 to 31 may 2012) and another one is the price of an item
can be between the £.1.00and £1000.
Digit check: it is the extra number that is put at the end of digit field, it compares the result by
putting the code in the calculation. If the result would not be matched to the check digit than that
number would have to be input again (Ict, 2018).
Figure 13 digit check

4.5 explain how control mechanisms have been used
There is various access control mechanism used for database management, and provide the
suggestions on when and how to use each. Here various access technologies are given in order
to complexity.
Flow control: in the frame of DBMS flow of information can be controlled by this and it
regulates and control the information among all available object.
Interface control:
It prevents the indirect discloser of information that can be accessed by user and determine the
data. This is the simplest mechanism used to restrict access to any service technique. When
attributes are used on any method, then this method demands the specific caller ID.
Access control: it is the most widely used method in database management for control and
regulate the data there are few rules that are defined by the security policies for direct accessing
of data (Fit, 2018).

Conclusion :
A database management is system software that manages and create the database. It gives a way
to users and costumer to create, manages and retrieves the data.
Here for this given scenario, I discussed database management and I design a rational model for
this scenario, a DBMS make it simple to understand and create the database and also update it.
Generally, three things are managed by DBMS, such as the data, and logidcal structure is defined
by database model.These basic elements used to enhance the data integrity, concurrency and
uniformity in the procedure.
The DBMS is useful centralise representation of data which may be accessed by various users
from different locations in a controlled way.

Referencing:
Varela, R. (2016). Database System and Models Assignment | Assignment Help. [online]
Available at: http://www.programmingassignmentshelp.net/database-system-models-assignment/
[Accessed 1 Jun. 2018].
Panwar, A. (2018). Types of Database Management Systems. [online] Available at:
https://www.c-sharpcorner.com/UploadFile/65fc13/types-of-database-management-systems/
[Accessed 1 Jun. 2018].
Owlgen, W. (2018). What is top -down approach to database design? When it is used? -
Owlgen.com. [online] Owlgen. Available at: https://www.owlgen.com/question/what-is-top-
down-approach-to-database-design-when-it-is-used [Accessed 4 Jun. 2018].
Owlgen W. (2018). What is bottom -up approach to database design?. [online] Available at:
https://www.owlgen.com/question/what-is-bottom-up-approach-to-database-design-when-it-is-
used [Accessed 4 Jun. 2018].
Hardik, p. (2018). Distributed Database Management System. [online] Available at:
https://www.slideshare.net/hardikpatil444/distributed-database-management-system [Accessed 4
Jun. 2018].
Coronel, R. (2018). Chapter 9 Database Design - ppt video online download. [online] Available
at: http://slideplayer.com/slide/5153218/ [Accessed 5 Jun. 2018].
Rochio, V. (2016). Database System and Models Assignment | Assignment Help. [online]
Available at: http://www.programmingassignmentshelp.net/database-system-models-assignment/
[Accessed 5 Jun. 2018].
Smartdraw, (2018). Data Flow Diagram - [online] Available at:
https://www.smartdraw.com/data-flow-diagram/ [Accessed 5 Jun. 2018].
Chaitanya, S. (2015). Foreign key in DBMS. [online] beginnersbook.com. Available at:
https://beginnersbook.com/2015/04/foreign-key-in-dbms/ [Accessed 5 Jun. 2018].
Argondesign, (2018). 5 aspects of a good user interface – Electronic design services with Argon
Design Ltd. . [online] Available at: http://www.argondesign.com/news/2014/feb/5/5-aspects-
good-user-interface/ [Accessed 5 Jun. 2018].
Developer, (2018). Logical Versus Physical Database Modeling - Developer.com. [online]
Available at: https://www.developer.com/tech/article.php/641521/Logical-Versus-Physical-
Database-Modeling.htm [Accessed 5 Jun. 2018].

Chegg, (2018). Database scheme. [online] Available at: http://www.chegg.com/homework-
help/questions-and-answers/derive-relational-database-schema-er-model-deriving-relation-
schemas-sure-combine-schemas--q9322461 [Accessed 5 Jun. 2018].
Chang. (2018). HIERARCHICAL DATABASE MODEL. [online] Available at:
http://people.cs.pitt.edu/~chang/156/14hier.html [Accessed 5 Jun. 2018].
tutorialspoint. (2018). DBMS Data Independence. [online] Available at:
https://www.tutorialspoint.com/dbms/dbms_data_independence.htm [Accessed 5 Jun. 2018].
Vincent, G. (2013). 11 Features any database, SQL or NoSQL, should have. [online] Available
at: https://www.datasciencecentral.com/profiles/blogs/11-features-any-database-sql-or-nosql-
should-have [Accessed 5 Jun. 2018].
Coursehero. (2018). A Explain the benefits of using manipulation and query tools in a relational.
[online] Available at: https://www.coursehero.com/file/p7hbm587/A-Explain-the-benefits-of-
using-manipulation-and-query-tools-in-a-relational/ [Accessed 5 Jun. 2018].
Querytool. (2018). Main features of AQT . [online] Available at:
http://www.querytool.com/help/366.htm [Accessed 5 Jun. 2018].
Graubner, J. (2018). What is "technical documentation"?. [online] Transcom.de. Available at:
https://www.transcom.de/transcom/en/technische-dokumentation.htm [Accessed 5 Jun. 2018].
Steve, E. (2010). The difference between Verification and Validation. [online] Available at:
https://www.easterbrook.ca/steve/2010/11/the-difference-between-verification-and-validation/
[Accessed 5 Jun. 2018].
Fit (2018). Access Control in Database Management Systems. [online] Available at:
http://www.fit.vutbr.cz/~cvrcek/confers98/datasem/datasem.html.cz [Accessed 5 Jun. 2018].
Tutorialspoint (2018). Acceptance Testing. [online] Available at:
https://www.tutorialspoint.com/software_testing_dictionary/acceptance_testing.htm [Accessed 5
Jun. 2018].
McKay, V. (2018). What is Data Modeling? | Analysis and Design | FAQ. [online] Selectbs.com.
Available at: http://www.selectbs.com/analysis-and-design/what-is-data-modeling [Accessed 5
Jun. 2018].