當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

电子表格转换成数据库_创建数据库，将电子表格转换为关系数据库，第1部分...

發(fā)布時間：2023/11/29 数据库 21 豆豆

生活随笔收集整理的這篇文章主要介紹了电子表格转换成数据库_创建数据库，将电子表格转换为关系数据库，第1部分... 小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

電子表格轉(zhuǎn)換成數(shù)據(jù)庫

Part 1: Creating an Entity Relational Diagram (ERD)

第1部分：創(chuàng)建實體關(guān)系圖(ERD)

A Relational Database Management System (RDMS) is a program that allows us to create, update, and manage a relational database. Structured Query Language (SQL) is a programming language used to communicate with data stored in the RDMS. The SQL skill for using a RDMA is required for many data-related positions these days. In the social media forums like Quora or Reddit, there are many people who search for a public database for practicing their SQL querying skills. However, although there are many public data sets in a single spreadsheet, there are not many public databases online. Even if you found a data set for the topic you have interest in, the format of the data is usually just one spreadsheet, not a database for most cases. Therefore, it will be very useful to know how to convert a data set in one spreadsheet to a database with multiple tables fitting a relational database format. Knowing the process of this conversion can give us many chances to practice SQL querying skills with a variety of databases.

關(guān)系數(shù)據(jù)庫管理系統(tǒng) (RDMS)是允許我們創(chuàng)建，更新和管理關(guān)系數(shù)據(jù)庫的程序。結(jié)構(gòu)化查詢語言 (SQL)是一種編程語言，用于與RDMS中存儲的數(shù)據(jù)進行通信。如今，與許多數(shù)據(jù)相關(guān)的職位都需要使用RDMASQL技能。在Quora或Reddit等社交媒體論壇中，有很多人在搜索公共數(shù)據(jù)庫以練習(xí)其SQL查詢技能。但是，盡管單個電子表格中有許多公共數(shù)據(jù)集，但在線的公共數(shù)據(jù)庫并不多。即使您找到了感興趣的主題的數(shù)據(jù)集，數(shù)據(jù)的格式通常也只是一個電子表格，在大多數(shù)情況下不是數(shù)據(jù)庫。因此，了解如何將一個電子表格中的數(shù)據(jù)集轉(zhuǎn)換為具有多個符合關(guān)系數(shù)據(jù)庫格式的表的數(shù)據(jù)庫將非常有用。了解這種轉(zhuǎn)換的過程可以使我們有很多機會來練習(xí)各種數(shù)據(jù)庫SQL查詢技能。

This is the first article in a two part series. The goal of this series is to show how to create a relational database for SQL. The whole process is to convert a data in one spreadsheet to a relational database for SQL. In this first article I create an Entity Relational Diagram (ERD) which is a graphical representation showing the relationships between entities.

這是分兩部分的系列文章中的第一篇。本系列的目的是展示如何為SQL創(chuàng)建關(guān)系數(shù)據(jù)庫。整個過程是將一個電子表格中的數(shù)據(jù)轉(zhuǎn)換為SQL的關(guān)系數(shù)據(jù)庫。在第一篇文章中，我創(chuàng)建一個實體關(guān)系圖(ERD) ，該圖是顯示實體之間關(guān)系的圖形表示。

數(shù)據(jù)集 (The Data Set)

Let’s find a data set for this practice. There are many public data on the Kaggle dataset. Among a bunch of data sets, I selected a data set named Sample Sales Data. The following pictures show the original format of the data which is contained in one spreadsheet.

讓我們?yōu)樵摼毩?xí)找到一個數(shù)據(jù)集。 Kaggle數(shù)據(jù)集上有許多公共數(shù)據(jù)。在一系列數(shù)據(jù)集中，我選擇了一個名為Sample Sales Data的數(shù)據(jù)集。下圖顯示了一個電子表格中包含的數(shù)據(jù)的原始格式。

Sample Sales Data from the Kaggle Dataset來自Kaggle數(shù)據(jù)集的樣本銷售數(shù)據(jù)

The data has 25 columns and 2824 rows including headers. The list of the headers is as follows:

數(shù)據(jù)有25列和2824行，包括標(biāo)題。標(biāo)頭列表如下：

ORDERNUMBER: the identification number for each order
ORDERNUMBER ：每個訂單的標(biāo)識號
QUANTITYORDERED: the quantity ordered
QUANTITYORDERED ：訂購的數(shù)量
PRICEEACH: the actual price paid for the transaction (variable across transactions)
PRICEEACH ：為交易支付的實際價格(隨交易而變化)
ORDERLINENUMBER: the number of the order line
ORDERLINENUMBER ：訂單行的編號
SALES: the amount of sales
銷售：銷售額
ORDERDATE: the order date
ORDERDATE ：訂單日期
STATUS: the shipping status (Shipped, Resolved, Cancelled, On Hold, Disputed, and In Progress)
狀態(tài) ：運輸狀態(tài)(已發(fā)貨，已解決，已取消，保留，有爭議和進行中)
QTR_ID: the quarter of the order date
QTR_ID ：訂購日期的四分之一
MONTH_ID: the month of the order date
MONTH_ID ：訂購日期的月份
YEAR_ID: the year of the order date
YEAR_ID ：訂單日期的年份
PRODUCTLINE: the category of products
PRODUCTLINE ：產(chǎn)品類別
MSRP: the manufacture’s suggested retail price (constant across transactions)
MSRP ：制造商的建議零售價(在交易中保持不變)
PRODUCTCODE: the identification code for each product
PRODUCTCODE ：每個產(chǎn)品的識別碼
CUSTOMERNAME: the names of customers
客戶名稱：客戶名稱
PHONE: the phone numbers of customers
電話：客戶的電話號碼
ADDRESSLINE1: addressline 1 for customers
ADDRESSLINE1 ：客戶的地址行1
ADDRESSLINE2: address line 2 for customers
ADDRESSLINE2 ：客戶的地址行2
CITY: city names for customers
城市：客戶的城市名稱
STATE: state names for customers (only for customers located in the US)
STATE ：客戶的州名(僅適用于位于美國的客戶)
POSTALCODE: postal codes for customers
郵政編碼：為客戶郵政編碼
COUNTRY: countries for customers
國家：客戶所在的國家
TERRITORY: the regional names of each country (NA, EMEA, Japan, and APAC)
地區(qū) ：每個國家的區(qū)域名稱(NA，EMEA，日本和亞太地區(qū))
CONTACTLASTNAME and CONTACTFIRSTNAME: the last and first names of customers
CONTACTLASTNAME和CONTACTFIRSTNAME ：客戶的姓氏和名字
DEALSIZE: the deal sizes of orders
DEALSIZE ：訂單的交易大小

創(chuàng)建實體關(guān)系圖(ERD) (Creating an Entity Relationship Diagram (ERD))

The information contained in the data can be basically divided into three entities: Customers, Products, and Orders.

數(shù)據(jù)中包含的信息基本上可以分為三個實體：客戶， 產(chǎn)品和訂單。

顧客 (Customers)

Based on the columns in the data, the attributes to be included in the entity for Customers are CUSTOMERNAME, CONTACTLASTNAME, CONTACTFIRSTNAME, PHONE, ADDRESSLINE1, ADDRESSLINE2, POSTALCODE, CITY, STATE, COUNTRY, and TERRITORY. CustomerID is added into the entity as the primary key. And for convenience sake, the cases of the attributes are changed as follows:

根據(jù)數(shù)據(jù)中的列，要包含在“客戶”實體中的屬性是CUSTOMERNAME ， CONTACTLASTNAME，CONTACTFIRSTNAME， PHONE ， ADDRESSLINE1 ， ADDRESSLINE2 ， POSTALCODE ， CITY，STATE，COUNTRY和TERRITORY 。 CustomerID作為主鍵添加到實體中。為了方便起見，屬性的情況如下更改：

The problem of keeping City, State, Country, and Territory in the entity for Customers is that there will be many duplicate rows in the table for Customers. Therefore, entities for City, Country, and Territory are separately created, and then the links between them and Customers are made using foreign keys. The reason why the entity for State is not created is that there are many null values in State so that states do not determine countries and territories in this data.

保留城市，州，國家和地區(qū)的問題在客戶實體中是表中的客戶會有很多重復(fù)的行。因此，將分別創(chuàng)建用于城市，國家和地區(qū)的實體，然后使用外鍵在它們和客戶之間建立鏈接。為什么不建立國有實體的原因是，在許多國家空值，這樣的狀態(tài)并不確定這個數(shù)據(jù)的國家和地區(qū)。

In the ERD, there are three types of relationships between entities: One-to-One, One-to-Many(or Many-to-One), and Many-to-Many. The relationships between Territories and Countries, Countries and Cities, and Cities and Customers are One-to-Many relationships. For example, each customer lives in one city in this data while one city has many customers. Therefore, using the Crow’s Foot Notation, the ERD between Customers, Cities, Countries, and Territories is as follows:

在ERD中，實體之間存在三種類型的關(guān)系：一對一，一對多(或多對一)和多對多。 區(qū)域與國家，國家和城市以及城市與客戶之間的關(guān)系是一對多關(guān)系。例如，在此數(shù)據(jù)中，每個客戶居住在一個城市中，而一個城市有許多客戶。因此，使用烏鴉腳符號，客戶之間的ERD，城市，國家，和地區(qū)如下：

The Crow’s Foot Notations for Customers, Cities, Countries, and Territories客戶，城市，國家和地區(qū)的魚尾紋符號

產(chǎn)品展示 (Products)

The attributes to be included in the entity for Products are PRODUCTCODE, PRODUCTLINE, MSRP, and ORDERNUMBER. Among these attributes, PRODUCTCODE can be a candidate for the primary key in the entity since it is unique and does not have null values. However, in order to keep the format of the primary keys consistent across entities, ProductID is added into the entity and is set to be the primary key. The letter cases are changed as follows:

產(chǎn)品實體中要包含的屬性是PRODUCTCODE ， PRODUCTLINE ， MSRP和ORDERNUMBER 。在這些屬性中， PRODUCTCODE可以是實體中主鍵的候選者，因為它是唯一的并且沒有空值。但是，為了使主鍵的格式在各個實體之間保持一致，產(chǎn)品編號被添加到實體中并設(shè)置為主鍵。字母大小寫更改如下：

As we saw in the case for the entity for Customers, including Productline in this entity generates duplicate rows as well. Therefore, the entity for ProductLines is created separately, and is linked to the entity for Products using a foreign key. Since each product is classified into one product line while each product line has many products, the relationship between ProductLines and Products is One-to-Many.

正如我們在“ 客戶 ”實體案例中看到的那樣，在該實體中包括Productline也會生成重復(fù)的行。因此， ProductLine的實體是單獨創(chuàng)建的，并使用外鍵鏈接到Products的實體。由于每個產(chǎn)品分類為一個產(chǎn)品線，而每個產(chǎn)品線具有許多產(chǎn)品，因此ProductLine和Products之間的關(guān)系為一對多。

The Crows’ Notation for ProductLines and Products產(chǎn)品線和產(chǎn)品的烏鴉符號

This is not the final version for Products since OrderNumber will be included in the entity for Orders as well. Including OrderNumber in the entity for Orders will make some changes in the entity for Products. This will be covered after the entity for Orders is created.

這并非以來ORDERNUMBER 產(chǎn)品將包含在訂單的實體以及最終版本。在訂單的實體，包括訂單號碼將在產(chǎn)品實體的一些變化。創(chuàng)建訂單實體后將對此進行介紹。

命令 (Orders)

The entity for Orders should include ORDERNUMBER, ORDERDATE, PRODUCTCODE, STATUS, QUARTER_ID, MONTH_ID, YEAR_ID,and CUSTOMERNAME as its attributes. OrderID is added into this entity as the primary key. The names and cases of the attributes are changed as follows for convenience sake:

訂單實體應(yīng)包括ORDERNUMBER ， ORDERDATE ， PRODUCTCODE ， STATUS ， QUARTER_ID ， MONTH_ID ， YEAR_ID和CUSTOMERNAME作為其屬性。 OrderID作為主鍵添加到該實體中。為了方便起見，屬性的名稱和大小寫更改如下：

In order to remove duplicate rows generated by including Status and CustomerName in this entity, the entities for Status and CustomerNames are created separately and are linked to the entity for Orders using foreign keys. The entity for CustomerNames (which is equivalent to the Customers) was already created previously. The relationships of Orders with Status and Customers are Many-to-One, respectively.

為了刪除通過在此實體中包含Status和CustomerName生成的重復(fù)行，分別創(chuàng)建了Status和CustomerNames的實體，并使用外鍵將其鏈接到Order的實體。 CustomerNames的實體(等同于Customer )是先前已創(chuàng)建的。訂單與狀態(tài)和客戶的關(guān)系分別是多對一的。

The Crow’s Foot Notations for Orders, Customers, and Status訂單，客戶和狀態(tài)的魚尾紋

訂單和產(chǎn)品 (Orders and Products)

Each order contains a list of products in it. This means that Orders and Products are related to each other. What is the relationship between these two entries? Each product can be ordered by multiple orders and each order can contain multiple products. Therefore, the relationship is many-to-many.

每個訂單中都包含一個產(chǎn)品列表。這意味著訂單和產(chǎn)品相互關(guān)聯(lián)。這兩個條目之間是什么關(guān)系？每個產(chǎn)品可以按多個訂單訂購，每個訂單可以包含多個產(chǎn)品。因此，這種關(guān)系是多對多的。

The Crow’s Foot Notation for Orders and Products 1訂單和產(chǎn)品的魚尾紋1

The way to create tables which are in the many-to-many relationship is to create a junction table between the two original tables and then to make one-to-many relationships between the original tables and the junction table. The junction table is the table for the combination of Orders and Products, and then one-to-many relationships are made between Orders and the junction, and between Products and the junction. The primary key for this junction entity should be the composite of OrderID and ProductID.

創(chuàng)建作為在許多一對多的關(guān)系表的方法是將原來的兩個表之間創(chuàng)建一個結(jié)表，然后做一個對許多原始表和聯(lián)接表之間的關(guān)系。聯(lián)結(jié)表是Orders和Products組合的表，然后在Orders和聯(lián)結(jié)之間以及Products和聯(lián)結(jié)之間建立一對多關(guān)系。此聯(lián)結(jié)實體的主鍵應(yīng)為OrderID的組合和ProductID 。

The remaining columns which are not included in any entity so far are QUANTITYORDERED, PRICEEACH, ORDERLINENUMBER, SALES, and DEALSIZE. These columns cannot be determined by Orders or Products by themselves. For example, the quantity of a product in an order cannot be identified by an order or a product alone. Only the combination of an order and a product can identify it. Therefore, those columns are included in the junction entity as its attributes. The entities and their relationships for Orders and Products are as follows:

到目前為止，尚未包含在任何實體中的其余列為QUANTITYORDERED ， PRICEEACH ， ORDERLINENUMBER ， SALES和DEALSIZE 。這些列不能由訂單或產(chǎn)品自行確定。例如，訂單中的產(chǎn)品數(shù)量無法通過訂單或單獨的產(chǎn)品來識別。只有訂單和產(chǎn)品的組合才能識別它。因此，這些列作為其屬性包含在聯(lián)結(jié)實體中。訂單和產(chǎn)品的實體及其關(guān)系如下：

The Crow’s Foot Notations for Orders and Products 2訂單和產(chǎn)品的魚尾紋2

Due to duplicate rows, the entity for DealSizes is separately created and linked to the entity for Orders_Products using foreign key for SizeID.

由于重復(fù)行，為DealSizes實體被單獨創(chuàng)建并鏈接到實體使用SizeID外鍵Orders_Products。

整個ERD (The Entire ERD)

Now, we completed creating all the entities for this data and making relationships between them. The entire ERD is as follows:

現(xiàn)在，我們完成了為該數(shù)據(jù)創(chuàng)建所有實體并在它們之間建立關(guān)系。整個ERD如下：

The Final Version of the ERD for Sample Sales Data樣品銷售數(shù)據(jù)ERD的最終版本

The usefulness of the ERD is that it makes us to easily understand the relationships between the columns (or the entities) in the data set and to see the entire structure of the data set at a glance.

ERD的有用之處在于，它使我們能夠輕松了解數(shù)據(jù)集中的列(或?qū)嶓w)之間的關(guān)系，并一目了然地看到數(shù)據(jù)集的整個結(jié)構(gòu)。

下一步是什么？ (What’s Next?)

This article covered how to create an Entity Relationship Diagram using a data set. The data for this practice is Sample Sales Data obtained from the Kaggle dataset. It is briefly covered how to divide the original data set into entities and make relationships between the entities.

本文介紹了如何使用數(shù)據(jù)集創(chuàng)建實體關(guān)系圖。該實踐的數(shù)據(jù)是從Kaggle數(shù)據(jù)集獲得的樣本銷售數(shù)據(jù) 。簡要介紹了如何將原始數(shù)據(jù)集劃分為實體以及如何在實體之間建立關(guān)系。

In the next article, the practice of converting a data in a single spread sheet to a relational database by using MySQL will be covered.

在下一篇文章中，將介紹通過使用MySQL將單個電子表格中的數(shù)據(jù)轉(zhuǎn)換為關(guān)系數(shù)據(jù)庫的實踐。

翻譯自: https://medium.com/swlh/creating-a-database-converting-a-spreadsheet-to-a-relational-database-part-1-2a9a228bf77a