Database Design and Implementation - University Portfolio Assessment

This assignment solution focuses on database design and implementation, specifically addressing a portfolio assessment. The solution includes a physical design diagram and a series of SQL queries for creating and populating database tables. The database schema encompasses tables for clients, products, orders, suppliers, and their relationships, with appropriate primary and foreign keys. The SQL code, formatted with proper indentation and notation, demonstrates the creation of tables, insertion of sample data, and the establishment of relationships between tables using foreign keys and constraints. The solution addresses all aspects of the assignment brief, providing a comprehensive and practical implementation of a relational database.

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.