northwindChanges.sql

/* Steven Demurjian's Original Database Edits */

CREATE TABLE NORTHWIND.DeliveryType 
(DeliveryTypeId INT NOT NULL, DeliveryTypeName VARCHAR(15), Description MEDIUMTEXT,
PRIMARY KEY (DeliveryTypeId));
INSERT INTO NORTHWIND. DeliveryType VALUES (1, 'Overnight', 'Next Business Day');
INSERT INTO NORTHWIND. DeliveryType VALUES (2, 'Two Day', 'Two Business Days');
INSERT INTO NORTHWIND. DeliveryType VALUES (3, 'Other', 'Show Up Whenever');

CREATE TABLE NORTHWIND.ShipmentType 
(ShipmentTypeId INT NOT NULL, ShipmentTypeName VARCHAR(15), Description MEDIUMTEXT,
PRIMARY KEY (ShipmentTypeId));
INSERT INTO NORTHWIND. ShipmentType VALUES (1, 'Ground', 'By Truck');
INSERT INTO NORTHWIND. ShipmentType VALUES (2, 'Air', 'By Plane');
INSERT INTO NORTHWIND. ShipmentType VALUES (3, 'Sea', 'By Boat');

CREATE TABLE NORTHWIND.Payment (PaymentID INT NOT NULL, OrderID INT NULL, 
Total DECIMAL(10,2),  PaymentTypeID INT, ConfNum NVARCHAR(24),
PRIMARY KEY (PaymentID));

CREATE TABLE NORTHWIND.PaymentType (PaymentTypeID INT NOT NULL, 
Name NVARCHAR(120),PRIMARY KEY (PaymentTypeID));
INSERT INTO NORTHWIND. PaymentType VALUES (1, 'Android Pay');
INSERT INTO NORTHWIND. PaymentType VALUES (2, 'Apple Pay');
INSERT INTO NORTHWIND. PaymentType VALUES (3, 'Credit Card');
INSERT INTO NORTHWIND. PaymentType VALUES (4, 'Debit Card');
INSERT INTO NORTHWIND. PaymentType VALUES (5, 'Paypall');
INSERT INTO NORTHWIND. PaymentType VALUES (6, 'Bank Account');


CREATE TABLE NORTHWIND.Shipments (ShipmentsID INT NOT NULL AUTO_INCREMENT, 
OrderID INT NULL, RequiredDate DATETIME NULL,PRIMARY KEY (ShipmentsID));

CREATE TABLE NORTHWIND.ProductsToAddress (ProductToAddressID INT NOT NULL 
AUTO_INCREMENT,OrderID INT, ShipmentsID INT NULL,ShipAddrID INT NULL, 
ProductID INT NULL, ShipmentTypeID INT NULL, DeliveryTypeID INT NULL, PRIMARY KEY
(ProductToAddressID));  

CREATE TABLE northwind.ShipAddresses (ShipAddrID INT NOT NULL AUTO_INCREMENT, 
ShipmentsID INT NOT NULL, RequiredDate 
DATETIME NULL, ShippedDate DATETIME NULL, ShipVia INT NULL, 
Freight DECIMAL(10,4) NULL, ShipName VARCHAR(40) NULL, ShipAddress 
VARCHAR(60) NULL, ShipCity VARCHAR(15) NULL, ShipRegion VARCHAR(15) NULL, 
ShipPostalCode VARCHAR(10) NULL, ShipCountry VARCHAR(15) NULL, 
PRIMARY KEY (ShipAddrID));

INSERT INTO NORTHWIND.Shipments (NORTHWIND.Shipments.ShipmentsID, 
NORTHWIND.Shipments.OrderID)
SELECT NORTHWIND.ORDERS.OrderID, NORTHWIND.ORDERS.OrderID
FROM NORTHWIND.ORDERS;

SELECT * FROM NORTHWIND.Shipments;

INSERT INTO NORTHWIND.ProductsToAddress (NORTHWIND.ProductsToAddress.OrderID, NORTHWIND.ProductsToAddress.ShipmentsID, 
NORTHWIND.ProductsToAddress.ProductID)
SELECT NORTHWIND.`order details`.OrderID, 
(NORTHWIND.`order details`.OrderID+30000), NORTHWIND.`order details`.ProductID
FROM NORTHWIND.`order details`;

SELECT * FROM NORTHWIND.productstoaddress;

INSERT INTO NORTHWIND.ShipAddresses (NORTHWIND.ShipAddresses.ShipAddrID, 
NORTHWIND.ShipAddresses.ShipmentsID, NORTHWIND.ShipAddresses.RequiredDate, 
NORTHWIND.ShipAddresses.ShippedDate, NORTHWIND.ShipAddresses.ShipVia, 
NORTHWIND.ShipAddresses.Freight, NORTHWIND.ShipAddresses.ShipName,
NORTHWIND.ShipAddresses.ShipAddress, NORTHWIND.ShipAddresses.ShipCity,
NORTHWIND.ShipAddresses.ShipRegion,NORTHWIND.ShipAddresses.ShipPostalCode,
NORTHWIND.ShipAddresses.ShipCountry)
SELECT  NORTHWIND.orders.OrderID, (NORTHWIND.orders.OrderID+30000), 
NORTHWIND.orders.RequiredDate, NORTHWIND.orders.ShippedDate, 
NORTHWIND.orders.ShipVia, NORTHWIND.orders.Freight, NORTHWIND.orders.ShipName,
NORTHWIND.orders.ShipAddress, NORTHWIND.orders.ShipCity, 
NORTHWIND.orders.ShipRegion, NORTHWIND.orders.ShipPostalCode,
NORTHWIND.orders.ShipCountry
FROM NORTHWIND.orders;

SELECT * FROM NORTHWIND.ShipAddresses;

ALTER TABLE NORTHWIND.ORDERS DROP  RequiredDate; 
ALTER TABLE NORTHWIND.ORDERS DROP  ShippedDate; 
ALTER TABLE NORTHWIND.ORDERS DROP  Freight;
ALTER TABLE NORTHWIND.ORDERS DROP  ShipName; 
ALTER TABLE NORTHWIND.ORDERS DROP  ShipAddress;
ALTER TABLE NORTHWIND.ORDERS DROP  ShipCity;
ALTER TABLE NORTHWIND.ORDERS DROP  ShipRegion;
ALTER TABLE NORTHWIND.ORDERS DROP  ShipPostalCode;
ALTER TABLE NORTHWIND.ORDERS DROP  ShipCountry;


CREATE TABLE NORTHWIND.Company (CompanyID INT NOT NULL,
CompanyName VARCHAR(40), ContactName VARCHAR(30),ContactTitle VARCHAR(30), 
Address VARCHAR(60), City VARCHAR(15),Region VARCHAR(15), PostalCode VARCHAR(10), 
Country VARCHAR(15),Phone VARCHAR(24), Fax VARCHAR(24),PRIMARY KEY (CompanyID));

INSERT INTO NORTHWIND.Company (NORTHWIND.COMPANY.CompanyId, NORTHWIND.COMPANY.CompanyName, 
NORTHWIND.COMPANY.ContactName, NORTHWIND.COMPANY.ContactTitle, NORTHWIND.COMPANY.Address, NORTHWIND.COMPANY.City, NORTHWIND.COMPANY.Region, NORTHWIND.COMPANY.PostalCode, 
NORTHWIND.COMPANY.Country,NORTHWIND.COMPANY.Phone, NORTHWIND.COMPANY.Fax)
SELECT NORTHWIND.SUPPLIERS.SupplierId, NORTHWIND.SUPPLIERS.CompanyName, 
NORTHWIND.SUPPLIERS.ContactTitle,NORTHWIND.SUPPLIERS.ContactName, NORTHWIND.SUPPLIERS.Address, 
NORTHWIND.SUPPLIERS.City, NORTHWIND.SUPPLIERS.Region, NORTHWIND.SUPPLIERS.PostalCode, 
NORTHWIND.SUPPLIERS.Country, NORTHWIND.SUPPLIERS.Phone, NORTHWIND.SUPPLIERS.Fax 
FROM NORTHWIND.SUPPLIERS;

ALTER TABLE NORTHWIND.SUPPLIERS DROP  CompanyName; 
ALTER TABLE NORTHWIND.SUPPLIERS DROP  ContactName; 
ALTER TABLE NORTHWIND.SUPPLIERS DROP  ContactTitle; 
ALTER TABLE NORTHWIND.SUPPLIERS DROP  Address; 
ALTER TABLE NORTHWIND.SUPPLIERS DROP  City ;
ALTER TABLE NORTHWIND.SUPPLIERS DROP  Region; 
ALTER TABLE NORTHWIND.SUPPLIERS DROP  PostalCode;
ALTER TABLE NORTHWIND.SUPPLIERS DROP  Country;
ALTER TABLE NORTHWIND.SUPPLIERS DROP  Phone;
ALTER TABLE NORTHWIND.SUPPLIERS DROP  Fax;

ALTER TABLE NORTHWIND.SUPPLIERS ADD CompanyId INT;

UPDATE NORTHWIND.SUPPLIERS
SET NORTHWIND.SUPPLIERS.CompanyId = NORTHWIND.SUPPLIERS. SupplierId;

SELECT * FROM NORTHWIND.SUPPLIERS;

INSERT INTO NORTHWIND.Company (NORTHWIND.COMPANY.CompanyId, 
NORTHWIND.COMPANY.CompanyName,NORTHWIND.COMPANY.Phone)
SELECT (NORTHWIND.SHIPPERS.ShipperId+1000), 
NORTHWIND.SHIPPERS.CompanyName, NORTHWIND.SHIPPERS.Phone 
FROM SHIPPERS;

ALTER TABLE NORTHWIND.SHIPPERS DROP  CompanyName; 
ALTER TABLE NORTHWIND.SHIPPERS DROP  Phone;
ALTER TABLE NORTHWIND.SHIPPERS ADD   CompanyId INT;

UPDATE NORTHWIND.shippers SET NORTHWIND.shippers.CompanyId = 
NORTHWIND.shippers.ShipperID;

SELECT * FROM NORTHWIND.SHIPPERS;



/* End of Steven Demurjian's Original Database Edits */



/*******BEGINNING OF OUR EDITS*****************/
/*-- users --*/
CREATE TABLE IF NOT EXISTS northwind.users
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL,
    password CHAR(60) NOT NULL,
    customerid VARCHAR(5),
    employeeid INT (11),
    role VARCHAR(20) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE INDEX id_UNIQUE (id ASC)
);

create table if not exists northwind.paymentcardinfo(

    id int unsigned not null auto_increment,
    userId int(11) unsigned not null,
    cardType varchar(15) not null,
    cardNumber varchar(16) NOT NULL,
    cardCVC CHAR(60) NOT NULL,
    expDate date NOT NULL,
    holderName varchar(50) not null,    
    paymentType int(11) NOT NULL,

    primary key (id),
    foreign key (userId) references users(id),
    foreign key (paymentType) references paymenttype(paymentTypeID)
);


alter table northwind.paymentcardinfo
modify cardcvc varchar(3);


/*-- mobilepaymnetinfo --*/
create table northwind.mobilepaymentinfo(
    id int unsigned not null auto_increment,
    userId int(11) unsigned not null,
    email varchar(255) not null,
    paymentType int(11) not null,
    primary key (id),
    foreign key (paymentType) references paymenttype(paymentTypeID),
    foreign key (userId) references users(id)
);

alter table northwind.mobilepaymentinfo
	modify id int not null,
	drop primary key,
	add primary key (userId, paymentType);

alter table northwind.mobilepaymentinfo
	drop id;

alter table northwind.mobilepaymentinfo
	modify column email varchar(255) after paymentType;


ALTER TABLE northwind.employees
MODIFY notes MEDIUMTEXT;



/*-- Please verify this table is correct --
-- shoppingcart -- */

create table if not exists northwind.shoppingcart(
cartid int not null auto_increment,
userid int(10) unsigned,
productID int(11),
productName varchar(40),
quantity int,

primary key (cartid),
foreign key (userid) references users(id),
foreign key (productID) references products(ProductId)

);


/*-- invoice table --*/

CREATE TABLE IF NOT EXISTS northwind.invoice (
OrderID int(11) NOT NULL,
ProductID int(11) NOT NULL,
totalCost decimal(10,4) NOT NULL DEFAULT '0.0000',
PRIMARY KEY (OrderID, ProductID));

/*update company table to make CompanyID auto increment*/
ALTER TABLE northwind.company
MODIFY COLUMN CompanyID INT(11) AUTO_INCREMENT;

ALTER TABLE northwind.payment
MODIFY COLUMN PaymentID INT(11) AUTO_INCREMENT;

ALTER TABLE northwind.company
AUTO_INCREMENT = 1004;


update northwind.productstoaddress
Set northwind.productstoaddress.ShipAddrID=northwind.productstoaddress.OrderID;