Database Design | Assignment

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');