資料庫期末專題

資料表結構 bulletin

TABLE

CREATE TABLE `bulletin` (
  `bulletin_ID` int(100) NOT NULL,
  `title` varchar(256) NOT NULL,
  `content` varchar(256) NOT NULL,
  `time` datetime NOT NULL,
  `sender` varchar(256) NOT NULL,
  `receiver` varchar(256) NOT NULL,
  `course_ID` varchar(256) NOT NULL,
  `class` varchar(256) NOT NULL
) 

INDEX

ALTER TABLE `bulletin`
  ADD PRIMARY KEY (`bulletin_ID`),
  ADD KEY `course` (`course_ID`,`class`);

Normalization

F = {
  bulletin_ID

$\to$ title
  bulletin_ID

$\to$ content
  bulletin_ID

$\to$ time
  bulletin_ID

$\to$ sender
  bulletin_ID

$\to$ receiver
  bulletin_ID

$\to$ course_ID
  bulletin_ID

$\to$ class
}

1. bulletin_ID：代表一個特定的公告，可以決定唯一的title, content, time, sender, receiver, course_ID, class
2. bulletin_ID是唯一的candidate key
   符合符合3NF，也符合BCNF(2)。

限制式

ALTER TABLE `bulletin`
  ADD CONSTRAINT `b_course_fk` 
  FOREIGN KEY (`course_ID`,`class`) 
  REFERENCES `course` (`course_ID`, `class`) ON DELETE CASCADE ON UPDATE CASCADE;

TRIGGER

DROP TRIGGER IF EXISTS `bulletin_add`;
DELIMITER $$
CREATE TRIGGER `bulletin_add` AFTER INSERT ON `bulletin` FOR EACH ROW BEGIN
	DECLARE s1 VARCHAR(256) character set utf8;
    DECLARE s2 VARCHAR(256) character set utf8;
    SET s2 = " add new bulletin: ";
	SET s1 = CONCAT(s2,NEW.title);
	INSERT INTO log VALUES(s1,NEW.time);
END
$$
DELIMITER ;

資料表結構 calendar

TABLE

CREATE TABLE `calendar` (
  `Event` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `EventType` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `startDate` date NOT NULL,
  `endDate` date DEFAULT NULL
) 

INDEX

ALTER TABLE `calendar`
  ADD PRIMARY KEY (`Event`,`startDate`);

Normalization

F = {
  Event, startDate

$\to$ startDate
  Event, startDate

$\to$ endDate
}

1. Event, startDate：代表一個特定的活動，可以決定唯一的endDate
2. Event, startDate是candidate key
3. Event, endDate也是candidate key
   符合符合3NF，但不符合BCNF(3)。

資料表結構 course

TABLE

CREATE TABLE `course` (
  `course_ID` varchar(256) NOT NULL,
  `name` varchar(256) NOT NULL,
  `class` varchar(256) NOT NULL,
  `teacher` varchar(256) NOT NULL,
  `semester` varchar(256) NOT NULL,
  `dept` varchar(256) NOT NULL,
  `credit` int(20) NOT NULL,
  `grade` int(20) NOT NULL
) 

INDEX

ALTER TABLE `course`
  ADD PRIMARY KEY (`course_ID`,`class`);

Normalization

F = {
  course_ID, class

$\to$ name
  course_ID, class

$\to$ teacher
  course_ID, class

$\to$ semester
  course_ID, class

$\to$ dept
  course_ID, class

$\to$ credit
  course_ID, class

$\to$ grade
}

1. course_ID, class：代表一個特定的課程，可以決定唯一的name, teacher, semester, dept, credit, grade
2. course_ID, class是唯一的candidate key
3. 且course_ID與class並沒有superkey dependency
   符合符合3NF，也符合BCNF(2,3)。

資料表結構 course_information

TABLE

CREATE TABLE `course_information` (
  `course_ID` varchar(256) NOT NULL,
  `class` varchar(256) NOT NULL,
  `information` varchar(10000) NOT NULL,
  `hot` float NOT NULL,
  `count` int(11) NOT NULL
) 

INDEX

ALTER TABLE `course_information`
  ADD PRIMARY KEY (`course_ID`,`class`),
  ADD KEY `course` (`course_ID`,`class`);

Normalization

F = {
  course_ID, class

$\to$ information
  course_ID, class

$\to$ hot
  course_ID, class

$\to$ count
  count

$\to$ hot
}

1. course_ID, class：代表一個特定的課程資訊，可以決定唯一的information, hot, count
2. course_ID, class是唯一的candidate key
3. count可以決定hot屬於傳遞依賴
   符合2NF，但不符合3NF(3)

限制式

ALTER TABLE `course_information`
  ADD CONSTRAINT `i_course_fk` 
  FOREIGN KEY (`course_ID`,`class`) 
  REFERENCES `course` (`course_ID`, `class`) ON DELETE CASCADE ON UPDATE CASCADE;

資料表結構 log

TABLE

CREATE TABLE `log` (
  `content` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
  `time` datetime NOT NULL
) 

INDEX

ALTER TABLE `log`
  ADD PRIMARY KEY (`time`);

Normalization

F = {
  time

$\to$ context
  context

$\to$ time
}

1. time：代表一個特定的系統日誌，可以決定唯一的context
2. time是candidate key
   符合3NF，也符合BCNF(2)

資料表結構 message

TABLE

CREATE TABLE `message` (
  `mess_ID` int(20) NOT NULL,
  `text` varchar(256) NOT NULL,
  `time` datetime NOT NULL,
  `hot` int(11) NOT NULL,
  `course_ID` varchar(256) NOT NULL,
  `class` varchar(256) NOT NULL
) 

INDEX

ALTER TABLE `message`
  ADD PRIMARY KEY (`mess_ID`),
  ADD KEY `course` (`course_ID`,`class`) USING BTREE;

Normalization

F = {
  mess_ID

$\to$ text
  mess_ID

$\to$ time
  mess_ID

$\to$ hot
  mess_ID

$\to$ course_ID
  mess_ID

$\to$ class
}

1. mess_ID：代表一個特定的留言板訊息，可以決定唯一的text,time,hot,course_ID,class
2. mess_ID是唯一的candidate key
   符合3NF，也符合BCNF(2)

限制式

ALTER TABLE `message`
  ADD CONSTRAINT `m_course_fk` 
  FOREIGN KEY (`course_ID`,`class`) 
  REFERENCES `course` (`course_ID`, `class`) ON DELETE CASCADE ON UPDATE CASCADE;

TRIGGER

DROP TRIGGER IF EXISTS `message_add`;
DELIMITER $$
CREATE TRIGGER `message_add` AFTER INSERT ON `message` FOR EACH ROW BEGIN
	DECLARE s1 VARCHAR(256) character set utf8;
    DECLARE s2 VARCHAR(256) character set utf8;
    SET s2 = " add new message: (";
	SET s1 = CONCAT(s2,NEW.class);
    SET s2 = ", ";
    SET s1 = CONCAT(s1,s2);
    SET s1 = CONCAT(s1,NEW.course_ID);
    SET s2 = ") -> ";
    SET s1 = CONCAT(s1,s2);
    SET s1 = CONCAT(s1,NEW.text);
	INSERT INTO log VALUES(s1,NEW.time);
END
$$
DELIMITER ;

資料表結構 user

TABLE

CREATE TABLE `user` (
  `user_ID` varchar(256) NOT NULL,
  `user_name` varchar(256) NOT NULL,
  `account` varchar(256) NOT NULL,
  `password` varchar(256) NOT NULL,
  `authorization` tinyint(1) NOT NULL
) 

INDEX

ALTER TABLE `user`
  ADD PRIMARY KEY (`user_ID`);

Normalization

F = {
  user_ID

$\to$ user_name
  user_ID

$\to$ account
  user_ID

$\to$ password
  user_ID

$\to$ authorization
  account

$\to$ user_name
  account

$\to$ user_ID
  account

$\to$ password
  account

$\to$ authorization
}

1. user_ID：代表一個特定的user，可以決定唯一的user_name, account, password, authorization
2. user_ID是candidate key
3. account也是candidate key
   符合3NF，但不符合BCNF(3)