topics based on SQL AND DDL QUERIES

All SQL statements should be copied from MySQL into the word document. All screenshots that arerequired should be captured and inserted into the document in the appropriate position to show thatyou have completed the requirement. Screenshots that are submitted in addition to the assignmentdocument will not be marked.Databases must not be included as part of a submission.

Charles Sturt University Subject Outline
ITC556 201530 S I-24 January 2015-Version 2 Page 24 of 32
Presentation
Online
Requirements
Online quiz must be completed online on the due date
Assessment item 5
Create and Query Databases using SQL
Value: 15%
Due date: 10-May-2015
Return date: 31-May-2015
Submission method options
Alternative submission method
Task
Part A. Write SQL queries (25 marks)
Tasks:
Write SQL SELECT statements to retrieve the following information from the prime_minister
Database: Answer all questions (25 marks)
1. Find all deputy ministers of Australia who are Protectionist at the time of their appointment.
List them by number, deputy name and party. Order the list by ascending date of deputy name. (2
marks).
2. List the name of the prime ministers who have got married more than once. Order the list by
ascending date of name. (3 marks)
3. Find all the prime ministers, spouse name and representing state who have served over 5 years
as a prime minister. Order the list by descending order of representing state. (5 marks).
4. Who are the Leaders of the Opposition who were not members of a Labor party and who gained
their position after 01/01/1990. List their name, date of election in the format month, day of week and
year, eg: January 1st, 1990, State represented and spouse. Order the list by descending order of date of
appointment. (5 marks)
5. List the name, party and number of ministries held of all prime ministers with their party and
the number of the ministries they have held if they have held more than one ministry? List the details
in descending order of the number of ministries held and ascending order of the names. (5 marks)
6. Who was the Governor General(s) started between 2008 and 2009? Who was/were the prime
minister at that time and Deputy Prime Ministers worked with that prime minister? List the Governor
General, Prime Minister and their Deputy Prime Minister. (5 marks)
Part B. Create Tables using SQL DDL (35 marks)
The following questions are based on the Institute ERD as shown below:
Charles Sturt University Subject Outline
ITC556 201530 S I-24 January 2015-Version 2 Page 25 of 32
Tasks:
1. Write the SQL DDL to create the database that contains each of the relations shown in the
above ERD.
You will need to provide:
Your DDL code for each table that you create; (20 marks)
A screenshot showing each table that is created (5 marks)
2. Write and execute SQL definition commands for each of the following queries:
a. Add the attributes email address, emergency contact name and emergency contact
number to theStudent table. Give those attributes an appropriate size and data type (3 marks).
b. Change the attribute Course.courseDuration from type Varchar(20) to type INT. (2
marks)
3. Write and execute SQL commands for the following:
Add the following customers to the student table. (3 marks)
Write a command that will remove the Jones that lives in Orange from the Renters table
(2 marks)
Charles Sturt University Subject Outline
ITC556 201530 S I-24 January 2015-Version 2 Page 26 of 32
studId firstName lastName contactNo address DOB
11501234 John Smith 0292919344 63, George Street, Parramatta 13/06/1982
11501235 Peter McDonald 0292915566 6, Kent Street, Sydney 24/08/1985
11501236 Anne Sugar 0294567814 58, Parkes Street, Harris Park 15/06/1982
4. Write and execute SQL commands to do that following.
a. Add a new database table to record payments made by students. This table should include
the details such as amount, payment date, and payment type. It should also create a relationship
to the student table. (5 marks)
b. Give a brief discussion on the relationship between the student table and the payment
table. Your discussion should include the relationship connectivity, cardinality, relationship
participation, degree, whether the relationship is a strong or a weak relationship.
(5 marks)
Rationale
This assignment will assess your knowledge and ability to use SQL to create database tables. It will
also test your ability to write both simple and complex queries given a set of results that are required
and to the run those queries to return the requested data.
Marking criteria
The following criteria will be used to assess Assessment Item 4:
SQL Queries – DML: All SQL code in MySQL must be copied and pasted into your submission file.
Outputs of each query also should have shown followed by the query.
HD DI CR PS FL
Accurate and elegant
SQL that answers all
parts of the question.
SQL code copied
from MySQL as well
as a screen shot of
code and results into
the submission file
Good SQL that
answers most parts
(75% ~ 85%) of the
question. SQL code
copied from MySQL
as well as a screen
shot of code and
results into the
submission file
Satisfactory SQL that
answers some parts
(65% ~ 75%) of the
question. SQL code
copied from MySQL
as well as a screen
shot of code and
results into the
submission file
Partial SQL that
answers a small part
(50% ~ 65%) of the
question. SQL code
copied from MySQL
as well as a screen
shot of code and
results into the
submission file
Incomplete or
irrelevant SQL that
does not answer the
question (< 50%).
Some missing parts
or non-matching SQL
code with the given
results.
Question 5B:
Comprehensive
explanation that
applies to the work
done and is correctly
explained
Question 5B:
Adequate explanation
that applies to the
work done and is
correctly explained
Question 5B:
Partial explanation
that applies to the
work done and is
correctly explained
Question 5B:
Some explanation
that applies to the
work done and is
correctly explained
Question 5B:
Poor or no
explanation that
applies to the work
done and is correctly
explained
SQL Queries – DDL:
HD DI CR PS FL
Accurate and correct
SQL command and
syntax use that
answers all parts of
the question
Mostly accurate SQL
command and syntax
use that answers most
parts of the question
(75% ~ 85%)
Partially-correct SQL
command and syntax
use that answers
some parts of the
question (65% ~
75%)
Poor or incorrect
SQL command and
syntax use that
answers few parts of
the question (50% ~
65%)
Incomplete or
irrelevant SQL
command and syntax
use that does not
answer the question
(< 50%)
Charles Sturt University Subject Outline
ITC556 201530 S I-24 January 2015-Version 2 Page 27 of 32
Presentation
This assessment item is required to be submitted in Word format (.doc, or .docx).
All SQL statements should be copied from MySQL into the word document. All screenshots that are
required should be captured and inserted into the document in the appropriate position to show that
you have completed the requirement. Screenshots that are submitted in addition to the assignment
document will not be marked.
Databases must not be included as part of a submission.
Any extensions required must be applied for before the due date of the assignment.
Requirements
Online submission via Turnitin is required for this assignment.
Assessment item 6
Final Exam
Value: 50%
Date: To be advised
Duration: 2 hours 10 minutes
Submission method options
N/A – submission not required/applicable
Rationale
Covering all topics, this assessment task has been designed to assess your ability to:
be able to apply database theory to the design and implementation of relational databases;
be able to analyse and model business database requirements using Entity Relationship
Diagrams;
be able to analyse a database design and apply Normalisation theory and techniques;
be able to implement a database design using Structured Query Language (SQL);
be able to query a database using SQL;
be able to explain the use of transaction processing, security and database integrity in
relation to relational databases.
Requirements
The examination consists of:
Multiple choice questions,
short and long answer questions.
The examination is a Closed book examination.
All questions must be answered
Marking criteria
Question 1 has a similar criteria to Assignment 1 and 3
Question 2 has a similar criteria to Assignment 2.
Question 3 has a similar criteria to Assignment 4.
Question 4 each part is marked out of 5. Lecturer’s professional judgement.
Question 5 each correct answer receives one mark. An incorrect answer receives zero marks.