ICT285: You are working with a database that stores information about suppliers, parts, and projects. The Supply relation records instances of a Supplier: Databases Assignment, MU

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:

SUPPLIER (SNo, SupplierName, City)
PART (PNo, PartName, Weight)
PROJECT (JobNo, JobName, StartYear, Country)
SUPPLY (SNo, PNo, JobNo, Quantity)

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 started 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

These tables exist in Rhea and are owned by the user. 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 the background of the case and table structures.

List the details of any works of art
List the details of any work of art that has a Surrealist style.
List the details of the works of art currently held in the gallery.
List the sales for each customer. 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.
The sum of the acquisition price of works of art for each year
Calculate the profit made on works of art that have been sold
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, 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 has sold a work of art called “Gorillas in the Mist” by the renowned French artist, Gallic Symbol (b. 1972) to the Gallery. 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.
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 columns 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.

Your match table must also include a record of 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 that a team would ever score more than 999 goals in a match.

The post ICT285: You are working with a database that stores information about suppliers, parts, and projects. The Supply relation records instances of a Supplier: Databases Assignment, MU appeared first on Singapore Assignment Help.