Pulling data from database 2

1. Relational Databases week 4 Pulling data from database 2

2. The tasks of the seminar were intended to force you to think about where the data comes from and how the relations relate to each other. Let’s look at this problem on the SQL prompt. Seminar solution

3. Based on the results of most groups in the seminar task, identifying the tables to be used was the hardest element, followed by how to relate them. This weeks lecture will be primarily based from the SQL prompt, we will look at a variety of information that we want to retrieve from the data and then determine how to extract this information. Take notes! This will cover some new tips such as regarding formatting the information and page layout that will be useful in the seminar. Logically approaching select statements

4. Student Enrolled Marks Department StaffMember Class Subject We will be using the seminar database so spend a couple of minutes noting the ERD down... Task 1

5. In the seminar we wrote an SQL statement that retrieved essentially a list of who was teaching when and where. We can take this simple query and adapt it to show which classes are being taught by adding the subject id from the class table. Task 1 .... select name, day, classtime, room from staffmember, class where teacher = staffid order by name, day, classtime; select name, subjectid, day, classtime, room from staffmember, class where teacher = staffid order by name, day, classtime;

6. As can be seen from the above results, the subjectid is not that useful when trying to figure out who is teaching what so we need to retrieve instead, the subject name. NAME SUBJECTI DAY CLASSTIME ROOM -------------------- -------- --------------- ---------- --------------- Akhtar Ali COMP3411 Wednesday 11am Elec Eng G25 Andrew Turnbull COMP0055 Monday 9am Pandon S3 Ben Wightman COMP9315 Wednesday 3pm Webster B Emma-Jane Phillips COMP0519 Friday 11am Pandon S2 Emma-Jane Phillips COMP8755 Monday 11am Pandon F4 Emma-Jane Phillips COMP0023 Monday 2pm Pandon G1 Emma-Jane Phillips COMP0519 Tuesday 11am Pandon G5 .....

7. Task 1 .... • The subject name is held in a different table • This now means that we have an additional table to relate to (joins are covered later, so we are still relating via the WHERE clause) • Which tables now relate to each other and how? • What is the column/attribute that indicates the subject name? • Staffmember relates to the class table by staffmember.staffid = class.teacher • Subject relates to class by subject.subjectid = class.subjectid • Subject name is held in the SNAME attribute/column

8. Task 1 solution • Now we know how the tables all relate which gives us the where clause conditions and we also know what the additional column is about. • Using the previous code to build on, we can adapt to give the desired result. select name, sname, day, classtime, room from class, subject, staffmember where teacher = staffid and class.subjectid = subject.subjectid;

9. comments • If you have to build from multiple sources, always try to sort out the relationships between the different relations. • Where you have tables/relations with shared attribute names (columns with the same names in each table) you need to stipulate the tables they come from to prevent the DBMS throwing errors. • If a relationship between two tables is based on columns with different names, you do not need to stipulate the table names but it may be good practice to consider doing so.

10. Task 2 • Generate a list of students who are enrolled on module COMP9311 • You have approx 5 mins to work with the person next to you to write out the SQL for this. SQL> desc student Name ------------------- STUDENTID STUNAME MAJOR STAGE AGE SQL> desc enrolled Name ------------------- STUDENTID SUBJECTID

11. Task 2 solution select stuname from student, enrolled where student.studentid = enrolled.studentid and subjectid = 'COMP9311'; STUNAME -------------------- Joe Smith George Smith Greg Smith

12. Ways to make output clearer • Quite often the results you see are wrapped across the screen making it difficult to view • Column headings repeat every 14 rows returned making it harder to view results • Set linesize 200 sets the environment variable to display 200 characters before wrapping to the next line • Set pagesize 100 sets the environment variable to display 100 rows before repeating headings.

13. Do you really need to list the names? • We may not want to display the data but simply count the records • This can be used to check a join is working correctly if you are not sure about it, then select * from student, enrolled where subjectid = ‘COMP9311’ would return ‘222’ • This would indicate something is wrong with the code as we don’t have 222 students in the sample database. select count(*) from student, enrolled where student.studentid = enrolled.studentid and subjectid = 'COMP9311'; The above returns the number of students enrolled rather than listing their details.

14. Worked example 3 • Generate a class list for all classes taught by Emma-Jane Phillips (names of students and subject taught). • Which tables do we need to relate together? • How do these relate to each other? Class gives the teacher FK, staffmember gives staff name, student gives the student names, subject gives the subject name, enrolled gives the link between subject and student • Class & subject (on subjectid) • Class & staffmember (on teacher & staffid) • Enrolled & student (on studentid) • Subject & enrolled (on subjectid)

15. Solution! select stuname, sname from staffmember, class, subject, enrolled, student where staffid = teacher and class.subjectid = subject.subjectid and subject.subjectid = enrolled.subjectid and enrolled.studentid = student.studentid and name = 'Emma-Jane Phillips'; OR select stuname, sname from (class NATURAL JOIN subject) NATURAL JOIN (enrolled NATURAL JOIN student) INNER JOIN STAFFMEMBER ON teacher=staffid WHERE name = 'Emma-Jane Phillips‘;