Report on Relational Database Principles and Designing Of SQL

Running Head: RELATIONAL DATABASE PRINCIPLES AND DESIGNING USING SQL 1
Relational Database Principles and Designing Of SQL Database
[student Name]
[university]
Authors’ Note
The principals of designing the relational databases using SQL.

2RELATIONAL DATABASE PRINCIPLES AND DESIGNING USING SQL
Table of Contents
Question 1 Relational algebra.............................................................................................. 3
Question 2 SELECT queries............................................................................................... 3
Question 3 Further SQL................................................................................................... 13
a. The SQL to create the VENUE table .........................................................................13
b. The SQL to create the EVENT table..........................................................................15
c. The SQL to add the Maracana Stadium to the VENUE table.............................................17
d. The SQL to add an attribute Sport to EVENT ...............................................................18
e. The SQL to record Maracana Stadium capacity of 90,000..............................................19
Question 4 Normalisation................................................................................................. 20
a. The problems with the existing design.......................................................................20
b. Currently relation normal form................................................................................ 20
c. Convert the relation to a set of relations in at least Third Normal Form (3NF)......................21
d. How the new design addresses the problems identified in (a)...........................................29
Question 5 Conceptual Design........................................................................................... 30
a. The entity-relationship diagram (ERD) for the FastFoods4U database...............................30
b. List of assumptions made in creating the data model......................................................31
References................................................................................................................... 32

3RELATIONAL DATABASE PRINCIPLES AND DESIGNING USING SQL
Question 1 Relational algebra.
a. π AttendeeName,, InstitutionName(Ϭ ATTENDEE)
b. π WorkshopName, , Capacity, Theme=‘Virtual Reality’ (Ϭ WORKSHOP).
c. π AttendeeName, Theme =‘Virtual Reality’ and Theme= ‘Big Data’ or Theme= ‘Big Data’ (Ϭ ATTENDEE x
WORKSHOP x PARTICIPANT)
d. π AttendeeName,, AttendeeNo= AttendeeNo, WorkshopNo= WorkshopNo, and InstitutionName= InstitutionName State=’ Queensland’(Ϭ
ATTENDEE x WORKSHOP x INSTITUTION x PARTICIPANT x
PRESENTER)
e. π AttendeeName,, AttendeeNo= AttendeeNo, WorkshopNo= WorkshopNo, and InstitutionName= InstitutionName,
InstitutionName =‘Murdoch University’, theme=’ Green IT’ , state =‘ Tasmania’(Ϭ ATTENDEE x WORKSHOP x
INSTITUTION x PARTICIPANT x PRESENTER)
f. π WorkshopNo,WorkshopName, Theme, AttendeeName AttendeeNo= AttendeeNo, WorkshopNo= WorkshopNo (Ϭ
ATTENDEE x WORKSHOP x PARTICIPANT).
g. π AttendeeName AttendeeNo= AttendeeNo, WorkshopNo= WorkshopNo , WorkshopName=’ Introduction to Oracle ‘ and WorkshopName=
‘
Normalisation 101 ‘ (Ϭ ATTENDEE x WORKSHOP x PARTICIPANT).
h. π PresenterName, Biography, InstitutionName ,InstitutionName=InstitutionName and State)='South Australia' (Ϭ PRESENTER
x INSTITUTION)
i. π AttendeeName, AttendeeNo= AttendeeNo, WorkshopNo= WorkshopNo ,WorkshopName !=‘ Relational Algebra Masterclass’ (Ϭ
ATTENDEE x WORKSHOP x PARTICIPANT)
j. π AttendeeName, AttendeeNo= AttendeeNo, WorkshopNo= WorkshopNo (Ϭ ATTENDEE x WORKSHOP x
PARTICIPANT).

4RELATIONAL DATABASE PRINCIPLES AND DESIGNING USING SQL
Question 2 SELECT queries.
A. The details of all works of art that are expressionist style
.
QUERY
SELECT w.*,a.LASTNAME, a.FIRSTNAME FROM WORK w INNER JOIN ARTIST a ON
w.ARTISTID=a.ARTISTID WHERE w.DESCRIPTION LIKE '%expressionist style%';
RESULTS
B. List the details of any work of art currently held in the gallery
QUERY
SELECT t.ACQUISATIONPRICE,t.ASKINGPRICE,aw.* FROM TRANS t LEFT JOIN
(SELECT w.*,a.LASTNAME, a.FIRSTNAME FROM WORK w INNER JOIN ARTIST a ON
w.ARTISTID=a.ARTISTID) aw ON t.WORKID = aw.WORKID ;
RESULTS

5RELATIONAL DATABASE PRINCIPLES AND DESIGNING USING SQL
C. The list of any work of art that has only one copy recorded in the
database .
QUERY
Select title,count(title) from work group by title;
RESULTS

6RELATIONAL DATABASE PRINCIPLES AND DESIGNING USING SQL
D. The names of all deceased artists and the age they were when they
died.
QUERY
SELECT FIRSTNAME,LASTNAME,(DATEDECEASED-DATEOFBIRTH)AS AGE FROM
ARTIST WHERE DATEDECEASED>0;
RESULTS