HS2021 Database Design and Use Individual Assignment Solution

This assignment solution focuses on database design and implementation using SQL. It begins with describing the relationships within an Entity-Relationship Diagram (ERD) involving tables like CUSTOMER, SALESPERSON, SHOPORDER, PRODUCT, and ORDERLINE. The solution includes SQL statements for dropping and creating these tables, defining primary and foreign keys, and incorporating check constraints. Data insertion is demonstrated with sample values, and the solution addresses constraint errors. The assignment then explores database queries, including counting total and female orders, grouping data by gender, and ordering results. Additional tables, such as MANAGER, QUALITYCHECK, and ALLOCATION, are created and populated. The solution covers various SQL queries, including those involving joins, aggregations, and subqueries to retrieve specific information, such as price differences, combined names, and addresses. The assignment concludes with queries using left outer joins, grouping by customer and product, and filtering based on specific criteria like the total sold quantity and average quality scores. A bibliography of relevant database systems resources is also provided.

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

1
DATABASE DESIGN
1.1 Relationships
There are five tables in the mentioned ERD: ‘CUSTOMER’, ‘SALESPERSON’,
‘SHOPORDER’, ‘PRODUCT’ and ‘ORDERLINE’. Each table has appropriate attributes
with one primary key in it.
The ‘CUSTOMER’ table has these following attributes: ‘custid’ (primary key),
‘cfirstname’, ’csurname’, ‘cgender’, ‘billingaddr’. For ‘SALESPERSON’ table the attributes
are: ‘spid’ (primary key), ‘spfirstname’, ‘spsurname’, ‘spgender’. For ‘SHOPORDER’ table:
‘ordid’ (primary key), ‘custid’ (as a foreign key), ‘spid’ (as a foreign key), ‘deliveryaddress’.
For ‘PRODUCT’ table ‘prodid’ (primary key), ‘prodname’, ‘currentprice’. For
‘ORDERLINE’ table: ‘ordlineno’ (primary key), ‘prodid’ (as a foreign key), ‘qtysold’,
‘qtydelivered’, ‘salesprice’.
The SALESPERSON table has the data of customers and salespersons as there are
foreign keys of respective table contained in it. This table also contains the details of order as
it connected to the table ORDERLINE. The PRODUCT table fetches the data of the
PRODUCT table as it contains the primary key of PRODUCT table as a foreign key.
--ONE Customer MUST have ONE unique id.
--ONE Customer can have MULTIPLE orders.
--ONE Salesperson can deliver MULTIPLE orders.
--MULTIPLE products can be ordered.
--ONE manager can check MULTIPLE products

2
DATABASE DESIGN
--checking ONCE A WEEK
1.2 Statements for Drop Tables
drop table customer cascade constraints;
drop table salesperson cascade constraints;
drop table shoporder cascade constraints;
drop table orderline cascade constraints;
drop table product cascade constraints;
drop table qualitycheck cascade constraints;
drop table manager cascade constraints;
drop table allocation cascade constraints;
1.1. Create Tables
FOR CUSTOMER:
create table customer (custid number(4) primary key, cfirstname varchar(30) not null,
csurname varchar(30) not null,
cgender varchar(1) check (cgender in ('M', 'F')), billingaddress varchar(30) not null);
FOR SALESPERSON

3
DATABASE DESIGN
create table salesperson( spid number(4) primary key, spfirstname varchar(30) not null,
spsurname varchar(30) not null, spgender varchar(1) check(spgender in ('M','F'))) ;
FOR SHOPORDER:
create table shoporder(orderid number(4) not null, deliveryaddress varchar(30), custid
number(4) not null, spid number(4) not null, primary key (orderid, custid, spid),
constraint fk_customer FOREIGN KEY (custid) REFERENCES customer(custid),
constraint fk_salesperson FOREIGN KEY (spid) REFERENCES salesperson(spid));
FOR PRODUCT:
create table product(prodid number(4) primary key, prodname varchar(30) not null,
currentprice number(6,2) not null check(currentprice <=5000 and currentprice >=0));
FOR ORDERLINE:
create table orderline (
qtysold number(4) not null check(qtysold >=0 and qtysold <=99),
qtydelivered number(4) not null check(qtydelivered >=0 and qtydelivered <=99),
salesprice number(6,2) not null check(salesprice <=5000 and salesprice >=0),
orderid number(4) not null, prodid number(4) not null, custid number(4), spid number(4),
primary key(orderid, prodid,custid,spid),

4
DATABASE DESIGN
constraint fk_shoporder foreign key (orderid, custid, spid) REFERENCES
shoporder(orderid,custid,spid),
constraint fk_product FOREIGN KEY (prodid) REFERENCES product(prodid));
1.2. Insert Customers
VALUES FOR CUSTOMER:
insert into CUSTOMER values( 1, 'Casey', 'CartWright','F','1 High St Kew');
insert into CUSTOMER values( 2, 'Evan', 'Chambers','M','8 Red St Rye');
insert into CUSTOMER values( 3, 'Calvin', 'Owens','M','7 Long Rd Lara');
insert into CUSTOMER values( 4, 'Frannie', 'Morgan','F','9 Down Pde Upwey');
insert into CUSTOMER values( 5, 'Cappie', 'Jones','M','Mist St Toorak');
insert into CUSTOMER values( 6, 'Dana', 'Stockwell','F','2 Tree St Epping');
insert into CUSTOMER values( 7, 'Ash', 'Howard','F','4 Elm Ave Elwood');
select * from CUSTOMER ORDER BY custid asc;

5
DATABASE DESIGN
1.3. Check constraint error
insert into CUSTOMER values( 8, 'Million', 'Hastings','X','3 Blue St, Kew');
1.6 Insert Products
insert into PRODUCT values(31, 'Lounge Chair',799);
insert into PRODUCT values(32, 'Study Lamp',150);
insert into PRODUCT values(33, 'Large Desk',550);
insert into PRODUCT values(34, 'Hallway Table',1200);
insert into PRODUCT values(35, 'Kitchen Stool',220);
insert into PRODUCT values(36, 'Lamp Stand (Tall)',189);
insert into PRODUCT values(37, 'Zzz King Size Single Bed',400);
insert into PRODUCT values(38, 'Beside Lamp',99);
insert into PRODUCT values(39, 'Coffee table',650);
1.7. Check constraint error
insert into PRODUCT values(40, 'Cupboard',-99);
insert into PRODUCT values(41, 'Bookcase',8765);

6
DATABASE DESIGN
1.8. Insert Salespersons
insert into SALESPERSON values(21, 'Serena',' Van der Woodsen','F');
insert into SALESPERSON values(22, 'Dan',' Humphrey','M');
insert into SALESPERSON values(23, 'Blair',' Waldorf','F');
insert into SALESPERSON values(24, 'Chuck',' Bass','M');
insert into SALESPERSON values(25, 'Lily',' Van der Woodsen','F');
insert into SALESPERSON values(26, 'Nate','Archibald','M');
1.9. Insert Shop Orders
insert into SHOPORDER values(41, 1, 23, NULL);
insert into SHOPORDER values(51, 1, 23, NULL);
insert into SHOPORDER values(42, 2, 21, NULL);

7
DATABASE DESIGN
insert into SHOPORDER values(43, 2, 23, NULL);
insert into SHOPORDER values(49, 2, 24, NULL);
insert into SHOPORDER values(44, 3, 26, '1 John St Hawthorn');
insert into SHOPORDER values(48, 3, 26, NULL);
insert into SHOPORDER values(45, 4, 22, '1254 Dunstall Rd Coorparoo ');
insert into SHOPORDER values(47, 4, 26, '727 Hudson Rd Glenorchy ');
insert into SHOPORDER values(50, 4, 22, '517 Franklin St Dowerin');

8
DATABASE DESIGN
insert into SHOPORDER values(46, 6, 21, NULL);
1.10. FK Error
insert into SHOPORDER values(66, 5, 29, NULL);
insert into SHOPORDER values(67, 9, 26, NULL);
1.11. Insert Oder Lines
insert into orderline values(2,0,750,41,31,1,23);

9
DATABASE DESIGN
insert into orderline values(1,0,170,41,36,1,23);
insert into orderline values(1,1,500,42,33,2,21);
insert into orderline values(6,5,220,43,35,2,23);
insert into orderline values(1,0,760,44,31,3,26);
insert into orderline values(1,0,1100,44,34,3,26);
insert into orderline values(1,0,180,44,36,3,26);
insert into orderline values(2,2,175,45,36,4,22);

10
DATABASE DESIGN
insert into orderline values(2,2,380,46,37,6,21);
insert into orderline values(2,2,90,46,38,6,21);
insert into orderline values(1,0,600,47,39,4,26);
insert into orderline values(1,0,200,47,35,4,26);
insert into orderline values(2,0,175,47,36,4,26);
insert into orderline values(1,0,85,47,38,4,26);
insert into orderline values(2,0,750,48,34,3,26);

11
DATABASE DESIGN
insert into orderline values(1,1,1200,49,31,2,24);
insert into orderline values(2,0,750,50,31,4,22);
insert into orderline values(1,0,180,50,36,4,22);
insert into orderline values(10,10,190,51,35,1,23);
1.12. FK errors
insert into orderline values(2,2,200,49,40,1,23);
insert into orderline values(10,10,175,52,36,1,23);