RDBMS Assignment

題目要求：

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 original source.

In your scenario, you will be working with data from the following sources:

• Staff information held in a spreadsheet at 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.
• Customer data output as a CSV file from a bespoke customer relationship management system.
• Product information maintained in a spreadsheet exported from your supplier's database.

After completing this lab, you will be able to:

• Identify entities.
• Identity attributes.
• Create an entity relationship diagram (ERD) using the pgAdmin ERD Tool.
• Normalize tables.
• Define keys and relationships.
• Create database objects by generating and running the SQL script from the ERD Tool.
• Create a view and export the data.
• Create a materialized view and export the data.
• Import data into a MySQL database.

事前準備：

1. Open a new terminal from the side-by-side Cloud IDE.
2. Use the start_postgres command to start a PostgreSQL service session in the Cloud IDE.
3. Use the pgAdmin weblink to open pgAdmin in a new tab in your browser.

資料庫設計的需求單

• Product table:
  
  
  
  
  

1. Create an ERD

題目要求：

Now that you have defined some of your attributes and entities, you can determine the tables and columns for them and create an ERD.

1. Create a new database named COFFEE, view the schemas in the new COFFEE database, and then start a new ERD project.
2. Add a table to the ERD for the sale transactions entity using the information in the following table. Consider what naming convention to use so that your colleagues will be able to understand your data and to ensure that the names are valid in other RDBMS. And use the sample data shown in the image in Task 1 to determine appropriate data types for each column.
   
   
   
   
   
3. Add a table to the ERD for the product entity using the information in the following table. Consider what naming convention to use so that your colleagues will be able to understand your data and to ensure that the names are valid in other RDBMS. And use the sample data shown in the image in Task 1 to determine appropriate data types for each column.
   
   
   
   
   

---

• pgAdmin 的介面
  
  

  
  
  

• 新增 Table
  
  

  
  
  
  
  
  
  
  

• 設定後的結果
  
  

  
  
  

2. Normalize tables

題目要求：

When reviewing your ERD you notice that it does not conform to second normal form. In this task, you will normalize some of the tables within the database.

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.
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 two tables to later create a relationship between them.

• 重新整理 table 裡的資料，把資料在細分到新的 table 上。
  
  
  
  
  
  • sales_transaction 的 product_id, quantity 以及 price 資料存到新的 sales_detail table。
  • 為了 sales_detail table 與 sales_transaction 保持關聯性，所以新增 transaction_id 資料。

4. Review the data in the product table. Note that the product category and product type columns contain redundant data.
5. Determine which columns should be stored in a separate table to reduce redundant data and to put this table into second normal form.
6. 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 two tables to later create a relationship between them.

• 參考product table 新增 product_type 的 table，並把多餘的資料清除。
  
  
  
  
  

---

3. Define keys and relationships

題目要求：

After normalizing your tables, you can define their primary keys and define relationships between the tables in your ERD.

1. Identify an appropriate column in each table to be a primary key and create the primary keys in the tables in your ER

• 在各個table建立primary key。
  
  
  
  
  
• 開啟primary key設定。
  
  
  
  
  
• 設定後的結果。
  
  
  
  
  

2. Identify the relationships between the following pairs of tables and then create the relationships in your ERD:
   • sales_detail to sales_transaction
   • sales_detail to product
   • product to product_type

• 繪製關係圖
  
  
  
  
  
• 設定後的結果
  
  
  
  
  

---

4. Create database objects by generating and running the SQL script from the ERD Tool

題目要求：

Now that your design is complete, you will generate an SQL script from your ERD which you could use to create your database schema. For the purposes of this project, you will then use a provided SQL script to ensure that you will be able to successfully load the sample data into the schema. Finally, you will load the existing data from the various data sources into your new database schema.

1. Use the Generate SQL functionality in the ERD Tool to create an SQL script from your ERD.
2. Download the GeneratedScript.sql file below to your local computer storage.
   • GeneratedScript.sql
3. In pgAdmin, open the Query Tool, upload and open the GeneratedScript.sql file from your local computer storage, and then execute the script to create the tables defined in the ERD. Verify that the tables now exist in the public schema of the COFFEE database.

• 上傳GeneratedScript.sql檔案
  
  
  
  
  
• 打開已上傳檔案,並執行SQL文本
  
  
  
  
  
• 重新整理頁面，就會看到SQL執行後的結果。
  
  
  
  
  

5. Download the CoffeeData.sq file below to your local computer storage.
   • CoffeeData.sql
6. In pgAdmin, open another instance of the Query Tool, upload and open the CoffeeData.sql file from your local computer storage, and then execute the script to populate the tables you just created.

• 依照以上的步驟上傳CoffeeData.sql檔案。

7. In pgAdmin, view the first 100 rows of the sales_detail table.

• ​​SELECT * FROM sales_detail LIMIT 100;
  

• 回傳結果
  
  
  
  
  

---

5. Create a view and export the data

題目要求：

The external payroll company have requested a list of employees and the locations at which they work. This should not include the CEO or CFO who own 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.

1. In your COFFEE database, create a new view named staff_locations_view using the following SQL:
   ​​​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 results of the query to a file named staff_locations_view.csv on your local computer storage.

• 下載回傳結果的CSV檔
  
  
  
  
  

---

6. Create a materialized view and export the data

題目要求：

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.

1. In your COFFEE database, create a new materialized view named product_info_m-view using the following SQL:
   ​​​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 results of the query to a file named product_info_m-view.csv on your local computer storage.

• 依照以上的步驟下載檔案，並把檔案命名為product_info_m-view.csv
  
  
  
  
  

---

7. Import data into a MySQL database

題目要求：

The marketing consultant has asked you to upload the product information to their MySQL database.

1. In the terminal from the side-by-side Cloud IDE, use the start_mysql command to start a My SQL service session in the Cloud IDE.
2. Use the browser weblink to open phpMyAdmin in a new tab in your browser.
3. 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.

• 創建新的資料庫，命名為 coffee_shop_products
  
  
  
  
  
• 把 product_info_m-view.csv 檔案上載至資料庫中
  
  
  
  
  
• 測試上傳是否有成功
  
  
  
  
  
  • 成功取得回傳
    
    
    

---

總結：

這次的練習讓我了解到如何使用pgAdmin和phpMyAdmin操作資料庫。