This paper circulates around the core theme of A database records information about tutorials in a particular unit offering (such as ICT285 this semester). together with its essential aspects. It has been reviewed and purchased by the majority of students thus, this paper is rated 4.8 out of 5 points by the students. In addition to this, the price of this paper commences from £ 99. To get this paper written from the scratch, order this assignment now. 100% confidential, 100% plagiarism-free.
A database records information about tutorials in a particular unit offering (such as ICT285 this semester). A student is supposed to attend only one of several available tutorials, each of which is taken by a tutor at a particular day and time. A tutor might take several different tutorials.
The schema for this database is as follows: (note that primary keys are shown underlined, foreign keys in bold).
STUDENT (StudentNo, FamilyName, FirstName, StudentAddress, StudentEmail) TUTORIAL (TutorialNo, DayAndTime, Lab) TUTOR (StaffNo, TutorName, TutorEmail) CLASS (StaffNo, TutorialNo) ATTENDS (StudentNo, TutorialNo)
Provide relational algebra (NOT SQL) queries to find the following information. Each question is worth 2 marks.
a. List the student number, full name and email of all students. b. List the day and time for all tutorials held in lab Lon 3.32 c. List the day and time, lab and tutor name for all tutorials. d. List the student number and full name of all students in the Wednesday 10.30 lab. e. List the student number and full name of all students in Val’s classes. f. List the student number, full name, tutorial details and tutor name of all students. g. List the student number of any students who have signed up to attend both the Monday 4.30
and Monday 5.30 tutorial. h. List the student number of any students who have signed up to attend any or all of the
Wednesday tutorials (they are at 9.30, 10.30 and 11.30) i. List all the students who aren’t in any tutorial yet. j. List the full name of any student who has signed up to attend all tutorials.
Question 2: SQL – SELECT queries (20 marks)
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 as in dtoohey’s 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. Each question is worth 2 marks.
a. List the details of any work of art (including the name of the Artist who created the work) that has an Expressionist style.
b. List the details of any works of art (including the name of the Artist who created the work) that have more than one copy recorded in the database.
c. List the details of the works 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).
d. List the purchases each customer has made. The query should return the details of the customer, the work of art purchased, the price and the date purchased.
e. List the names of the deceased artists and the year they died.
f. Calculate 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 2007, and one work of art purchased for $500 in 2008, then the sums would be $2500 and $500, for 2007 and 2008 respectively). The result table should show year and sum of acquisition price.
g. Calculate the total profit made on all the works of art that have been sold (the profit/loss on an individual work of art is the difference between the acquisition price and the sales price).
h. Which artist has had the most works of art sold, and how many of the artist’s works have been sold?
i. List the artists and the average profit on the sales of their works. The result table should be listed from highest to lowest average profit.
j. Find the customer name of any customers who have an interest in ALL artists.
Question 3: Further SQL (15 marks)
You have been given the following specifications of a simple database for keeping track of lab bookings in a university (note that primary keys are shown underlined, foreign keys in bold):
LAB (RoomID, Building, RoomNo)
BOOKING (BookingID, DayOfWeek, TimeOfDay, Duration, UnitCode, RoomID)
Based on the table specifications provided, answer the following questions. Each question is worth 3 marks.
a. Give the SQL to create the LAB table. All data types should be VARCHAR2 (25), and Building and RoomNo should not be permitted to be null. Include the primary key constraint.
b. Give the SQL to create the BOOKING table. Use appropriate data types, and include the primary key constraint. The foreign key should be defined and referential integrity should be set such that if a lab has bookings it should not be able to be deleted.
c. Give the SQL to add a record to the LAB table to record the lab with RoomID 12345, in the Loneragan Building, room 3.032.
d. Give the SQL to add the attribute Capacity to LAB. The datatype should be an appropriate numeric type.
e. Give the SQL to record the fact that room 12345 has capacity 28.
Question 4: Normalisation (20 marks)
The following question is based upon the APPOINTMENT relation below that lists details of appointments in a dentist surgery.
StaffNo: Unique number used to identify each dentist DName: The name of the dentist PatName: Name of the patient for whom the appointment has been booked PatNum: Unique number allocated to individual patients PatTelNo: Telephone number of the patient ApptDateTime: The time and date when the appointment has been booked RoomNo: The room in which the appointment will take place RoomExt: The telephone extension number of the treatment room.
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.
a. Explain the problems with the existing design, in terms of the potential modification anomalies that it might exhibit. b. What normal form is the relation currently in? Explain your reasoning. c. Convert the relation to a set of relations in at least Third Normal Form (3NF). d. Explain how your new design addresses the problems you identifie
