database
정규화 신경쓰기 (1NF, 2NF, 3NF, BCNF) db create CREATE TABLE subjects ( subjectId INT PRIMARY KEY AUTO_INCREMENT, subjectName VARCHAR(255) NOT NULL, description TEXT, grade INT NOT NULL, semester INT NOT NULL ); CREATE TABLE professors ( professorId INT PRIMARY KEY AUTO_INCREMENT, professorName VARCHAR(255) NOT NULL, email VARCHAR(255), department VARCHAR(255) ); CREATE TABLE topics ( topicId INT PRIMARY KEY AUTO_INCREMENT, subjectId INT, topicTitle VARCHAR(255) NOT NULL, topicOrder INT NOT NULL, description TEXT, FOREIGN KEY (subjectId) REFERENCES subjects(subjectId) ); CREATE TABLE subtopics ( subtopicId INT PRIMARY KEY AUTO_INCREMENT, topicId INT, subtopicTitle VARCHAR(255) NOT NULL, subtopicOrder INT NOT NULL, description TEXT, FOREIGN KEY (topicId) REFERENCES topics(topicId) ); CREATE TABLE detailedTopics ( detailedTopicId INT PRIMARY KEY AUTO_INCREMENT, subtopicId INT, detailedTopicTitle VARCHAR(255) NOT NULL, detailedTopicOrder INT NOT NULL, description TEXT, FOREIGN KEY (subtopicId) REFERENCES subtopics(subtopicId) ); CREATE TABLE detailedTopicProfessorMapping ( mappingId INT PRIMARY KEY AUTO_INCREMENT, detailedTopicId INT, professorId INT, year YEAR, FOREIGN KEY (detailedTopicId) REFERENCES detailedTopics(detailedTopicId), FOREIGN KEY (professorId) REFERENCES professors(professorId) ); CREATE TABLE lectureNotes ( lectureNoteId INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(255) NOT NULL, version VARCHAR(50), year YEAR, isCurrent BOOLEAN DEFAULT 1, uploadDate DATE, filePath VARCHAR(255), notes TEXT ); CREATE TABLE lectureSlides ( slideId INT PRIMARY KEY AUTO_INCREMENT, lectureNoteId INT, detailedTopicId INT, slideNumber INT NOT NULL, slideTitle VARCHAR(255), slideContent TEXT, imagePath VARCHAR(255), pdfPage INT, pdfCoordinates VARCHAR(255), tableData TEXT, notes TEXT, FOREIGN KEY (lectureNoteId) REFERENCES lectureNotes(lectureNoteId), FOREIGN KEY (detailedTopicId) REFERENCES detailedTopics(detailedTopicId) ); CREATE TABLE textbooks ( textbookId INT PRIMARY KEY AUTO_INCREMENT, textbookTitle VARCHAR(255) NOT NULL, author VARCHAR(255), version VARCHAR(50) NOT NULL, publicationYear YEAR NOT NULL, isCurrent BOOLEAN DEFAULT 1, filePath VARCHAR(255) );