###### tags: `database`

# Assignment 1: Relational Models & Normal Form

> Author: NTHU CS 109062264 楊子慶
>
> Course: CS471000 Introduction to Database Systems
>
> Instructor: Shan-Hung Wu

## Problem 1

### Problem Description

* Scenario

Let's say we want to design a database to manage the relationships between projects, professors, and students.

- Each professor has an id, a name, and a rank.
- Each project has a project number, a sponsor name (e.g. MOST), a starting date, an ending date, and a budget.
- Each student has a student id, a name, and a degree program (Bachelor, Master or PhD).
- Each project is managed by a professor. It is possible that a professor manages multiple projects.
- Each student is supervised by a professor. It is possible that a professor supervises multiple students.
- A student is able to work on a project. It is possible that a student participates in multiple projects.
- It should record when a student starts working on a project, when he/she stops, and how much he/she is paid per month.

* Question

1. Please design an ER model and draw an ER diagram for this scenario. (30 points)
2. Please design a relational model for this scenario with the following constraints. (30 points)
- You should use SQL `CREATE TABLE` to present your schema.
- You should `identify primary keys and foreign keys relationships` in your SQLs.
- You can only use [data types in PostgreSQL][1].
- The relations should at least follow `the 3rd normal form`.

[1]: https://www.postgresql.org/docs/14/datatype.html

### ER Model in ER Diagram

Notice that

- Ovals are attributes.
- Rectangles (along with ovals) are entity classes.
- Diamonds are relationships.
- Relationships may also have attributes.
- Primary keys are underlined.

graph hw1 {
    rankdir = RL;
    Professors[shape=box];
    pf_id[label=<<u>pf_id</u>>];
    pf_id -- Professors; pf_name -- Professors;
    rn[label="rank"];
    rn -- Professors;
    Students[shape=box];
    s_id[label=<<u>s_id</u>>];
    s_id -- Students; s_name -- Students;
    degree -- Students;
    Projects[shape=box];
    proj_number[label=<<u>proj_number</u>>];
    proj_number -- Projects;
    sponser_name -- Projects;
    start_date -- Projects;
    end_date -- Projects;
    budget -- Projects;
    Supervise[shape=diamond];
    Professors -- Supervise; Supervise -- Students;
    Manage[shape=diamond];
    Professors -- Manage; Manage -- Projects;
    Work_on[shape=diamond];
    Work_on -- Students; Work_on -- Projects;
    start_work[label="start_work_date"];
    end_work[label="end_work_date"];
    payment_per_month -- Work_on;
    start_work -- Work_on;
    end_work -- Work_on;
}

### Relational Model in SQL form

CREATE TABLE Professors (
    pf_id     INTEGER,
    pf_name   VARCHAR(20),
    rank      VARCHAR(30),
    PRIMARY KEY (pf_id)
);
CREATE TABLE Students (
    s_id      INTEGER,
    s_name    VARCHAR(20),
    degree    VARCHAR(10),
    PRIMARY KEY (s_id)
);
CREATE TABLE Projects (
    proj_number   INTEGER,
    sponsor_name  VARCHAR(20),
    start_date    Date,
    end_date      Date,
    budget        INTEGER,
    PRIMARY KEY (proj_number)
);
CREATE TABLE Manage (
    pf_id         INTEGER,
    proj_number   INTEGER,
    PRIMARY KEY (pf_id, proj_number),
    FOREIGN KEY (pf_id)       REFERENCES Professors,
    FOREIGN KEY (proj_number) REFERENCES Projects
);
CREATE TABLE Supervise (
    s_id         INTEGER,
    pf_id        INTEGER,
    PRIMARY KEY (s_id, pf_id),
    FOREIGN KEY (s_id)     REFERENCES Students,
    FOREIGN KEY (pf_id)    REFERENCES Professors
);
CREATE TABLE WorkOn {
    s_id               INTEGER,
    proj_number        INTEGER,
    start_work_date    Date,
    end_work_date      Date,
    payment_per_month  INTEGER,
    PRIMARY KEY (s_id, proj_number),
    FOREIGN KEY (s_id)        REFERENCES Students,
    FOREIGN KEY (proj_number) REFERENCES Projects
}

## Problem 2

### Problem Description

|forum_name|popularity|post_id|title|article|reply|
|---|---|---|---|---|---|
|Gossiping|100|131|Girlfriend|How can I get girlfriend ?|["Haha", "I don't know"]
|Gossiping|100|252|Firends|I don't have a friend...|["Haha", "I can be", "QQ"]
|Joke|23|46|Knock|Knock! Knock! ...|["Then?", "What's the point ?"]
|Joke|23|151|Santa Claus|Hold! Hold! Hold!|["XDD"]

Here are its functional dependencies:
- forum_name -> popularity
- {forum_name, post_id} -> {title, article, reply}

Please decompose the table to make it **follow the 3rd normal form and identify which field(s) should be primary key(s)**. You must also keep the data in the table. You may add new fields to preserve the above relationships. (40 points)

### Decomposed Tables

Underlined attributes are primary keys.

* forum information table

|<u>forum_name<u>|popularity|
|---|---|
|Gossiping|100|
|Joke|23|

* Article table (`article_id -> { forum_name, post_id }`)

| <u>article_id<u> | forum_name| post_id | title | article |
|:----------------:| ---------------- | ------------- | ----------- | -------------------------- |
| 0 | Gossiping | 131 | Girlfriend | How can I get girlfriend ? |
| 1 | Gossiping | 252 | Firends | I don't have a friend... |
| 2 | Joke | 46 | Knock | Knock! Knock! ... |
| 3 | Joke | 151 | Santa Claus | Hold! Hold! Hold!|

* Reply table

Since number of replies is unfixed for each articles, I split the reply message into a new table.

|<u>reply_id<u>|article_id|reply|
|---|---|---|
|0|0|"Haha"
|1|0|"I don't know"
|2|1|"Haha"
|3|1|"I can be"
|4|1|"QQ"
|5|2|"Then?"
|6|2|"What's the point ?"
|7|3|"XDD"