We're Open
+44 7340 9595 39
+44 20 3239 6980

THE APPROPRIATE SQL STATEMENTS FOR EACH QUERY

  100% Pass and No Plagiarism Guaranteed

THE APPROPRIATE SQL STATEMENTS FOR EACH QUERY

Task

 

Notes:

 

This assignment requires you to use MySQL to complete the tasks listed below.

 

The instructions listed below relate to MySQL.

 

Typing the SQL statements or the results is NOT acceptable and will result in 0 marks for the assessment 

 

Your Submission


You are required to submit:

 

  1. The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into your submission file, and
  2. Screenshots of the resultant tables which are to be pasted into your submission file immediately after the SQL code for that query.
  3. Typing or manually drawing the results is NOT acceptable.
     

Tasks

 

Part 1


Open the database prime_minister database (prime_minister.sql ) from the ITC556 Interact Resources Databases folder. Answer the following queries using this database.

 

  1. Find certain Governors General of Australia.

 

  • Find all Governors General of Australia who were Barons at the time of their appointment. List them by Title and name and date that they were appointed to the position. Order the list by ascending date of appointment. (5 marks)
  • Now, format the date of appointment as day of the week, day of the month, month and year; eg. Monday, 01 January, 1901. Order the list by ascending date of appointment. (5 Marks)
  1. Find certain Leaders of the Opposition.

    • Find all Leaders of the Opposition and their date of appointment, who assumed their position after 01/01/1980. (5 marks)
    • For each Leader of the Opposition listed, add their wife’s name and their date of marriage. (5 Marks)
  2. Find certain Governors General of Australia.

    • List the title, name, date of appointment for Governors General of Australia who were appointed between 01 January 1930 and 01 January 1960. Order by ascending date of appointment. (5 marks)
    • Now add to the results of q3a, the list of Prime Ministers who appointed them and all Leaders of the Opposition who served during their appointment as Governor General of Australia. Order by ascending date of appointment. (5 marks)
  3. Who are the Opposition Leaders who subsequently became Prime Minister after 1930?

    • List their name, the date they were elected Opposition Leader and the date they were elected Prime Minister. The dates must be formatted as day of the week, day of the month, month in digits and year in four digits; eg. Monday, 01/01/1901. Order the list in ascending date of appointment as Prime Minister. (15 marks)
    • Now add their Deputy Prime Minister’s name and the party that they led. Order the list by ascending date of appointment as Opposition Leader. (10 marks)

Part 2

 

The design of the Prime Ministers database is now very old. You have been asked to review this design, as shown in the ERD below and advise how it could be updated. You are to complete the following tasks:

  1. Advise how you would improve the ability to query information in this database. For simplicity, use only the tables prime_minister, governor_general, ministry and opposition in your answer.
  2. What new integrity constraints would you use in each of these tables? (5 marks)
  3. Why would you use these integrity constraints? Explain how your constraints would improve queries on the tables. (10 marks)
  4. Write the DDL code that would implement your new integrity constraints for the following tables:

 

  1. Prime_minister (5 marks)
  2. Governor General (5 marks)
  • Ministry (5 marks)
  1. Opposition (5 marks)


(Note: You must keep all of the data attributes currently in these tables. Your implementation should include all existing data attributes and any new integrity constraints) 

Rationale

This assessment will test your ability to:

 

  • Be able to implement a database design using Structured Query Language (SQL);
  • Be able to query a database using SQL.
     

Marking Criteria

 

Marking Rubric

 

Task

HD

DI

CR

PS

FL

SQL tasks 1 – 5

The SQL statements accurately retrieve and format all the required information using concise and correct SQL syntax.

The SQL statements retrieve and format > 75% of the required information using correct SQL syntax.

The SQL statements retrieve and format > 60% of the required information using correct SQL syntax.

The SQL statements retrieve and format > 50% of the required information using mostly correct SQL syntax

The SQL statements retrieve and format < 50% of the required information using SQL syntax that contains frequent errors

Define new integrity constraints

Accurate and well-defined explanation of all the integrity constraints to be used and reasons for their use, without errors, is provided with evidence of synthesized application of concepts.

Provides correct identification and explanation of all the integrity constraints to be used with most reasons for their use, with evidence of applying the concepts correctly.

Good identification and explanation of most of the integrity constraints to be used with some reasons for their use, referencing some links between the concepts and application.

Reasonable identification and explanation of most of the integrity constraints to be used with some reasons for their use

Poor or inadequate identification and explanation of few or none of the integrity constraints to be used

Explain how integrity constraints improve queries

Comprehensive explanation of use of integrity constraints to improve query performance, with evidence of synthesized application of concepts.

Provides accurate explanation of use of integrity constraints to improve query performance, with evidence of applying the concepts correctly.

Good explanation of use of integrity constraints to improve query performance, referencing some links between the concepts and application.

Adequate explanation of use of integrity constraints to improve query performance

Poor or inadequate explanation of use of integrity constraints to improve query performance

Write DDL code for 4 new table implementations

The SQL DDL statements accurately create new tables, with all correct integrity constraints using concise and correct SQL syntax.

The SQL DDL statements create new tables, with >75% correct integrity constraints using correct SQL syntax.

The SQL DDL statements create new tables, with >60% correct integrity constraints using correct SQL syntax.

The SQL DDL statements create new tables, with >50% correct integrity constraints using mostly correct SQL syntax

The SQL DDL statements do not accurately create new tables, with

 

Marking Guide

 

Task

Mark

Score

1 a

5

 

1 b

5

 

2 a

5

 

2 b

5

 

3 a

5

 

3 b

5

 

4 a

15

 

4 b

10

 

5 a

15

 

5 b

10

 

5 c i

5

 

5 c ii

5

 

5 c iii

5

 

5 c iv

5

 

Total

100

 

Final Mark

15

 

 

Presentation

 

You are required to submit:

 

  1. The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into a single submission file, and
  2. Screenshots of the resultant tables which are to be pasted into your submission file immediately after the SQL code for that query.
  3. Typing or manually drawing the results is NOT acceptable.

100% Plagiarism Free & Custom Written,
Tailored to your instructions


International House, 12 Constance Street, London, United Kingdom,
E16 2DQ

UK Registered Company # 11483120


100% Pass Guarantee

STILL NOT CONVINCED?

View our samples written by our professional writers to let you comprehend how your work is going to look like. We have categorised this into 3 categories with a few different subject domains

View Our Samples

We offer a £ 2999

If your assignment is plagiarised, we will give you £ 2999 in compensation

Recent Updates

Details

  • Title: THE APPROPRIATE SQL STATEMENTS FOR EACH QUERY
  • Price: £ 109
  • Post Date: 2024-08-28T18:23:38+00:00
  • Category: Assignment
  • No Plagiarism Guarantee
  • 100% Custom Written

Customer Reviews

 THE APPROPRIATE SQL STATEMENTS FOR EACH QUERY THE APPROPRIATE SQL STATEMENTS FOR EACH QUERY
Reviews: 5

A masterpiece of assignment by , written on 2020-03-12

I ordered the literature review for my research project in rush delivery. I was not expecting that they would complete my order 2 hours before my chosen time. The quality is also nice. Thank you for help.
Reviews: 5

A masterpiece of assignment by , written on 2020-03-12

I received my order last night and now I’m writing my reviews. My assignment has all the points I needed along with a good style. The citations used are relatable and professional. The best thing is the discount I got because I recommended my friend too to use their service. I am so pleased to use this effective service. The features are also amazing, everything is good. Will come again soon!