Database Design and SQL

Running head: DATABASE DESIGN AND SQL
Database design and SQL
Name of the Student
Name of the University
Authors note

DATABASE DESIGN AND SQL
1
Part 1
Developed ERD Diagram

DATABASE DESIGN AND SQL
2
Task 1
Connecting to the database from Mysql Work bench
Creating and connecting database
Customer table
CREATE TABLE `salesmarketingdb`.`customer` (

DATABASE DESIGN AND SQL
3
`customernumber` INT NOT NULL AUTO_INCREMENT,
`customername` VARCHAR(45) NOT NULL,
`customeraddress` VARCHAR(70) NOT NULL,
`customercity` VARCHAR(45) NOT NULL,
`customerzip` VARCHAR(15) NOT NULL,
`customercontact` VARCHAR(45) NOT NULL,
`customercol` VARCHAR(45) NOT NULL,
PRIMARY KEY (`customernumber`));
Product table
CREATE TABLE `salesmarketingdb`.`product` (

DATABASE DESIGN AND SQL
4
`productid` INT NOT NULL AUTO_INCREMENT,
`productname` VARCHAR(45) NOT NULL,
`productdesc` VARCHAR(50) NOT NULL,
`productprice` VARCHAR(45) NOT NULL,
`productcategory` VARCHAR(20) NOT NULL,
PRIMARY KEY (`productid`));
Sales table
CREATE TABLE `salesmarketingdb`.`salestable` (
`idsales` INT(11) NOT NULL,
`customernumber` INT(11) NOT NULL,
`productid` INT(11) NOT NULL,
`salesquantity` INT NOT NULL,

DATABASE DESIGN AND SQL
5
`salesamont` INT NOT NULL,
`salesdate` DATE NOT NULL,
PRIMARY KEY (`idsales`));
Delivery table
CREATE TABLE `salesmarketingdb`.`delivery` (
`iddelivery` INT NOT NULL AUTO_INCREMENT,
`deliveryaddress` VARCHAR(75) NOT NULL,
`deliveryzip` VARCHAR(15) NOT NULL,
`customerid` INT(11) NOT NULL,
`deliverymethod` VARCHAR(45) NOT NULL,
PRIMARY KEY (`iddelivery`));
Adding foreign key relationship