• 스키마 새로 생성 필요 이름은 projectdb로
• 템플릿과 추가 의견은 맨 아래 추가 의견 작성란 페이지 사용 바랍니다.
• • 1. Cust 테이블 (고객) CREATE TABLE Cust ( cust_id INT AUTO_INCREMENT PRIMARY KEY, cust_email VARCHAR(100) NOT NULL, cust_pwd VARCHAR(255) NOT NULL, cust_name VARCHAR(50) NOT NULL, cust_phone VARCHAR(20), cust_regdate DATETIME, cust_update DATETIME ); /* ALTER TABLE Cust ADD CONSTRAINT uq_cust_email UNIQUE (cust_email); */
• • 2. Admin 테이블 (관리자) CREATE TABLE Admin ( admin_id INT AUTO_INCREMENT PRIMARY KEY, admin_email VARCHAR(100) NOT NULL, admin_pwd VARCHAR(255) NOT NULL, admin_name VARCHAR(50) NOT NULL, admin_phone VARCHAR(20), admin_regdate DATETIME, admin_update DATETIME ); /* ALTER TABLE Admin ADD CONSTRAINT uq_admin_email UNIQUE (admin_email); */
• • 3. Category 테이블 (카테고리) CREATE TABLE Category ( category_id INT AUTO_INCREMENT PRIMARY KEY, parent_category_id INT, category_name VARCHAR(100) NOT NULL, category_update DATETIME );

#ALTER TABLE Category

• • 4. Product 테이블 (제품) CREATE TABLE Product ( product_id INT AUTO_INCREMENT PRIMARY KEY , category_id INT NOT NULL, product_name VARCHAR(200) NOT NULL, description TEXT, product_price INT NOT NULL, discount_rate DOUBLE NOT NULL DEFAULT 0.0, product_img VARCHAR(255), product_regdate DATETIME, product_update DATETIME );

/* ALTER TABLE Product ADD CONSTRAINT chk_product_price CHECK (product_price > 0); ALTER TABLE Product ALTER product_regdate SET DEFAULT (NOW()); ALTER TABLE Product ALTER product_update SET DEFAULT (NOW()); */

• • 5. Order 테이블 (주문) CREATE TABLE cust_order ( order_id INT AUTO_INCREMENT PRIMARY KEY , cust_id INT NOT NULL, order_date DATETIME NOT NULL, total_amount INT NOT NULL, shipping_address VARCHAR(200) NOT NULL, shipping_name VARCHAR(50) NOT NULL, shipping_phone VARCHAR(20) NOT NULL );
• • 6. Address 테이블 (주소) CREATE TABLE Address ( address_id INT AUTO_INCREMENT PRIMARY KEY, cust_id INT NOT NULL, address_name VARCHAR(50) NOT NULL, postal_code VARCHAR(10) NOT NULL, address VARCHAR(200) NOT NULL, detail_address VARCHAR(200) NOT NULL, is_default BOOLEAN DEFAULT FALSE, address_update DATETIME );
• • 7. Cart 테이블 (장바구니) CREATE TABLE Cart ( cart_id INT AUTO_INCREMENT PRIMARY KEY , cust_id INT NOT NULL, product_id INT NOT NULL, product_qt INT NOT NULL DEFAULT 1, cart_regdate DATETIME );
• • ALTER TABLE Cart -- ADD CONSTRAINT uq_cart_cust_product UNIQUE (cust_id, product_id); -- ALTER TABLE Cart ADD CONSTRAINT chk_product_qt CHECK (product_qt > 0); -- ALTER TABLE Cart ALTER cart_regdate SET DEFAULT (NOW());
• • 8. OrderItem 테이블 (주문상품) CREATE TABLE OrderItem ( orderitem_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, unit_price INT NOT NULL );
• • ALTER TABLE OrderItem -- ADD CONSTRAINT uq_orderitem_order_product UNIQUE (order_id, product_id);
• • 9. Payment 테이블 (결제) CREATE TABLE Payment ( payment_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, payment_method VARCHAR(50) NOT NULL, payment_amount INT NOT NULL, payment_date DATETIME NOT NULL, transaction_id VARCHAR(100) NOT NULL );
• • ALTER TABLE Payment -- ADD CONSTRAINT uq_payment_order_id UNIQUE (order_id), -- ADD CONSTRAINT uq_payment_transaction_id UNIQUE (transaction_id);
• • 10. Shipping 테이블 (배송정보) CREATE TABLE Shipping ( shipping_id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, shipping_company VARCHAR(50) NOT NULL, tracking_number VARCHAR(100) NOT NULL, shipped_date DATETIME, delivered_date DATETIME, estimated_delivery DATETIME, delivery_memo VARCHAR(200), shipping_fee INT NOT NULL DEFAULT 0, shipping_update DATETIME );
• • ALTER TABLE Shipping -- ADD CONSTRAINT uq_shipping_order_id UNIQUE (order_id);
• • Foreign Key 제약 조건 추가 (모든 테이블 생성 및 다른 제약 조건 추가 후)

/* -- Category 테이블 ALTER TABLE Category ADD CONSTRAINT fk_category_parent FOREIGN KEY (parent_category_id) REFERENCES Category(category_id);