๋ฐ์ํ
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');