|
1. 프로젝트 개요
2. 프로젝트 일정
3. 프로젝트 산출물
3-1. 관련 업무 요구 분석 및 개체관계
3-2. ERD
3-3. ERwin
3-4. 정규화
3-5. 테이블 작성, 인덱스 생성
3-6. 데이터 입력
3-7. 데이터 수정 및 삭제
3-8. 데이터 검색
3-9. View
3-10. 데이터 갱신 후 회복
|
Index 항목을 클릭하면 해당 위치로 이동합니다.
해당 게시물은 Oracle Database Project의 요약본입니다.
3.5 [ Project 5 ] 테이블 생성 & 인덱스 생성
3.5.1 데이터베이스 테이블 생성
CREATE TABLE Member(
member_id VARCHAR2(10) PRIMARY KEY,
car_id VARCHAR2(10),
member_name VARCHAR2(13),
address VARCHAR2(50),
phone_number VARCHAR2(13)
);
CREATE TABLE Admin(
admin_id VARCHAR2(10) PRIMARY KEY,
admin_name VARCHAR2(10),
admin_phone VARCHAR2(13),
admin_email VARCHAR2(30)
);
CREATE TABLE ParkingLot(
parking_slot VARCHAR2(4) PRIMARY KEY,
admin_id VARCHAR2(10),
rate NUMBER(10),
FOREIGN KEY (admin_id) REFERENCES Admin (admin_id)
);
CREATE TABLE Parking(
parking_id VARCHAR2(10) PRIMARY KEY,
member_id VARCHAR2(10),
parking_slot VARCHAR2(4),
in_time Date,
out_time Date,
FOREIGN KEY (member_id) REFERENCES Member (member_id),
FOREIGN KEY (parking_slot) REFERENCES ParkingLot (parking_slot) ON DELETE CASCADE
);
CREATE TABLE Reservation(
reservation_id NUMBER(10) PRIMARY KEY,
member_id VARCHAR2(10),
parking_slot VARCHAR2(4),
in_time Date,
out_time Date,
FOREIGN KEY (member_id) REFERENCES Member (member_id) ON DELETE CASCADE,
FOREIGN KEY (parking_slot) REFERENCES ParkingLot (parking_slot)
);
CREATE TABLE Pay(
pay_id NUMBER(10) PRIMARY KEY,
parking_slot VARCHAR2(4),
parking_fee NUMBER(10),
payment VARCHAR2(10),
admin_id VARCHAR2(13),
FOREIGN KEY (parking_slot) REFERENCES ParkingLot (parking_slot),
FOREIGN KEY (admin_id) REFERENCES Admin (admin_id)
FOREIGN KEY (member_id) REFERENCES Member (member_id) ON DELETE CASCADE
);
3.5.2 인덱스 생성
Index를 생성하는 이유
- 생성한 Database안에서 데이터의 검색을 빠르게 수행하기 위해 사용된다.
Table 내에서 기본키로 결정된 속성을 Table생성 시 자동적으로 Index가 만들어지기 때문에 따로 Index를 생성하지 않아도 된다.
3.6 [ Project 6 ] 데이터 입력
3.6.1 생성된 테이블에 데이터 입력
- ADMIN Table
INSERT INTO ADMIN VALUES('Alpha','김일층','010-3352-7351','wkek54@naver.com');
INSERT INTO ADMIN VALUES('Beta','박이층','010-8874-5663','cngks123@naver.com');
INSERT INTO ADMIN VALUES('Gamma','정삼층','010-7332-6654','kosh5099@naver.com');
INSERT INTO ADMIN VALUES('Delta','최사층','010-5882-4553','cdyywe1@daum.net');
- MEMBER Table
INSERT INTO MEMBER VALUES('LJK12','42머6325','이재근','충청남도 논산시 내동 30-1','010-4704-2874');
INSERT INTO MEMBER VALUES('LSH23','18가1029','이성호','충청남도 논산시 내동 98-4','010-8497-4884');
INSERT INTO MEMBER VALUES('YHW34','20사2837','윤효원','충청남도 논산시 와야리 26-3','010-7911-1466');
INSERT INTO MEMBER VALUES('CYE45','96로4651','최여은','충청남도 논산시 와야리 125','010-8916-5564');
INSERT INTO MEMBER VALUES('CSH56','79나9876','최서현','충청남도 논산시 내동 45-1','010-9495-2577');
INSERT INTO MEMBER VALUES('LWK67','66자1646','이용경','충청남도 논산시 와야리 246-5','010-4957-1948');
INSERT INTO MEMBER VALUES('KSM78','12거1846','김신명','충청남도 논산시 내동 시민로 132','010-6852-4915');
INSERT INTO MEMBER VALUES('JWS89','49모7979','장원석','충청남도 논산시 내동 33-5','010-7738-4957');
INSERT INTO MEMBER VALUES('KCW90','88아9487','김찬우','충청남도 논산시 내동 47-1','010-2598-5594');
INSERT INTO MEMBER VALUES('JSY01','54가1384','정성연','충청남도 논산시 내동 40-64','010-8716-4435');
INSERT INTO MEMBER VALUES('CJW52','88모1489','최제우','충청남도 논산시 내동 60-12','010-5546-4187');
INSERT INTO MEMBER VALUES('KSJ19','90거7152','강수지','충청남도 논산시 내동 시민로 112','010-8447-5564');
INSERT INTO MEMBER VALUES('KHS20','84고7412','고현성','충청남도 논산시 와야리 29-13','010-8844-1124');
INSERT INTO MEMBER VALUES('LJW38','54미8475','이정우','충청남도 논산시 내동 38-9','010-7531-9513');
INSERT INTO MEMBER VALUES('HYH31','49저6541','한영환','충청남도 논산시 내동 31-1','010-4845-1674');
INSERT INTO MEMBER VALUES('MJY11','27모4949','마제영','충청남도 논산시 내동 12-3','010-7485-9685');
INSERT INTO MEMBER VALUES('PJH63','13조9632','박지현','충청남도 논산시 내동 97-3','010-1452-6523');
- PARKINGLOT Table
INSERT INTO PARKINGLOT VALUES('A-01','Alpha','2000');
INSERT INTO PARKINGLOT VALUES('A-02','Alpha','2000');
INSERT INTO PARKINGLOT VALUES('A-03','Alpha','2000');
INSERT INTO PARKINGLOT VALUES('A-04','Alpha','2000');
INSERT INTO PARKINGLOT VALUES('A-05','Alpha','2000');
INSERT INTO PARKINGLOT VALUES('B-01','Beta','1500');
INSERT INTO PARKINGLOT VALUES('B-02','Beta','1500');
INSERT INTO PARKINGLOT VALUES('B-03','Beta','1500');
INSERT INTO PARKINGLOT VALUES('B-04','Beta','1500');
INSERT INTO PARKINGLOT VALUES('B-05','Beta','1500');
INSERT INTO PARKINGLOT VALUES('C-01','Gamma','1000');
INSERT INTO PARKINGLOT VALUES('C-02','Gamma','1000');
INSERT INTO PARKINGLOT VALUES('C-03','Gamma','1000');
INSERT INTO PARKINGLOT VALUES('C-04','Gamma','1000');
INSERT INTO PARKINGLOT VALUES('C-05','Gamma','1000');
INSERT INTO PARKINGLOT VALUES('D-01','Delta','1000');
INSERT INTO PARKINGLOT VALUES('D-02','Delta','1000');
INSERT INTO PARKINGLOT VALUES('D-03','Delta','1000');
INSERT INTO PARKINGLOT VALUES('D-04','Delta','1000');
INSERT INTO PARKINGLOT VALUES('D-05','Delta','1000');
- PARKING Table
INSERT INTO PARKING VALUES('1','CSH56','A-01',TO_DATE('20/04/15 07:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 10:00','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('2','KSJ19','B-05',TO_DATE('20/04/15 08:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 22:00','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('3','JWS89','D-02',TO_DATE('20/04/15 09:30','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 16:30','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('4','KCW90','C-04',TO_DATE('20/04/15 10:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 14:00','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('5','CJW52','A-02',TO_DATE('20/04/15 10:30','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 23:30','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('6','JSY01','D-04',TO_DATE('20/04/15 11:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 14:00','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('7','YHW34','B-01',TO_DATE('20/04/15 11:30','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 15:00','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('8','HYH31','A-05',TO_DATE('20/04/15 12:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 18:00','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('9','KHS20','C-04',TO_DATE('20/04/15 12:30','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 21:00','RR/MM/DD HH24:MI'));
INSERT INTO PARKING VALUES('10','LSH23','B-02',TO_DATE('20/04/15 16:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 20:00','RR/MM/DD HH24:MI'));
- RESERVATION Table
INSERT INTO RESERVATION VALUES('1','YHW34','A-05',TO_DATE('20/04/15 07:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 10:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('2','MJY11','B-03',TO_DATE('20/04/15 18:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 20:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('3','CYE45','A-01',TO_DATE('20/04/15 14:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 16:30','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('4','LJK12','C-02',TO_DATE('20/04/15 04:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 09:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('5','LJW38','A-03',TO_DATE('20/04/15 14:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 23:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('6','LSH23','C-01',TO_DATE('20/04/15 13:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 14:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('7','CSH56','D-05',TO_DATE('20/04/15 11:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 15:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('8','KSM78','A-04',TO_DATE('20/04/15 09:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 18:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('9','JWS89','A-04',TO_DATE('20/04/15 19:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 21:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('10','LWK67','B-03',TO_DATE('20/04/15 10:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 20:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('11','PJH63','D-01',TO_DATE('20/04/15 13:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 21:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('12','JSY01','A-01',TO_DATE('20/04/15 19:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 21:00','RR/MM/DD HH24:MI'));
INSERT INTO RESERVATION VALUES('13','LJK12','C-03',TO_DATE('20/04/15 10:00','RR/MM/DD HH24:MI'),TO_DATE('20/04/15 23:00','RR/MM/DD HH24:MI'));
- PAY Table
INSERT INTO PAY VALUES('1','C-02','LJK12','5000','카드','Gamma');
INSERT INTO PAY VALUES('2','A-05','YHW34','6000','카드','Alpha');
INSERT INTO PAY VALUES('3','A-01','CSH56','6000','현금','Alpha');
INSERT INTO PAY VALUES('4','C-04','KCW90','4000','카드','Gamma');
INSERT INTO PAY VALUES('5','C-01','LSH23','1000','카드','Gamma');
INSERT INTO PAY VALUES('6','D-04','JSY01','3000','카드','Delta');
INSERT INTO PAY VALUES('7','D-05','CSH56','4000','현금','Delta');
INSERT INTO PAY VALUES('8','B-01','YHW34','5250','카드','Beta');
INSERT INTO PAY VALUES('9','A-01','CYE45','5000','카드','Alpha');
INSERT INTO PAY VALUES('10','D-02','JWS89','5000','카드','Delta');
INSERT INTO PAY VALUES('11','A-04','KSM78','18000','현금','Alpha');
INSERT INTO PAY VALUES('12','A-05','HYH31','12000','카드','Alpha');
INSERT INTO PAY VALUES('13','B-02','LSH23','6000','카드','Beta');
INSERT INTO PAY VALUES('14','B-04','MJY11','3000','카드','Beta');
INSERT INTO PAY VALUES('15','B-03','LWK67','15000','현금','Beta');
INSERT INTO PAY VALUES('16','A-04','JWS89','4000','카드','Alpha');
INSERT INTO PAY VALUES('17','D-01','PJH63','8000','카드','Delta');
INSERT INTO PAY VALUES('18','A-01','JSY01','4000','카드','Alpha');
INSERT INTO PAY VALUES('19','C-04','KHS20','8500','현금','Gamma');
INSERT INTO PAY VALUES('20','B-05','KSJ19','21000','카드','Beta');
INSERT INTO PAY VALUES('21','A-03','LJW38','18000','카드','Alpha');
INSERT INTO PAY VALUES('22','C-03','LJK12','13000','카드','Gamma');
INSERT INTO PAY VALUES('23','A-02','CJW52','26000','현금','Alpha');