Database Basic

1. Introduction to Databases

Database is a special system that stores data. When we say 'database', it typically means "relational database". Database has a "Table" which contains information about what we care about.

Let me give an example of retail services. In a database of retail service, there will be a time table with their product. Also, there will be a table of customers, orders, or items.

Like this relational database, there are each information about each feature. Also, each table has a relationship with other tables so we can find the connection of each information. 

Example of the retail database, when we find a specific order then we can notice who the consumer is, which product he/she order, how many product is remaining that the customer ordered.

At this time, there is a common object that connects table to table. We call this "primary key". Also,. they're an object located in a particular table, We call this "foreign key".

 

2. Relational Database

CustomerID	FirstName	LastName
1	Dan	Drayton
2	Victoria	Gray

Customer Table

 

InvoiceNo	Date	CustomerID
1234	1/1/2020	1
1235	1/1/2020	2
1236	1/1/2020	1

Order Table

InvoiceNo	ItemNo	ProductID	Quantity
1234	1	123	1
1235	1	123	2
1235	2	321	1
1236	1	321	1

Item Table

ProductId	Name	Price
123	Widget	1.99
321	Gizmo	2.45

Product Table

 

First, When you see the Customer table and the Order table, you can find out that "CustomerID" is the same array in each table. At this time, "CustomerID" is the primary key. "FirstName", "LastName" is not placed anywhere in each table. So, we call them a "Foreign Key". Also, compare to "Order Table' and "Item Table", InvoiceNo is the common array to them. "ProductID" is a common array to "Item Table" and "Product Table". So, Finding this common array and move to other tables can see overall information about the specialized object. This is a relational database.

 

There are many kinds of databases. The "Data Warehouse" is commonly used in Big Data. The example table are presented below.

InvoiceNo	ItemNo	DateID	CustomerID	ProductID	Quantity	Price
1234	1	20200101	1	123	1	1.99
1235	1	20200101	2	123	2	1.99
1235	2	20200101	2	321	1	2.49
1236	1	20200101	1	321	1	2.49

FactSales

As you can see, All of data is assembled to one table, We call this "Fact Table". Based on Fact Table, The detailed, sorted table create, we call them "Dim Table". Look example of the dim table below.

 

ProductID	Name	ListPrice
123	Wideet	1.99
321	Gizmo	2.45

DimProduct

CustomerID	Name
1	Dan Drayton
2	Victoria Gray

DimCustomer

DataID	Date	Month	Day
20200101	1/1/2020	January	Sunday
20200102	1/2/2020	January	Monday
20200102	1/3/2020	January	Tuesday

DimDate

 

From Fact Table, you can make specific dim tables. Like Product, Date, Customer, etc. You can also find "Primary Key" to Fact Table to Dim Table also vise versa.