ICT285: Databases - Relational Algebra - Normalisation - Felicity Stillwell Hospital (FSH) Case Study - IT/Computer Science Assignment Help

Assignment Task:

This assignment requires you to answer a number of 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:
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. 
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.

1. List the name and university of all lecturers.
2. List the lecture name and for all lectures on the topic of ‘Biological Diversity’.  
3. List the names of students who attended a lecture on the topic of ‘Biological Diversity’, ‘Bugs and Bubs’, or both. 
4. List the names of all students who attended a lecture by a lecturer from a university in Malaysia. 
5. 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.
6. List the lecture number, name and topic of all lectures, and the names of the students that attended, if any.
7. List the name of any students who went to both the lecture named ‘Introduction to Biology’ and the lecture ‘Darwin 101’.
8. List the name, biography, and university of all lecturers from a university in Greece.
9. List the names of the students who did NOT attend the lecture named ‘Grassland Cultivation Masterclass’.
10. List the names of any students who went to all lectures. 

Question 2: SQL – SELECT queries (ICT285)
This question is based on the View Ridge Gallery database you have been using in the labs. See the textbook for the 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.

1. 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.
2. 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.
3. List the title of any work of art that has two (and only two) copies recorded in the database.
4. List the names of all deceased artists and the age they were when they died.
5. 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. 
6. 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.
7. List the total amount of sales of works of art by the gallery for each year, and the number of works sold that year.
8. Which artist has had the most works of art sold, and how many of the artist’s works have been sold?
9. 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)
10. 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. 

1. 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. 
2. 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. 
3. 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. 
4. 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.
5. Give the SQL to record the fact that all lectures have had their capacity increased by 10.

Question 4: Normalisation (ICT285)
The following question is based upon the APPOINTMENT relation below that lists details of appointments in a Podiatry Clinic. 

 

• You have been asked to design a relational database based on this design. You know that there are problems with the current design and that it will need to be modified in order to work effectively.
• You need to write a report that addresses the following:
• What are the specific problems associated with the current design and why do they arise (about ½ page)?
• How would you change the current design and how does your new design address the problems you have identified with the current design.
• In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3 and how they apply to this problem. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.

Question 5: Conceptual Design
The Felicity Stillwell Hospital (FSH) is a small hospital located in the southern suburbs of Perth, specializing 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.

1. Create and maintain records recording the details of members of staff (Personnel Officer)
2. Search for staff who have particular qualifications or previous work experience (Personnel Officer)
3. Produce a report listing the details of staff allocated to each ward (Personnel Officer)
4. Create and maintain records recording the details of patients referred to the hospital (all staff)
5. Create and maintain records recording the details of patients referred to the out-patient clinic (Charge Nurse)
6. Produce a report listing the details of patients referred to the out-patient clinic (Charge Nurse and Medical Director)
7. Create and maintain records recording the details of patients referred to a particular ward (Charge Nurse)
8. Produce a report listing the details of patients currently located in a particular ward (Charge Nurse and Medical Director)
9. Produce a report listing the details of patients currently on the waiting list for a particular ward (Charge Nurse and Medical Director)
10. Create and maintain records recording the details of suppliers for the Hospital (Medical Director)
11. Create and maintain records detailing requisitions for supplies for particular wards (Charge Nurse)
12. Produce a report listing the details of supplies provided to specific wards (Charge Nurse and Medical Director)

This ICT285: 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.