Guide to Project for Supervisors
ITECH 7401 LEADERSHIP IN IT PROJECT MANAGEMENT
ITECH 1103 BIG DATA AND ANALYTICS
Assignment: SQL Database
Overview
The purpose of this task is to develop student’s skills in designing and implementing a relational database for a given case study.
Timelines and Expectations
Percentage Value of Task: 25%
Due: Refer to Course Descripto
Minimum time expectation: Preparation for this task will take approximately 20 hours
Assessment Details
Background
You are to design a scenario of your choosing. This scenario will be modelled initially as an ERD, and then implemented in SQL statements. As well as creating the requisite SQL Tables, you will need to manufacture data that is imported into these Tables. You will then be required to develop several SQL queries to provide key functionality for your database.
Requirements
1. Scenario choice: your chosen scenario must include at least three entities which are related to each other. This really can be anything you choose, but you are advised to choose something you have knowledge of, to make the exercise easier. An example could be a ho
y that you have – perhaps you enjoy a sport, and you wish to develop a database that models some form of performance related to the teams and games played in this sport. Another example could be a part-time job you may have, and you may wish to develop a database to assist some aspect of that business. Another example could be some area of science, perhaps an engineering type application that models a power station or a factory or an experiment of some kind. Because this “
ief” is very open-ended, you are recommended to check your chosen scenario with the teaching staff to make sure it is suitable. A final consideration when thinking of your scenario are the list of SQL queries that you will need to execute within your database. For instance you will need to include certain search and statistical functions, so your scenario will need to be sophisticated enough to make these queries possible. PLEASE NOTE: each student is required to develop a UNIQUE scenario – it will not be permitted that two students use the exact same scenario.
2. Your ER Diagram must include at least three entities. There is no upper bound on how many entities you choose, but you are advised to include no more than five. Design your ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated. You will need to normalise all of your entities, to resolve any many to many relationships.
Observe the following restrictions when creating your scenario:
· One (or more) of your entities must have a numeric field
· One (or more) of your entities must contain an alphanumeric (varchar) field
· One of your entities must contain a compound key
· Do not have include cyclic relationship (A->B->C->A)
Your attribute names, primary and foreign keys should be indicated as per the conventions given in the lecture slides (i.e. attributes as proper nouns, primary key underlined and foreign keys in italic
All many to many relationships should be resolved, and you may wish to include a discussion of normalisation including the normal form that each entity is in and why that is optimal.
3. For each entity, you must create some example data for that entity. Include at least 10 rows of data for each entity. This data must be initially stored in a text file that will be imported into the database.
4. Write the SQL statements to create the table structures from your developed ERD. The structures should contain the attributes specified in your ERD. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by your ERD. Write the SQL code to insert your data from (3) into the SQL table structures.
5. Write the SQL statements that provide the following database functionality:
Query 1. Write an SQL statement that co
ectly uses the ‘LIKE’ symbol on an alphanumeric field to select a subset of data. Explain the purpose of your query for your database solution.
Query 2. Write an SQL statement that co
ectly implements a JOIN statement over two or more entities to select a subset of data. Explain the purpose of your query for your database solution.
Query 3. Write an SQL statement that co
ectly uses the ‘>’ operator to select a subset of data for a single entity. Explain the purpose of your query for your database solution.
Query 4. Write an SQL statement that co
ectly uses the ‘GROUP BY’ operator to select an aggregation of data (e.g. SUM, AVG, COUNT). Explain the purpose of your query for your database solution.
Query 5. Write an SQL statement that co
ectly uses the ‘IN’ symbol to select a subset of data for a single entity. Explain the purpose of your query for your database solution.
Query 6. Write an SQL statement that co
ectly uses the ‘UPDATE’ symbol to modify at least three rows of data in one of your tables. Explain the purpose of your query for your database solution.
6. Write a short section (two or three paragraphs) about some aspect of your scenario that might be too difficult or very complex to implement in an SQL database.
Documentation requirements
1. Prepare a report (single Word or PDF document) which contains the following:
a). ER diagram based on your created scenario
). Example data
c). A copy of your SQL code – you must also provide associated output (as screengrabs, inserted into the report)
d). Statement of any resources used. These includes full disclosure of assistance from all sources including tutors and other students. Full APA referencing of any resources used.
Report file name: ITECH1103_Assignment1_Report_yourname_studentID
A separate copy of the SQL statements, file name: ITECH1103_Assignment1_SQL_yourname_studentID.txt
Submission
The assignment is to be submitted via the Assignment 1 submission box in Moodle. This can be found in the Assessments section of the course Moodle shell.
1. The report file described above EITHER as a MS word file or a PDF. If you are using MacOS, please submit as a PDF.
2. The database file, as described above.
Please note that the SQL files must be either .txt or .sql so they can be run If they are in a word or pdf file there will be extra characters that will prevent the files from running without e
ors and will cost marks.
Marking Criteria/Ru
ic
Assessment Criteria
Marking Scale
ER Diagram
· Completeness of diagram, Co
ect notation and convention used, All assumptions clearly noted, Primary and foreign keys, Resolution of many to many relationships
/10
SQL Statements – Table creation
· Co
ect translation of the ER diagram submitted with appropriate tables, Types and restrictions on attributes given, Primary keys co
ectly implemented, Foreign keys co
ectly implemented
/10
Data Creation and Import
All data imported co
ectly by SQL
/8
SQL Statements – Database functionality
· Queries 1 - 6
/18
Data modelling complexity
/4
· Discussion
Total Mark [50 marks]
0.0
Total Worth [15%]
0.0
Feedback
Feedback and marks will be provided in Moodle. Marks will also be available in FDL Marks.
Plagiarism
Plagiarism is the presentation of the expressed thought or work of another person as though it is one's own without properly acknowledging that person. You must not allow other students to copy your work and must take care to safeguard against this happening. More information about the plagiarism policy and procedure for the university can be found at http:
federation.edu.au/students/learning-and-study/online-help-with/plagiarism
Please refer to the Course Description for information regarding late assignments, extensions, and special consideration. A reminder all academic regulations can be accessed via the university’s website, see: http:
federation.edu.au/staff/governance/legal/feduni-legislation
CRICOS Provider No. 00103D
ITECH2004 Assignment1 ER-SQL 2020.docx
Page 1 of 5
ITECH1103 Big Data and Analytics
Page 4 of 5
Guide to Project for Supervisors
ITECH 1103 BIG DATA AND ANALYTICS
ITECH1103 Assignment SQL Database.docx Page 1 of 5
Assignment: SQL Database
Overview
The purpose of this task is to develop student’s skills in designing and implementing a relational
database for a given case study.
Timelines and Expectations
Percentage Value of Task: 25%
Due: Refer to Course Descriptor
Minimum time expectation: Preparation for this task will take approximately 20 hours
Assessment Details
Background
You are to design a scenario of your choosing. This scenario will be modelled initially as an ERD, and
then implemented in SQL statements. As well as creating the requisite SQL Tables, you will need to
manufacture data that is imported into these Tables. You will then be required to develop several SQL
queries to provide key functionality for your database.
ITECH 1103 BIG DATA AND ANALYTICS
ITECH1103 Assignment SQL Database.docx Page 2 of 5
Requirements
1. Scenario choice: your chosen scenario must include at least three entities which are related to
each other. This really can be anything you choose, but you are advised to choose something
you have knowledge of, to make the exercise easier. An example could be a ho
y that you
have – perhaps you enjoy a sport, and you wish to develop a database that models some form
of performance related to the teams and games played in this sport. Another example could be a
part-time job you may have, and you may wish to develop a database to assist some aspect of
that business. Another example could be some area of science, perhaps an engineering type
application that models a power station or a factory or an experiment of some kind. Because this
“
ief” is very open-ended, you are recommended to check your chosen scenario with the
teaching staff to make sure it is suitable. A final consideration when thinking of your scenario are
the list of SQL queries that you will need to execute within your database. For instance you will
need to include certain search and statistical functions, so your scenario will need to be
sophisticated enough to make these queries possible. PLEASE NOTE: each student is required
to develop a UNIQUE scenario – it will not be permitted that two students use the exact same
scenario.
2. Your ER Diagram must include at least three entities. There is no upper bound on how many
entities you choose, but you are advised to include no more than five. Design your ER Diagram
with all entity names, attribute names, primary and foreign keys, relationships, cardinality and
participation indicated. You will need to normalise all of your entities, to resolve any many to
many relationships.
Observe the following restrictions when creating your scenario:
- One (or more) of your entities must have a numeric field
- One (or more) of your entities must contain