Coursera 笔记

Module 04 · Final Project and Assessment

Final Project: Database Design and Implementation期末项目：数据库设计与实现

Congratulations! You have finished the modules. Now is the time to put your skills to the test. In the project you will design a database using an ERD tool and implement the database schema using PostgreSQL, MySQL and (optionally) Db2.
恭喜！您已完成所有模块。现在到了测试您技能的时候了。在项目中，您将使用 ERD 工具设计数据库，并使用 PostgreSQL、MySQL 和（可选的）Db2 实现数据库模式。

Estimated time needed: 90 minutes
预计所需时间：90 分钟

Congratulations! You have finished the modules. Now is the time to put your skills to the test. Read through the scenario below.
恭喜！您已完成模块学习。现在到了测试您技能的时候了。请阅读以下场景。

Scenario 场景

In this scenario, you have recently been hired as a Data Engineer by a New York-based coffee shop chain looking to expand nationally by opening several franchise locations. They want to streamline operations and revamp their data infrastructure as part of their expansion process.
在这个场景中，您最近被一家位于纽约的连锁咖啡店聘为数据工程师，该咖啡店计划通过开设多家特许经营店实现全国扩张。他们希望优化运营并作为扩张过程的一部分改造其数据基础设施。

Your job is to design their relational database systems for improved operational efficiencies and make it easier for their executives to make data-driven decisions.
你的工作是设计他们的关系数据库系统，以提高运营效率，并使他们的高管能够更轻松地做出数据驱动的决策。

Currently, their data resides in several systems: accounting software, supplier databases, point of sales (POS) systems, and even spreadsheets. You will review the data in all of these systems and design a central database to house all of the data. You will then create the database objects and load them with source data. Finally, you will create subsets of data your business partners require, export them, and load them into staging databases using several RDBMS.
目前，他们的数据存储在多个系统中：会计软件、供应商数据库、销售点（POS）系统和电子表格等。你需要审查所有这些系统中的数据，并设计一个中央数据库来存储所有数据。然后，你将创建数据库对象，并将源数据加载到其中。最后，你将创建业务伙伴所需的数据子集，导出它们，并使用多个关系数据库管理系统（RDBMS）将它们加载到暂存数据库中。

Software used in this project 本项目使用的软件

In this project, you will use PostgreSQL Database, MySQL. These are all relational database management systems (RDBMS) designed to store, manipulate, and retrieve the data efficiently.
在这个项目中，您将使用 PostgreSQL 数据库和 MySQL。这些都是关系型数据库管理系统（RDBMS），旨在高效地存储、操作和检索数据。

Data used in this project 本项目使用的数据

In this project, you will be working with a subset of data from the Coffee shop sample data.
在这个项目中，你将使用 Coffee shop 样本数据的一个子集。

You will use a modified version of the data for the project, so to succeed in the project, download the linked files when prompted in the instructions. You do not need to use any data from the source.
你将使用数据的修改版本来进行项目，因此要成功完成项目，请在说明中提示时下载链接的文件。你不需要使用任何源数据。

In your scenario, you will be working with data from the following sources:
在你的场景中，你将使用来自以下来源的数据：

  • Staff information held in a spreadsheet at headquarters (HQ)
    总部（HQ）处存储在电子表格中的员工信息
  • Sales outlet information held in a spreadsheet at HQ
    总部存放于电子表格中的销售网点信息
  • Sales data output as a CSV file from the POS system in the sales outlets
    销售网点 POS 系统输出为 CSV 文件的销售数据
  • Customer data output as a CSV file from a custom customer relationship management system
    自定义客户关系管理系统输出为 CSV 文件的客户数据
  • Product information maintained in a spreadsheet exported from your supplier's database
    从供应商数据库导出的电子表格中维护的产品信息

Objectives 目标

After completing this lab, you will be able to:
完成此实验后，您将能够：

  • Identify entities  识别实体
  • Identify attributes  识别属性
  • Create an entity relationship diagram (ERD) using the pgAdmin ERD tool
    使用 pgAdmin ERD 工具创建实体关系图（ERD）
  • Normalize tables  规范化表
  • Define keys and relationships
    定义键和关系
  • Create database objects by generating and running the SQL script from the ERD tool
    通过从 ERD 工具生成并运行 SQL 脚本来创建数据库对象
  • Create a view and export the data
    创建视图并导出数据
  • Create a materialized view and export the data
    创建物化视图并导出数据
  • Import data into a MySQL database using phpMyAdmin GUI tool
    使用 phpMyAdmin 图形界面工具将数据导入 MySQL 数据库

Task 1: Identify entities 任务 1：识别实体

The first step when designing a new database is to review any existing data and identify the entities for your new system.
设计新数据库的第一步是审查现有数据，并确定新系统的实体。

  1. The following image shows sample data from each source you will be working with to design your new central database. Review the image and identify the entities you plan to create.
    下图展示了您将用于设计新中央数据库的每个来源的样本数据。查看图像并确定您计划创建的实体。
    • Note: You can download a copy of this image or open it in another browser tab for reference later in the lab.
      注意：您可以下载此图像的副本，或在另一个浏览器标签页中打开它，以便在实验中后期参考。
  2. Make a list of the entities you have identified. Take a screenshot and save it as Task1.jpg or Task1.png.
    列出您已确定的实体。截取屏幕截图并将其保存为 Task1.jpg 或 Task1.png。

Task 2: Identify attributes 任务 2：识别属性

In this task, you will identify the attributes of one of the entities you plan to create.
在这个任务中，你需要识别你计划创建的一个实体的属性。

  1. Using the information from the sample data in the image from Task 1, identify the entity's attributes that will store the sales transaction data.
    使用任务 1 中图像中的示例数据，识别将存储销售交易数据的实体的属性。
  2. Make a list of the sales transaction attributes that you identified. Take a screenshot and save it as Task2.jpg or Task2.png.
    列出你识别的销售交易属性。截取屏幕截图并保存为 Task2.jpg 或 Task2.png。

Task 3: Create an ERD 任务 3：创建实体关系图

Now that you have defined some of your attributes and entities, you can determine the tables and columns for them and create an entity-relationship diagram (ERD).
现在你已经定义了一些属性和实体，你可以确定它们的表和列，并创建一个实体关系图（ERD）。

  1. Open a new terminal from the side-by-side Cloud IDE.
    从并排的云 IDE 中打开一个新的终端。
  2. Use the button below to start a PostgreSQL service session in the Cloud IDE.
    使用下面的按钮在云 IDE 中启动 PostgreSQL 服务会话。

Open and Start PostgreSQL in IDE
在 IDE 中打开并启动 PostgreSQL。

  1. Use the pgAdmin weblink to open pgAdmin in a new tab in your browser.
    使用 pgAdmin 网页链接在浏览器的新标签页中打开 pgAdmin。
  2. Create a new database named COFFEE, view the schemas in the new COFFEE database, and then start a new ERD project.
    创建一个名为 COFFEE 的新数据库，查看新 COFFEE 数据库中的模式，然后开始一个新的 ERD 项目。
  3. Add a table to the ERD for the sale transactions entity using the information in the following table. Consider the naming convention to use so that your colleagues can understand your data and ensure that the names are valid in other RDBMS. Use the sample data shown in the image in Task 1 to determine appropriate data types for each column.
    使用以下表格中的信息为销售交易实体在 ERD 中添加一个表。考虑使用命名约定，以便您的同事能够理解您的数据，并确保这些名称在其他 RDBMS 中有效。使用任务 1 中显示的示例数据来确定每列的适当数据类型。
  4. Take a screenshot of your ERD and save it as Task3A.png or Task3A.jpg.
    对您的 ERD 进行截图并保存为 Task3A.png 或 Task3A.jpg。
  5. Add a table to the ERD for the product entity using the information in the following table.
    在 ERD 中为产品实体添加一个表格，使用以下表格中的信息。
  6. Take a screenshot of your ERD and save it as Task3B.png or Task3B.jpg.
    对你的 ERD 进行截图并保存为 Task3B.png 或 Task3B.jpg。

Task 4: Normalize tables 任务 4：规范化表格

When reviewing your ERD, you notice it does not conform to the second normal form. In this task, you will normalize some of the tables within the database.
在审查您的 ERD 时，您发现它不符合第二范式。在本任务中，您将对数据库中的某些表进行规范化。

  1. Review the data in the sales transaction table. Note that the transaction id column does not contain unique values because some transactions include multiple products.
    审查销售交易表中的数据。请注意，交易 ID 列不包含唯一值，因为某些交易包含多个产品。
  2. Determine which columns should be stored in a separate table to remove the repeating rows and to put this table into second normal form.
    确定哪些列应存储在单独的表中，以消除重复行并将该表置于第二范式。
  3. Add a new table named sales_detail to the ERD, define the columns in the new table, and delete the moved columns from the sales transaction table, leaving a matching column in each of the two tables to create a relationship between them later.
    在 ERD 中添加一个名为 sales_detail 的新表，定义新表中的列，并从销售交易表中删除已移动的列，在两个表中保留一个匹配的列，以便以后创建它们之间的关系。
  4. Take a screenshot of your ERD and save it as Task4A.png or Task4A.jpg.
    对你的 ERD 进行截图并保存为 Task4A.png 或 Task4A.jpg。
  5. Review the data in the product table. Note that the product category and product type columns contain redundant data.
    查看产品表中的数据。注意产品类别和产品类型列包含冗余数据。
  6. Determine which columns should be stored in a separate table to reduce redundant data and to put this table into a second normal form.
    确定哪些列应该存储在单独的表中，以减少冗余数据并将该表转换为第二范式。
  7. Add a new table named product_type to the ERD, define the columns in the new table, and delete the moved columns from the product table, leaving a matching column in each of the two tables to create a relationship between them later.
    在 ERD 中添加一个名为 product_type 的新表，定义新表中的列，并从产品表中删除移动的列，在两个表中保留匹配的列，以便之后创建它们之间的关系。
  8. Take a screenshot of your ERD and save it as Task4B.png or Task4B.jpg.
    对你的 ERD 进行截图并保存为 Task4B.png 或 Task4B.jpg。

Task 5: Define keys and relationships 任务 5：定义键和关系

After normalizing your tables, you can define their primary keys and relationships between the tables in your ERD.
在规范化您的表格后，您可以在您的 ERD 中定义它们的主键和表之间的关系。

  1. Identify an appropriate column in each table to be a primary key and create the primary keys in the tables in your entity-relationship diagram (ERD).
    在每张表中确定一个合适的列作为主键，并在您的实体关系图（ERD）中创建主键。
  2. Take a screenshot of your ERD and save it as Task5A.png or Task5A.jpg.
    对您的 ERD 进行截图并保存为 Task5A.png 或 Task5A.jpg。
  3. Identify the relationships between the following pairs of tables and then create the relationships in your ERD:
    确定以下表对之间的关系，然后在您的 ERD 中创建这些关系：
    • sales_detail to sales_transaction   sales_detail 到 sales_transaction
    • sales_detail to product   sales_detail 到 product
    • product to product_type   product 到 product_type
  4. Take a screenshot of your ERD and save it as Task5B.png or Task5B.jpg.
    对你的 ERD 进行截图并保存为 Task5B.png 或 Task5B.jpg。

Task 6: Create database objects by generating and running the SQL script from the ERD tool 任务 6：通过生成并运行 ERD 工具的 SQL 脚本创建数据库对象

Now that your design is complete, you will generate an SQL script from your ERD, which you can use to create your database schema. For this project, you will then use a given SQL script to ensure that you can load the sample data into the schema. Finally, you will load the existing data from various sources into your new database schema.
现在您的设计已经完成，您将根据您的 ERD 生成一个 SQL 脚本，您可以使用该脚本来创建您的数据库模式。在这个项目中，您将使用一个给定的 SQL 脚本来确保您可以将示例数据加载到模式中。最后，您将从各种来源将现有数据加载到您的新数据库模式中。

  1. Use the Generate SQL functionality in the ERD tool to create an SQL script from your ERD.
    使用 ERD 工具中的生成 SQL 功能，根据您的 ERD 创建一个 SQL 脚本。
  2. Download the following GeneratedScript.sql file to your local computer.
    将以下 GeneratedScript.sql 文件下载到您的本地计算机。
  3. In pgAdmin, open the query tool, upload and open the GeneratedScript.sql file from your local computer, and then run the script to create the tables defined in the ERD. Verify that the tables exist in the COFFEE database’s public schema now.
    在 pgAdmin 中，打开查询工具，从本地计算机上传并打开 GeneratedScript.sql 文件，然后运行脚本以创建 ERD 中定义的表。现在验证表是否存在于 COFFEE 数据库的公共模式中。
  4. Take a screenshot of the tables shown in the tree-view pane on the left side of the page and save it as Task6A.png or Task6A.jpg.
    将页面左侧树形视图中显示的表截图，并保存为 Task6A.png 或 Task6A.jpg。
  5. Download the following CoffeeData.sql file to your local computer.
    将以下 CoffeeData.sql 文件下载到本地计算机。
  6. In pgAdmin, open another instance of the Query tool, upload and open the CoffeeData.sql file from your local computer, and then run the script to populate the tables you just created.
    在 pgAdmin 中，打开 Query 工具的另一个实例，从本地计算机上传并打开 CoffeeData.sql 文件，然后运行脚本以填充您刚刚创建的表。
  7. In pgAdmin, view the first 100 rows of the sales_detail table.
    在 pgAdmin 中，查看 sales_detail 表的前 100 行。
  8. Take a screenshot of the Data Output pane and save it as Task6B.png or Task6B.jpg.
    将数据输出窗格的屏幕截图保存为 Task6B.png 或 Task6B.jpg。

Task 7: Create a view and export the data 任务 7：创建视图并导出数据

The external payroll company has requested a list of employees and the locations at which they work. This list should not include the CEO or CFO who owns the company. In this task, you will create a view in your PostgreSQL database that returns this information and export the results to a CSV file.
外部薪酬公司已要求一份员工及其工作地点的清单。该清单不应包括公司所有者 CEO 或 CFO。在此任务中，你将在你的 PostgreSQL 数据库中创建一个视图，该视图返回这些信息，并将结果导出到 CSV 文件。

  1. In your COFFEE database, create a new view named staff_locations_view using the following SQL:
    在你的 COFFEE 数据库中，使用以下 SQL 创建一个名为 staff_locations_view 的新视图：
    SELECT staff.staff_id,
staff.first_name,
staff.last_name,
staff.location
FROM staff
WHERE "position" NOT IN ('CEO', 'CFO');
  2. View all the rows returned from the view.
    显示视图返回的所有行。
  3. Save the query results to a file named staff_locations_view.csv on your local computer.
    将查询结果保存到本地计算机上的名为 staff_locations_view.csv 的文件中。
  4. Take a screenshot of the view shown in the tree-view pane on the left side of the page with the results in the Data Output pane, and save it as Task7.png or Task7.jpg.
    截取页面左侧树形视图区域中显示的结果，在数据输出视图中，并将其保存为 Task7.png 或 Task7.jpg。

Task 8: Create a materialized view and export the data 任务 8：创建物化视图并导出数据

A marketing consultant requires access to your product data in their MySQL database for a marketing campaign. You will create a materialized view in your PostgreSQL database that returns this information and export the results to a CSV file.
一位营销顾问需要访问您产品数据，以便在他们的 MySQL 数据库中开展营销活动。您将在您的 PostgreSQL 数据库中创建一个物化视图，该视图返回这些信息，并将结果导出到 CSV 文件。

  1. In your COFFEE database, create a new materialized view named product_info_m-view using the following SQL:
    在您的 COFFEE 数据库中，使用以下 SQL 创建一个名为 product_info_m-view 的新物化视图：
    SELECT product.product_name, product.description, product_type.product_category
FROM product
JOIN product_type
ON product.product_type_id = product_type.product_type_id;
  2. Refresh the materialized view with data.
    使用数据刷新物化视图。
  3. View all the rows returned from the view.
    显示视图返回的所有行。
  4. Save the query results to a file named product_info_m-view.csv on your local computer.
    将查询结果保存到本地计算机上的名为 product_info_m-view.csv 的文件中。
  5. Take a screenshot of the view shown in the tree-view pane on the left side of the page with the results in the Data Output pane, and save it as Task8.png or Task8.jpg.
    对页面左侧的树视图窗格中显示的视图（数据输出窗格中包含结果）进行截图，并保存为 Task8.png 或 Task8.jpg。

Task 9: Import staff_location data into a MySQL database 任务 9：将 staff_location 数据导入 MySQL 数据库

The external payroll company has asked you to upload the staff location information to their MySQL database.
外部薪酬公司要求您将员工位置信息上传到他们的 MySQL 数据库。

  1. Click the button below to start a MySQL instance in the Cloud IDE.
    点击下方按钮在云 IDE 中启动 MySQL 实例。

Open and Start MySQL in IDE
在 IDE 中打开并启动 MySQL

  1. Open phpMyAdmin in a new tab in your browser.
    在浏览器新标签页中打开 phpMyAdmin。
  2. In phpMyAdmin, create a new database named STAFF_LOCATIONS, then import the location information saved in the staff_locations_view.csv file you exported from the view you created in Task 7.
    在 phpMyAdmin 中创建一个名为 STAFF_LOCATIONS 的新数据库，然后导入您从任务 7 中创建的视图导出的 staff_locations_view.csv 文件中保存的位置信息。
  3. Explore the new table and then view the data in it.
    探索新表，然后查看其中的数据。
  4. Take a screenshot of the contents of the new table and save it as Task9.png or Task9.jpg.
    对新表的内容进行截图并保存为 Task9.png 或 Task9.jpg。

Task 10: Import coffee_shop_products data into a MySQL database 任务 10：将 coffee_shop_products 数据导入 MySQL 数据库

The marketing consultant has asked you to upload the product information to their MySQL database.
营销顾问要求您将产品信息上传到他们的 MySQL 数据库。

  1. In phpMyAdmin, create a new database named coffee_shop_products, and then import the product information saved in the product_info_m-view.csv file from your materialized view into a new table in the coffee_shop_products database.
    在 phpMyAdmin 中，创建一个名为 coffee_shop_products 的新数据库，然后将您在 product_info_m-view.csv 文件中保存的产品信息导入到 coffee_shop_products 数据库中的新表中。
  2. Browse the contents of the new table.
    浏览新表的内容。
  3. Take a screenshot of the contents of the new table and save it as Task10.png or Task10.jpg.
    对新表的内容进行截图并保存为 Task10.png 或 Task10.jpg。

Task 11[Optional]: Import staff_location data into Db2 database 任务 11[可选]: 将 staff_location 数据导入 Db2 数据库

The external payroll company has asked you to upload the staff location information to their Db2 database.
外部薪酬公司要求您将员工位置信息上传至他们的 Db2 数据库。

  1. In a new browser tab, go to cloud.ibm.com/login, log in using your credentials, and then open a console for your Db2 on the Cloud instance you created earlier in this course.
    在新浏览器标签页中，访问 cloud.ibm.com/login，使用您的凭证登录，然后打开您在本课程早期创建的云上 Db2 实例的控制台。
  2. Use the Load Data feature to load a new table named STAFF_LOCATIONS with the staff location information saved in the staff_locations_view.csv file you exported from the view you created in Task 7.
    使用加载数据功能，将您从任务 7 中创建的视图导出的 staff_locations_view.csv 文件中保存的员工位置信息加载到名为 STAFF_LOCATIONS 的新表中。
  3. Explore the new table and then view the data in it.
    探索新表，然后查看其中的数据。
  4. Take a screenshot of the contents of the new table and save it as Task9.png or Task9.jpg.
    对新表的内容进行截图并保存为 Task9.png 或 Task9.jpg。

Recap 回顾

In this project, you created the objects and views that you exported in a MySQL database.
在这个项目中，你创建了在 MySQL 数据库中导出的对象和视图。

Author(s) 作者

© IBM Corporation. All rights reserved.