Chao Huang

Chao Huang

Ph.D. student in M.E. at UTokyo.

Data Preparation [02]: Database Basics

2 minute read

Published:

Google Data Analytics: Prepare Data for Exploration - Week 3

Relational Databases

A relational database is a database that contains a series of tables that can be connected to show relationships.

Tables in a relational database are connected by keys (fields they have in common).

  • Primary key: A field within a table in which each value is unique
  • Foreign key: A field within a table that is a primary key in another table

drawing

  • Primary key is not requisite for some tables.
  • A table can have only one primary key, but it can have multiple foreign keys.
  • A primary key may also be constructed using multiple columns of a table, which is called a composite key.

Metadata

Metadata is data about data. In database management, it provides information about other data and helps to interpret the contents of the data within a database.

Types of Metadata

  • Descriptive metadata is metadata that describes a piece of data and can be used to identify it at a later point in time.
  • Structural metadata is metadata that indicates how a piece of data is organized and whether it’s part of one or more than one data collection.
  • Administrative metadata is metadata that indicates the technical source of a digital asset.

Metadata Repository

A metadata repository is a database specifically created to store metadata. These repositories describe where metadata came from, keep it in an accessible form so it can be used quickly and easily, and keep it in a common structure for everyone who may need to use it. Metadata repositories make it easier and faster to bring together multiple sources for data analysis.

Metadata repositories

  • describe the state and location of the metadata
  • describe the structure of the tables inside,
  • describe how data flows through the repository
  • keep track of who accesses the metadata and when

Data Sources

General

Public health datasets

Public climate datasets

Public social-political datasets

SQL Platforms

Guidelines For Writing SQL Queries

SQL Best Practices

Comments