20170421_jeffrey - silenceuncrio/diary GitHub Wiki

0845

今天十點要開會

先 survey 一下 link failover

0925

ariel 在 M300 Link 群組上告知她今天請假

下禮拜一再開會

今天預計要把 code 放到 release branch

先來幫 aaron 做 openvpn 追加工程的 WEB UI

1100

繼續修 GroupBuying 這個 database

Step 1: Discover entities and assign attribute

  • Users
    • (PK) UserId
    • UserName
    • Email
    • Password
  • Directors
    • (PK) DirectorId
  • Groups
    • (PK) GroupId
    • Name
    • Description
    • PeriodStart
    • PeriodEnd
  • Orders
    • OrderId
    • DateOrdered
    • TotalCost
  • Products
    • (PK) ProductId
    • Name
    • Description
    • Cost

Step 2: Derive unary and binary relationships

Users Directors Groups Orders Products
Users - - - - -
Directors is - - - -
Groups create - - -
Orders create has - -
Products has has -

1210

Step 3: Create simplified Entity-Relationship diagram

image

Step 4: List assertions for all relationships

  • A User can be a Director
  • A Director must be a User
  • A Director can create Group
  • A Group must be created by a Director
  • A User can create GroupOrder
  • A GroupOrder must be created by a User
  • A group can have many GroupOrders
  • A GroupOrder must belong to a Group
  • A GroupOrder must have at least one Product
  • A Product can belong to many GroupOrders
  • A Group must have at least one Product
  • A Product must belong to only one Group

Step 5: Create detailed E-R diagram using assertions

  • A User can be a Director (0:1)
  • A Director must be a User (1:1)
  • A Director can create many Groups (0:N)
  • A Group must be created by a Director (1:1)
  • A User can create many GroupOrder (0:N)
  • A GroupOrder must be created by a User (1:1)
  • A group can have many GroupOrders (0:N)
  • A GroupOrder must belong to a Group (1:1)
  • A GroupOrder must have at least one Product (1:N)
  • A Product can belong to many GroupOrders (0:N)
  • A Group must have at least one Product (1:N)
  • A Product must belong to only one Group (1:1)

image

1305

Step 6: Transform the detailed E-R diagram into an implementable R-M diagram

Step 6-1: Many-to-many relationships

image

1315

Step 6-2: One-to-many relationships

image

1325

Step 6-3: One-to-one relationships

image

1330

重新整理一下 R-M Diagram 的 relations

  • Users
    • (PK) UserId
    • UserName
    • Email
    • Password
  • Directors
    • (PK) DirectorId
    • (FK) UserId
  • Groups
    • (PK) GroupId
    • Name
    • Description
    • PeriodStart
    • PeriodEnd
    • (FK) DirectorId
  • GroupOrders
    • (PK) GroupOrderId
    • DateOrdered
    • (FK) UserId
    • (FK) GroupId
  • Products
    • (PK) ProductId
    • Name
    • Description
    • Cost
    • (FK) GroupId
  • OrderProducts
    • (PK) OrderProductId
    • Quantity
    • TotalCost
    • (FK) GroupOrderId
    • (FK) ProductId

整理 SQL Command 前把每個 relation 整理成一個表格

Users

Table Column Data Type Length Indexed Requuired(Default)
Users (PK) UserId Number NA Yes, Primary Key Yes
UserName Character 50 Yes Yes
Email Character 75 No Yes
Password Character 50 No Yes

Directors

Table Column Data Type Length Indexed Requuired(Default)
Directors (PK) DirectorId Number NA Yes, Primary Key Yes
(FK) UserId Number NA Yes, Foreign Key Yes

Groups

Table Column Data Type Length Indexed Requuired(Default)
Groups (PK) GroupId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Description Character 250 No No
PeriodStart Date NA Yes Yes
PeriodEnd Date NA Yes Yes
(FK) DirectorId Number NA Yes, Foreign Key Yes

GroupOrders

Table Column Data Type Length Indexed Requuired(Default)
GroupOrders (PK) GroupOrderId Number NA Yes, Primary Key Yes
DateOrdered Date NA Yes Yes
(FK) UserId Number NA Yes, Foreign Key Yes
(FK) GroupId Number NA Yes, Foreign Key Yes

Products

Table Column Data Type Length Indexed Requuired(Default)
Products (PK) ProductId Number NA Yes, Primary Key Yes
Name Character 50 Yes Yes
Description Character 250 No No
Cost Number 12 No Yes(0)
(FK) GroupId Number NA Yes, Foreign Key Yes

OrderProducts

Table Column Data Type Length Indexed Requuired(Default)
OrderProducts (PK) OrderProductId Number NA Yes, Primary Key Yes
Quantity Number 6 No Yes(1)
TotalCost Number 12 No Yes(0)
(FK) GroupOrderId Number NA Yes, Foreign Key Yes
(FK) ProductId Number NA Yes, Foreign Key Yes

1415

整理成 SQL Command 吧

CREATE TABLE Users (
  UserId int(11) NOT NULL AUTO_INCREMENT,
  UserName varchar(50) NOT NULL,
  Email varchar(75) NOT NULL,
  Password varchar(50) NOT NULL,

  PRIMARY KEY (UserId),
  INDEX UserName (UserName)
);

CREATE TABLE Directors (
  DirectorId int(11) NOT NULL AUTO_INCREMENT,

  UserId int(11) NOT NULL,

  PRIMARY KEY (DirectorId),
  FOREIGN KEY UserId (UserId)
    REFERENCES Users (UserId) 
    ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Groups (
  GroupId int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(50) NOT NULL,
  Description varchar(250) DEFAULT NULL,
  PeriodStart date NOT NULL,
  PeriodEnd date NOT NULL,

  DirectorId int(11) NOT NULL,

  PRIMARY KEY (GroupId),
  FOREIGN KEY DirectorId (DirectorId)
    REFERENCES Directors (DirectorId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX Name (Name)
);

CREATE TABLE GroupOrders (
  GroupOrderId int(11) NOT NULL AUTO_INCREMENT,
  DateOrdered date NOT NULL,

  UserId int(11) NOT NULL,
  GroupId int(11) NOT NULL,

  PRIMARY KEY (GroupOrderId),
  FOREIGN KEY UserId (UserId)
    REFERENCES Users (UserId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY GroupId (GroupId)
    REFERENCES Groups (GroupId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX DateOrdered (DateOrdered)
);

CREATE TABLE Products (
  ProductId int(11) NOT NULL AUTO_INCREMENT,
  Name varchar(50) NOT NULL,
  Description varchar(250) DEFAULT NULL,
  Cost int(12) NOT NULL DEFAULT 0,

  GroupId int(11) NOT NULL,

  PRIMARY KEY (ProductId),
  FOREIGN KEY GroupId (GroupId)
    REFERENCES Groups (GroupId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  INDEX Name (Name)
);

CREATE TABLE OrderProducts (
  OrderProductId int(11) NOT NULL AUTO_INCREMENT,
  Quantity smallint(6) NOT NULL DEFAULT 1,
  TotalCost int(12) NOT NULL DEFAULT 0,

  GroupOrderId int(11) NOT NULL,
  ProductId int(11) NOT NULL,

  PRIMARY KEY (OrderProductId),
  FOREIGN KEY GroupOrderId (GroupOrderId)
    REFERENCES GroupOrders (GroupOrderId) 
    ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY ProductId (ProductId)
    REFERENCES Products (ProductId) 
    ON UPDATE CASCADE ON DELETE RESTRICT
);

1515

利用 monkeyjj 上的 phpMyAdmin 來執行這些 SQL Command

已經成功地把 table 都建立出來了

可以試著新增一些資料了

1645

SELECT * FROM `Users`
UserId UserName Email Password
1 jenny [email protected] jenny
2 謝嘎 [email protected] shega
3 Minie Heidi [email protected] minie_heidi
4 Li Ying Wei [email protected] li_ying_wei
5 邱佩萱 [email protected] tropesheng
6 Huei Wun [email protected] huei_wun
7 Tina Wang [email protected] tina_wang
8 劉羿伶 [email protected] luelin 
SELECT * FROM `Directors`
DirectorId UserId
1 1

雖然從 Directors 可以知道有哪些 Users

但能不能把 User 的 UserName 和 Email 一起顯示呢?

這要使用 inner join

SELECT d.DirectorId, d.UserId, u.UserName, u.Email 
FROM Directors d 
INNER JOIN Users u ON d.UserId = u.UserId
DirectorId UserId UserName Email
1 1 jenny [email protected]

1710

SELECT * FROM `Groups
GroupId Name Description PeriodStart PeriodEnd DirectorId
1 輕鬆熊一團 輕鬆熊一團 - description 2017­04­12 2017­04­18 1

那我要怎麼知道這一團是誰開的

這就要 join Groups, Directors 和 Users 三個 table 才行

SELECT * FROM Groups g 
INNER JOIN Directors d on g.DirectorId = d.DirectorId 
INNER JOIN Users u on d.UserId = u.UserId
GroupId Name Description PeriodStart PeriodEnd DirectorId DirectorId UserId UserId UserName Email Password
1 輕鬆熊一團 輕鬆熊一團 - description 2017­04­12 2017­04­18 1 1 1 1 jenny [email protected] jenny

稍微篩一下欄位

SELECT g.GroupId, g.Name, g.PeriodStart, g.PeriodEnd, u.UserId, u.UserName, u.Email
FROM Groups g 
INNER JOIN Directors d on g.DirectorId = d.DirectorId 
INNER JOIN Users u on d.UserId = u.UserId
GroupId Name PeriodStart PeriodEnd UserId UserName Email
1 輕鬆熊一團 2017­04­12 2017­04­18 1 jenny [email protected]

1730

來看看 Products 這個 table

ProductId Name Description Cost GroupId
1 皇冠熊 皇冠熊­ description 220 1
2 熊哥 熊哥 ­description 220 1
3 熊妹 熊妹 description 220 1
4 幸運草熊 幸運草熊­ description 220 1
Add a custom footer
⚠️ **GitHub.com Fallback** ⚠️