요구사항

1. 고객은 고객아이디, 이름, 비밀번호, 나이로 되어있다.
2. 고객아이디는 기본키로 지정하고 고객이름과 등급속성은 반드시 값을 입력해야하고, 포인트 속성은 값을 입력하지 않으면 0이 기본으로 입력 되도록한다.
3. 고객은 등급과 포인트로 관리되어진다.
4. 제품은 제품번호, 제품명을가지고있다.
5. 제품번호를 기본키로 지정하고 재고량이 항상 0개이상 1000개이하를 유지하도록한다.
6. 제품에서 제품의 재고량을 파악할 수있고 제품의 단가 및 제품의 제조업체에 대한 세부정보를 알수있다.
7. 고객이 제품을 주문하면 주문번호, 고객아이디, 제품번호, 수량, 주문일자를 기록한다.
8. 주문번호는 기본키로 지정하고, 고객아이디는 고객테이블의 고객아이디를 참조하는 외래키이고,
9. 제품번호는 제품테이블의 제품번호를 참조하는 외래키가 되도록한다.

이런 요구사항이 주어졌을때 우선 개체와 속성 그리고 관계로 나누어봤습니다

-- 개체 속성 관계
-- 개체: 고객, 제품, 주문
-- 고객속성: 고객아이디, 이름, 비밀번호, 나이, 등급, 포인트, 기입일
-- 제품속성: 제품번호, 제품명, 재고량, 단가, 제조업체
-- 주문속성: 주문번호, 수량, 주문일자
-- 관계: 고객1: 주문N / 주문1: 제품1

이 정의서를 엑셀로 옮겨보면 이렇게 나오게 됩니다

이제 mysql 워크벤치로 가서 테이블을 만들어보겠습니다

customer

create table customer(
	customer_id varchar(50) not null,
    customer_name varchar(100) not null,
    password varchar(100) not null,
    age int,
    rating varchar(20) not null,
    points int default 0,
    primary key (customer_id)
);

customer_id를 primary key로 설정

product

create table product(
	pro_number int auto_increment,
    pro_name varchar(100),
    inventory int,
    price int,
    manufactor varchar(100),
    primary key (pro_number),
    check (inventory>=0 and inventory<=1000) -- 값의 범위 지정할때 쓰는 check table
);

pro_number를 primary key로 설정
또한 pro_number를 ai로 주어서 스스로증가하게 설정
요구사항에 재고량의 범위가 0~1000으로 제한되어 있어서
check(inventory>=0 and inventory<=1000)으로 제한을 걸어뒀습니다

buy

create table buy(
	order_number int auto_increment,
    customer_id varchar(50),
    pro_number int,
    qty int not null,
    order_date datetime default now(),
    primary key (order_number),
    foreign key (customer_id) references customer(customer_id),
    foreign key (pro_number) references product(pro_number)
);

order_number를 primary key로 설정
또한 order_number에 ai를 주어서 스스로 증가
buy테이블에는 외래키가 2개 있으므로
다 설정해주었습니다

리버스 엔지니어링으로 ERD 만들어보기

mysql 워크벤치에 포함되어있는 리버스 엔지니어링으로 ERD를 만들어보았습니다.
mysql workbench로 ERD만들기

예제 풀어보기

cu004 고객이 2개 이상 주문한 제품명 제품코드 수량 주문일자 출력

select p.pro_name, p.pro_number, b.qty, b.order_date
from product p, buy b
where p.pro_number = b.pro_number
and customer_id='cu004' and qty>=2;

고객테이블에서 나이가 입력되지 않은 고객정보출력

select * from customer where age is null;

고객테이블에서 나이가 입력된 고객 정보 출력

select * from customer where age is not null;

제조업체별 제품 수와 제품중 가장 비싼 가격을 검색

select manufactor, count(pro_name), max(price) from product group by manufactor;

주문을 한번이상 한 고객의 수 출력

select count(distinct customer_id) from buy where qty>=1;

구매테이블에서 cust003 고객이 주문한 제품의 이름

select b.customer_id, p.pro_name
from buy b, product p
where b.pro_number = p.pro_number
and b.customer_id='cu003';

'제품 E'를 생산한 제조업체가 만든 제품들의 제품명, 가격 출력

select pro_name, price from product
where manufactor=(select manufactor from product where pro_name='제품 E');

cu005 고객이 주문한 제품의 제품명, 제조업체 출력

select pro_name, manufactor from product
where pro_number in (select pro_number from buy where customer_id='cu005');

주문제품별 수량 합게를 출력

select pro_name, sum(qty)
from product natural join buy
group by pro_name;

나이가 30세 이상인 고객이 주문한 제품명, 주문일자

select pro_name, order_date, age, customer_id
from customer natural join product natural join buy
where age>=30;