yeah im aware of the rules etc, but this is just for a university project of mine so peoples info won't be at risk. thanks for replying so quickly!
here's my database:
DROP TABLE IF EXISTS Customer CASCADE;
DROP TABLE IF EXISTS Products CASCADE;
DROP TABLE IF EXISTS Category CASCADE;
DROP TABLE IF EXISTS Orders CASCADE;
DROP TABLE IF EXISTS Order_items CASCADE;
-- Create a Database table to represent the "Customer" entity.
CREATE TABLE Customer(
id int(11) NOT NULL auto_increment,
username VARCHAR(30) NOT NULL,
firstname VARCHAR(25) NOT NULL,
secondname VARCHAR(25) NOT NULL,
address1 VARCHAR(40) NOT NULL,
address2 VARCHAR(30) NOT NULL,
city VARCHAR(25) NOT NULL,
postcode VARCHAR( NOT NULL,
country VARCHAR(15) NOT NULL,
mobilenumber VARCHAR(12) NULL,
email VARCHAR(50) NOT NULL,
password VARCHAR(12) NOT NULL,
privledge VARCHAR(1) NOT NULL DEFAULT 1,
PRIMARY KEY (id)
);
CREATE TABLE Products (
ItemId MEDIUMINT NOT NULL AUTO_INCREMENT,
Name VARCHAR(25) NOT NULL,
Description VARCHAR(50) NOT NULL,
Price DECIMAL(8,2) NOT NULL,
ImageName VARCHAR(25) NOT NULL,
CategoryId VARCHAR(12) NOT NULL REFERENCES Category(CategoryId),
PRIMARY KEY (itemId)
);
CREATE TABLE Category(
CategoryId VARCHAR( NOT NULL,
CategoryType VARCHAR (32) NOT NULL,
PRIMARY KEY (CategoryId)
);
CREATE TABLE Order_items (
OrderId int(5) NOT NULL,
ItemId int(4) NOT NULL,
qty int(3),
price decimal(5,2),
PRIMARY KEY (OrderId,ItemId)
);
CREATE TABLE Orders (
OrderId int(5) NOT NULL auto_increment,
id int(5) NOT NULL,
date varchar(10),
instructions varchar(128),
creditcard char(16),
expirydate char(5),
PRIMARY KEY (OrderId)
);