RELATIONAL ALGEBRA DATABASES

Running head: ICT285 DATABASES
ICT285 Databases
Name of the Student
Name of the University
Author’s note:

ICT285 DATABASES1
Table of Contents
Question 1: Relational algebra.........................................................................................................2
Question 2: SQL – SELECT queries...............................................................................................3
Question 3: Further SQL.................................................................................................................7
Question 4: Normalization...............................................................................................................9
Question 5: Conceptual Design.....................................................................................................11
Bibliography:.................................................................................................................................13

ICT285 DATABASES2
Question 1: Relational algebra
a) LecturerName, UniversityName (LECTURER*LECTURER.UniversityName=UNIVERSITY.UniversityName
UNIVERSITY)
b) LecturerName, Description (Topicc=“Biological Diversity” (LECTURER*LECTURER.LecturerName =
LECTURE.LecturerName LECTURE))
c) StudentName (Topicc=“Biological Diversity” OR Topic=“Bugs and Bubs” (STUDENT*Student.StudentNo = PARTICIPANT.
StudentNo PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE)
d) StudentName (Country =“Malaysia” (((STUDENT*Student.StudentNo = PARTICIPANT. StudentNo
PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE)*LECTURE.LecturerName= LECTURER. LecturerName
LECTURER)* LECTURER .UniversityName =UNIVERSITY.UniversityName UNIVERSITY)
e) StudentName (STUDENT.UniversityName =“Murdoch University” AND Topic=“Biological Diversity”
(((STUDENT*Student.StudentNo = PARTICIPANT. StudentNo PARTICIPANT) *PARTICIPANT.LectureNo=LECTURE.LectureNo
LECTURE) *LECTURE.LecturerName= LECTURER. LecturerName LECTURER)* LECTURER .UniversityName
=UNIVERSITY.UniversityName AND country = “Italy” UNIVERSITY)
f) LectureNo, LectureName, Topic, StudentName ((STUDENT*Student.StudentNo = PARTICIPANT. StudentNo
PARTICIPANT) RIGHT JOIN PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE)
g) StudentName (Topicc=“Introduction to Biology” AND Topic=“Darwin 101” ((STUDENT*Student.StudentNo =
PARTICIPANT. StudentNo PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE))
h)  LecturerName, Biography, UniversityName (Country=“Greece” (LECTURER*LECTURER.UniversityName =
UNIVERSITY.UniversityName UNIVERSITY))

ICT285 DATABASES3
i) StudentName (STUDENT)
MINUS
StudentName (Topicc=“Grassland Cultivation Masterclass” ((STUDENT*Student.StudentNo = PARTICIPANT. StudentNo
PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE))
j) StudentName ((count(distinct PARTICIPANT.LectureNo)= (select count(*) from LECTURE) (STUDENT*Student.StudentNo =
PARTICIPANT. StudentNo PARTICIPANT)*PARTICIPANT.LectureNo=LECTURE.LectureNo LECTURE)
Question 2: SQL – SELECT queries
Query 1
Select WORK.*, ARTIST.Name from WORK
inner join ARTIST
on WORK.ArtistID = ARTIST.artistid
where WORK.Description like '%Surrealist%';
Query 2
Select WORK.*, ARTIST.Name from WORK
inner join ARTIST
on WORK.ArtistID = ARTIST.artistid
inner join TRANSACTION
on WORK.WorkID = TRANSACTION.WorkID