Assignment
Overview
For this assignment you will implement and query a database from a supplied ER Diagram and sample data. You will be required to write the SQL statements to create the database structures; and the queries on the data to give the report results, as specified in the nnnnnnnn-query.sql template file. You also need to understand, and import the sample data found in the "ass2-itech1006-2017-INSERT sql" file.
Learning Outcomes Assessed
The following course learning outcomes are assessed by completing this assessment:
- A1.design and implement a relational database using a database management system
- A2.utilise a query language tools and techniques to obtain data and information from a database
- K5. describe relational algebra and its relationship to Structured Query Language (SQL)
- S1.interpret entity-relationship diagrams to implement a relational database
- S2.demonstrate skills in designing and building a database application using a commercially available database management system development tool
- S3.use a query language for data manipulation
Assessment Details
The board of A-league soccer have decided to modify their initial data base specifications, leading to a number of modifications from the initial model. The new aim of the A-league board is to provide members and fans access to data about players, clubs and matches, in order to help them with their tipping competitions.
You have been provided with the updated solution of the new logical data model, including the ER diagram and the data ready to be loaded in. You have been commissioned to continue your role, (in your capacity as a Database Management System consultant), to write the SQL statements to create the database and its tables; import the data; and to provide SQL solutions to a number of queries.
The database should be created based on the ER diagram, (found at the end of this document), and with the provided data. The provided sample data is to be used for inserting data into the tables. This data can be found in an accompanying file,"ass2- itech1006-2017-INSERT.sql". You will need to import this file into your database. You will also need to write some insert statements, (please refer to question j, in the "nnnnnnnn- query.sql" file). Further, in this template query file, you will also need to add the results to the rest of the provided queries.
To implement the database
1. Translate the provided ER diagram into a relational schema using the correct notations and standards for this course. It should include:
a. Table names
b. Attribute names and field types (as required by XAMPP)
c. Primary and foreign keys identified
(Please refer to the data file to get a better understanding of the types needed for the fields.)
2. Create a text file called nnnnnnnn-create.sql (where nnnnnnnn is your student number). The file should include:
a. The SQL statements to create a database called nnnnnnnnALEAGUE (where nnnnnnnn is your student ID - e.g. 30111145ALEAGUE)
b. The SQL statements to create the tables and attributes, including their primary keys, foreign keys and the relationships of the tables.
3. Import the data from the "ass2-itech1006-2017-INSERT.sql" file. You need to:
a. Read through this file and understand what the data types are
b. Rename nnnnnnnnALEAGUE (changing nnnnnnnn to your student number) in the USE [DATABASE] statement found near the top of the file.
4. Add the queries to the provided query template text file (nnnnnnnn-query).
a. Rename the nnnnnnnn component of the file name to your student number.
b. Provide the SQL statements necessary to display the results for all the English equivalent instructions a-l. Write these queries under each of the a-l commented query instructions in the file.
Your documentation should include:
1. A title page clearly stating your name and student ID and your tutor's name.
2. A copy of the relational schema you have developed from the ER diagram provided.
3. A copy of the two files you are required to create and include, (ie nnnnnnnn-create.sql and nnnnnnnn-query.sql).
4. Full APA referencing of any resources you have used to complete your submission
You should submit the following:
1. The documentation file described above EITHER as a MS word file, WPS file or a PDF. If you are using a mac, please submit in a PDF so I can be sure of opening it.
2. The nnnnnnnn-create.sql file (where nnnnnnnn is your student number).
3. The nnnnnnnn-query.sql file (where nnnnnnnn is your student number).
Please note that the SQL files must be in a text format (i.e. no formatting of the text). I will run the nnnnnnnn-create.sql and parse the nnnnnnnn-query.sql file.
Attachment:- Database.rar