Relational Algebra & SQL Queries and Conceptual Design

Relational Algebra Queries for Lecture Database

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. 

This question is based on the View Ridge Gallery database you have been using in the labs. See the textbook for 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 a screen capture for the result tables, but NOT for the SQL.

SQL SELECT Queries for Art Gallery Database

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 have 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 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 on 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.

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.

Question 4 Conceptual Design 

The Felicity Stillwell Hospital (FSH) is a small hospital located in the southern suburbs of Perth, specialising in the health care of elderly patients. As the first phase of a complete information systems redevelopment, you have been contracted to design the database that will support its data and processing requirements. Depending on how well you perform, you may be asked to implement the design at some later time.

Detailed information on the data recorded, maintained and accessed by hospital staff to support the management and operations of the hospital is described below (see FSH Detailed Requirements, FSH Processing Requirements and FSH Sample Forms and Reports below), including some of the paper forms and reports currently in use, and the main transaction requirements are summarised there. This description of data and processing should be the basis for your design.

What you have to do:

1.Create an entity-relationship diagram showing the data requirements of the system. Your ERD should be able to be implemented in a relational DBMS. You should use the ERD notation we have been using in the lectures, and should include a legend to explain the notation. You should include attributes in the ERD. The use of a drawing tool such as Visio will make this task easier. However, whichever tool you use, you must copy and paste the ERD into a word-processed document. This is because your tutor might not have access to the tools you have used. Please note that hand-drawn ERDs are not acceptable.

2.List and explain any assumptions you have made in creating the data model.