Database Design and Implementation PDF

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation
Name of the Student
Name of the University
Author’s note

1DATABASE DESIGN AND IMPLEMENTATION
Physical Design:
Figure 1: Physical Design
(Source: Created by Author)
SQL Queries:
CREATE TABLE `client` (
`cid` int(11) NOT NULL,
`forename` varchar(200) NOT NULL,

2DATABASE DESIGN AND IMPLEMENTATION
`surname` varchar(200) NOT NULL,
`addressL1` varchar(200) NOT NULL,
`addressL2` varchar(200) DEFAULT NULL,
`townCity` char(20) NOT NULL,
`postcode` char(11) NOT NULL,
`phone` char(15) NOT NULL
);
INSERT INTO `client` (`cid`, `forename`, `surname`, `addressL1`,
`addressL2`, `townCity`, `postcode`, `phone`) VALUES
(1, 'Jonathan', 'Hobbs', '76 Bishopgate Street', NULL, 'SEAVINGTON ST
MICHAE', 'TA19 2XY', '077 3282 9072');
CREATE TABLE `orders` (
`oid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
`dateOrdered` date NOT NULL,
`dateShipped` date DEFAULT NULL,
`paymentMethod` char(10) NOT NULL
);

3DATABASE DESIGN AND IMPLEMENTATION
INSERT INTO `orders` (`oid`, `cid`, `dateOrdered`, `dateShipped`,
`paymentMethod`) VALUES
(1, 1, '2020-03-02', NULL, 'Cash');
CREATE TABLE `product` (
`pid` int(11) NOT NULL,
`description` text NOT NULL,
`saleCost` decimal(10,2) NOT NULL,
`quantityRemaining` int(11) NOT NULL
);
INSERT INTO `product` (`pid`, `description`, `saleCost`,
`quantityRemaining`) VALUES
(1, 'This classic two-button suit in black twill is an all rounder,
perfect for business or pleasure.\r\n\r\nThe single-breasted jacket, with
notch lapels, is fully lined with straight jetted pockets. The buttoned waist
suit trousers are half-lined to the knee and include slanted hip pockets, belt
loops and button fastened jetted back pockets.\r\n\r\nYou can also choose to
add the matching single-breasted waistcoat which features jetted pockets plus
a breast pocket and benefits from an adjustable back and full lining.\r\n\r\
nAvailable in chest sizes 34 to 54, waist 28 to 36 and short, regular or long
length.', '119.99', 15);
CREATE TABLE `productonorder` (

4DATABASE DESIGN AND IMPLEMENTATION
`oid` int(11) NOT NULL,
`pid` int(11) NOT NULL,
`quantityOrdere` int(11) NOT NULL
);
INSERT INTO `productonorder` (`oid`, `pid`, `quantityOrdere`) VALUES
(1, 1, 1);
CREATE TABLE `supplier` (
`sid` int(11) NOT NULL,
`name` varchar(200) NOT NULL,
`addressL1` varchar(200) NOT NULL,
`addressL2` varchar(200) NOT NULL,
`townCity` char(20) NOT NULL,
`postcode` int(11) NOT NULL,
`email` varchar(200) NOT NULL,
`phone` char(15) NOT NULL,
`creditAmount` decimal(10,2) NOT NULL
);

5DATABASE DESIGN AND IMPLEMENTATION
INSERT INTO `supplier` (`sid`, `name`, `addressL1`, `addressL2`,
`townCity`, `postcode`, `email`, `phone`, `creditAmount`) VALUES
(1, 'Custom Suit Liverpool', '76 Boat Lane', 'RESOLVEN', 'Liverpool',
1234, '[e-mail]', '078 4332 4070', '1000.00');
CREATE TABLE `supplierofproduct` (
`sid` int(11) NOT NULL,
`pid` int(11) NOT NULL,
`unitCost` decimal(10,2) NOT NULL,
`dateSupplied` date NOT NULL,
`quantitySupplied` int(11) NOT NULL
);
INSERT INTO `supplierofproduct` (`sid`, `pid`, `unitCost`,
`dateSupplied`, `quantitySupplied`) VALUES
(1, 1, '100.00', '2020-03-01', 50);
ALTER TABLE `client`
ADD PRIMARY KEY (`cid`);
ALTER TABLE `orders`
ADD PRIMARY KEY (`oid`),

6DATABASE DESIGN AND IMPLEMENTATION
ADD KEY `cid` (`cid`);
ALTER TABLE `product`
ADD PRIMARY KEY (`pid`);
ALTER TABLE `productonorder`
ADD PRIMARY KEY (`oid`,`pid`),
ADD KEY `pid` (`pid`);
ALTER TABLE `supplier`
ADD PRIMARY KEY (`sid`);
ALTER TABLE `supplierofproduct`
ADD PRIMARY KEY (`sid`,`pid`),
ADD KEY `pid` (`pid`);
ALTER TABLE `orders`
ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `client`
(`cid`);
ALTER TABLE `productonorder`

7DATABASE DESIGN AND IMPLEMENTATION
ADD CONSTRAINT `productonorder_ibfk_1` FOREIGN KEY (`oid`) REFERENCES
`orders` (`oid`),
ADD CONSTRAINT `productonorder_ibfk_2` FOREIGN KEY (`pid`) REFERENCES
`product` (`pid`);
ALTER TABLE `supplierofproduct`
ADD CONSTRAINT `supplierofproduct_ibfk_1` FOREIGN KEY (`sid`)
REFERENCES `supplier` (`sid`),
ADD CONSTRAINT `supplierofproduct_ibfk_2` FOREIGN KEY (`pid`)
REFERENCES `product` (`pid`);

8DATABASE DESIGN AND IMPLEMENTATION
Bibliography:
Watts, J., 2018. Best Practice: Working with External SQL Database.