Skip to content

DB DESIGN

Jump to bottom
정재민 edited this page Jun 28, 2023 · 3 revisions

📦DB BLUE PRINT

Untitled

CREATE SQL QUERY

drop database developerdb;
create database developerdb;
use developerdb;

CREATE TABLE `User` (
	`userEmail`	varchar(50)	NOT NULL,
	`userName`	varchar(20)	NOT NULL,
	`userPwd`	varchar(20)	NOT NULL,
	`userBirth`	varchar(10)	NOT NULL,
	`userPhone`	varchar(20)	NOT NULL,
   PRIMARY KEY (`userEmail`)
);

CREATE TABLE `TechStack` (
    `techSeq` int auto_increment NOT NULL,
    `techName` varchar(30) NOT NULL,
    PRIMARY KEY (`techSeq`)
);

CREATE TABLE `UserSkill` (
    `userEmail` varchar(50) NOT NULL,
    `techSeq` int NOT NULL,
    `proficiency` varchar(10) NULL,
    PRIMARY KEY (`userEmail`, `techSeq`),
    FOREIGN KEY (`userEmail`) REFERENCES `User` (`userEmail`),
    FOREIGN KEY (`techSeq`) REFERENCES `TechStack` (`techSeq`)
);

CREATE TABLE `Board` (
	`boardSeq`	int auto_increment NOT NULL,
	`userEmail`	varchar(50)	NOT NULL,
	`title`	varchar(100)	NOT NULL,
	`threePoem`	varchar(100)	NOT NULL,
	`salary`	int	NOT NULL,
	`profile`	longText	NULL,
	`gitContribution` longText NULL,
    PRIMARY KEY (`boardSeq`, `userEmail`),
    FOREIGN KEY (`userEmail`) REFERENCES `User` (`userEmail`)
);

insert into User (userEmail, userName, userPwd, userBirth, userPhone)
values ('admin@naver.com', '관리자', '!admin1234', '2023-05-30', '010-1234-1234'),
('hmson7@gmail.com', '손흥민', 'Thsgmdals!@', '1992-07-08', '010-7787-5773'),
('jinjh404@jjsoft.com', '진정한', '@wjdgks3990', '1993-03-26', '010-9845-3990'),
('cultureland11@naver.com', '문상훈', 'tkdGns!##9703', '1991-05-09', '010-6635-9703'),
('juwy60@gmail.com', '이주원', '!pass1234', '1995-03-01', '010-5282-7028'),
('1jmhcho@gmail.com', '조명환', '!pass1234', '1995-01-11', '010-3073-4284'),
('wjdwoals9292@gmail.com', '정재민', '!pass1234', '1995-02-14', '010-4048-9915'),
('namgywjd2@gmail.com', '남효정', '!pass1234', '1995-05-05', '010-7651-4124'),
('kko07015@gmail.com', '김종완', '!pass1234', '1995-11-11', '010-7461-0369');

INSERT INTO TechStack (techName)
VALUES ('Java'), ('JavaScript'), ('Python'), ('Kotlin'), ('Swift'), ('TypeScript'),
       ('C'), ('C++'), ('ReactJS'), ('Redux'), ('VueJS'), ('AngularJS'), ('NextJS'),
       ('Spring'), ('NodeJS'), ('NestJS'), ('Unity'), ('Flask'), ('MySQL'), ('MongoDB');
       
INSERT INTO UserSkill (userEmail, techSeq, proficiency)
VALUES
    ('hmson7@gmail.com', 1, '상'),
    ('hmson7@gmail.com', 2, '중'),
    ('hmson7@gmail.com', 3, '중'),
    ('jinjh404@jjsoft.com', 4, '하'),
    ('jinjh404@jjsoft.com', 5, '중'),
    ('cultureland11@naver.com', 6, '상'),
    ('juwy60@gmail.com', 7, '중'),
    ('juwy60@gmail.com', 8, '하'),
    ('juwy60@gmail.com', 9, '상'),
    ('1jmhcho@gmail.com', 10, '중'),
    ('1jmhcho@gmail.com', 11, '상'),
    ('1jmhcho@gmail.com', 12, '중'),
    ('wjdwoals9292@gmail.com', 13, '상'),
    ('wjdwoals9292@gmail.com', 14, '하'),
    ('wjdwoals9292@gmail.com', 15, '중'),
    ('namgywjd2@gmail.com', 16, '중'),
    ('namgywjd2@gmail.com', 17, '하');

       
INSERT INTO Board (userEmail, title, threePoem, salary, profile, gitContribution)
VALUES
    ('admin@naver.com', '성공은 열정을 갖는 자에게 찾아온다', '손흥민\n트라이엄프\n김종완', 5000, '/profile/admin.png', '/git/contributions/admin'),
    ('hmson7@gmail.com', '웃는 얼굴로 열일하면 세상이 너에게도 웃어줄 것이다', '손흥민\n역사를 써가는\n나의 길', 6000, '/profile/hmson7.png', '/git/contributions/hmson7'),
    ('jinjh404@jjsoft.com', '노력하는 사람은 행운이 따른다', '진정한\n바람에 날리는\n내 꿈', 4500, '/profile/jinjh404.png', '/git/contributions/jinjh404'),
    ('cultureland11@naver.com', '한계를 믿으면 한계가 생긴다', '문상훈\n탐험하듯\n나아가는 길', 5500, '/profile/cultureland11.png', '/git/contributions/cultureland11'),
    ('juwy60@gmail.com', '도전과 실패는 성공을 위한 연습이다', '이주원\n프론트엔드\n전문가', 4800, '/profile/juwy60.png', '/git/contributions/juwy60'),
    ('1jmhcho@gmail.com', '오늘의 나는 어제의 나보다 발전한 모습이다', '조명환\n자유로운\n영혼', 7000, '/profile/1jmhcho.png', '/git/contributions/1jmhcho'),
    ('wjdwoals9292@gmail.com', '계획 없는 목표는 바람에 흩어진다', '정재민\n풍요로운\nIT 산업', 4500, '/profile/wjdwoals9292.png', '/git/contributions/wjdwoals9292'),
    ('namgywjd2@gmail.com', '성공의 비밀은 나에게 집중하는 것이다', '남효정\n도전하는\n컴퓨터 공학', 5200, '/profile/namgywjd2.png', '/git/contributions/namgywjd2'),
    ('kko07015@gmail.com', '행동으로 나타나지 않으면 그것은 모두 꿈이다', '김종완\n프로그래밍\n세상', 4800, '/profile/kko07015.png', '/git/contributions/kko07015');

Clone this wiki locally