三、假設有如下外籍移工聘任契約的表格資料，請以符合 BCNF（Boyce-Codd Normal Form）的 RDB（Relational Database）資料表來表示。經 BCNF 正規化後的資料表，需同時註明 primary/foreign keys，並將正規化後的資料表分別以 xml 檔案格式來描述。（30 分）
註：答案需包括 BCNF 正規化後的資料表（含內容、主鍵、外來鍵）、及正規化後資料表對應的 xml 檔案格式內容。
64dc6224dfd6b.jpg

詳解 (共 1 筆)

詳解 提供者：Allison

函數相依分析

雇主ID→雇主姓名,雇主電話

受照護者ID→受照護者姓名,受照護者出生年月日,受照護者性別

移工ID→移工姓名,移工國籍,移工出生年月日,移工性別

雇主ID,受照護者ID,移工ID→契約開始日期,契約結束日期

雇主ID, 受照護者ID,移工ID皆非為超鍵，因此不符合BCNF

原始資料表之候選鍵為（雇主ID, 受照護者ID, 移工ID）。然而，存在雇主ID、受照護者ID及移工ID分別決定其對應屬性的函數相依，其決定項並非超鍵，因此違反 BCNF。

經BCNF後為：

雇主表Employer(雇主ID,雇主姓名,雇主電話)

主鍵：雇主ID

受照護者表CareRecipient(受照護者ID,受照護者姓名,受照護者出生年月日,受照護者性別)

主鍵：受照護者ID

移工表MigrantWorker(移工ID,移工姓名,移工國籍,移工出生年月日,移工性別)

主鍵：移工ID

聘任紀錄表Contract(雇主ID,受照護者ID,移工ID,契約開始日期,契約結束日期)

主鍵：雇主ID,受照護者ID,移工ID

外鍵：雇主ID,受照護者ID,移工ID

XML

<Database>
  <EmployerTable>
    <Employer>
      <EmployerID>B001</EmployerID>
      <EmployerName>Alex</EmployerName>
      <EmployerPhone>111111</EmployerPhone>
    </Employer>
    <Employer>
      <EmployerID>B002</EmployerID>
      <EmployerName>Bob</EmployerName>
      <EmployerPhone>222222</EmployerPhone>
    </Employer>
    <Employer>
      <EmployerID>B003</EmployerID>
      <EmployerName>Connie</EmployerName>
      <EmployerPhone>333333</EmployerPhone>
    </Employer>
    <Employer>
      <EmployerID>B004</EmployerID>
      <EmployerName>Connie</EmployerName>
      <EmployerPhone>444444</EmployerPhone>
    </Employer>
  </EmployerTable>
  <CareRecipientTable>
    <CareRecipient>
      <CareRecipientID>C001</CareRecipientID>
      <CareRecipientName>Owen</CareRecipientName>
      <BirthDate>3/3/1930</BirthDate>
      <Gender>M</Gender>
    </CareRecipient>
    <CareRecipient>
      <CareRecipientID>C002</CareRecipientID>
      <CareRecipientName>Paula</CareRecipientName>
      <BirthDate>5/5/1950</BirthDate>
      <Gender>F</Gender>
    </CareRecipient>
    <CareRecipient>
      <CareRecipientID>C003</CareRecipientID>
      <CareRecipientName>Quin</CareRecipientName>
      <BirthDate>7/7/1970</BirthDate>
      <Gender>F</Gender>
    </CareRecipient>
    <CareRecipient>
      <CareRecipientID>C004</CareRecipientID>
      <CareRecipientName>Owen</CareRecipientName>
      <BirthDate>9/9/1990</BirthDate>
      <Gender>M</Gender>
    </CareRecipient>
  </CareRecipientTable>
  <MigrantWorkerTable>
    <MigrantWorker>
      <WorkerID>E001</WorkerID>
      <WorkerName>Ivy</WorkerName>
      <Nationality>Vietnam</Nationality>
      <BirthDate>6/6/1988</BirthDate>
      <Gender>F</Gender>
    </MigrantWorker>
    <MigrantWorker>
      <WorkerID>E002</WorkerID>
      <WorkerName>Jane</WorkerName>
      <Nationality>Indonesia</Nationality>
      <BirthDate>6/6/1988</BirthDate>
      <Gender>F</Gender>
    </MigrantWorker>
    <MigrantWorker>
      <WorkerID>E003</WorkerID>
      <WorkerName>Kevin</WorkerName>
      <Nationality>Indonesia</Nationality>
      <BirthDate>9/9/1988</BirthDate>
      <Gender>M</Gender>
    </MigrantWorker>
    <MigrantWorker>
      <WorkerID>E004</WorkerID>
      <WorkerName>Jane</WorkerName>
      <Nationality>Vietnam</Nationality>
      <BirthDate>8/8/1998</BirthDate>
      <Gender>F</Gender>
    </MigrantWorker>
  </MigrantWorkerTable>
  <ContractTable>
    <Contract>
      <EmployerID>B001</EmployerID>
      <CareRecipientID>C001</CareRecipientID>
      <WorkerID>E001</WorkerID>
      <StartDate>6/1/2009</StartDate>
      <EndDate>5/31/2012</EndDate>
    </Contract>
    <Contract>
      <EmployerID>B001</EmployerID>
      <CareRecipientID>C002</CareRecipientID>
      <WorkerID>E002</WorkerID>
      <StartDate>6/1/2009</StartDate>
      <EndDate>5/31/2012</EndDate>
    </Contract>
    <Contract>
      <EmployerID>B002</EmployerID>
      <CareRecipientID>C001</CareRecipientID>
      <WorkerID>E003</WorkerID>
      <StartDate>6/1/2012</StartDate>
      <EndDate>5/31/2018</EndDate>
    </Contract>
    <Contract>
      <EmployerID>B003</EmployerID>
      <CareRecipientID>C003</CareRecipientID>
      <WorkerID>E001</WorkerID>
      <StartDate>7/1/2012</StartDate>
      <EndDate>6/30/2022</EndDate>
    </Contract>
    <Contract>
      <EmployerID>B004</EmployerID>
      <CareRecipientID>C004</CareRecipientID>
      <WorkerID>E004</WorkerID>
      <StartDate>9/1/2022</StartDate>
      <EndDate>8/31/2025</EndDate>
    </Contract>
  </ContractTable>
</Database>