Murdoch UniversityICT 285FT_ICT285C_Assignment1 Database

Document Content and Description Below

ICT 285 Database Assignment 1 Prepared by Name: Tan Chye Hock Student Number: 33315968 Unit: ICT285 Class: C Assignment: 1Table of Contents Question 1: Relation Algebra.......................... ...........................................................................................................1 Part a....................................................................................................................................................................1 Part b....................................................................................................................................................................1 Part c....................................................................................................................................................................1 Part d....................................................................................................................................................................1 Part e....................................................................................................................................................................2 Part f.....................................................................................................................................................................2 Part g....................................................................................................................................................................2 Part h....................................................................................................................................................................3 Part i.....................................................................................................................................................................3 Part j.....................................................................................................................................................................3 Question 2: SQL – SELECT queries...........................................................................................................................4 Part a....................................................................................................................................................................4 Part b....................................................................................................................................................................5 Part c....................................................................................................................................................................6 Part d....................................................................................................................................................................7 Part e....................................................................................................................................................................8 Part f.....................................................................................................................................................................8 Part g..................................................................................................................................................................10 Part h..................................................................................................................................................................10 Part i...................................................................................................................................................................11 Part j...................................................................................................................................................................11 Question 3: Further SQL........................................................................................................................................13 Part a..................................................................................................................................................................13 Part b..................................................................................................................................................................13 Part c..................................................................................................................................................................14 Part d..................................................................................................................................................................14 Part e..................................................................................................................................................................15 Question 4: Normalisation....................................................................................................................................16 Part a..................................................................................................................................................................16 Part b..................................................................................................................................................................16 Part c..................................................................................................................................................................17 Part d..................................................................................................................................................................17 Question 5: Conceptual Design.............................................................................................................................18 Assumption:......................................................................................................................................................19 iICT285 Tan Chye Hock Database 33315968 Question 1: Relation Algebra Question 1 Part a a. List the name and institution of all presenters.  PresenterName,Insitution (PRESENTER) -> SOLUTION Question 1 Part b b. List the workshop name and capacity for all workshops on the theme of ‘Virtual Reality’. σ Theme=’Virtual Reality’(WORKSHOP) -> VRWORKSHOP  WorkshopName, Capacity (VRWORKSHOP) -> SOLUTION Question 1 Part c c. List the names of attendees who attended a workshop on the theme of ‘Virtual Reality’, ‘Big Data’, or both.  WorkshopNo(σ Theme = ‘Big Data’ OR Theme=’Virtual Reality’ (WORKSHOP)) -> T1  AttendeeNo(PARTICIPANT* PARTICIPANT.WorkshopNo = T1.WorkshopNo T1) -> T2  AttendeeName(ATTENDEE* ATTENDEE.AttendeeNo = T2.AttendeeNo T2) -> SOLUTION Question 1 Part d d. List the names of all attendees who attended a workshop run by a presenter from an institution in Queensland.  InsitutionName(σ State = ‘Queensland’ (INSITUTION)) -> T1  PresenterName(PRESENTER* PRESENTER.InsitutionName = T1.InsitutionName T1) ->T2  WorkshopNo(WORKSHOP* WORKSHOP.PresenterName = T2.PresenterName T2) -> T3  AttendeeNo(PARTICIPANT* PARTICIPANT.WorkshopNo = T3.WorkshopNo T3) ->T4  AttendName(ATTENDEE* ATTENDEE.AttendeeNo = T4.AttendeeNo T4) -> SOLUTION 1ICT285 Tan Chye Hock Database 33315968 Question 1 Part e e. List the names of attendees from Murdoch University who went to a workshop on the theme of ‘Green IT’ run by a presenter from an institution in Tasmania.  InsitutionName (σ State=’Tasmania’(INSITUTION)) -> T1 T1* T1.InsitutionName = PRESENTER.InsitutionName PRESENTER -> T2  PresenterName (T2) -> T3 T3* T3.PresenterName = WORKSHOP.PresenterName WORKSHOP -> T4  WorkshopNo, Theme (T4) -> T5  WorkshopNo (σ Theme = ‘Green IT’ (T5)) -> T6 T6* T6.WorkshopNo = PARTICIPANT.WorkshopNo PARTICIPANT -> T7  AttendeeNo (T7) -> T8 T8* T8.AttendeeNo = ATTENDEE.AttendeeNo ATTENDEE -> T9  AttendeeName, InsitutionName (T9) -> T10  AttendeeName (σ InsitutionName = ‘Murdoch University’ (T10)) -> SOLUTION Question 1 Part f f. List the number, name and theme of all workshops, and the names of their attendees, if any.  WorkshopNo, WorkshopName, Theme (WORKSHOP) -> T1 T1* T1.WorkshopNo = PARTICIPANT.WorkshopNo PARTICIPANT -> T2  WorkshopNo, WorkshopName, Theme, AttendeeNo (T2) -> T3 T3* T3.AttendeeNo = ATTENDEE.AttendeeNo ATTENDEE -> T4  WorkshopNo, WorkshopName, Theme, AttendeeName (T4) -> SOLUTION Question 1 Part g g. List the name of any attendees who went to both the workshop named ‘Introduction to Oracle’ and the workshop ‘Normalisation 101’.  WorkshopNo (σ WorkshopName = ‘Introduction to Oracle’ (WORKSHOP)) -> T1  AttendeeNo (T1* T1.WorkshopNo = PARTICPANT.WorkshopNo PARTICIPANT) -> T2  AttendeeName (T T2* T2.AttendeeNo = ATTENDEE.AttendeeNo ATTENDEE) -> T3  WorkshopNo (σ WorkshopName = ‘Normalisation 101’ (WORKSHOP)) -> A1  AttendeeNo (A1* A1.WorkshopNo = PARTICPANT.WorkshopNo PARTICIPANT) -> A2  AttendeeName (A2* A2.AttendeeNo = ATTENDEE.AttendeeNo ATTENDEE) -> A3 T3 U A3 -> SOLUTION 2ICT285 Tan Chye Hock Database 33315968 Question 1 Part h h. List the name, biography and institution of all presenters from an institution in South Australia.  InsitutionName (σ State = ‘South Australia’ (INSITUTION)) -> T1 T1* T1.InsitutionName = PRESENTER.InsitutionName PRESENTER -> T2  PresenterName, Biography, InsitutionName (T2) -> SOLUTION Question 1 Part i i. List the names of the conference attendees who did NOT participate in the workshop named ‘Relational Algebra Masterclass’.  WorkshopNo (σ WorkshopName = ‘Relational Algebra Masterclass’ (WORKSHOP)) -> T1  AttendeeNo (T1* T1.WorkshopNo = PARTICIPANT.WorkshopNo PARTICIPANT) -> T2 T2 T2.AttendeeNo = ATTENDEE.AttendeeNo ATTENDEE -> T3  AttendeeName (T3) -> T4  AttendeeName (ATTENDEE) -> A1 A1 – T4 -> SOLUTION Question 1 Part j j. List the names of attendees who went to all workshops.  AttendeeNo, AttendeeName (ATTENDEE) -> A1 A1* A1.AttendeeNo = PARTICIPANT.AttendeeNo PARTICIPANT -> A2  AttendeeName, WorkshopNo (A2) -> A3  WorkshopNo (WORKSHOP) -> T1 A3  T1 -> SOLUTION LEGEND: Symbol Description σ Restrict  Project X Cartesian Product * Join  Division U Union - Different | Minus  Intersect 3ICT285 Tan Chye Hock Database 33315968 Question 2: SQL – SELECT queries Question 2 Part a a. List the details of any work of art (including the name of the artist who created the work) that have been described as Expressionist style. SELECT WorkID, Title, Copy, Medium, Description, CONCAT (FirstName,LastName) AS ArtistName FROM WORK JOIN ARTIST ON WORK.ARTISTID=ARTIST.ARTISTID WHERE Description LIKE '%Expressionist style%'; Result: WORKID TITLE COPY MEDIUM DESCRIPTION Artist Name 588 Universal Field 114/500 High Quality Limited Print Northwest School Abstract Expressionist style Mark Tobey 587 Broadway Boggie 433/500 High Quality Limited Print Northwest School Abstract Expressionist style Mark Tobey 565 Farmer's Market #2 268/500 High Quality Limited Print Northwest School Abstract Expressionist style Mark Tobey 564 Farmer's Market #2 267/500 High Quality Limited Print Northwest School Abstract Expressionist style Mark Tobey 589 Color Floating in Time 487/500 High Quality Limited Print Northwest School Abstract Expressionist style Paul Horiuchi 566 Into Time 323/500 High Quality Limited Print Northwest School Abstract Expressionist style Paul Horiuchi 596 Surf and Bird 366/500 High Quality Limited Print Northwest School Expressionist style Morris Graves 595 Surf and Bird 365/500 High Quality Limited Print Northwest School Expressionist style Morris Graves 594 Surf and Bird 362/500 High Quality Limited Print Northwest School Expressionist style Morris Graves 578 MidCentury Hibernatio n 362/500 High Quality Limited Print Northwest School Expressionist style Morris Graves 511 Surf and Bird 142/500 High Quality Limited Print Northwest School Expressionist style Morris Graves [Show More]

Last updated: 1 year ago

Preview 1 out of 23 pages