Apply query language - Australia Assignments

Student Name			Student Number
Unit Code/s & Name/s	ICTPRG431 Apply query language in relational databases
Cluster Name If applicable
Assessment Type	☒ Assignment ☐ Project ☐ Case Study ☐ Portfolio ☐ Third Party Report (Workplace) ☐ Third Party Report (Peer) ☐ Exam
Assessment Name	DBMS and SQL Assignment		Assessment Task No.			1 of 2
Assessment Due Date	Week 15		Date Submitted			/ /
Assessor Feedback:
Attempt 1	Satisfactory ☐	Unsatisfactory ☐			Date		/ /
Assessor Name			Assessor Signature
☐ Student provided with feedback and reassessment arrangements (check box when completed)				Date scheduled for reassessment			/ /
Attempt 2	Satisfactory ☐	Unsatisfactory ☐			Date		/ /
Assessor Name			Assessor Signature
Note to Assessor: Please record below any reasonable adjustment that has occurred during this assessment e.g. written assessment given orally.

Assessment Criteria / Benchmarks The evidence submitted demonstrates that the student has satisfactorily:	Attempt 1		Attempt 2
	Date __/__/__		Date __/__/__
	Y	N	Y	N
PART 1. Background knowledge of Structured Query Language (SQL) and database management system (DBMS)
Task 1. DBMS and SQL knowledge of concepts and terminology
Brief description and example provided for the following terms and phrases: Data types Primary key Foreign key Null values Indexing Expressions Logical operators Transactions Stored Procedures Views Triggers	☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐	☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐	☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐	☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐ ☐
Characteristics of a DBMS has been presented and three current industry accepted DBMS have been compared by: Features and cost.	☐ ☐	☐ ☐	☐ ☐	☐ ☐
Main components of SQL, DML, DDL and DCL have been explained and their functionality presented.	☐	☐	☐	☐
1.4 Two methods used in industry to gather data have been evaluated and the most reliable one selected. Justification provided.
1.5 DBMS connection methods: a) A DBMS connection methods that is language and platform specific has been presented. b) The client-side or server-side nature has been explained for DBMS connections methods.	☐ ☐	☐ ☐	☐ ☐	☐ ☐
1.6 Three techniques used in data mining to find patterns in the data have been discussed.	☐	☐	☐	☐
1.7 Three characteristics of reliable data sources have been defined.	☐	☐	☐	☐
1.8 Two desirable features of an information system have been specified.	☐	☐	☐	☐
1.9 Three common relational database administrative tools have been evaluated and one has been selected for a given scenario.	☐	☐	☐	☐
1.10 Stored procedures functionality has been outlined and the relation between atomicity and transactions has been determined.	☐	☐	☐	☐
1.11 Two (or more) types of parameters used in stored procedures have been identified and their functionality outlined.	☐	☐	☐	☐
1.12 The data analysis technique implied in the statement provided has been named and the type of analysis suited for this technique explained.	☐	☐	☐	☐
Task 2. Selecting and setting up the DBMS environment
1.13 There is evidence that the student has correctly setup the SQL/DBMS environment and has accessed the necessary files for the tasks. DBMS accessed Administrator account crated/obtained Data source files (export/dump) accessed	☐ ☐ ☐	☐ ☐ ☐	☐ ☐ ☐	☐ ☐ ☐
1.14 An appropriate set of steps to resolve he data import problem has been provided.	☐	☐	☐	☐
PART 2. Create a database, create tables, alter tables and table properties
Task 1. Create database and tables
A database has been created and named as per instructions.	☐	☐	☐	☐
2.2 Four tables have been created using an appropriate naming convention and the details provided.	☐	☐	☐	☐
2.3 Relationship between tables and cardinality ratio have been identified and a simple ERD has been presented.	☐ ☐	☐ ☐	☐ ☐	☐ ☐
2.4 Primary keys for each table have been identified and set to auto_increment.	☐	☐	☐	☐
2.5 Precision parameters have been identified and set for each table’s columns	☐	☐	☐	☐
2.6 Indexes have been identified for each table.	☐	☐	☐	☐
Task 2. Alter table structure
2.7 SQL statement to alter table and add a new column has been provided.	☐	☐	☐	☐
2.8 SQL statement to alter table and modify data type date has been provided.	☐	☐	☐	☐
2.9 SQL statement to alter table and add CHECK constraint has been provided.	☐	☐	☐	☐
Task 3. Populate the database using CSV files
2.10 Evidence that the tables created have been populated by importing raw data provided in the csv files.	☐	☐	☐	☐
Task 4. Testing the tables with queries
2.11 SQL statement to perform calculations on numerical data (price column) has been provided and includes percentages and other arithmetic calculations. Screenshot has been provided	☐ ☐	☐ ☐	☐ ☐	☐ ☐
2.12 SQL statement to extract data based on time calculations on a date_time data type column. Screenshot has been provided	☐ ☐	☐ ☐	☐ ☐	☐ ☐
2.13 SQL statement using three (3) aggregate functions. Screenshot has been provided	☐ ☐	☐ ☐	☐ ☐	☐ ☐
2.14 SQL statement using string/text operations with the concat function. Screenshot has been provided	☐ ☐	☐ ☐	☐ ☐	☐ ☐
2.15 SQL statement using aggregate functionality with group by operator. Screenshot has been provided	☐ ☐	☐ ☐	☐ ☐	☐ ☐
2.16 SQL transaction to insert data into two (2) tables simultaneously using a view. Screenshot has been provided	☐ ☐	☐ ☐	☐ ☐	☐ ☐
End of Marking Criteria

Tags: alwaysopen, assignmenthelpaustralia, assignmenthelpmelbourne, assignmenthelpsydney, assignmenthelpwebsites, london, londonstudent, melbourne, myassignmenthelp, plagiarismfreework, studentassignmenthelp