How do I complete my database project - mysql

See more:

MySQL

workbench

Hi yall! This is a small database for MySQL. My company is a bakery store where I have to develop a simple database for them. I have to start from the beginning. Right now I have set up my ER diagrams and Business rules. But somehow it makes me feel like this database is weird. So can you guys have any recommend for me.
I am using MySQL Workbench. I have created 5 tables PASTRY, ORDER, ORDERItem, CUSTOMER and EMPLOYEE. (ORDERItem is a linking table). I have run my script and it worked perfectly fine. FYI, I'm new to Database.


Do you guys have any suggestions! Thank you so much!

What I have tried:

/* my project*/
BEGIN;

DROP TABLE IF EXISTS PASTRY;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS EMPLOYEE;
DROP TABLE IF EXISTS ORDERs;
DROP TABLE IF EXISTS ORDERItem;


CREATE TABLE PASTRY (
BAKEID INTEGER NOT NULL,
BAKE_CATEGORY VARCHAR(20) NOT NULL,
BAKE_PRICE NUMERIC (8,2) NOT NULL,
BAKE_QOH SMALLINT NOT NULL,
PRIMARY KEY (BAKEID));

CREATE TABLE CUSTOMER (
CUS_ID NUMERIC PRIMARY KEY,
CUS_FNAME VARCHAR(15) NOT NULL,
CUS_LNAME VARCHAR(15) NOT NULL,
CUS_PHONE CHAR(12) NOT NULL,
CUS_BALANCE NUMERIC(9,2) DEFAULT 0.00,
CONSTRAINT CUS_UI1 UNIQUE (CUS_FNAME, CUS_LNAME));

CREATE TABLE EMPLOYEE (
EMP_ID NUMERIC PRIMARY KEY,
EMP_FNAME VARCHAR(15) NOT NULL,
EMP_LNAME VARCHAR(15) NOT NULL,
EMP_PHONE CHAR(12));

CREATE TABLE ORDERs (
ORDER_ID INTEGER PRIMARY KEY,
CUS_ID NUMERIC NOT NULL,
EMP_ID NUMERIC NOT NULL REFERENCES EMPLOYEE (EMP_ID),
ORDER_AMOUNT NUMERIC(9,2) DEFAULT 0.00,
ORDER_DATE DATETIME NOT NULL,
ORDER_STATUS VARCHAR(50));


CREATE TABLE ORDERItem (
BAKEID INTEGER NOT NULL REFERENCES PASTRY (BAKEID),
ORDER_ID INTEGER NOT NULL REFERENCES ORDERs (ORDER_ID));

INSERT INTO PASTRY VALUES(101,'Apple Pie', 17.25 , 20);
INSERT INTO PASTRY VALUES(102,'Chocolate Roll', 15 , 10);
INSERT INTO PASTRY VALUES(103,'Eclair', 7 , 35);
INSERT INTO PASTRY VALUES(104,'Banana Muffin', 4 , 40);
INSERT INTO PASTRY VALUES(105,'Cinnamon Roll', 4 , 22);
INSERT INTO PASTRY VALUES(106,'Opera Cake', 30 , 6);
INSERT INTO PASTRY VALUES(107,'Macaron', 3 , 45);
INSERT INTO PASTRY VALUES(108,'Butter Roll', 12 , 12);
INSERT INTO PASTRY VALUES(109,'Coconut Cake', 15 , 46);
INSERT INTO PASTRY VALUES(110,'Rasberry Cake', 15 , 10);
INSERT INTO PASTRY VALUES(111,'Strawberry Moussee', 19 , 10);

INSERT INTO ORDERs VALUES(111, 10011, 111, 92.50,'2017-11-03', 'done');
INSERT INTO ORDERs VALUES(112, 10012, 112, 98,'2017-11-03', 'waiting');
INSERT INTO ORDERs VALUES(113, 10013, 113, 100, '2017-11-03', 'waiting');
INSERT INTO ORDERs VALUES(114, 10014, 114, 158.58,'2017-11-03', 'partially done');
INSERT INTO ORDERs VALUES(115, 10015, 115, 25,'2017-11-03', 'done');
INSERT INTO ORDERs VALUES(116, 10016, 116, 69,'2017-11-03', 'waiting');
INSERT INTO ORDERs VALUES(117, 10017, 117, 78,'2017-11-03', 'done');
INSERT INTO ORDERs VALUES(118, 10018, 118, 150,'2017-11-03', 'delivered');
INSERT INTO ORDERs VALUES(119, 10019, 119, 30,'2017-11-03', 'delivered');


INSERT INTO ORDERItem VALUES(101, 10011);
INSERT INTO ORDERItem VALUES(102, 10012);
INSERT INTO ORDERItem VALUES(103, 10013);
INSERT INTO ORDERItem VALUES(104, 10014);
INSERT INTO ORDERItem VALUES(105, 10015);
INSERT INTO ORDERItem VALUES(106, 10016);
INSERT INTO ORDERItem VALUES(107, 10017);
INSERT INTO ORDERItem VALUES(108, 10018);
INSERT INTO ORDERItem VALUES(109, 10019);


INSERT INTO CUSTOMER VALUES(10011,'Will', 'Smith','985-859-9856' , 25.60);
INSERT INTO CUSTOMER VALUES(10012,'John', 'Destiny','562-985-3654' , 85);
INSERT INTO CUSTOMER VALUES(10013,'Derek', 'Alison','817-125-3685', 283.36);
INSERT INTO CUSTOMER VALUES(10014,'Shawn', 'Brown','817-259-3506' , 85.20);
INSERT INTO CUSTOMER VALUES(10015,'Peter', 'Hobbs','817-254-0301' , 45.52);
INSERT INTO CUSTOMER VALUES(10016,'Bob', 'Shawn','817-825-0258' , 85);
INSERT INTO CUSTOMER VALUES(10017,'Leif', 'Sean','857-003-6985' , 25);
INSERT INTO CUSTOMER VALUES(10018,'Holland', 'Leif','859-559-6975' , 20);
INSERT INTO CUSTOMER VALUES(10019,'Myers', 'Sergio','125-254-6548' , 45.30);


INSERT INTO EMPLOYEE VALUES(111, 'Eliza', 'Johnson','817-458-0252');
INSERT INTO EMPLOYEE VALUES(112, 'Ella', 'Zhao','817-458-0325');
INSERT INTO EMPLOYEE VALUES(113, 'Lisa', 'Swift','817-125-2587');
INSERT INTO EMPLOYEE VALUES(114, 'Noah', 'Hope','817-025-3584');
INSERT INTO EMPLOYEE VALUES(115, 'Alex', 'Alison','258-256-0369');
INSERT INTO EMPLOYEE VALUES(116, 'Emma', 'Morgan', '256-025-4856');
INSERT INTO EMPLOYEE VALUES(117, 'Taylor', 'Doe','817-365-6975');
INSERT INTO EMPLOYEE VALUES(118, 'Taylor', 'Dannon','817-458-6812');
INSERT INTO EMPLOYEE VALUES(119, 'Taylor', 'Williams','817-005-6855');

COMMIT;