Data Models Assignment
Qualification - BTEC Higher National Diploma in Computing and Systems Development
Unit number and title - Unit 33 Data Analysis and Design
QFC Level - Level 5
Scenario
You are working in a company which is developing an application for the organisation of fixtures and results in the English Premier League. Your job as a data analysis and designer is to implement the data requirements summarized below. The people involved in the league include players, coaches, managers, and referees. Each person is identified by a unique personnel id. They are also described by their first and last names along with the date and place of birth. Players are further described by other attributes such as their position on the field (goal-keeper, defender, midfielder, or striker) and have a squad number associated with them.
Teams are uniquely identified by their names. Teams are also described by the city in which they are located. Teams have one manager, a number of coaches, and a number of players. One of these players is the captain. At the beginning of each season all fixtures should be available to view in the database. Each team plays every other team twice. Games are played between two teams with one assigned referee. For any particular game on a particular date, there is one team designated as the hometeam and the other team designated as the awayteam. Once the game has taken place, the final result of the game is recorded. The score and the scorers are recorded for each team.
LO1
TASK 1: Understand data models and database technologies
By relating your answer to the above scenario where applicable:
1.1 Critically compare different data models and schemas.
1.2. Compare database systems with file based systems and also discuss the benefits andlimitations of different database technologies. Relate your answer to the above scenario.
1.3. Analyse different approaches to database design.
LO2
TASK 2: Be able to design and implement relational database systems
In this task you are expected to design a relational database system to meet the requirement given in the above scenario and subsequently build a relational database system based on a prepared design.
You will apply a range of database tools and techniques to enhance the user interface. To this end:
2.1 Discuss the principles of normalization and briefly show (by example) the steps you followed to achieve normal forms in relation to the given scenario.
2.2 Using the Crow's Foot notation, design an Entity Relationship (ER) model for the given requirement in a modelling tool of your choice. All entity types, their attributes and relationships must be clearly shown. You will also be required to show all cardinality and participation constraints.
2.3 Map the ER model devised in (2) above into a set of relations in the relational data model. Clearly state any de-normalization, if any, in all your relations and clearly state the reasons for de-normalizing relations in your implemented solution.
2.4 Using appropriate SQL commands (DDL) createa set of database tables using any relational database. Your tables should also show all constraints applied at either column or table level. You must include the code used in your report.
2.5 Show your grasp of the use of the DBMS IDE (e.g. SQL Management Studio) for SQL query execution as well as visual query design. You may use screen shots for this explaining the different screen sections of the IDE.
LO3
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 Populate the database with a small amount of data. The data should be meaningful but does not need to be extensive. These sites may be useful for quickly generating data.
You should produce, demonstrate, and document the SQL queries listed below for your system
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 viewfor 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.
LO4
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.
4.2 Create brief user documentation for the relational database system developed.
4.3 By giving a tabular V&V document, explain how verification and validation has been addressed.
4.4 Explain control mechanisms and show how these techniques have been used in developing your system.