School of Engineering: 1006 Lab Report on SQL Queries & Schema

This document presents a comprehensive lab report focusing on SQL query implementation and relational schema design within a database context. It includes a relational schema detailing tables such as REVIEWER, CUSTOMER, AUTHOR, REVIEW, WRITTEN_BY, INVENTORY, CATEGORY, WHOLESALER, BOOK, E-BOOK, E-BOOK-TYPE, ORDER, and ORDER-LINE, along with their respective fields and datatypes. The report provides SQL statements for various queries (A through L), accompanied by figures illustrating the query results. The queries cover a range of database operations, including data selection, filtering, joining tables, and aggregation. The document concludes with a list of references used for the SQL implementations and schema design. Desklib provides students access to similar solved assignments and past papers.

School of Engineering and Information Technology
COURSE / UNIT CODE: 1006 SUBMITTED TO: haidar al-khalidi,chirag tiwari
FAMILY NAME: GIVEN NAME:
PARTNER: Lab Report Assignment Other
GROUP: Date Due: Extension
Authorised:
New Due Date: Office Use Only
Date Submitted:
4/06/2017 4/06/2017
TITLE OF SUBMISSION:
Intentional plagiarism or collusion amounts to cheating
Plagiarism: Plagiarism means to take and use another person’s ideas and or manner of expressing them and to pass these off as
one’s own by failing to give appropriate acknowledgement, including the use of material from any source, staff,
students or the internet, published and unpublished works.
Collusion: Collusion means unauthorised collaboration on assessable written, oral or practical work with another person.Where
there are reasonable grounds for believing that intentional plagiarism or collusion has occurred, this will be reported
to the Associate Dean (Education) or nominee, who may disallow the work concerned by prohibiting assessment or
refer the matter to the Faculty Discipline Panel for a hearing.
Student Statement:
 I have read the university’s plagiarism policy and understand the consequences of engaging in plagiarism and
collusion.
 I have taken proper care to safeguard this work and made all reasonable efforts to ensure it could not be copied.
 No part of this assignment has been previously submitted as part of another unit/course.
 I acknowledge and agree that the assessor of this assignment may for the purposes of assessment, reproduce the
assignment and:
i. provide to another member of faculty and any external marker; and/or
ii. submit it to a text matching software; and/or I certify that I have not plagiarised the work of others or participated in unauthorised collaboration when preparing
this assignment.
Signature: Date: 4/06/2017
Comments:
GRADE:
Privacy Statement
The information on this form is collected for the primary purpose of assessing your assignment and ensuring the academic integrity requirements of the University are
met. Other purposes of collection include recording your plagiarism and collusion declaration, attending to course and administrative matters and statistical analyses. If
you choose not to complete all the questions on this form it may not be possible for University to assess your assignment. You have a right to access personal
information that the University holds about you, subject to any exceptions in relevant legislation.

School of Engineering and Information Technology
Contents
RELATIONAL SCHEMA..............................................................................................................................................3
SQL statements...............................................................................................................................................................7
Query A....................................................................................................................................................................... 7
Query B.......................................................................................................................................................................7
Query C........................................................................................................................................................................ 8
Query D....................................................................................................................................................................... 8
Query E........................................................................................................................................................................9
Query F........................................................................................................................................................................ 9
Query G....................................................................................................................................................................... 9
Query H..................................................................................................................................................................... 10
Query I....................................................................................................................................................................... 10
Query J...................................................................................................................................................................... 11
Query K......................................................................................................................................................................13
Query L...................................................................................................................................................................... 13
References.....................................................................................................................................................................14
List of Figures:
Figure 1 Query A............................................................................................................................................................7
Figure 2 Query B.............................................................................................................................................................7
Figure 3 Query C.............................................................................................................................................................8
Figure 4 Query D............................................................................................................................................................8
Figure 5 Query E.............................................................................................................................................................9
Figure 6 Query F.............................................................................................................................................................9
Figure 7 Query G............................................................................................................................................................9
Figure 8 Query H..........................................................................................................................................................10
Figure 9 Query I............................................................................................................................................................10
Figure 10 query J part1..................................................................................................................................................11
Figure 11 query J part2..................................................................................................................................................11
Figure 12 query J part3..................................................................................................................................................12
Figure 13 query J part4..................................................................................................................................................12
Figure 14 Query K........................................................................................................................................................13
Figure 15 Query L.........................................................................................................................................................13

School of Engineering and Information Technology
RELATIONAL SCHEMA
TABLE NAME FIELDS DATATYPES DESCRIPTION
REVIEWER reviewerID INT(50) PRIMARY KEY (Auto
Increment)
name Text
email VARCHAR(400)
avatar Int(50)
customer Int(50) FOREIGN KEY References
CUSTOMER(CUSTOMERID)
TABLE NAME FIELDS DATATYPES DESCRIPTION
CUSTOMER customerID INT PRIMARY KEY
(Auto Increment)
firstName Text
lastName Text
phone VARCHAR(400)
email VARCHAR(400)
address VARCHAR(400)
suburb Text
postcode INT(50)
loyaltyDiscount VARCHAR(400)
TABLE NAME FIELDS DATATYPES DESCRIPTION
AUTHOR authorID INT(50) PRIMARY
KEY( Auto
Increment)
name TEXT
country TEXT
bio VARCHAR(400)

School of Engineering and Information Technology
TABLE NAME FIELDS DATATYPES DESCRIPTION
REVIEW reviewerID INT(50) FOREIGN KEY References
REVIEWER(REVIEWERID)
inventoryID INT(50) FOREIGN KEY References
INVENTORY(INVENTORYID
reviewText VARCHAR(400)
reviewDate DATE
TABLE NAME FIELDS DATATYPES DESCRIPTION
WRITTEN_BY authorID INT(50) FOREIGN KEY References
AUTHOR(AUTHORID)
inventoryID INT(50) FOREIGN KEY References
INVENTORY(INVENTORYID)
Role VARCHAR(400)
TABLE NAME FIELDS DATAYPES DESCRIPTION
INVENTORY inventoryID INT(50) PRIMARY KEY (Auto
Increment)
category INT(50) FOREIGN KEY References
CATEGORY(CATEGORYID)
Title VARCHAR(400)
abstract VARCHAR(400)
yearPublished INT(50)
unitPrice VARCHAR(400)
TABLE NAME FIELDS DATATYPES DESCRIPTION
CATEGORY categoryID INT(50) PRIMARY KEY
( Auto Increment)

School of Engineering and Information Technology
name TEXT
description VARCHAR(400)
TABLE NAME FIELDS DATATYPES DESCRIPTION
WHOLESALER wholesalerID INT(50) PRIMARY KEY ( Auto
Increment)
name TEXT
contactName TEXT
phone VARCHAR(400)
email VARCHAR(400)
address VARCHAR(400)
suburb VARCHAR(400)
postcode INT(50)
country TEXT
comments VARCHAR(400)
TABLE NAME FIELDS DATATYPES DESCRIPTION
BOOK inventoryID INT(50) FOREIGN KEY
References
INVENTORY(INVENT
ORYID)
wholesaler INT(50)
qtyInStock VARCHAR(400)
TABLE NAME FIELDS DATATYPES DESCRIPTION
E-BOOK inventoryID INT(50) FOREIGN KEY
References
INVENTORY(INVENT
ORYID)
EBookType INT(50) FOREIGN KEY
References E-BOOK-
TYPE(EBOOKTYPEID

School of Engineering and Information Technology
)
TABLE NAME FIELDS DATATYPES DESCRIPTION
E-BOOK-TYPE ebookTypeID INT(50) PRIMARY KEY
( Auto Increment)
fileType VARCHAR(400)
description VARCHAR(400)
TABLE NAME FIELDS DATATYPES DESCRIPTION
ORDER-LINE orderID INT(50) FOREIGN KEY
References
ORDER(ORDERID)
inventoryID INT(50) FOREIGN KEY
References
INVENTORY(INVENT
ORYID)
quantity INT(50)
amount VARCHAR(400)
TABLE NAME FIELDS DATATYPES DESCRIPTION
ORDER orderID INT(50) PRIMARY KEY
( Auto Increment)
customerID INT(50)
dateOrdered DATE
dateFulfilled DATE
postageAmount VARCHAR(400)
discountApplied VARCHAR(400)

School of Engineering and Information Technology
SQL statements
Query A
Figure 1 Query A
Query B

School of Engineering and Information Technology
Figure 2 Query B
Query C
Figure 3 Query C
Query D

School of Engineering and Information Technology
Figure 4 Query D

School of Engineering and Information Technology
Query E
Figure 5 Query E
Query F
Figure 6 Query F
Query G

School of Engineering and Information Technology
Figure 7 Query G
Query H
Figure 8 Query H
Query I
Figure 9 Query I

School of Engineering and Information Technology
Query J
Figure 10 query J part1
Figure 11 query J part2