ICT285 - Databases - Relational Algebra - SQL Select Queries - IT Assignment Help

Assignment Task

This assignment requires you to answer several questions on relational database principles and SQL, and to design a database based on a case study.
The assignment addresses the following learning outcomes for the unit:
Apply your knowledge of relational database principles and theory to create effective and efficient database designs
Define, create and manage relational database systems using SQL


Question 1: Relational algebra
You are working with a database that stores information about suppliers, parts and projects. The Supply relation records instances of a Supplier supplying a Part for a Project.
The schema for the database used in this question is as follows: (note that primary keys are shown underlined, foreign keys in bold).
SUPPLIER (SNo, SupplierName, City)
PART (PNo, PartName, Weight)
PROJECT (JobNo, JobName, StartYear, Country)
SUPPLY (SNo, PNo, JobNo, Quantity)
Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.

NOTE:
You can use the symbols , , etc or the words ‘PROJECT’, ‘RESTRICT’ etc as you prefer.
You do not need to try to make efficient queries – just correct ones.
Where you use a join, always show the join condition.
List the quantity of parts supplied on JobNo 4745.
List the weight of the Part named “Left-handed screwdriver”.
List the Project name and Part name of any Parts where fewer than 500 of the part has been supplied to a particular project.
List the name of any Suppliers who have supplied Parts to the Project called “New Academic Building” or to the Project called “Removal of Asbestos”.
List the name of any Suppliers who have supplied Parts to the Project called “New Academic Building” and to the Project called “Removal of Asbestos”.
List the names of Suppliers who have supplied the Part with the name “Fufful Valve”.
List the names of Projects that have had Parts supplied by “Insightly Co#” or a Supplier located in Dubai.
List the details of any supplier who has supplied parts to a project that stared in 2020 and (the project is) located in Singapore.
List the name of any part that was not used on a project that commenced in 2020.
List the name of any part that has been supplied to all projects that commenced in 2020.

Question 2: SQL – SELECT queries
Provide SQL queries and the result tables for the following:
Please ensure that you include the result table as well as your SQL; you can copy and paste this from either your ssh client or SQL Developer. Each query is worth 2 marks. These tables exist in arion and are owned by the user dtoohey. You may, if you wish, create your own copies of the tables under your own account. If you do so, you should ensure that you copy the sample data in dtoohey’s tables.
These queries are based on the View Ridge Gallery database you have been using in the Lab sessions. Please see Chapters 6 and 7 of Kroenke for background to the case and table structures.
Marks are allocated not only for correct answers, but also for best practice in the creation of the queries.
List the details of any works of art (including the Artist who created the work) that have more than three copies recorded in the database.
List the details of any work of art (including the Artist who created the work) that has a Surrealist style.
List the details of the works of art (including the Artist who created the work, and the acquisition and asking price details) currently held in the gallery (i.e., works of art that have not been sold).
List the sales for each customer (i.e., when a customer purchases a work of art from the Gallery, a transaction line is created. For a purchase, there will be values in the DateSold and SalesPrice columns). The query should include the details of the customer, the transaction and the work of art purchased.
List the names of the deceased artists and the number of years of age they were when they died (for example, an artist born in 1950 and deceased in 2001 has an age of 51).
The sum of the acquisition price of works of art for each year (for example, if there were two works of art purchased for $1500 and $1000 in 2019, and one work of art purchased for $500 in 2020, then the sums would be $2500 and $500, for 2019 and 2020 respectively).
Calculate the profit made on works of art that have been sold (i.e., the profit/loss on an individual work of art is the difference between the acquisition price and the sales price).
Which artist has had the most works of art sold, and how many of the artist’s works have been sold?
Sales of which artist’s works have resulted in the highest average profit (i.e., the average of the profits made on each sale of works by an artist), and what is that amount?
Customer name of any customers who have an interest in ALL artists.

Question 3: Further SQL
Provide ALL of the SQL statements required to insert the details of the following: A customer, John Smith, of 47 Moodle Street, Highwater, WA, 6709, Australia (email: jsmith@someisp.com) has sold a work of art called “Gorillas in the Mist” by the renowned French artist, Gallic Symbol (b. 1972) to the Gallery (i.e., the Gallery purchased it from him). It is a unique Watercolour on Paper and is 45 * 35cm signed by the Artist. The purchase price was $4600 and the transaction took place on 27th July 2020. (7 marks)
You have been given the following specifications of a simple database for a netball association that keeps record of players, teams and matches.

Give the SQL to create the Match table. You may assume that the Player and Team tables have already been created, and that the PlayerID and TeamID colums are of the data type VARCHAR2(5). The result of the match refers to the result for the HomeTeam and can be only W, L or D (win, lose or draw).
Your match table must also include a column that records the scores of both teams in the match. Provide the SQL to amend the original table design to allow for this change in requirement. It is most unlikely (impossible) that a team would ever score more than 999 goals in a match.

This ICT285 - IT Assignment has been solved by our IT experts at My Uni Paper. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.
Be it a used or new solution, the quality of the work submitted by our assignment experts remains unhampered. You may continue to expect the same or even better quality with the used and new assignment solution files respectively. There’s one thing to be noticed that you could choose one between the two and acquire an HD either way. You could choose a new assignment solution file to get yourself an exclusive, plagiarism (with free Turnitin file), expert quality assignment or order an old solution file that was considered worthy of the highest distinction.