Semester 1 2020 CIS 2002 Assignment: Database Design and Queries

This document presents a complete solution to a database design assignment, addressing both SQL queries and data modeling aspects. Section B provides SQL queries for the JustLee Books database, covering topics like customer analysis, order processing, book information retrieval, and profit calculations. Section C focuses on data modeling, with Entity-Relationship (ER) diagrams and lists of relations for various scenarios, including member sessions, pilot assignments, mattress allocation, and tenant storage spaces. The solution adheres to the USQ data modeling and normalization methodology, providing a comprehensive guide for database design principles. The assignment includes detailed SQL queries and ER diagrams, offering a clear and structured approach to database design and implementation.

Running head: DATABASE DESIGN
DATABASE DESIGN
Name of the Student
Name of the University
Author Note

1
DATABASE DESIGN
Table of Contents
Section B –Queries..........................................................................................................................2
Section C – Data Modelling............................................................................................................4
Question 1....................................................................................................................................4
Question 2....................................................................................................................................4
Question 3....................................................................................................................................5
Question 4....................................................................................................................................6
Bibliography....................................................................................................................................7

2
DATABASE DESIGN
Section B –Queries
1. Write an SQL query that displays total number of customers (rename the field ‘Total
Customers’), number of customers without referrals (rename the field ‘Direct Customers’), and
number of referred customers (rename the field ‘Referred Customers’).
SELECT COUNT(customer#) AS "Total Customers" , SUM(CASE
WHEN referred IS NULL THEN 1 ELSE 0 END) AS "Direct
Customers", SUM(CASE WHEN referred IS NOT NULL THEN 1 ELSE
0 END) AS "Referred Customers" FROM customers;
2. Write a SQL query that displays customer number, last name, first name, and city for all
customers who reside in Florida and who have not been referred by another customer.
SELECT Customer#, Lastname, Firstname, City FROM customers
WHERE city='FLORIDA' AND referred IS NULL;
3. Display the full name of all the customers in a single column (by concatenating first name
followed by a comma then a space then last name), rename the filed ‘Customers’, region and
referred for all customers who live in any western region (that is in any region in the western
sector ‘W’, ‘SW’, ‘NW’) and who have been referred by another customer.
SELECT lastname||', '||firstname AS CUSTOMERS, Region,
referred FROM customers
WHERE region LIKE '%W%' AND referred IS NOT NULL;

3
DATABASE DESIGN
4. List the customer number, first name, last name, order number and order date for all orders
which have not been shipped. Order the resulting set by the order date in ascending order.
SELECT c.Customer#, c.Firstname, c.Lastname, o.order#,
o.orderdate FROM customers c
INNER JOIN orders o ON o.customer#=c.customer#
AND shipdate IS NULL ORDER BY o.orderdate ASC;
5. Display the ISBN, title, retail, difference between the cost and retail (rename the column
‘PROFIT’), difference between cost and retail minus the discount (rename the field ‘ACTUAL
PROFIT’) for books in the BOOKS table where the book has a discount and where the discount
is less than the difference between the cost and retail price of the book.
SELECT isbn, title, retail, (retail-cost) AS profit,
(retail-cost-NVL(discount,0)) AS "ACTUAL PROFIT" FROM
books
WHERE discount IS NOT NULL AND discount<(retail-cost);
6. Display the book title, cost, retail and calculate the profit and name the new field profit for all
the books where the profit from the sale of the book is more than 65% of the cost of the book.
SELECT title, cost, retail, (retail-cost) AS PROFIT FROM
books
WHERE (retail-cost)>(.65*cost);

4
DATABASE DESIGN
7. Display the ISBN, quantity, price paid for the book (rename the column ‘Paid Each’), and a
created field ‘Total Order Price’ (i.e. quantity * ‘Paid Each’) for all orders in the ORDERITEMS
table whose quantity is greater than 1. Order the list by ‘Total Order Price’ descending.
SELECT isbn, quantity, paideach AS "PAID EACH",
(quantity*paideach) AS
"Total Order Price" FROM orderitems
WHERE quantity>1 ORDER BY (quantity*paideach) DESC;
8. Display publisher name, book title and retail price for all the books after subtracting any
available discount for that book, only for those books that have a discount. Name the new field
‘DISCOUNTED PRICE’ and order by publisher name ascending and discounted price of the
book in descending order.
SELECT p.name, b.title, b.retail, ((b.retail)-
NVL(discount, 0))
AS "Discounted Price" FROM books b INNER JOIN publisher p
ON
p.pubid=b.pubid and b.discount IS NOT NULL
ORDER BY p.name ASC, ((b.retail)-nvl(discount, 0)) DESC;
9. List ISBN, title, retail, category and published date of all the books that have the (category of
‘COMPUTER’ or where the category contains the string ‘FAMILY’ anywhere in the category
description) and where (price of the books is more than 20 dollars and where the published date
is before the end of the financial year for 2003/2004 – i.e. 30 June 2004).

5
DATABASE DESIGN
SELECT isbn, title, retail,category, pubdate FROM books
WHERE
(category LIKE '%COMPUTER%' OR
category LIKE '%FAMILY%') AND retail>20
AND pubdate >'30-JUN-04';
10. Display the customer number, customer last name and city, customer zip code (rename the
field ‘Customer ZIP’) and referrer zip code (rename the field ‘Referrer ZIP’) for all the
customers who have been referred by a referrer with the same last name as the customer.
select m.Customer#, m.Lastname, m.city, m.zip
AS "Customer ZIP" , s.zip as "Referrer Zip"
FROM customers m inner join customers s on
m.customer#=s.referred and m.lastname=s.lastname;
Section C – Data Modelling
Question 1
ER Diagram
A List of Relations

6
DATABASE DESIGN
Member (Member#, Name, Address, ContactNumber, EmailAddresss, DOB)
Sessions (Booking#, Date, Time, Duration, Fees, TrainerID#, Member#)
Trainer (TrainerID#, Name, ContactNumber, Qualifications)
Question 2
ER Diagram
A List of Relations
Pilot (Pilot#, Name, DOB, FlyingLicenseNumber)
Assigned (Pilot#, FlightCode#, Role, TotaKilometers, TotalHours)
Flights (FlightCode#, Origin, Destination, Type)
Question 3
ER Diagram

7
DATABASE DESIGN
A List of Relations
Category (Category#, Name)
Mattress (ID#, BrandName, Category#)
Allocated (Allocation#, ConformRating, RatingDate, Brand#, ID#)
Bed (Brand#, Name, Size, Length, Width, Manufacturer#)
Manufacturer (Manufacturer#, Name, Description)
Question 4
ER Diagram
A List of Relations
Tenant (Tenant#, Firstname, Lastname, Contact, ApartmentNumber)
StorageSpaces (Space#, length, Width, Height, Status, Tenant#)

8
DATABASE DESIGN
Bibliography
Davis, W.S. and Yen, D.C. eds., 2019. The information system consultant's handbook: Systems
analysis and design. CRC press.
De Haan, L., Gorman, T., Jørgensen, I. and Caffrey, M., 2014. Beginning Oracle SQL. Apress.