---
tags: 資料庫期末
---
# 資料庫期末考
## 資料表結構 `bulletin`
### TABLE
``` sql=
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
```sql=
ALTER TABLE `bulletin`
ADD PRIMARY KEY (`bulletin_ID`),
ADD KEY `course` (`course_ID`,`class`);
```
### Normalization
F = {
  bulletin_ID $\rightarrow$ title
  bulletin_ID $\rightarrow$ content
  bulletin_ID $\rightarrow$ time
  bulletin_ID $\rightarrow$ sender
  bulletin_ID $\rightarrow$ receiver
  bulletin_ID $\rightarrow$ course_ID
  bulletin_ID $\rightarrow$ class
}
:::success
1. bulletin_ID：代表一個特定的公告，可以決定唯一的title, content, time, sender, receiver, course_ID, class
2. bulletin_ID是唯一的candidate key
符合符合3NF，也符合BCNF(2)。
:::
### 限制式
```sql=
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
```sql=
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 ;
```
<br>
## 資料表結構 `calendar`
### TABLE
``` sql=
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
```sql=
ALTER TABLE `calendar`
ADD PRIMARY KEY (`Event`,`startDate`);
```
### Normalization
F = {
  Event, startDate $\rightarrow$ startDate
  Event, startDate $\rightarrow$ endDate
}
:::success
1. Event, startDate：代表一個特定的活動，可以決定唯一的endDate
2. Event, startDate是candidate key
3. Event, endDate也是candidate key
符合符合3NF，但不符合BCNF(3)。
:::
<br>
## 資料表結構 `course`
### TABLE
``` sql=
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
```sql=
ALTER TABLE `course`
ADD PRIMARY KEY (`course_ID`,`class`);
```
### Normalization
F = {
  course_ID, class $\rightarrow$ name
  course_ID, class $\rightarrow$ teacher
  course_ID, class $\rightarrow$ semester
  course_ID, class $\rightarrow$ dept
  course_ID, class $\rightarrow$ credit
  course_ID, class $\rightarrow$ grade
}
:::success
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)。
:::
<br>
## 資料表結構 `course_information`
### TABLE
``` sql=
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
```sql=
ALTER TABLE `course_information`
ADD PRIMARY KEY (`course_ID`,`class`),
ADD KEY `course` (`course_ID`,`class`);
```
### Normalization
F = {
  course_ID, class $\rightarrow$ information
  course_ID, class $\rightarrow$ hot
  course_ID, class $\rightarrow$ count
  count $\rightarrow$ hot
}
:::success
1. course_ID, class：代表一個特定的課程資訊，可以決定唯一的information, hot, count
2. course_ID, class是唯一的candidate key
3. count可以決定hot屬於傳遞依賴
符合2NF，但不符合3NF(3)
:::
### 限制式
```sql=
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;
```
<br>
## 資料表結構 `log`
### TABLE
``` sql=
CREATE TABLE `log` (
`content` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
`time` datetime NOT NULL
)
```
### INDEX
```sql=
ALTER TABLE `log`
ADD PRIMARY KEY (`time`);
```
### Normalization
F = {
  time $\rightarrow$ context
  context $\rightarrow$ time
}
:::success
1. time：代表一個特定的系統日誌，可以決定唯一的context
2. time是candidate key
符合3NF，也符合BCNF(2)
:::
<br>
## 資料表結構 `message`
### TABLE
``` sql=
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
```sql=
ALTER TABLE `message`
ADD PRIMARY KEY (`mess_ID`),
ADD KEY `course` (`course_ID`,`class`) USING BTREE;
```
### Normalization
F = {
  mess_ID $\rightarrow$ text
  mess_ID $\rightarrow$ time
  mess_ID $\rightarrow$ hot
  mess_ID $\rightarrow$ course_ID
  mess_ID $\rightarrow$ class
}
:::success
1. mess_ID：代表一個特定的留言板訊息，可以決定唯一的text,time,hot,course_ID,class
2. mess_ID是唯一的candidate key
符合3NF，也符合BCNF(2)
:::
### 限制式
```sql=
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
```sql=
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 ;
```
<br>
## 資料表結構 `user`
### TABLE
``` sql=
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
```sql=
ALTER TABLE `user`
ADD PRIMARY KEY (`user_ID`);
```
### Normalization
F = {
  user_ID $\rightarrow$ user_name
  user_ID $\rightarrow$ account
  user_ID $\rightarrow$ password
  user_ID $\rightarrow$ authorization
  account $\rightarrow$ user_name
  account $\rightarrow$ user_ID
  account $\rightarrow$ password
  account $\rightarrow$ authorization
}
:::success
1. user_ID：代表一個特定的user，可以決定唯一的user_name, account, password, authorization
2. user_ID是candidate key
3. account也是candidate key
符合3NF，但不符合BCNF(3)
:::
Sign in with Wallet
Connect another wallet