Database Development and Management - SQL

Running head: DATABASE DEVELOPMENT AND MANAGEMENT
DATABASE DEVELOPMENT AND MANAGEMENT
Name of the Student
Name of the University
Author Note

DATABASE DEVELOPMENT AND MANAGEMENT
1
Table of Contents
Relational algebra:.....................................................................................................................2
SQL queries:...............................................................................................................................3
Further SQL...............................................................................................................................8
Normalization:..........................................................................................................................10

DATABASE DEVELOPMENT AND MANAGEMENT
2
Relational algebra:
a.
 LecturerName, UniversityName (LECTURER)
b.
 LectureName (σ Topic = ‘Biological Diversity’ (LECTURE))
c.
 StudentName (σ Topic = ‘Biological Diversity’ or Topic = ‘Bugs and Bubs’ (UNIVERSITY * U.UniversityName =
S.UniversityName STUDENT) * (U.UniversityName = LC.UniversityName LECTURER) * LC.LecturerName = L. LecturerName
LECTURE)))
d.
 StudentName (σ Country = ‘Malaysia’ (UNIVERSITY * U.UniversityName = S.UniversityName STUDENT)*
U.UniversityName = L.UniversityName LECTURER)
e.
 StudentName (σ UniversityName = ‘Murdoch University’, AND country = ‘Italy’ AND Topic = ‘Biological Diversity’
(UNIVERSITY * U.UniversityName = S.UniversityName STUDENT) * (U.UniversityName = LC.UniversityName
LECTURE) * LC.LecturerName = L. LecturerName LECTURE)))
f.
 LectureName, LectureNo, Topic, StudentName (UNIVERSITY * U.UniversityName = S.UniversityName STUDENT)
* (UNIVERSITY U.UniversityName = LC.UniversityName LECTURER) * LECTURER LC.LecturerName = L.
LecturerName LECTURE)))
g.

DATABASE DEVELOPMENT AND MANAGEMENT
3
 StudentName (σ LectureName = ‘Introduction to Biology’ AND LectureName = ‘Darwin 101’ (UNIVERSITY *
U.UniversityName = S.UniversityName STUDENT) * (UNIVERSITY U.UniversityName = LC.UniversityName
LECTURER) * LECTURER LC.LecturerName = L. LecturerName LECTURE)))
h.
 LecturerName, Biography, UniversityName (σ Country = ‘Greece’ (UNIVERSITY * U. UniversityName = LC.
UniversityName LECTURER))
i.
 StudentName (STUDENT) MINUS ( StudentName (σ LectureName = ‘Grassland Cultivation Masterclass’
(UNIVERSITY * U.UniversityName = S.UniversityName STUDENT) * (UNIVERSITY U.UniversityName =
LC.UniversityName LECTURER) * LECTURER LC.LecturerName = L. LecturerName LECTURE))))
j.
 StudentName (STUDENT S.StudentNo = P.StudentNo PARTICIAPANT)
SQL queries:
A. select w.workid, w.title,w.copy,w.medium, w.description, w.artistid, (FIRSTNAME ||
' ' || LASTNAME) AS ARTISTNAME from dtoohey.work w
JOIN DTOOHEY.artist a on w.artistid =a.artistid WHERE w.description LIKE
'%Surrealist%';
B. select w.workid, w.title, w.copy, w.description, (FIRSTNAME || ' ' || LASTNAME)
AS ARTISTNAME ,
t.AcquisitionPrice,t.AskingPrice from dtoohey.work w