Unit 33 Data Analysis and Design Assignment Solution

TASK 3: Be able to use manipulation and querying tools

3.1- Explain the benefits of using manipulation and query tools in a relational database system

3.2 – Produce, document, demonstrate  following SQL Queries:

• List the managers of each team.

• Output the full name of the top scorer in the league with the number of goals scored.

• Output the average number of goals per game of all games played.

• List all of the games that were played in a particular ‘city’.

       

• List all players who have played a game that was refereed by a particular referee (choose any referee).

           

• Create a database view for your system to produce a list of fixtures yet to be played along with the date of each game.

3.3 - Critically evaluate the validity of the data extracted using the above queries and comment on the design process followed to ensure that meaningful data is extracted through the use of query tools.

I have used SQL Server Management Studio to query the database analysis and design, in this project. It can be seen from above queries and screenshots that meaningful and relevant data is produced.
Query tools are SQL query software tools which allow us to execute and administer queries against most common databases. There are certain advantages of using query tools:

• They help us to access local as well as remote data easily.
• They have integrated – robust report writing feature.
• Most of the query tools are built upon client-server architecture, so provide complete client-server support.
• SQL Server is more robust query tool than SQL for MS Access.
• Oracle as a query tool is bulky and mostly useful for large databases; therefore SQL Server is more preferable and most widely used.

Thus by the help of SQL Queries, we can retrieve any kind of data quickly and securely.

TASK 4: Be able to test and document relational database systems

4.1 - Critically review and test the relational database system designed for the given scenario and thus provide a documentation to support the implementation and testing of the relational database system developed.

Relational database for English Premier League is as follows:

• Game(game_id(pk), team_name, date, city, game_score, refree_id(fk)
• Team(team_name(pk), coach_id(fk), manager_id(fk), team_score, city, player_id(fk), refree_id(fk), game_id(fk))
• Player(player_id(pk), player_fname, player_lname, player_dob, player_pob, player_score, squad_no, position_on_field, game_id(fk))
• Coach(coach_id(pk), coach_fname, coach_lname, coach_dob, coach_pob)
• Manager(Manger_id(pk), manager_fname, manager_lname, manager_dob, manager_pob)
• Refree(refree_id(pk), refree_pob, refree_dob, refree_fname, refree_lname)
  (pk = primary key, fk = foreign key)

Requirement of testing the above database: Databases are a collected of files and records containing different data types, interconnected to each other, therefore databases are heterogenous. With the presence of various data types and records, large databases may have integration and implementation errors, which affect system security, reliability, consistency and performance in negative way. Thus in order to obtain a database system which satisfies database properties like Atomicity, consistency, isolation, durability etc., it is important to test the database periodically.
Purpose of testing the database: Testing is done to find the errors in the database, thus improving its quality and effectiveness.
Many users are connected with single database and web application, so data is critical from business perspective.
Companies that are associated with data storage need to have a reliable and consistent storage medium. If any of the CRUD operations like insert, update, delete etc are performed without verifying the database for its consistency, then there is risk of system crash and data loss, which might affect all the connected users of database.

Disadvantages of testing the database:

• Databases keeps on changing constantly, as operations like insert, update and delete are being performed on them. Thus the setup required to test the database becomes very expensive and bulky.
• For testing the database, we need to start from scratch. Entire database has to be removed and new test cases have to be created.
• In order to include SQL semantic into test cases, a SQL generator has to be used to convert SQL statements.

Alternatively we can make use of SQL Query Analyser  while using SQL Server.

4.2 - Create brief user documentation for the relational database system developed.

In this project, I have created six database tables for English Premier League database: Game, Team, Players, Coach, Manager, Refree. Each table is identified by a unique primary key. Foreign key constraints are also defined in respective tables.
In this database we can see that:

• Players, coach, manager, refree are entities involved in English Premier League.
• 1 Coach, 1 refree and 1 manager each are assigned to each team.
• A team can be home team or away team. Each team has to play twice, once with home team and once with away team.
• Players can be captain, striker, midfielder, goal keeper, defender. A captain is assigned to each team.

In table game, game_id is the primary key and refree_id is the foreign key.
In table player, player_id is the primary key and game_id is foreign key.
In table coach, coach_id is the primary key.
In table manager, manager_id is the primary key.
In table refree, refree_id is the primary key.
In table team, team_name is the primary key. Coach_id, manager_id,player-id, refree_id, game_id are all foreign keys which reference Coach, manager, player, refree, game tables respectively.

4.3 - By giving a tabular V&V document, explain how verification and validation has been addressed.

Verification and Validation are two software testing processes, applied on any product or software to check its quality. Here is a list of differences between these two processes.

Verification	Validation
Verification process is always done before validating a software.	This step is performed after verifying the software for consistency and at end of development process.
Verification process checks that the product or software is designed to give all functionality to the client.	Validation process checks that the developed system / software / product meets user requirements and organization’s goals.
Verification involves periodic reviews, meetings and walkthroughs over the system to be developed.	Validation involves all kinds of testing(manual and automation) over the developed system / software. Manual Testing involves functional and Non Functional Testing.
The documents that are required for verification are test plans, code, design specifications, requirement specifications, traceability matrix etc.	Validation does not require documentation, it is performed on actual system / product / software.
It is a low level activity.	Validation is a high level activity.
Verification checks whether the product is being built in correct way.	Validation is performed to check whether developed product is according to requirement specification and that it meets user and organization’s requirements.
An example can be : If  we have to build a table. Then verification process involves checking that it has all four legs, which are of correct size, table top is of correct diameter.	Validation process will involve checking that the final table is exactly in accordance with the requirements of client.
Verification process answers questions like – Am I building the product in correct manner.	Validation process answers questions like – Am I building the right product, which will meet user requirements.
Advantages of Verification :   Checking the product specifications before actually starting to make the product saves cost. It helps to decrease the count of errors in the later part of product development. This process helps in understanding the product better.	Advantages of Validation :   If some requirements are missed during verification process then, they can be covered up during validation process. If any defects are missed during verification they can be covered up during validation process. The difference between actual results and expected results can be understood during validation. It covers up the defects of verification process. This process helps in building product as per user requirements.

4.4 - Explain control mechanisms and show how these techniques have been used in developing your system.

Ways of managing or controlling various objects or activities in the fashion we desire, are called control mechanisms. It is essentially moulding the environment variables as per our desire and requirements.
In business, control mechanisms have to be applied so that whatever we do helps us in gaining profit, and fulfills organization’s objectives.
In our project on English Premier league, we have applied some control checks over various entities like players who have to play within certain team.

• Every team has to play twice, once as home team and second time as away team.
• Scores and scorers for each team will be recorded.
• Refree, manager, coach have to be assigned to every team.
• Players can be captain, midfielder, goal keeper, striker and defender.
• One of the players will be captain for each team.

Thus various such control mechanisms have to be applied, so that functioning of the organization or English Premier League takes place smoothly.

For complete copy of this solution, order now from Assignment Help

References:

• Dynamic data extraction with SQL statement [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://msdn.microsoft.com/en-in/library/aa260428(v=vs.60).aspx>
• Database Testing [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://en.wikipedia.org/wiki/Database_testing>
• What is software verification [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://istqbexamcertification.com/what-is-verification-in-software-testing-or-what-is-software-verification/>
• Verification vs Validation [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://softwaretestingfundamentals.com/verification-vs-validation/>
• What is Validation [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://www.webopedia.com/TERM/V/validation.html>
• Difference between Verification and Validation [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://www.softwaretestingclass.com/difference-between-verification-and-validation/>
• What is Verification and Validation [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://www.softwaretestinghelp.com/what-is-verification-and-validation/>
• Control Mechanisms [Online]. [Accessed on 27 November 2014]. Available on world wide web: <http://www.businessdictionary.com/definition/control-mechanisms.html>
• Database Normalization [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://www.studytonight.com/dbms/database-normalization.php>
• Approach to database design and development [Online]. [Accessed on 28 November 2014]. Available on world wide web: <http://www.sigmer.com/products_services/database_solutions_lifecyle.php>
• Top Down vs Bottom Up Object database design [Online]. [Accessed on 29 November 2014]. Available on world wide web: <http://www.dba-oracle.com/t_object_top_down_bottom_up.htm>