CS250 CW1 Relational Algebra & Calculus 2019-20 Date issued: 30 September 2019 (Mon) Deadline: 28 October 2019 (Mon) This coursework is all about Relational Algebra and Calculus. It contains three parts, and you must complete all parts. Important! Please read through this coursework during the first week that it is issued and ensure you understand what is required. If you have any questions, please ask – do not leave this until the last day or two before the deadline. Please also read the General Notes and Advice from past students below. Marking Scheme Part 1 [25 marks]: 5 marks for each question, 5 questions. Part 2 [35 marks]: 5 marks for each question, 7 questions. Part 3 [40 marks]: 15 marks for each question, 2 questions. – 15 marks for a correct answer to each question; less than full marks if the expression is only partially correct, violates a restriction or is very inefficiently constructed. You must provide sufficient comments and steps detailing how you arrive at the answer. – Up to 10 marks overall will be awarded for the presentation and formatting of the coursework submission. Coursework Submission (please read the instructions carefully) You should submit the following to the Blackboard assignment collection page: Part 1+Part 2: fill the Blackboard test form for automatic marking, electronic submission. Part 3: a written report (pdf only), electronic submission. Important! there is a 4-page limit (A4) for Part3, with a minimum font size: 10 for Arial font (11 for Times New Roman). The minimum font size applies to all tables, images, screenshots, and handwriting in your pdf. All submitted pdfs will be printed in 2-up format for manual marking and feedback. If your text is smaller than the minimum font size, or is incomprehensible, NO marks will be given. Important! you must use the answer sheet (docx) provided, and convert it into pdf before submission. Non pdf format (e.g. doc, docx files) will NOT be marked. All pdfs will be automatically chopped to the specified page limit, and no further marks will be given beyond the page limit. You are required to work in a group of 3 students in this coursework, and use the provided submission answer sheets. Students in the same group will receive the same marks. The coursework is designed for 1 student to complete in three weeks (we are generous to offer four weeks). The intention of group work is for you to achieve a higher grade through discussion and sanity checks. You are therefore advised to: a) Do your own work individually, b) THEN cross check your work with your group in a meeting, c) If there are discrepancies, discuss and convince one another via discussion and sanity checks, d) Revise the answer appropriately and e) Submit one single piece of work for your group – equal contribution. Note that, you should not share your work in advance. If your group member does not provide any work for cross checking in the meeting, his contribution is 0% and should be reported in the answer sheets. Alternatively, you may choose to do the work together (e.g. sitting together in front of the same machine) as a group, and practise collaboration and cooperation. It is acceptable. It is your duty to ensure you and your partner contribute equally. All students in the same group will be given the same marks if you choose to do so. Note however that you may not be able to spot issues with your collaborative solutions, to learn from the others’ mistakes, and thus not learn all the material in the course. You must sign up to your group on the blackboard page two weeks before the due date. If you have not signed up to a group two weeks before the due date, the instructor will randomly assign you into group. You must then work with your new partner even though you may have been working with someone else. In the exceptional case where you have genuine reasons to do the coursework individually, you must seek approval from the instructor and provide satisfactory evidence (e.g., medical certificate). In the situation that your partner(s) did not collaborate/respond/meet up, you should indicate on the answer sheet and provide two proofs of attempted contacts (e.g. emails, facebook or sms messages) showing that you have tried to contact your partner(s) at least one week before the deadline. Your non-collaborating partner(s) will be given zero marks. If your non-collaborating partner(s) accuses you of the same, each of your coursework will be individually marked, but all your final marks will be halved because no collaborating/discussing effort is shown – it defeats the purpose of the group work, and unnecessarily increases your marker’s efforts. As always, you should keep a record of your own work before the CW marks are finalised. You may need to prove that you carry out the work yourself or as a group, e.g. in suspected cases of plagiarism. Standard University and College of Science rules apply for late submission, i.e. zero if handed in late. [Extenuating circumstances] If there are extenuating circumstances, you should follow the procedure for the waiver of these penalties, but you will need to provide satisfactory evidence. The rest of the group should inform the instructor and continue with the submission. The extenuating circumstance will be deal with centrally, and those affected (student / group) will be informed the appropriate actions subsequently. Dr Gary KL Tam [email protected] General Notes: (a) I have tried to provide all the information you require to complete this coursework. If you think some information is missing or not clear, you can either ask me for the information (preferably by email). (b) An electronic submission of the coursework, using blackboard and the provided answer sheets, is required. Ensure it is clearly labelled with your details and who the work is for. (c) Important: You are expected to submit your own work for this coursework. In fact, on the coursework submission cover sheet which all students sign and attach to submitted work it states “We are aware of the University policy on unfair practice and We certify that this coursework is the result of my own independent work except where otherwise stated, and that all other sources are explicitly acknowledged.” On many occasions when working on coursework and projects, it is useful to ask others (the lecturer, a postgraduate assistant, or other students) for hints or debugging help, or to talk generally about the written problems or programming strategies. Such activity is both acceptable and encouraged, but you must indicate in your submission any assistance you received. Any assistance received that is not given proper citation will be considered a violation of the policy on plagiarism. In any event, you are responsible for understanding and being able to explain on your own all written and programming solutions that you submit. The course staff will pursue aggressively all suspected cases of plagiarism and collusion, and they will be handled through official University channels. Academic Integrity and Academic Misconduct Statement By submitting this coursework, electronically and/or hardcopy, you state that you fully understand and are complying with the university’s policy on Academic Integrity and Academic Misconduct. The policy can be found at: https://myuni.swansea.ac.uk/academic-life/academic-misconduct CS-250 CW1 Advice from students of previous years Ensure you sign up on time for all group assignments. Try a different method of working together in groups. For example, if you collaborate throughout, try each going your separate ways through the coursework and then comparing later, to find multiple solutions. Also getting the recommended reading material really helped on this coursework. Spread the work out. It may seem like a short piece of coursework, but since you have to detail your steps, creating tables and comments can be time consuming and it’s easy to overlook mistakes in your methodology. Start coursework as soon as po
ssible, always keep a backup, don’t leave answering the problems to the last minute. Gary: most coursework are given with 3 weeks to work on. I give you 4 weeks. Make good use of them. Start early! Have printed slides ready to use. Helped us a lot. Do tables (transient results) as you go along (helps to check). (Gary: this applies to Part 2 & 3!) Work on the questions alone and then discuss with partner – compare answers and work out best solution. Try your best, don’t delay as you won’t like the result. Breaking the problem down into smaller parts makes it much easier to solve the whole equation. Sometimes thinking of the answer in SQL makes it easier to then translate into algebra. (Gary: know the differences!) Make sure to read the tutorial solutions to ensure you know how to answer. They helped us a lot with symbols which we weren’t sure of – what each relational algebra symbol does and the correct way to use it. Use this assignment to make it easier to keep track of the tables. Do it over separate days, as you spot mistakes and doing it all in one go is quite hard. Write out the relational algebra on paper first then type it up when you have an answer. Don’t leave the CW for the last few days, because it might be difficult, & the teacher might not reply in the last few days! (Gary: good suggestions! : ) ) Read questions when they’re released even if you’re not planning to do it there and then so you can at least get the list of the task. (Gary: Don’t under-estimate the difficulty) Use ‘Relax – relational algebra calculator’ to check your answers and explore how the algebra works. Gary: No, do NOT use “Relax – relational algebra calculator”. Much syntax in Relax is different from what we taught in class. Note that there are many versions of relational algebra (at least 3) and ER diagram (at least 4). The marker will stick to the one taught in lectures. It is okay to experience these tools, but if you do not understand the difference, it will make your life a lot more difficult, error-prone and marks will be deducted in CWs. Further, some syntax on Relax is over-simplified but some over-complicated. You’re warned. Don’t waste your time. Gary: In case you do not know, you can do subscript in Microsoft Word properly by …. OR Database Systems Coursework 1 Date issued: 29 September 2019 (Mon) (worth 10% of CS-250) Deadline: 28 October 2019 (Mon) 11am CS250 CW1 Relational Algebra & Calculus 2019-20 Part 1: Relational Algebra (Bookwork) Problem 1. Which group (below) all take two tables as input? a) Natural Join ⋈, Union ∪, Cartesian Product × b) Equal = , Or ∨, Not Equal ≠ c) Division ÷, And ∧, Intersection ∩ d) Union ∪, Projection Π, Selection σ Problem 2. Which operations require as input two tables with exactly the same attributes? a) And ∧, Or ∨, Not Equal ≠ b) Natural Join ⋈, Cartesian Product ×, Division ÷ c) Union ∪, Projection Π, Selection σ d) Set difference –, Intersection ∩, Union ∪ Problem 3. Which operation can output a table with a different schema as the input table? a) Union b) Selection c) Projection d) Intersection e) None of the above. Problem 4. Which statement(s) is/are true? For two arbitrary input tables, I – Natural Join is equivalent to Cartesian Product, except that they have different symbols. II – Natural Join always do the same thing as a Cartesian Product. III – Cartesian Product together with other operations can be used to do the same thing as a Natural Join. IV – Natural Join and Cartesian Product always return the same number of columns. V – Cartesian Product sometimes returns the same number of rows as a Natural Join. Problem 5. Which operation can handle two input tables with totally different attributes? a) Union ∪ b) Set difference – c) Cartesian Product × d) Intersection ∩ e) Equal = Part 2: Relational Algebra & Calculus (General Usage) There are tables with the following schemas (the underlined attributes are the primary keys): Student (Sid, Sname, Dept) students, their names and departments they are in Module (Mid, Mname, Credits) modules, their names, and credit values Study (Sid, Mid) which students take which modules Teach (Lid, Mid) which lecturers teach which modules Lecturer (Lid, Lname, Dept, Salary) lecturers, their name, associated dept and salary ** You can assume that upper/lower case letters do not matter for the following questions. For example, dept is equivalent to Dept. Lecturer is equivalent to lecturer. You are given the following data to attempt the questions below. Note that these data are for testing purpose only. That is, your relational algebra answer should satisfy the question even on a different set of data. Student Sid Sname Dept S1 Ian Davis Comp Science S2 Alex George Comp Science S3 Mike Grant Engineering S4 Mary Jones Mathematics S5 Jen Lewis Comp Science Study Sid Mid S1 CS-250 S1 CS-270 S2 CS-250 S2 EE-225 S3 CS-270 S4 MM-210 S5 CS-270 Module Mid Mname Credits CS-250 Database 20 CS-270 Algorithm 20 EE-225 Calculus 10 EE-330 Optimisation 20 MM-210 Algebra 10 Teach Lid Mid L1 CS-250 L2 CS-270 L2 CS-250 L3 EE-330 L4 MM-210 L5 EE-225 Lecturer Lid Lname Dept Salary L1 Gary KL Tam Comp Science 2000 L2 Mark Jones Mathematics 2500 L3 Phil Grant Mathematics 1000 L4 Neal Harman Mathematics 4000 L5 Matt Jones Engineering 2500 L6 Rita Borgo Engineering 5000 L7 Ben Mora Comp Science 1000 Problem 6. Get the names of students and the departments they are in. a) ρSname, Dept (Student) b) Π Sname, Dept (Student) c) {[Dept, Sname] | ∃ ∈ Student} d) σSname, Dept (Student) e) { | ∃ ∈ Student(s[Sname] = t[Sname] s[Dept] = t[Dept])} Problem 7. Which of the following expresses the same thing as Π salary ( σ Lid=“L1” Lid=“L2” (Lecturer)) ? Select all correct option(s) below. I) { |∃ ∈ Lecturer([salary] = s[salary] s[Lid] = “L1”) ∨ ∃ ∈ Lecturer([salary] = u[salary] u[Lid] = “L2”) } II) Π salary (σ Lid=“L1” (Lecturer) ∨ σ Lid=“L2” (Lecturer)) III) { | ∃ ∈ Lecturer(s[Lid] = ”L1”) ∨ ∃ ∈ Lecturer( u[Lid] = ”L2”)} IV) Π salary (σ Lid=“L1” (Lecturer)) ∪ (σ Lid=“L2” (Lecturer)) V) { |∃ ∈ Lecturer( = s[salary] s[Lid] = “L1”) ∨ ∃ ∈ Lecturer( = u[salary] u[Lid] = “L2”) } Problem 8. Get the names of lecturers who teach “CS-270” and earn more than £1000. Select all correct option(s) below. I) Π Lname (σ Mid = “CS-270” ⋈ (σ Salary = ‘more than 1000’ (Teach × Lecturer))) II) Π Lid (σ salary > 1000 ^ σ Mid = “CS-270”) III) Π Lname (σ Mid = “CS-270” ∩ Salary > 1000 (Teach ⋈ Lecturer)) IV) Π Lname (σ Mid = “CS-270” (Lecturer ⋈ Teach)) ∪ Π Lname (σ Salary > 1000 (Teach ⋈ Lecturer )) V) Π Lname (σ Salary > 1000 ∧ Mid = “CS-270” (Lecturer ⋈ Teach)) VI) Π Lname (σ Mid = “CS-270” ( σ Salary > 50000 (Lecturer ⋈ Teach)) ) VII) Π Lname (σ Mid = “CS-270” ^ σ Salary > 1000 (Lecturer ⋈ Teach)) VIII) None of the above. Problem 9. Get the name of lecturer(s) who teach “CS-250” as well as those who teach “CS-270”. Select all correct option(s) below. I) ΠLname (σ Mid = “CS-250” ∧ σ Mid = “CS-270” (Lecturer ⋈ Teach)) II) ΠLname (σ Mid = “CS-250” (Lecturer ⋈ Teach) ∩ σ Mid = “CS-270” (Lecturer ⋈ Teach)) III) T1← σ Mid = “CS-250” ∨ Mid = “CS-270” (ΠMid (Lecturer ⋈ Teach)) σ Mid=T1 (Lecturer ⋈ Teach) IV) ΠLname(σ Mid = “CS-250” ∪ Mid = “CS-270” (Lecturer ⋈ Teach)) V) ΠSname (σ Mid = “CS-250” (Student ⋈ Study)) ∪ ΠLname (σ Mid = “CS-270” (Lecturer ⋈ Teach)) VI) ΠLname (σ Mid = “CS-250” (Lecturer ⋈ Teach)) ∪ σ Mid = “CS-270” (ΠLname (Lecturer ⋈ Teach)) VII) ΠLname (σ Mid = “CS-250” ∨ “CS-270” (Lecturer ⋈ Teach)) VIII) None of the above. Problem 10. Get the name of students who study in Computer Science but are taught by a lecturer in Engineering
. Select all correct option(s) below. I) ΠSname (ΠSname (σdept =”Comp Science” (Student)) ⋈ σdept =”Engineering” (Lecturer) ) II) T2 ΠSname (σ dept = ”Engineering” (Student ⋈ Study ⋈ Teach ⋈ Lecturer) ) T1 ΠSname (σ dept =”Comp Science” (Student) ) T1 ∪ T2 III) T1 ΠSname, dept ( σdept =”Comp Science” (Student ⋈ Study ⋈ Module) ) T2 ΠSname, dept ( Student ⋈ Study ⋈ Module ⋈ Teach ⋈ ΠLid ( σdept =”Engineering” (Lecturer) ) ) ΠSname(T1) – ΠSname(T2) IV) T1 ΠSid (σdept =”Engineering” (Study ⋈ Module ⋈ Teach ⋈ Lecturer) ) T2 ΠSid (σdept ≠”Comp Science” (Student ⋈ Study) ) ΠSname((T1 – T2) ⋈ Student) V) T1 σdept =”Comp Science” (Student) T2 σdept =”Engineering” (Lecturer) ΠSname(T1 ⋈ Module ⋈ T2) VI) ΠSname (σ dept =”Comp Science” ∧ dept = ”Engineering” (Student ⋈ Study ⋈ Module ⋈ Teach ⋈ Lecturer)) VII) T1 σdept =”Comp Science” (Student ⋈ Study) T2 ΠMid ( σdept =”Engineering” (Teach ⋈ Lecturer) ) ΠSname (T1 ⋈ T2) VIII) T1 ΠSname, dept (σdept =”Comp Science” (Student ⋈ Study)) T2 ΠSname, dept (σdept =”Engineering” (Student ⋈ Study ⋈ Module ⋈ Teach)) Πe-name(T1-T2) Problem 11. The following relational algebra expressions return the same set of tuples, EXCEPT: a) T1 ← Π Sname (σ Mid = “CS-250” ( Student ⋈ Study )) ∩ Π Sname (σ Mid = “CS-270” ( Student ⋈ Study )) T2 ← Π Sname (σ Mid = “CS-250” ∨ Mid = “CS-270” ( Student ⋈ Study )) T2 – T1 b) T1 ← Π Sname (σ Mid = “CS-250” ( Student ⋈ Study )) T2 ← Π Sname (σ Mid = “CS-270” ( Student ⋈ Study )) (T1 – T2) ∪ (T2 – T1) c) T1 ← Π Sname, Mid (σ Mid = “CS-250” ∨ Mid = “CS-270” ( Student ⋈ Study )) T2 ← Π Mid (σ Mid = “CS-250” ∨ Mid = “CS-270” ( Student ⋈ Study )) Π Sname (T1) – (T1 ÷ T2) d) T1 ← Π Sname (σ Mid = “CS-250” ∨ Mid = “CS-270” ( Student ⋈ Study )) T2 ← Π Sname (σ Mid = “CS-250” ∧ Mid = “CS-270″ ( Student ⋈ Study )) T1 – T2 Problem 12. The following relational algebra statements return the same set of tuples, EXCEPT: a) ΠLid (Lecturer ⋈ ΠDept ( σ Lid=”L6″ (Lecturer) ) ) b) T1← σ Lid=”L6″ (Lecturer) Π Lecturer.Lid (σ T1.Dept = Lecturer.Dept (Lecturer ⋈ T1)) c) ΠLid, Dept (Lecturer) ÷ ΠDept ( σLid=”L6″ (Lecturer) ) d) T1 ← ΠDept (σ Lid=”L6” (Lecturer)) T2 ← ΠLid (Lecturer) ΠLid (Lecturer) – ΠLid (T2 × T1 – ΠLid, Dept (Lecturer)) Part 3: Relational Algebra (Hands-on) For the following problems, use the tables and data in Part 2: a) write the relational algebra expressions to answer the queries, b) detail your steps with intermediate results and comments, and c) state the final results. Example. Get the name of all students who are in Engineering department. Answer: ΠSname(σ Dept = “Engineering” (Student)) Transient Results: ΠSname(σ Dept = “Engineering” ( Student.Sid Student.Sname Student.Dept S1 Ian Davis Comp Science S2 Alex George Comp Science S3 Mike Grant Engineering S4 Mary Jones Mathematics S5 Jen Lewis Comp Science )) ΠSname( Student.Sid Student.Sname Student.Dept S3 Mike Grant Engineering ) Final result: Sname Mike Grant Your RA answers must produce the correct results, have valid syntax (e.g. valid input and output, proper symbols, subscripts, and correct number of matching brackets), and be clear and well-presented. Note that your RA answers should be valid even the data in the table changes (that is, the RA is answering the question in general). FAQ: Why should I bother to provide step-by-step transient results on the current dataset? a) Because you can debug your RA expressions with the data involved, and b) The marker(s) can check your logic and your understanding of RA constructs. c) If your RA fails, marker(s) can give constructive comments (where it fails), and even partial marks, based on the in/correct intermediate steps you provide. If there are no intermediate steps / transient results, an incorrect answer will result zero marks. If you are very confident your RA will work, you can skip some simple steps to save space. If there are too few steps, however, you risk losing all marks if your answer is incorrect. Problem 13. Get the names of students who are taught by at least one lecturer from a different department. Problem 14. Find the names of lecturers who earn more than everybody in the department “Comp Science”. (Hint: this is not finding the lecturers with the highest salary in the department “Comp Science”). —- End of CW1 —-
