231014_DB 과제물

각 테이블을 만든 뒤 데이터를 넣었다.

각자 다르게 하나하나 쓸 자신은 없어서 그냥 이름/주소는 적당히 돌려서 넣었고 어찌되었든 각 테이블에 들어가는 데이터의 고유 key가 오류가 없으면 될테니 1~10씩 정수 돌림자를 넣었다. 

 

select * from Customer_Information; -- 고객정보
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('kim', 'seoul', '01012345678', 'qwer@gmail', '2020-05-14');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'gyeonggi-do', '01023456789', 'asdf@gmail', '2022-01-26');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('hong', 'gyeonggi-do', '01022223333', 'zxcv@gmail', '2019-07-17');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('gang', 'gangwon-do', '01033334444', 'wert@gmail', '2018-03-20');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('jeong', 'busan', '01055556666', 'sdfg@gmail', '2015-04-08');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('choi', 'daejeon', '01066667777', 'xcvb@gmail', '2016-12-24');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'busan', '01077778888', 'erty@gmail', '2019-08-27');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('kim', 'chungnam', '01088889999', 'dfgh@gmail', '2021-04-06');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('song', 'seoul', '01044445555', 'cvbn@gmail', '2023-01-18');
INSERT INTO Customer_Information (customer_name, Address, tell, email, membership) VALUES ('Lee', 'seoul', '01011112222', 'rtyu@gmail', '2020-11-13');

select * from product_name; --상품명
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('1', 'ring', 'silver', '7.5g', 1);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('2', 'ring', 'gold', '3.8g', 2);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('3', 'earring', 'silver', '10g', 3);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('4', 'earring', 'gold', '7.5g', 4);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('5', 'top', 'silk', '200g', 5);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('6', 'top', 'linen', '340g', 6);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('7', 'bottom', 'leather', '450g', 7);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('8', 'bottom', 'linen', '300g', 8);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('9', 'shoes', 'rubber', '460g', 9);
INSERT INTO product_name (product_number, category, material, weight, customer_information_id) VALUES ('10', 'shoes', 'leather', '400g', 10);

select * from product_reservation; --예약
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('kim', '1', '2023-10-14', 'seoul', '01012345678', 'qwer@gmail', 1);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '2', '2023-10-11', 'gyeonggi-do', '01023456789', 'asdf@gmail', 2);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('hong', '3', '2020-08-20', 'gyeonggi-do', '01022223333', 'zxcv@gmail', 3);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('gang', '4', '2021-10-14', 'gangwon-do', '01033334444', 'wert@gmail', 4);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('jeong', '5', '2016-11-06', 'busan', '01055556666', 'sdfg@gmail', 5);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('choi', '6', '2018-02-25', 'daejeon', '01066667777', 'xcvb@gmail', 6);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '7', '2020-01-18', 'busan', '01077778888', 'erty@gmail', 7);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('kim', '8', '2023-10-23', 'chungnam', '01088889999', 'dfgh@gmail', 8);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('song', '9', '2023-06-10', 'seoul', '01044445555', 'cvbn@gmail', 9);
INSERT INTO product_reservation (customer_name, Product_number, confirmation_of_deposit, Address, tell, email, customer_information_id) VALUES ('Lee', '10', '2021-07-15', 'seoul', '01011112222', 'rtyu@gmail', 10);

select * from confirmation_of_deposit; --입금
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('kim', '01012345678', 'qwer@gmail', 'card', '123', 10, 1, 1, 1);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01023456789', 'asdf@gmail', 'card', '234', 13, 2, 2, 2);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('hong', '01022223333', 'zxcv@gmail', 'card', '345', 17, 3, 3, 3);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('gang', '01033334444', 'wert@gmail', 'card', '456', 20, 4, 4, 4);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('jeong', '01055556666', 'sdfg@gmail', 'card', '678', 55, 5, 5, 5);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('choi', '01066667777', 'xcvb@gmail', 'card', '789', 45.6, 6, 6, 6);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01077778888', 'erty@gmail', 'card', '912', 25, 7, 7, 7);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('kim', '01088889999', 'dfgh@gmail', 'card', '987', 18.4, 8, 8, 8);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('song', '01044445555', 'cvbn@gmail', 'card', '876', 20, 9, 9, 9);
INSERT INTO confirmation_of_deposit (customer_name, tell, email, deposit_type, card_number, price, product_reservation_id, product_name_id, customer_information_id) VALUES ('Lee', '01011112222', 'rtyu@gmail', 'card', '765', 8, 10, 10, 10);

select * from Questions; --문의
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('kim', '01012345678', '1', 'qwer@gmail', '2023-10-17', 1);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01023456789', '2', 'asdf@gmail', '2023-10-10', 2);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('hong', '01022223333', '3', 'zxcv@gmail', '2021-07-13', 3);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('gang', '01033334444', '4', 'wert@gmail', '2021-10-17', 4);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('jeong', '01055556666', '5', 'sdfg@gmail', '2016-10-30', 5);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('choi', '01066667777', '1', 'xcvb@gmail', '2018-02-25', 6);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01077778888', '2', 'erty@gmail', '2020-01-20', 7);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('kim', '01088889999', '3', 'dfgh@gmail', '2023-10-22', 8);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('song', '01044445555', '4', 'cvbn@gmail', '2023-06-15', 9);
INSERT INTO Questions (customer_name, tell, Inquiry_code, email, date_of_inquiry, customer_information_id) VALUES ('Lee', '01011112222', '5', 'rtyu@gmail', '2021-07-15', 10);

 

아래는 N:M 관계망 테이블을 만든 뒤 연결한 것.

select * from Questions_ProductName; --문의와 상품 연결
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (1, 1);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (2, 2);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (3, 3);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (4, 4);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (5, 5);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (6, 6);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (7, 7);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (8, 8);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (9, 9);
INSERT INTO Questions_ProductName (question_id, product_name_id) VALUES (10, 10);

select * from Questions_ProductReservation; --문의와 예약 연결
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (1, 1);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (2, 2);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (3, 3);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (4, 4);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (5, 5);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (6, 6);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (7, 7);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (8, 8);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (9, 9);
INSERT INTO Questions_ProductReservation (question_id, product_reservation_id) VALUES (10, 10);

select * from Questions_ConfirmationOfDeposit; --문의와 입금 정보 연결
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (1, 1);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (2, 2);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (3, 3);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (4, 4);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (5, 5);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (6, 6);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (7, 7);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (8, 8);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (9, 9);
INSERT INTO Questions_ConfirmationOfDeposit (question_id, confirmation_id) VALUES (10, 10);

예를 들자면 1번 문의와 1번 상품을 연결, 1번 문의와 1번 예약을 연결, 1번 문의와 1번 입금정보를 연결 하는 식.

모든 순서는 1~10 순번이라서 순번이 꼬이지는 않았다.

결국 1번 문의에 대한 1번 예약내용, 1번 상품, 1번 입금정보가 연결이 되는건데 연결은 어찌저찌 했지만 교수님이 의도한대로 연결망을 만들고 데이터를 집어넣은게 맞는지는 잘 모르겠다. 

너무 얼랑뚱땅해서 교수님이 놀라시면 어쩌지??