Relational Algebra - SQL SELECT Queries - IT/Computer Science Assignment 

Assignment Task:

The assignment addresses the following learning outcomes for the unit:
LO 2.Demonstrate an understanding of relational database principles and theory 
LO 3.Demonstrate practical skills in using SQL 
LO 4.Demonstrate practical skills in data modeling using entity-relationship modeling
LO 5.Demonstrate practical skills in normalization and convert a conceptual database design to a logical design in 3NF (partly)

Question 1: Relational algebra 
You have been asked to work with a database that records information about various lectures that are available for students at a conference. Each lecture addresses a topic, is presented by a lecturer. There are a limited number of seats in each of the venues, and this is reflected in the maximum number of students who can attend a lecture (capacity). 
The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).
STUDENT (StudentNo, StudentName, UniversityName)
UNIVERSITY (UniversityName, Country)
LECTURER (LecturerName, Biography, UniversityName)
LECTURE (LectureNo, LectureName, Topic, Description, Capacity, DateAndTime, LecturerName)
PARTICIPANT (StudentNo, LectureNo)
Provide relational algebra (NOT SQL) queries to find the following information.

Each question is worth 2 marks. 

a. List the name and university of all lecturers.
b.List the lecture name and for all lectures on the topic of ‘Biological Diversity’.  
c.List the names of students who attended a lecture on the topic of ‘Biological Diversity’, ‘Bugs and Bubs’, or both. 
d.List the names of all students who attended a lecture by a lecturer from a university in Malaysia. 
e.List the names of students from Murdoch University who went to a lecture on the theme of ‘Biological Diversity’ run by a lecturer from a university in Italy.
f.List the lecture number, name and topic of all lectures, and the names of the students that attended, if any.
g.List the name of any students who went to both the lecture named ‘Introduction to Biology’ and the lecture ‘Darwin 101’.
h.List the name, biography, and university of all lecturers from a university in Greece.
i. List the names of the students who did NOT attend the lecture named ‘Grassland Cultivation Masterclass’.
j.List the names of any students who went to all lectures.  
Question 2: SQL – SELECT queries  
This question is based on the View Ridge Gallery database you have been using in the labs. See the textbook for a background to the case and the table structures. 
The tables are: 
ARTIST
CUSTOMER
WORK
TRANS
CUSTOMER_ARTIST_INT
You can use the dtoohey tables that we have been using. If you prefer, you can create your own copies of these tables under your own account to work with. If you do so, you should ensure you copy the same sample data and constraints as in the dtoohey tables. 
Provide SQL AND result tables for the following queries. Paste the queries and the result tables from either your SSH client or SQL Developer into your assignment document. You can use screen capture for the result tables, but NOT for the SQL.

Each question is worth 2 marks.
a. List the full details of any work of art, including the name of the artist who created the work, that has been described as Surrealist.
b.List the details of any work of art (including the name of 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) with an asking price of > $400.
c.List the title of any work of art that has two (and only two) copies recorded in the database.
d.List the names of all deceased artists and the age they were when they died.
e.List the name of each artist represented in the database and the number of works of art by that artist, ordered from lowest to the highest number. 
f.List the work ID, title and artist name of all the works of art that sold for more than the average price of all sales, and the price they sold for.
g.List the total amount of sales of works of art by the gallery for each year, and the number of works sold that year.
h.Which artist has had the most works of art sold, and how many of the artist’s works have been sold?
i.Calculate the total profit made on all the works of art that have been sold (the profit/loss of a work of art is the difference between the acquisition price and the sales price)
j.List the name of any customers who have an interest in all artists.

 
Question 3: Further SQL 
You have been given the following specifications of a simple database for keeping track of lectures and those who presented the lecture at a national Biological Sciences conference (note that primary keys are shown underlined, foreign keys in bold).
You should run your SQL to demonstrate that it works correctly, and paste in the statements used plus the output from Oracle.  
PRESENTER (PresenterNo, PresenterName, Biography, InstitutionName)
LECTURE (LectureNo, LectureName, Description, Theme, Capacity, DateAndTime, PresenterNo)
Based on the table specifications provided, answer the following questions. Each question is worth 3 marks. 
a. Give the SQL to create the PRESENTER table. Choose appropriate data types.  None of the attributes should be allowed to be null. Include the primary key constraint. 
b.Give the SQL to create the LECTURE table. Use appropriate data types, and include the primary key and foreign key constraints. Referential integrity should be set such that if a presenter is deleted from the database, any lectures that he or she is running will also be deleted. 
c.Give the SQL to add your own record to the PRESENTER table. Include your name, ‘Murdoch University’ as your institution, and make up a short (10-20 words) biography. 
d.Give the SQL to add the attribute VenueName to LECTURE.  There are three possible venues: Building A, Building B and Building C, which should be enforced by the SQL definition.
e.Give the SQL to record the fact that all lectures have had their capacity increased by 10.

This IT/Computer Science Assignment has been solved by our IT/Computer Science 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.