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.
     - The relations should at least follow the 3rd normal form.

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.

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

  forum_name	popularity
  Gossiping	100
  Joke	23

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

  article_id	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.

  reply_id	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"