Database Manipulation and Querying

The assignment content provides eight queries that need to be evaluated, implemented, and tested using MySQL. The queries include various database operations such as selecting data, updating data, and grouping data. Additionally, the assignment requires the student to explain different database testing techniques and apply one of these techniques to their own database implementation.

School of Technology
Unit: Data Analysis and Design
Unit Level: HNC
Unit Code: DAD
Student Name: Date of Issue: 06 June 2016
Student ID: Date of Submission: 18 August 2016
Coursework Regulations
1 All coursework must be submitted via http://technology.stpmoodle.net/
2 Please check the submission date and time and ensure that coursework is submitted on time.
3 Requests for an extension must be in accordance with the guidelines set out in the college regulations,
with the necessary documentary evidence to support your request. Refer to The Student Handbook.
4 General guidelines for submission of coursework:
a) All work must be word-processed and must be of a “good” standard.
b) Document margins should not be more than 2.5cm or less than 1.5cm.
c) Font size in the range of 11 to 14 points distributed to including headings and body text. Preferred
typeface to be of a common standard such as Arial, Calibri or Times New Roman for the main text.
c) All work completed including any software constructedmay not be used for any purpose other than the
purpose of intended study without prior written permission from St Patrick’s College.
Remember to keep evidence of your submitted coursework.
Plagiarism is presenting somebody else’s work as your own. It includes: copying information directly from
the Web or books without referencing the material; submitting joint coursework as an individual effort;
copying another student’s coursework; stealing coursework from another student and submitting it as your
own work. Suspected plagiarism will be investigated and if found to have occurred will be dealt with
according to the procedures set down by the College. Please see your Student Handbook for further details
of what is / isn’t plagiarism.
Assignment
Brief

School of Technology
Assignment front sheet
Qualification Unit number and title
Pearson BTEC HND Diploma in Network
Engineering and Telecommunications Systems Custom Unit: Data Analysis and Design
Student name Assessor name: Tanveer Ahmad, Robert Chambers
Date issued Completion date Submitted on
06 June 2016 18 August 2016
Assignment title Assignment: Data Analysis and Design
LO Learning outcome
(LO) AC In this assessment you will have the opportunity to
present evidence that shows you are able to:
Task
no.
Evidence
(Page no)
LO 1
Understand data models
and Database
technology
1.1
Provide evidence to support an
understanding of different data models.
Compare and contrast different data model
structures and examine their contribution to
database development
1
1.2
Examine case material that focuses on
developments such as data mining and
data warehousing 1
1.3 Analyse the different approaches to
database design 1
LO 2
Design a relational
database to meet user
requirements 2.1
Apply data analysis and design techniques to
develop a fully relational database with
minimum of six tables
2
2.2
Verify that a design meets user requirements
and provide justification of the database
design 2
2.3
Use a range of database tools and techniques
to demonstrate a more advanced level of
understanding and application
2
LO 3 Use manipulation and
query tools and
techniques
3.1
Incorporate a query language/languages into
the database design
3

3.2
Use a range of visual tools to enhance the
database design 3
3.3 Demonstrate the extraction of meaningful data
through the use of query tools
3
LO 4
Implement and test
database design
4.1 Provide documentation to support the
database implementation 4
4.2
Demonstrate ways in which the database has
considered the areas of verification and
validation
4
4.3 Evaluate a range of testing techniques and
apply one to your own database design.
4
Learner declaration
I certify that the work submitted for this assignment is my own and research sources are fully acknowledged.
Student signature: Date:
In addition to the above PASS criteria, this assignment gives you the opportunity to submit evidence in order
to achieve the following MERIT and DISTINCTION grades
Grade Descriptor Indicative characteristic/s Contextualisation
M1 Identify and apply strategies
to find appropriate solutions
 complex problems with more than
one data type have been
explored
 An effective approach to study
and research has been applied
To achieve M1 learners need to
consider example in the discussion of
Task - 2 in 2.1 with source of
information using Harvard reference
system.
M2 Select/design and apply
appropriate methods/techniques
 Use a range of methods and
techniques to collect, analyse and
process data
 Apply and analyse detailed
knowledge and skills, using
relevant theories and techniques
 the design of methods/techniques
has been justified
Learner must range of methods and
techniques to collect, analyse and
process data and apply data analysis
techniques to design the database
solution. (Whole Task 2)
M3 Present and communicate
appropriate findings
 The appropriate structure and
approach has been used
 Presentation have been used
appropriately and technical
language has been accurately
used
To achieve M3 you must produce
documentation to set format i.e.
coding with appropriate screen shots.
With appropriate technical language
has been used throughout (Whole
Task 2 and 3.1)
D1 Use critical reflection to
evaluate own work and justify
valid conclusions
 Conclusions have been arrived at
through synthesis of ideas and
have been justified.
 Realistic improvements have
been proposed against defined
characteristics for success
To achieve D1 Analysis and Design
of the database solution should be
robust with suggested improvement.
(Whole Tasks 2, 3.1 and 4.1)

D2 Take responsibility for
managing and organising data
 Autonomy/independence has
been demonstrated
 substantial activities, or
investigations have been
planned, managed and organised
 activities have been managed
To achieve D2 the similarity of the
learners' work should be minimum
level. All the activities such as system
analysis, design, implementation
coding with screen shots and testing
need to be carried out and presented
according to the assignment
deliverable.
(Tasks 3 and 4.1)
D3 Demonstrate
convergent/lateral/ creative
thinking using SQL queries.  Self-evaluation has taken place
 Convergent and lateral thinking
have been applied
 problems have been solved
 innovation and creative thought
have been applied
Database solution should be robust
so that all the transaction query
should produce meaningful data.
Learners should critically evaluate the
developed solution by mentioning the
limitations and future improvements.
(Task 4)

Assignment title Data Analysis and Design
Case Study: Developing a database for Sports Goods sale business
Sporting Goods Direct (SGD) is a directly selling catalogue based retailer of Sporting Equipment. Customers select the
items and complete an order form, which is processed by SGD. Recently SGD has seen a considerable growth in the
business with lot more sales than the first quarter of the year 2015.
Demand for their stock have grown, therefore they need a well-designed relational database for stock maintenance and
customer record purposes.
Sporting Goods Direct sells many Items under many Categories:
 Men
 Women
 Children
 Footwear
 Clothes
 Accessories etc
The minimum details to be stored for each item are Item ID, Item Name, Category, Description, Price, Quantity, and
Re-order Quantity.
The Customers need to register before being able to purchase an item. The information customers must provide are:
 First Name
 Last Name
 Date of Birth
 Email
 Contact Number
 Address
Many Suppliers can supply items sold in the store and one supplier can supply many items. Each item is supplied at a
particular price. The store needs to record details of each supplier in the database. The respective details are: Supplier
ID, Supplier Name, Address and Contact Number. All transactions between the store and the supplier need to be
recorded.
Sporting Goods Direct needs to maintain and record all the Orders made with details like Item Ordered, Quantity
Ordered, Order Date, and Total Amount. Their system later generates an order form, which contains Order Number,
Item Code(s), and the Quantity Ordered.
Design a database for SGD indicating the necessary keys and relationships. Draw ER Diagram and map them to
Relations ensuring that the database is in the third normal form.
You must design a database with the above mentioned specification indicating the necessary keys and relationships.
Draw ERD diagram for the entities along with their attributes such that the database is in the third normal form (Apply
1NF, 2NF and 3NF).
Notice: Assignment must meet all requirements/learning outcomes specified in order to achieve Pass.
Task 1 (LO: 1.1, 1.2, 1.3, M1, D2)
1.1 Compare and contrast the integrated database environment with the traditional file processing environment.
Analyse the different data model approaches to database design [Assessment Criteria: 1. 1, 1.3]
1.2 Illustrated with suitable examples from the given case study the meaning of data warehousing and data mining
[Assessment Criteria: 1. 2]
Task 2 (LO: 2.1, 2.2, 2.3, M2, M3, D1 & D2)
2.1 Create a schema listing the attributes for each entity identified and show that your schema is normalised (at least in
3NF). Identify primary, foreign and composite keys if there are any. [Assessment Criteria: 2. 3]

Hint:
2.2 Draw an entity relation diagram that will enable you to create a database for online sport items sales. Resolve
“many to many” relationships if there are any. State any necessary assumptions. [Assessment Criteria: 2. 3]
Hint : Example of ER
2.3 Use query language (MySQL) to create the database with minimum six tables with necessary attributes and
declare primary and foreign keys where necessary. Justify that the implementation meets the user requirements in
the case study. [Assessment Criteria: 2. 1, 2.2]
Hint:
Task 3 (LO: 3.1, 3.2, 3.3 M3, D2& D3)
3.1 Insert at least 5 records into each table created. Also provide a screenshot of the records in each table.
[Assessment Criteria: 3. 1]
Hint:
3.2 Using MySQL as visual tool, demonstrate the extraction of meaningful data through the use of “Select” statement.
Provide screenshots with MySQL code for the following Queries. [Assessment Criteria: 3.2 & 3. 3]
Hint:
Query transactions
a. List all the available items in the store.
b. List all the customers’ name, address, phone and email.
c. List all the orders for a customer, for example: Jason James in ascending order by date.
d. List all the customer IDs, names, order-date, items’ ID and items’ name purchased in March 2016.
e. List the customer names whose purchase is more than £50
f. Count all the items sold in April 2016.
g. Display the total amount of items sold in May 2015.
h. Update the customer Jason James city from Luton to London.
INSERT INTO table_name
VALUES (value1,value2,value3,...);
SELECT column_name,column_name......
FROM table_name..........
WHERE column_name operator value;
1st Normal
Entity Name
Attribute
1
Attribute1 Attribute
1
...... ........ ........... ...........
2nd Normal
..................................................................................................................
3rd Normal
....................................................................................................................

Task 4 (LO: 4.1, 4.2, 4.3, M1, M3, D1, D2)
4.1 Evaluate each query above, providing the SQL statement and show the output from the query. [Assessment
Criteria: 4.1]
4.2 Using MySQL, implement and test the verification and validation process with above 8 queries from the database
illustrating the understanding of the various features of SQL (update, sorting, joining tables, conditions using the
where clause, grouping, set functions, sub-queries etc.). [Assessment Criteria: 4.2]
Hint: Example
4.3 4.3 Explain different database testing techniques. Apply one of these techniques to your own database
implementation. [Assessment Criteria: 4.3]

Achievement Summary
Qualification
HND Diploma in Network Engineering
and Telecommunications Systems Assessor name Tanveer Ahmad,
Robert Chambers
Unit Number and
title
Unit: Data Analysis and Design Student name
LO Learning outcome
(LO) AC
To achieve the criteria the evidence must show that
the student is able to: Achieved
LO 1
Understand data
models and
Database
technology
1.1
Provide evidence to support an understanding of
different data models. Compare and contrast
different data model structures and examine their
contribution to database development
1.2
Examine case material that focuses on
developments such as data mining and data
warehousing
1.3 Analyze the different approaches to database
design
LO 2 Design a
relational
database to
meet user
requirements
2.1
Apply data analysis and design techniques to
develop a fully relational database with minimum
of six tables
2.2
Verify that a design meets user requirements and
provide justification of the database design
2.3
Use a range of database tools and techniques to
demonstrate a more advanced level of
understanding and application
LO 3
Use
manipulation
and query tools
and techniques
3.1
Incorporate a query language/languages into the
database design
3.2 Use a range of visual tools to enhance the
database design
3.3 Demonstrate the extraction of meaningful data
through the use of query tools
LO 4
Implement
and test
database
design
4.1 Provide documentation to support the database
implementation
4.2 Demonstrate ways in which the database has
considered the areas of verification and validation
4.3 Evaluate a range of testing techniques and apply
one to your own database design.
Higher Grade Achievements
Grade descriptor Achieved Grade descriptor
Achieved
M1: Identify and apply strategies to find
appropriate solutions
D1: Use critical reflection to evaluate
own work and justify valid
conclusions
M2: Select / design and apply appropriate
methods / techniques
D2: Take responsibility for managing
and organising data in a database
M3: Present and communicate
appropriate findings
D3: Demonstrate convergent /lateral
/creative thinking in using SQL
queries