ICT218 Databases TM 2016 Assignment 1 Worth: 15% of your final grade. Due: Sunday, 19 June 2016, 11:55 PM Submit to: LMS, via the Assignments tool. Submit as a SINGLE Word document including all parts of the assignment. Ensure you complete the declaration that is part of the submission process. You do not need to include a separate cover sheet but you should include your name and student number as part of your document filename. Your name and student number should also be included within in the assignment document. Late assignments that do not have an extension will be penalised at the rate of 5% per day. This is an INDIVIDUAL assignment. 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: 1. Demonstrate an understanding of database principles and theory, particularly those relating to the Relational Model 2. Construct database queries using the Relational Algebra and SQL 3. Design a database, demonstrating practical skills in normalisation and data modelling Marks are distributed as follows: Question 1: Relational algebra 20 Question 2: SQL Select queries 20 Question 3: Further SQL 15 Question 4: Normalisation 20 Question 5: Conceptual design 25 Total 100 Question 1: Relational algebra (20 marks) 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 identified in (a) and preserves all the information in the original design. StaffNo Dname PatName PatNum PatTelNo ApptDateTime RoomNo RoomExt S1011 Scott Bilstein Jill White P400 0159-454-369 10-JUL-15 0900 R1 7711 S1011 Scott Bilstein Ian Bell P895 0418-427-839 10-JUL-15 0930 R2 7712 S1024 Harry Lopez John Jamieson P398 0001-568-032 10-JUL-15 1000 R1 7711 S1024 Harry Lopez Gayle Gordon P668 0427-824-954 10-JUL-15 1030 R1 7711 S1011 Scott Bilstein Ian Bell P895 0418-427-839 11-JUL-15 0845 R1 7711 S1032 Boris Wilson Gayle Gordon P668 0427-824-954 11-JUL-15 0845 R2 7712 S1032 Boris Wilson Gayle Gordon P668 0427-824-954 12-JUL-15 0845 R2 7712 Question 5: Conceptual Design (25 marks) Use the case study description and list of requirements below to create an entity-relationship diagram showing the data requirements of the Alumni Relations database. Your ERD should be able to be implemented in a relational DBMS. Mudrock University has a large alumni grouping which consists of graduates and businesses that have supported the university over time. The Director of Alumni Relations is frustrated with the current Alumni database because it is not much more than a list of members. As such, she wants to develop a new database for Alumni Relations, which will provide her with the data she needs to do her job and grow the alumni. She wants the database to capture all relevant information on the university's alumni, including qualifications and work experience, and any donations they have made to the university. In particular, she wants to record all the qualifications an alumnus has from Mudrock (not just the first. For example, if someone graduated with a BSc Computer Science in 2014 and then went on to do a Master of IT in 2016, both qualifications would be recorded.) As well as the qualification, she wants to record the school that the alumnus was in (e.g. School of Arts; School of Engineering and IT, School of Veterinary and Life Sciences), and also the general discipline area (information technology, history, biology). The Director also wants to keep track of the alumni social and professional development events that are organized; she wants to know when and where they were held, what the focus of the event was (for example, it might be a social event or it might be PD), who the intended audience for the event was (could be all alums, or it could be alums from a particular school or discipline area, or it could alums from a particular city – or even a mixture of all of these). She also wants to know who was invited, who RSVP'd and who attended. Finally, a number of businesses also support Almuni Relations with donations and other forms of support (such as internships or travel grants); as such, the Director wishes to be able to know which businesses provided what support and when that support was provided. The database will have to support the following querying and reporting requirements: 1. A report that displays alumni information for a specified qualification for a particular year (e.g., 2012 BSc BIS graduates). The report should list, for the specified area and year of graduation, each alumnus's name, e-mail address, the degree earned, work phone number, and home phone number. Note that a similar report could be required for any discipline and any graduating year. 2. For a specified city (e.g., Singapore), a report listing all alumni who live in that city. The report will display the name of the city, and for each alumnus in that city, their name, home address, email address, work phone, and home phone. 3. A report listing all business donors who have donated a total amount greater than $25,000. The report will be sorted in descending order of the total donated amount. 4. A report that displays all donations made. The Name and ID of the donor, as well as the date and amount of the donation must be displayed. 5. A report listing all alumni working for a particular company (e.g., NORTEL). This report must also display the date an individual joined the company, as well as, their job title and salary. 6. A report that displays the employment history for a particular alumnus. The report must show, for each employer that alumnus has worked for, the employer name, the most recent job title the alumnus had with that employer company (e.g., Vice-President), the date the alumnus joined the company, and the date the alumnus left the company (if applicable). What you have to do:  Use the case study description and list of requirements to create an entity-relationship diagram showing the data requirements of the Alumni Relations database. Your ERD should be able to be implemented in a relational DBMS.  List and explain any assumptions you have made in creating the data model.  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.  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. Some important things to note:  You don't have to create the database or any of the reports at this point. However, Assignment 2 will involve creating the database from your design, so you should be satisfied that it will work.  Part of understanding a system at sufficient enough detail to model well, involves asking questions. If you are not sure about some detail of the case study, you should ask on the Discussion Forum in LMS.  The University email server strips out any Visio (.vsd) files that are sent, even if they are included in a zip archive. So, if you want to send a draft of your design to your tutor by email, you will need to change the extension to something other than .vsd (.blah works well), or (probably simpler) paste the diagram into a word document.