TASK 2
MySQL Database
drop database shopping; create database shopping; use shopping;
create table if not exists Customer( EmailAddress varchar(50) NOT NULL, FirstName varchar (50) NOT NULL, LastName varchar(50) NOT NULL, Address varchar(100) NOT NULL, City varchar(50) NOT NULL, State varchar(50) NOT NULL, Mobile varchar(50) NOT NULL, Password varchar(20) NOT NULL, PRIMARY KEY (EmailAddress) )ENGINE =InnoDB; create table if not exists PaymentType( Payment_Type varchar(50) NOT NULL , P_Description varchar(100) NOT NULL, primary key(Payment_Type) )ENGINE =InnoDB; create table if not exists Payment( PaymentID INT NOT NULL AUTO_INCREMENT, Amount decimal(10,2) NOT NULL, Payment_Type varchar(50) NOT NULL, CardNumber varchar(50) NULL, PRIMARY KEY (PaymentID), FOREIGN KEY (Payment_Type) REFERENCES PaymentType(Payment_Type))ENGINE =InnoDB; create table if not exists Feedback( FeedbackID INT NOT NULL AUTO_INCREMENT, Message varchar(500) NULL, Rating int NOT NULL, FeedbackDate Date NOT NULL, PRIMARY KEY (FeedbackID) )ENGINE =InnoDB; create table if not exists OrderForm( OrderID INT NOT NULL AUTO_INCREMENT, OrderDate Date NOT NULL, EmailAddress varchar(50) NOT NULL, OrderDescription varchar(500) NOT NULL, DeliverStatus varchar(50)NOT NULL, DeliverDate Date NOT NULL, PaymentID INT NOT NULL, FeedbackID INT NOT NULL,
Application Life Cycle
11 | P a g e
PRIMARY KEY (OrderID), FOREIGN KEY (PaymentID) REFERENCES Payment(PaymentID), FOREIGN KEY (FeedbackID) REFERENCES Feedback(FeedbackID) )ENGINE =InnoDB; create table if not exists Product( ProductID INT NOT NULL AUTO_INCREMENT, ProductName varchar(50) NOT NULL, ProductDescription varchar(500) NOT NULL, ProductPrice decimal(10,2) NOT NULL, QuantityRemain numeric(3) NOT NULL, PRIMARY KEY (ProductID) )ENGINE =InnoDB;
create table if not exists OrderDetail( OrderID INT NOT NULL, ProductID INT NOT NULL, Quantity int NOT NULL, Total_Price decimal(10,2) NOT NULL, PRIMARY KEY (OrderID, ProductID), FOREIGN KEY (OrderID) REFERENCES Orderform(orderID), FOREIGN KEY (ProductID) REFERENCES Product(ProductID))ENGINE =InnoDB;
Insert into Customer(EmailAddress, FirstName, LastName, Address, City, State, Mobile, password) values ('[email protected]', 'Stiva', 'Teoh', '23, Dame Street', 'Dublin 1', 'Dublin', '0878799567', 'abc'), ('[email protected]','Kavin', 'Mcdonald', '38 Grey Street','INGGARDA','INGGARDA', '0249534256', 'def'); Insert into PaymentType(Payment_Type, P_Description) values ('CoD','Cash on Delivery' ), ('Net Banking','Net Banking' ), ('Credit Card','Credit Card' ), ('Debit Card','Debit Card' ); Insert into Payment( Amount, Payment_Type, CardNumber) values (40.00, 'CoD','4444555566667777'), (250.00, 'Net Banking','1234123412341234'); Insert into Feedback( Message, Rating, FeedbackDate) values ( 'Nice', '8',STR_TO_DATE("1-15-2017","%m-%d-%Y")), ('Intermediate', '4',STR_TO_DATE("3-2-2017","%m-%d-%Y")); Insert into OrderForm(OrderDate, EmailAddress, OrderDescription, DeliverStatus, DeliverDate, paymentID, FeedbackID)
Application Life Cycle
12 | P a g e
values(STR_TO_DATE("3-26-2017","%m-%d-%Y"), '[email protected]', 'Deliver on time please', 'Deliver', STR_TO_DATE("3-29-2017","%m-%d-%Y"), 1, 1); Insert into Product( ProductName, ProductDescription, ProductPrice, QuantityRemain) values ('Sony Camera', 'High spec with low price', 239.99, 6), ('Iphone 7 Plus', 'Dual Camera', 369.90, 3); Insert into OrderDetail(OrderID, ProductID, Quantity, Total_Price) values ('1', '1', '2', 479.98);