
업데이트
사용자 친화적인 디자인으로 Auto-SQL을 사용함에 있어 배우는 시간이 크게 필요하지 않습니다.
한 개의 RDBMS와 언어에 국한되지 않고 여러 RDBMS와 언어를 지원합니다.
간단한 소셜로그인으로 다른 사용자에게 공유기능을 제공하여 빠른 피드백과 협업이 가능합니다.
각 DBMS의 문법을 고려하지 않고 오롯이 데이터베이스 설계 능력을 빠르게 배양할 수 있습니다.
데이터베이스 설계에 대하여 학습합니다.
배운 내용을 응용하여 데이터베이스를 설계하고 협업합니다.
원하는 데이터베이스를 선택하여 SQL을 Export합니다.
CREATE TABLE buy
(
orderno number NOT NULL COMMENT '주문번호',
res_id varchar2(20) NULL COMMENT '주문자아이디',
res_phone char(11) NULL COMMENT '주문자 성명',
res_requirement varchar2(100) NULL COMMENT '결제수단',
totalprice number NULL COMMENT '총가격',
orderdate date NULL COMMENT '주문날짜',
Field VARCHAR(255) NULL COMMENT '재기번호',
PRIMARY KEY (orderno)
) COMMENT '주문';
CREATE TABLE cart
(
pID varchar2(30) NOT NULL COMMENT '상품아이디',
pname varchar2(30) NULL COMMENT '상품이름',
amount number NULL COMMENT '수량',
pprice number(10) NULL COMMENT '가격',
how varchar2(30) NULL COMMENT '결제수단',
PRIMARY KEY (pID)
) COMMENT '주문확인';
CREATE TABLE member
(
memberid varchar2(15) NOT NULL COMMENT '회원아이디',
membername varchar2(20) NULL COMMENT '회원이름',
gender char(1) NULL COMMENT '성별',
age number NULL COMMENT '나이',
email varchar2(30) NULL COMMENT '이메일',
phone char(11) NULL COMMENT '전화번호',
grade varchar2(15) NULL COMMENT '등급',
enrolldate date NULL COMMENT '회원가입일',
delflag char(1) NULL COMMENT '회원탈퇴여부',
deletedate date NULL COMMENT '회원탈퇴일',
regflag char(1) NULL COMMENT '비회원여부',
password varchar2(300) NULL COMMENT '비밀번호',
PRIMARY KEY (memberid)
) COMMENT '회원';
CREATE TABLE product
(
pID varchar2(30) NOT NULL COMMENT '상품번호',
pname varchar2(30) NULL COMMENT '상품이름',
pprice number(10) NULL COMMENT '상품가격',
pcategory varchar2(30) NULL COMMENT '상품종류',
description varchar2(50) NULL COMMENT '상품설명',
pcompany varchar2(30) NULL COMMENT '제조회사',
pstock number NULL COMMENT '상품재고',
PRIMARY KEY (pID)
) COMMENT '상품';
CREATE TABLE product_io
(
iono number NOT NULL COMMENT '입출고번호',
pID varchar2(30) NOT NULL COMMENT '상품아이디',
orderno number NOT NULL COMMENT '주문번호',
amount number NULL COMMENT '입출고량',
status varchar2(10) NULL COMMENT '상태',
pDate date NULL COMMENT '입출고날짜',
memberid varchar2(15) NOT NULL COMMENT '회원아이디',
PRIMARY KEY (iono, pID, orderno)
) COMMENT '입출고테이블';
ALTER TABLE product_io
ADD CONSTRAINT FK_product_TO_product_io
FOREIGN KEY (pID)
REFERENCES product (pID);
ALTER TABLE product_io
ADD CONSTRAINT FK_buy_TO_product_io
FOREIGN KEY (orderno)
REFERENCES buy (orderno);
ALTER TABLE product_io
ADD CONSTRAINT FK_member_TO_product_io
FOREIGN KEY (memberid)
REFERENCES member (memberid);
ALTER TABLE cart
ADD CONSTRAINT FK_product_TO_cart
FOREIGN KEY (pID)
REFERENCES product (pID);
CREATE TABLE buy
(
orderno number NOT NULL,
res_id varchar2(20) ,
res_phone char(11) ,
res_requirement varchar2(100),
totalprice number ,
orderdate date ,
Field VARCHAR(255) ,
CONSTRAINT PK_buy PRIMARY KEY (orderno)
);
COMMENT ON TABLE buy IS '주문';
COMMENT ON COLUMN buy.orderno IS '주문번호';
COMMENT ON COLUMN buy.res_id IS '주문자아이디';
COMMENT ON COLUMN buy.res_phone IS '주문자 성명';
COMMENT ON COLUMN buy.res_requirement IS '결제수단';
COMMENT ON COLUMN buy.totalprice IS '총가격';
COMMENT ON COLUMN buy.orderdate IS '주문날짜';
COMMENT ON COLUMN buy.Field IS '재기번호';
CREATE TABLE cart
(
pID varchar2(30) NOT NULL,
pname varchar2(30),
amount number ,
pprice number(10) ,
how varchar2(30),
CONSTRAINT PK_cart PRIMARY KEY (pID)
);
COMMENT ON TABLE cart IS '주문확인';
COMMENT ON COLUMN cart.pID IS '상품아이디';
COMMENT ON COLUMN cart.pname IS '상품이름';
COMMENT ON COLUMN cart.amount IS '수량';
COMMENT ON COLUMN cart.pprice IS '가격';
COMMENT ON COLUMN cart.how IS '결제수단';
CREATE TABLE member
(
memberid varchar2(15) NOT NULL,
membername varchar2(20) ,
gender char(1) ,
age number ,
email varchar2(30) ,
phone char(11) ,
grade varchar2(15) ,
enrolldate date ,
delflag char(1) ,
deletedate date ,
regflag char(1) ,
password varchar2(300),
CONSTRAINT PK_member PRIMARY KEY (memberid)
);
COMMENT ON TABLE member IS '회원';
COMMENT ON COLUMN member.memberid IS '회원아이디';
COMMENT ON COLUMN member.membername IS '회원이름';
COMMENT ON COLUMN member.gender IS '성별';
COMMENT ON COLUMN member.age IS '나이';
COMMENT ON COLUMN member.email IS '이메일';
COMMENT ON COLUMN member.phone IS '전화번호';
COMMENT ON COLUMN member.grade IS '등급';
COMMENT ON COLUMN member.enrolldate IS '회원가입일';
COMMENT ON COLUMN member.delflag IS '회원탈퇴여부';
COMMENT ON COLUMN member.deletedate IS '회원탈퇴일';
COMMENT ON COLUMN member.regflag IS '비회원여부';
COMMENT ON COLUMN member.password IS '비밀번호';
CREATE TABLE product
(
pID varchar2(30) NOT NULL,
pname varchar2(30),
pprice number(10) ,
pcategory varchar2(30),
description varchar2(50),
pcompany varchar2(30),
pstock number ,
CONSTRAINT PK_product PRIMARY KEY (pID)
);
COMMENT ON TABLE product IS '상품';
COMMENT ON COLUMN product.pID IS '상품번호';
COMMENT ON COLUMN product.pname IS '상품이름';
COMMENT ON COLUMN product.pprice IS '상품가격';
COMMENT ON COLUMN product.pcategory IS '상품종류';
COMMENT ON COLUMN product.description IS '상품설명';
COMMENT ON COLUMN product.pcompany IS '제조회사';
COMMENT ON COLUMN product.pstock IS '상품재고';
CREATE TABLE product_io
(
iono number NOT NULL,
pID varchar2(30) NOT NULL,
orderno number NOT NULL,
amount number ,
status varchar2(10),
pDate date ,
memberid varchar2(15) NOT NULL,
CONSTRAINT PK_product_io PRIMARY KEY (iono, pID, orderno)
);
COMMENT ON TABLE product_io IS '입출고테이블';
COMMENT ON COLUMN product_io.iono IS '입출고번호';
COMMENT ON COLUMN product_io.pID IS '상품아이디';
COMMENT ON COLUMN product_io.orderno IS '주문번호';
COMMENT ON COLUMN product_io.amount IS '입출고량';
COMMENT ON COLUMN product_io.status IS '상태';
COMMENT ON COLUMN product_io.pDate IS '입출고날짜';
COMMENT ON COLUMN product_io.memberid IS '회원아이디';
ALTER TABLE product_io
ADD CONSTRAINT FK_product_TO_product_io
FOREIGN KEY (pID)
REFERENCES product (pID);
ALTER TABLE product_io
ADD CONSTRAINT FK_buy_TO_product_io
FOREIGN KEY (orderno)
REFERENCES buy (orderno);
ALTER TABLE product_io
ADD CONSTRAINT FK_member_TO_product_io
FOREIGN KEY (memberid)
REFERENCES member (memberid);
ALTER TABLE cart
ADD CONSTRAINT FK_product_TO_cart
FOREIGN KEY (pID)
REFERENCES product (pID);
CREATE TABLE buy
(
orderno number NOT NULL COMMENT '주문번호',
res_id varchar2(20) NULL COMMENT '주문자아이디',
res_phone char(11) NULL COMMENT '주문자 성명',
res_requirement varchar2(100) NULL COMMENT '결제수단',
totalprice number NULL COMMENT '총가격',
orderdate date NULL COMMENT '주문날짜',
Field VARCHAR(255) NULL COMMENT '재기번호',
PRIMARY KEY (orderno)
) COMMENT '주문';
CREATE TABLE cart
(
pID varchar2(30) NOT NULL COMMENT '상품아이디',
pname varchar2(30) NULL COMMENT '상품이름',
amount number NULL COMMENT '수량',
pprice number(10) NULL COMMENT '가격',
how varchar2(30) NULL COMMENT '결제수단',
PRIMARY KEY (pID)
) COMMENT '주문확인';
CREATE TABLE member
(
memberid varchar2(15) NOT NULL COMMENT '회원아이디',
membername varchar2(20) NULL COMMENT '회원이름',
gender char(1) NULL COMMENT '성별',
age number NULL COMMENT '나이',
email varchar2(30) NULL COMMENT '이메일',
phone char(11) NULL COMMENT '전화번호',
grade varchar2(15) NULL COMMENT '등급',
enrolldate date NULL COMMENT '회원가입일',
delflag char(1) NULL COMMENT '회원탈퇴여부',
deletedate date NULL COMMENT '회원탈퇴일',
regflag char(1) NULL COMMENT '비회원여부',
password varchar2(300) NULL COMMENT '비밀번호',
PRIMARY KEY (memberid)
) COMMENT '회원';
CREATE TABLE product
(
pID varchar2(30) NOT NULL COMMENT '상품번호',
pname varchar2(30) NULL COMMENT '상품이름',
pprice number(10) NULL COMMENT '상품가격',
pcategory varchar2(30) NULL COMMENT '상품종류',
description varchar2(50) NULL COMMENT '상품설명',
pcompany varchar2(30) NULL COMMENT '제조회사',
pstock number NULL COMMENT '상품재고',
PRIMARY KEY (pID)
) COMMENT '상품';
CREATE TABLE product_io
(
iono number NOT NULL COMMENT '입출고번호',
pID varchar2(30) NOT NULL COMMENT '상품아이디',
orderno number NOT NULL COMMENT '주문번호',
amount number NULL COMMENT '입출고량',
status varchar2(10) NULL COMMENT '상태',
pDate date NULL COMMENT '입출고날짜',
memberid varchar2(15) NOT NULL COMMENT '회원아이디',
PRIMARY KEY (iono, pID, orderno)
) COMMENT '입출고테이블';
ALTER TABLE product_io
ADD CONSTRAINT FK_product_TO_product_io
FOREIGN KEY (pID)
REFERENCES product (pID);
ALTER TABLE product_io
ADD CONSTRAINT FK_buy_TO_product_io
FOREIGN KEY (orderno)
REFERENCES buy (orderno);
ALTER TABLE product_io
ADD CONSTRAINT FK_member_TO_product_io
FOREIGN KEY (memberid)
REFERENCES member (memberid);
ALTER TABLE cart
ADD CONSTRAINT FK_product_TO_cart
FOREIGN KEY (pID)
REFERENCES product (pID);
CREATE TABLE buy
(
orderno number NOT NULL,
res_id varchar2(20) ,
res_phone char(11) ,
res_requirement varchar2(100),
totalprice number ,
orderdate date ,
Field VARCHAR(255) ,
CONSTRAINT PK_buy PRIMARY KEY (orderno)
);
COMMENT ON TABLE buy IS '주문';
COMMENT ON COLUMN buy.orderno IS '주문번호';
COMMENT ON COLUMN buy.res_id IS '주문자아이디';
COMMENT ON COLUMN buy.res_phone IS '주문자 성명';
COMMENT ON COLUMN buy.res_requirement IS '결제수단';
COMMENT ON COLUMN buy.totalprice IS '총가격';
COMMENT ON COLUMN buy.orderdate IS '주문날짜';
COMMENT ON COLUMN buy.Field IS '재기번호';
CREATE TABLE cart
(
pID varchar2(30) NOT NULL,
pname varchar2(30),
amount number ,
pprice number(10) ,
how varchar2(30),
CONSTRAINT PK_cart PRIMARY KEY (pID)
);
COMMENT ON TABLE cart IS '주문확인';
COMMENT ON COLUMN cart.pID IS '상품아이디';
COMMENT ON COLUMN cart.pname IS '상품이름';
COMMENT ON COLUMN cart.amount IS '수량';
COMMENT ON COLUMN cart.pprice IS '가격';
COMMENT ON COLUMN cart.how IS '결제수단';
CREATE TABLE member
(
memberid varchar2(15) NOT NULL,
membername varchar2(20) ,
gender char(1) ,
age number ,
email varchar2(30) ,
phone char(11) ,
grade varchar2(15) ,
enrolldate date ,
delflag char(1) ,
deletedate date ,
regflag char(1) ,
password varchar2(300),
CONSTRAINT PK_member PRIMARY KEY (memberid)
);
COMMENT ON TABLE member IS '회원';
COMMENT ON COLUMN member.memberid IS '회원아이디';
COMMENT ON COLUMN member.membername IS '회원이름';
COMMENT ON COLUMN member.gender IS '성별';
COMMENT ON COLUMN member.age IS '나이';
COMMENT ON COLUMN member.email IS '이메일';
COMMENT ON COLUMN member.phone IS '전화번호';
COMMENT ON COLUMN member.grade IS '등급';
COMMENT ON COLUMN member.enrolldate IS '회원가입일';
COMMENT ON COLUMN member.delflag IS '회원탈퇴여부';
COMMENT ON COLUMN member.deletedate IS '회원탈퇴일';
COMMENT ON COLUMN member.regflag IS '비회원여부';
COMMENT ON COLUMN member.password IS '비밀번호';
CREATE TABLE product
(
pID varchar2(30) NOT NULL,
pname varchar2(30),
pprice number(10) ,
pcategory varchar2(30),
description varchar2(50),
pcompany varchar2(30),
pstock number ,
CONSTRAINT PK_product PRIMARY KEY (pID)
);
COMMENT ON TABLE product IS '상품';
COMMENT ON COLUMN product.pID IS '상품번호';
COMMENT ON COLUMN product.pname IS '상품이름';
COMMENT ON COLUMN product.pprice IS '상품가격';
COMMENT ON COLUMN product.pcategory IS '상품종류';
COMMENT ON COLUMN product.description IS '상품설명';
COMMENT ON COLUMN product.pcompany IS '제조회사';
COMMENT ON COLUMN product.pstock IS '상품재고';
CREATE TABLE product_io
(
iono number NOT NULL,
pID varchar2(30) NOT NULL,
orderno number NOT NULL,
amount number ,
status varchar2(10),
pDate date ,
memberid varchar2(15) NOT NULL,
CONSTRAINT PK_product_io PRIMARY KEY (iono, pID, orderno)
);
COMMENT ON TABLE product_io IS '입출고테이블';
COMMENT ON COLUMN product_io.iono IS '입출고번호';
COMMENT ON COLUMN product_io.pID IS '상품아이디';
COMMENT ON COLUMN product_io.orderno IS '주문번호';
COMMENT ON COLUMN product_io.amount IS '입출고량';
COMMENT ON COLUMN product_io.status IS '상태';
COMMENT ON COLUMN product_io.pDate IS '입출고날짜';
COMMENT ON COLUMN product_io.memberid IS '회원아이디';
ALTER TABLE product_io
ADD CONSTRAINT FK_product_TO_product_io
FOREIGN KEY (pID)
REFERENCES product (pID);
ALTER TABLE product_io
ADD CONSTRAINT FK_buy_TO_product_io
FOREIGN KEY (orderno)
REFERENCES buy (orderno);
ALTER TABLE product_io
ADD CONSTRAINT FK_member_TO_product_io
FOREIGN KEY (memberid)
REFERENCES member (memberid);
ALTER TABLE cart
ADD CONSTRAINT FK_product_TO_cart
FOREIGN KEY (pID)
REFERENCES product (pID);
// 주문
@Data
@Entity
public class Buy {
// 주문번호
@Id
private String orderno;
// 주문자아이디
private String resId;
// 주문자 성명
private String resPhone;
// 결제수단
private String resRequirement;
// 총가격
private String totalprice;
// 주문날짜
private LocalDate orderdate;
// 재기번호
private String field;
// 입출고테이블
@OneToMany(mappedBy = "buy")
private List<ProductIo> productIoList = new ArrayList<>();
}
// 주문확인
@Data
@Entity
public class Cart {
// 상품이름
private String pname;
// 수량
private String amount;
// 가격
private String pprice;
// 결제수단
private String how;
// 상품
@Id
@ManyToOne
@JoinColumn(name = "p_id")
private Product product;
}
// 회원
@Data
@Entity
public class Member {
// 회원아이디
@Id
private String memberid;
// 회원이름
private String membername;
// 성별
private String gender;
// 나이
private String age;
// 이메일
private String email;
// 전화번호
private String phone;
// 등급
private String grade;
// 회원가입일
private LocalDate enrolldate;
// 회원탈퇴여부
private String delflag;
// 회원탈퇴일
private LocalDate deletedate;
// 비회원여부
private String regflag;
// 비밀번호
private String password;
// 입출고테이블
@OneToMany(mappedBy = "member")
private List<ProductIo> productIoList = new ArrayList<>();
}
// 상품
@Data
@Entity
public class Product {
// 상품번호
@Id
private String pId;
// 상품이름
private String pname;
// 상품가격
private String pprice;
// 상품종류
private String pcategory;
// 상품설명
private String description;
// 제조회사
private String pcompany;
// 상품재고
private String pstock;
// 입출고테이블
@OneToMany(mappedBy = "product")
private List<ProductIo> productIoList = new ArrayList<>();
// 주문확인
@OneToMany(mappedBy = "product")
private List<Cart> cartList = new ArrayList<>();
}
@Data
public class ProductIoId implements Serializable {
private String iono;
private Product product;
private Buy buy;
}
// 입출고테이블
@Data
@Entity
@IdClass(ProductIoId.class)
public class ProductIo {
// 입출고번호
@Id
private String iono;
// 입출고량
private String amount;
// 상태
private String status;
// 입출고날짜
private LocalDate pDate;
// 상품
@Id
@ManyToOne
@JoinColumn(name = "p_id")
private Product product;
// 주문
@Id
@ManyToOne
@JoinColumn(name = "orderno")
private Buy buy;
// 회원
@ManyToOne
@JoinColumn(name = "memberid")
private Member member;
}
// 주문
public class Buy {
// 주문번호
public string Orderno { get; set; }
// 주문자아이디
public string ResId { get; set; }
// 주문자 성명
public string ResPhone { get; set; }
// 결제수단
public string ResRequirement { get; set; }
// 총가격
public string Totalprice { get; set; }
// 주문날짜
public DateTime Orderdate { get; set; }
// 재기번호
public string Field { get; set; }
}
// 주문확인
public class Cart {
// 상품아이디
public string PId { get; set; }
// 상품이름
public string Pname { get; set; }
// 수량
public string Amount { get; set; }
// 가격
public string Pprice { get; set; }
// 결제수단
public string How { get; set; }
}
// 회원
public class Member {
// 회원아이디
public string Memberid { get; set; }
// 회원이름
public string Membername { get; set; }
// 성별
public string Gender { get; set; }
// 나이
public string Age { get; set; }
// 이메일
public string Email { get; set; }
// 전화번호
public string Phone { get; set; }
// 등급
public string Grade { get; set; }
// 회원가입일
public DateTime Enrolldate { get; set; }
// 회원탈퇴여부
public string Delflag { get; set; }
// 회원탈퇴일
public DateTime Deletedate { get; set; }
// 비회원여부
public string Regflag { get; set; }
// 비밀번호
public string Password { get; set; }
}
// 상품
public class Product {
// 상품번호
public string PId { get; set; }
// 상품이름
public string Pname { get; set; }
// 상품가격
public string Pprice { get; set; }
// 상품종류
public string Pcategory { get; set; }
// 상품설명
public string Description { get; set; }
// 제조회사
public string Pcompany { get; set; }
// 상품재고
public string Pstock { get; set; }
}
// 입출고테이블
public class ProductIo {
// 입출고번호
public string Iono { get; set; }
// 상품아이디
public string PId { get; set; }
// 주문번호
public string Orderno { get; set; }
// 입출고량
public string Amount { get; set; }
// 상태
public string Status { get; set; }
// 입출고날짜
public DateTime PDate { get; set; }
// 회원아이디
public string Memberid { get; set; }
}
출력 결과