Microsoft Word - Software Project Database Design_ver1.01_.docx

Size: px

Start display at page:

Download "Microsoft Word - Software Project Database Design_ver1.01_.docx"

민좌 사
5 years ago
Views:

1 Software Project Database Design Version Library DB Scenario Team

2 개정이력 개정번호개정일자개정자개정사유개정내용승인일자비고 서오석신규제정문서의초안생성

3 Table of Contents 1.0 ER Diagram... 5 Logical Review... 5 Physical Review Table Detail Design DDL CREATE SQL 저자 도서위치정보 출판사정보 도서정보 저자서적분류 회원직급 대출권한 회원 대출정보 블랙리스트 Data loading 저자 도서위치정보 출판사정보 도서정보 저자서적분류 회원직급 대출권한 회원... 21

4 4.9 대출정보 블랙리스트 Grant SQL... 23

5 1.0 ER Diagram Logical Review Physical Review

6 2.0 Table Detail Design 시스템명 도서대출시스템 페이지 1 테이블 ID USERS 작성일 테이블명 회원 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 회원번호 USER_NUM NUMBER(8) N Y 학번 2 성별 SEX VARCHAR2(1) 1: 남자 2: 여자 3 성 FIRST_NAME VARCHAR2(15) 4 이름 LAST_NAME VARCHAR2(15) 5 소속 COURSE VARCHAR2(15) 6 전화번호 PHONE_NUM VARCHAR2(15) BER 7 이메일 E_MAIL VARCHAR2(25) 8 집주소 ADDRESS VARCHAR2(100) 9 권한번호 AUTHORITY_N NUMBER(8) Y UM 10 회원직급번호 TYPE_NUM NUMBER(2) Y Primary key USER_NUM 시스템명 도서대출시스템 페이지 2 테이블 ID LEND_AUTHORITY 작성일 테이블명 대출권한 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 권한번호 AUTHORITY_N NUMBER(8) N Y UM 2 권한별내용 AUTHORITY_D SC VARCHAR2(25) 1 1: 일반 2: 장기연체 Primary key AUTHORITY_NUM

7 시스템명 도서대출시스템 페이지 3 테이블 ID USER_TYPE 작성일 테이블명 회원직급 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 회원직급번호 TYPE_NUM NUMBER(2) N Y 1: 대학생 2: 대학원생 3: 교수 2 직급이름 TYPE_DESC VARCHAR2(15) 3 직급별대여권수 GRADE_DESC NUMBER(2) Primary key TYPE_NUM 시스템명 도서대출시스템 페이지 4 테이블 ID BOOK 작성일 테이블명 도서정보 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 도서번호 BOOK_NUM NUMBER(8) N Y 2 도서위치번호 LOCATION_NU NUMBER(4) N Y M 3 출판사번호 PUBLISHER_N NUMBER(8) Y UM 4 도서이름 TITLE VARCHAR2(100) 5 도서카테고리 TYPE VARCHAR2(10) 6 출판일자 PUBLISHED_D DATE ATE 7 도서표준 STANDARD VARCHAR2(1) 1:ISBN 2:ISSN 8 도서표준코드 STANDARD_C ODE VARCHAR2(13) Primary key BOOK_NUM

8 시스템명 도서대출시스템 페이지 5 테이블 ID BOOK_LEND_INFO 작성일 테이블명 대여정보 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 회원번호 USER_NUM NUMBER(8) N Y 2 도서번호 BOOK_NUM NUMBER(8) N Y 3 도서위치번호 LOCATION_NU M NUMBER(4) 4 대출일자 LEND_DATE DATE 5 반납일자 RETURN_DATE DATE Primary key USER_NUM, BOOK_NUM Y 시스템명 도서대출시스템 페이지 6 테이블 ID LOACTION 작성일 테이블명 도서위치정보 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 위치번호 LOCATION_NU M 2 위치명 LOCATION_DE SC Primary key LOCATION_NUM NUMBER(4) N Y VARCHAR2(10) 시스템명 도서대출시스템 페이지 7 테이블 ID PUBLISHER 작성일 테이블명 출판사정보 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 출판사번호 PUBLISHER_N NUMER(8) N Y UM 2 출판사이름 PUBLISHER_N VARCHAR2(30) AME 3 출판사주소 ADDRESSS VARCHAR2(100) 4 전화번호 PHONE_NUM VARCHAR2(15) BER 5 이메일 E_MAIL VARCHAR2(25)

9 Primary key PUBLISHER_NUM 시스템명 도서대출시스템 페이지 8 테이블 ID WROTE 작성일 테이블명 저자서적분류 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 도서번호 BOOK_NUM NUMBER(8) N Y Y 2 저자번호 AUTHOR_NU NUMBER(8) N Y Y M 3 저자분류 SEQUENCE VARCHAR2(1) 1: 주저자 2: 부저자 Primary key BOOK_NUM, AUTHOR_NUM 시스템명 도서대출시스템 페이지 9 테이블 ID AUTHOR 작성일 테이블명 저자 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 저자번호 AUTHOR_NU NUMBER(8) N Y M 2 성별 SEX VARCHAR2(1) 1: 남자 2: 여자 3 이름 LAST_NAME VARCHAR2(15) 4 성 FIRST_NAME VARCHAR2(15) 5 주소 ADDRESS VARCHAR2(100) 6 전화번호 PHONE_NUM VARCHAR2(15) BER 7 이메일 E_MAIL VARCHAR2(25) Primary key AUTHOR_NUM

10 시스템명 도서대출시스템 페이지 10 테이블 ID BLACKLIST 작성일 테이블명 블랙리스트 작성자 서오석 순번 한글명 영문명 데이터타입 ( 길이 ) NULL Default PK FK 속성정의 1 회원번호 LIST_NUM NUMBER(8) N Y 학번 2 불량사유 DESCRIPTION VARCHAR2(200) Primary key BLACKLIST 3.0 DDL CREATE SQL 3.1 저자 CREATE TABLE AUTHOR ( AUTHOR_NUM NUMBER(8) NOT NULL, SEX VARCHAR2(1) NULL, LAST_NAME VARCHAR2(15) NULL, FIRST_NAME VARCHAR2(15) NULL, ADDRESS VARCHAR2(100) NULL, PHONE_NUMBER VARCHAR2(15) NULL, E_MAIL VARCHAR2(25) NULL, CONSTRAINT XPKAUTHOR PRIMARY KEY (AUTHOR_NUM) ); 3.2 도서위치정보 CREATE TABLE LOCATION ( LOCATION_NUM NUMBER(4) NOT NULL, LOCATION_DESC VARCHAR2(10) NULL, CONSTRAINT XPKLOCATION PRIMARY KEY (LOCATION_NUM) ); 3.3 출판사정보 CREATE TABLE PUBLISHER ( PUBLISHER_NUM NUMBER(8) NOT NULL, PUBLISHER_NAME VARCHAR2(15) NULL, ADDRESS VARCHAR2(100) NULL, PHONE_NUMBER VARCHAR2(15) NULL, E_MAIL VARCHAR2(25) NULL, CONSTRAINT XPKPUBLISHER PRIMARY KEY (PUBLISHER_NUM) );

11 3.4 도서정보 CREATE TABLE BOOK ( BOOK_NUM NUMBER(8) NOT NULL, LOCATION_NUM NUMBER(4) NOT NULL, PUBLISHER_NUM NUMBER(8) NOT NULL, TITLE VARCHAR2(100) NULL, TYPE VARCHAR2(10) NULL, PUBLISHED_DATE DATE NULL, STANDARD VARCHAR2(1) NULL, STANDARD_CODE VARCHAR2(13) NULL, CONSTRAINT XPKBOOK PRIMARY KEY (BOOK_NUM), CONSTRAINT R_15 FOREIGN KEY (LOCATION_NUM) REFERENCES LOCATION, CONSTRAINT R_9 FOREIGN KEY (PUBLISHER_NUM) REFERENCES PUBLISHER ); 3.5 저자서적분류 CREATE TABLE WROTE ( BOOK_NUM NUMBER(8) NOT NULL, SEQUENCE VARCHAR2(1) NULL, AUTHOR_NUM NUMBER(8) NOT NULL, CONSTRAINT XPKWROTE PRIMARY KEY (AUTHOR_NUM, BOOK_NUM), CONSTRAINT R_12 FOREIGN KEY (AUTHOR_NUM) REFERENCES AUTHOR, CONSTRAINT R_11 FOREIGN KEY (BOOK_NUM) REFERENCES BOOK ); 3.6 회원직급 CREATE TABLE USER_TYPE ( TYPE_NUM NUMBER(2) NOT NULL, TYPE_DESC VARCHAR2(15) NULL, GRADE_DESC NUMBER(2) NULL, CONSTRAINT XPKUSER_TYPE PRIMARY KEY (TYPE_NUM) );

12 3.7 대출권한 CREATE TABLE LEND_AUTHORITY ( AUTHORITY_NUM NUMBER(2) NOT NULL, AUTHORITY_DESC VARCHAR2(25) NULL, CONSTRAINT XPKLEND_AUTHORITY PRIMARY KEY (AUTHORITY_NUM) ); 3.8 회원 CREATE TABLE USERS ( USER_NUM NUMBER(8) NOT NULL, AUTHORITY_NUM NUMBER(8) NOT NULL, SEX VARCHAR2(1) NULL, FIRST_NAME VARCHAR2(15) NULL, LAST_NAME VARCHAR2(15) NULL, COURSE VARCHAR2(15) NULL, PHONE_NUMBER VARCHAR2(15) NULL, E_MAIL VARCHAR2(25) NULL, ADDRESS VARCHAR2(100) NULL, TYPE_NUM NUMBER(2) NOT NULL, CONSTRAINT XPKUSERS PRIMARY KEY (USER_NUM), CONSTRAINT R_17 FOREIGN KEY (AUTHORITY_NUM) REFERENCES LEND_AUTHORITY, CONSTRAINT R_3 FOREIGN KEY (TYPE_NUM) REFERENCES USER_TYPE ); 3.9 대출정보 CREATE TABLE BOOK_LEND_INFO ( LEND_NUM NUMBER(8) NOT NULL, LEND_DATE DATE NULL, RETURN_DATE DATE NULL, BOOK_NUM NUMBER(8) NOT NULL, USER_NUM NUMBER(8) NOT NULL, CONSTRAINT XPKBOOK_LEND_INFO PRIMARY KEY (LEND_NUM), CONSTRAINT R_20 FOREIGN KEY (BOOK_NUM) REFERENCES BOOK, CONSTRAINT R_19 FOREIGN KEY (USER_NUM)

13 REFERENCES USERS ); 3.10 블랙리스트 CREATE TABLE BLACKLIST ( LIST_NUM NUMBER(8) NOT NULL, USER_NUM NUMBER(8) NOT NULL, DESCRIPTION VARCHAR2(200) NULL, CONSTRAINT XPKBLACKLIST PRIMARY KEY (LIST_NUM) ); 4.0 Data loading 4.1 저자 INSERT INTO AUTHOR ('4', '2', 'Noh', 'Taesin', 'Seoul', ' ', 'Notae@daum.net'); INSERT INTO AUTHOR ('5', '1', 'Sung', 'Kiwon', 'Busan', ' ', 'Soul@naver.com'); INSERT INTO AUTHOR ('6', '1', 'Kim', 'Jieun', 'Anyang', ' ', 'Elegarden@smu.ac.kr'); INSERT INTO AUTHOR ('7', '2', 'Iron', 'Maiden', 'LA', ' ', 'fear@nate.com'); INSERT INTO AUTHOR ('8', '1', 'Dream', 'Theater', 'Boston', ' ', 'Dream1982@nate.com'); INSERT INTO AUTHOR ('9', '1', 'Off', 'Spring', 'Suyoo', ' ', 'spring@naver.com'); INSERT INTO AUTHOR ('10', '1', 'Green', 'Day', 'Walla', ' ', 'Walla2@smu.ac.kr'); INSERT INTO AUTHOR ('11', '1', 'Ivy', 'Hyori', 'Music', ' ', 'Sexygirl@daum.net'); INSERT INTO AUTHOR ('12', '1', 'Jung', 'Down', 'Heart', ' ', 'Mylover@smu.ac.kr'); INSERT INTO AUTHOR

14 ('13', '2', 'Seo', 'Osuk', 'Hongeun', ' ', INSERT INTO AUTHOR ('14', '1', 'Rionel', 'Messi', 'Argen', ' ', INSERT INTO AUTHOR ('15', '1', 'Frank', 'Lampard', 'England', ' ', 4.2 도서위치정보 INSERT INTO LOCATION ('1', 'Fiction'); INSERT INTO LOCATION ('2', 'Computer'); INSERT INTO LOCATION ('3', 'History'); INSERT INTO LOCATION ('4', 'Science'); INSERT INTO LOCATION ('5', 'Biography'); INSERT INTO LOCATION ('6', 'Nonfiction'); INSERT INTO LOCATION ('7', 'Finance'); INSERT INTO LOCATION ('8', 'Art'); INSERT INTO LOCATION ('9', 'Etc'); 4.3 출판사정보 ('1', 'Bantam', 'Suwon', ' ', ('2', 'Penguin USA', 'Tokyo', ' ',

15 ('3', 'Addison-Wesley', 'Hawai', ' ', ('4', 'Press', 'Seoul', ' ', ('5', 'AK Peters', 'Anyang', ' ', ('6', 'Palgrave', 'Busan', ' ', ('7', 'Random House', 'Ansan', ' ', ('8', 'Black Rabbit', 'Kyoto', ' ', ('9', 'Oxford Univ', 'Osaka', ' ', ('10', 'Skylight Pub', 'Inchon', ' ', ('11', 'School Special', 'Gumi', ' ', ('12', 'McGraw Hill', 'Jeju', ' ', ('13', 'Prentice Hall', 'Sanghai', ' ', ('14', 'Perseus', 'Boston', ' ', ('15', 'Taschen', 'Seoul', ' ', ('16', 'McGraw Media', 'Suwon', ' ',

16 ('17', 'John Sons', 'Inchon', ' ', ('18', 'Independent', 'Suwon', ' ', 4.4 도서정보 ('1', '1', '1', '50 Great Short Stories', 'Fiction', ' ', '1', ' '); ('2', '1', '2', 'Charlie and the Chocolate Factory', 'Fiction', ' ', '1', ' '); ('3', '2', '3', 'Art of Computer Programming Vol.1', 'Computer', ' ', '1', ' '); ('4', '2', '4', 'Ai Techniques for Game Programming', 'Computer', ' ', '1', ' X'); ('5', '2', '5', 'Computer Arithmetic Algorithms', 'Computer', ' ', '1', ' '); ('6', '3', '6', 'Andreas Capellanus On Love?', 'History', ' ', '1', ' '); ('7', '3', '6', 'Langland''s Early Modern Id', 'History', ' ', '1', ' X'); ('8', '1', '2', 'Around the World in 80 Days', 'Fiction', ' ', '1', ' X'); ('9', '1', '7', 'The Giver', 'Fiction', ' ', '1', ' '); ('10', '2', '5', 'Data Structure', 'Computer', ' ', '2', ' '); ('11', '4', '8', 'Food And the World', 'Science', ' ', '1', ' '); ('12', '5', '9', 'Francis Crick and James Watson', 'Biography', ' ', '1', ' ');

17 ('13', '5', '9', 'Galileo Galilei', 'Biography', ' ', '2', ' '); ('14', '2', '10', 'Java Methods a And Ab', 'Computer', ' ', '1', ' '); ('15', '6', '11', 'Total Reading', 'Nonfiction', ' ', '2', ' X'); ('16', '7', '12', 'The Big Book of Flip Charts', 'Finance', ' ', '2', ' '); ('17', '7', '13', 'Options, Futures, and Other Derivatives', 'Finance', ' ', '2', ' '); ('18', '8', '14', 'World Graphic Design', 'Art', ' ', '1', ' '); ('19', '8', '15', 'Marc Chagall (Taschen Portfolio)', 'Art', ' ', '1', ' '); ('20', '2', '16', 'Oracle Database 10G Xml N SQL', 'Computer', ' ', '2', ' '); ('21', '2', '17', 'Enterprise Architecture Planning', 'Computer', ' ', '1', ' '); ('22', '2', '18', 'Maximum Lego Nxt', 'Computer', ' ', '2', ' '); ('23', '2', '4', 'Developing Drivers', 'Computer', ' ', '1', ' '); ('24', '2', '16', 'Aix 5L Administration', 'Computer', ' ', '1', ' '); ('25', '2', '5', 'Games And Simulations', 'Computer', ' ', '1', ' '); ('26', '9', '10', 'Ribbonwork The Complete Guide', 'Etc', ' ', '1', ' '); ('27', '9', '10', 'The Secrets of Fashioning Flowers', 'Etc', ' ', '2', ' ');

18 ('28', '7', '13', 'Grid Computing', 'Finance', ' ', '1', ' '); ('29', '7', '9', 'Introduction to the Economics', 'Finance', ' ', '1', ' '); ('30', '2', '15', 'Google Pagerank and Beyond', 'Computer', ' ', '2', ' '); ('31', '2', '13', 'Human-Computer Interaction', 'Computer', ' ', '1', ' '); 4.5 저자서적분류 ('1', '1', '1'); ('1', '5', '2'); ('2', '2', '1'); ('3', '3', '1'); ('4', '4', '1'); ('5', '5', '1'); ('6', '6', '1'); ('6', '13', '2'); ('7', '7', '1'); ('8', '8', '1');

19 ('9', '9', '1'); ('9', '3', '2'); ('10', '10', '1'); ('11', '11', '1'); ('12', '12', '1'); ('13', '13', '1'); ('14', '14', '1'); ('15', '15', '1'); ('16', '14', '1'); ('16', '4', '2'); ('17', '7', '1'); ('17', '15', '2'); ('18', '4', '1'); ('18', '7', '2');

20 ('19', '2', '1'); ('20', '2', '1'); ('21', '7', '1'); ('22', '9', '1'); ('22', '3', '2'); ('23', '10', '1'); ('23', '5', '2'); ('24', '5', '1'); ('24', '11', '2'); ('25', '13', '1'); ('26', '12', '1'); ('27', '12', '1'); ('28', '9', '1'); ('29', '13', '1');

21 ('29', '7', '2'); ('30', '6', '1'); ('30', '14', '2'); ('31', '3', '1'); 4.6 회원직급 insert into user_type values(1,' 대학생 ',5); insert into user_type values(2,' 대학원생 ',10); insert into user_type values(3,' 교수 ',15); 4.7 대출권한 insert into lend_authority values(1,' 일반 '); insert into lend_authority values(2,' 장기연체 '); 4.8 회원

22 대출정보 insert into book_lend_info values('20', ,' ',' '); insert into book_lend_info values('19', ,' ',' '); insert into book_lend_info values('15', ,' ',' '); insert into book_lend_info

23 values('18', ,' ',' '); insert into book_lend_info values('17', ,' ',' '); insert into book_lend_info values('16', ,' ',' '); insert into book_lend_info values('10', ,' ',' '); insert into book_lend_info values('11', ,' ',' '); insert into book_lend_info values('12', ,' ',' '); 4.10 블랙리스트 INSERT INTO BLACKLIST ('1', ' ', ' 책분실 '); INSERT INTO BLACKLIST ('2', ' ', ' 장기미반납 '); INSERT INTO BLACKLIST ('3', ' ', ' 장기미반납 '); INSERT INTO BLACKLIST ('4', ' ', ' 장기미반납 '); 5.0 Grant SQL GRANT SELECT ON USERS TO ADMIN; GRANT SELECT ON USER_TYPE TO ADMIN; GRANT SELECT ON LEND_AUTHORITY TO ADMIN; GRANT SELECT ON BLACKLIST TO ADMIN; GRANT SELECT ON BOOK_LEND_INFO TO ADMIN; GRANT SELECT ON LOCATION TO ADMIN; GRANT SELECT ON PUBLISHER TO ADMIN; GRANT SELECT ON AUTHOR TO ADMIN; GRANT SELECT ON WROTE TO ADMIN; GRANT SELECT ON BOOK TO ADMIN; GRANT INSERT ON USERS TO ADMIN; GRANT INSERT ON USER_TYPE TO ADMIN; GRANT INSERT ON LEND_AUTHORITY TO ADMIN; GRANT INSERT ON BLACKLIST TO ADMIN; GRANT INSERT ON BOOK_LEND_INFO TO ADMIN; GRANT INSERT ON LOCATION TO ADMIN; GRANT INSERT ON PUBLISHER TO ADMIN;

24 GRANT INSERT ON AUTHOR TO ADMIN; GRANT INSERT ON WROTE TO ADMIN; GRANT INSERT ON BOOK TO ADMIN; GRANT UPDATE ON USERS TO ADMIN; GRANT UPDATE ON USER_TYPE TO ADMIN; GRANT UPDATE ON LEND_AUTHORITY TO ADMIN; GRANT UPDATE ON BLACKLIST TO ADMIN; GRANT UPDATE ON BOOK_LEND_INFO TO ADMIN; GRANT UPDATE ON LOCATION TO ADMIN; GRANT UPDATE ON PUBLISHER TO ADMIN; GRANT UPDATE ON AUTHOR TO ADMIN; GRANT UPDATE ON WROTE TO ADMIN; GRANT UPDATE ON BOOK TO ADMIN; GRANT DELETE ON USERS TO ADMIN; GRANT DELETE ON USER_TYPE TO ADMIN; GRANT DELETE ON LEND_AUTHORITY TO ADMIN; GRANT DELETE ON BLACKLIST TO ADMIN; GRANT DELETE ON BOOK_LEND_INFO TO ADMIN; GRANT DELETE ON LOCATION TO ADMIN; GRANT DELETE ON PUBLISHER TO ADMIN; GRANT DELETE ON AUTHOR TO ADMIN; GRANT DELETE ON WROTE TO ADMIN; GRANT DELETE ON BOOK TO ADMIN;

10.ppt

10.ppt : SQL. SQL Plus. JDBC. SQL >> SQL create table : CREATE TABLE ( ( ), ( ),.. ) SQL >> SQL create table : id username dept birth email id username dept birth email CREATE TABLE member ( id NUMBER NOT NULL