{"id":2839,"date":"2021-10-23T23:52:02","date_gmt":"2021-10-24T07:52:02","guid":{"rendered":"https:\/\/wonghoi.humgar.com\/blog\/?p=2839"},"modified":"2021-11-15T22:14:27","modified_gmt":"2021-11-16T06:14:27","slug":"relational-database-concepts-heterogenous-data-tables-spreadsheets","status":"publish","type":"post","link":"https:\/\/wonghoi.humgar.com\/blog\/2021\/10\/23\/relational-database-concepts-heterogenous-data-tables-spreadsheets\/","title":{"rendered":"Data Relationships of Spreadsheets: Relational Database vs. Heterogenous Data Tables"},"content":{"rendered":"\n

This blog post is development in process. Will fill in the details missing details (especially pandas) later. Some of the MATLAB syntax are inaccurate in the sense that it’s just a description that is context dependent (such as column names can be cellstr, char string or linear\/logical indices).<\/p>\n\n\n\n

From data relationship point of view, relation database (RDMBS), heterogenous data tables (MATLAB’s dataset\/table or Python Panda’s Dataframe) are the same thing. But a proper database have to worry about concurrency issues and provide more consistency tools (ACID model). <\/p>\n\n\n\n

Heterogenous data tables are almost always column-oriented database (mainly for analyzing data) where MySQL and Postgres are row-store database. You can think of column-store database as Struct of Arrays (SoA) and row-store database as Array of Struct (AoS). Remember locality = performance: in general, you want to put the stuff you frequently want to access together as close to each other as possible.<\/p>\n\n\n\n

<\/p>\n\n\n\n

Mechanics:<\/p>\n\n\n\n

Concepts<\/strong><\/th>SQL<\/strong><\/th>MATLAB table<\/strong><\/th>Pandas Dataframe<\/strong><\/th><\/tr><\/thead>
<\/td><\/td><\/td><\/td><\/tr>
tables<\/td>FROM<\/td>(work with T)<\/td>(work with df)<\/td><\/tr>
columns
variables
fields <\/td>		SELECT <\/td>T.(field)<\/code>
T(:, cols\/varnames) <\/code><\/td>		<\/td><\/tr>
rows
records <\/td>		WHERE

HAVING <\/td>		T( cond(T), : )<\/code>

T_grp( cond(T_grp), : ) <\/code><\/td>		<\/td><\/tr>
conditions<\/td>NOT
IS
IN
BETWEEN <\/td>		~<\/code>
==, isequal*()<\/code>
ismember()<\/code>
a<=b & b<=c<\/code><\/td>		<\/td><\/tr>
Inject table to
another table<\/td>		INSERT INTO t2
SELECT vars FROM t1
WHERE rows<\/td>		T2(end+(1:#rows), vars) = T1(rows, vars)<\/code>
(Doable, throws warning)<\/td>
<\/td><\/tr>
Insert record\/row<\/td>INSERT INTO t (c1, c2, ..)
VALUES (v1, v2, ..)<\/td>		T=[T; {v1, v2, ...}]<\/code>
(Cannot default for unspecified column*)<\/td>		<\/td><\/tr>
update records\/elements<\/td>UPDATE table
SET column = content
WHERE row_cond<\/td>		T.(col)(row_cond) = content<\/code><\/td><\/td><\/tr>
New table
from selection<\/td>		SELECT vars
INTO t2
FROM t1
WHERE rows<\/td>		T2 = T1(rows, vars) <\/code><\/td><\/td><\/tr>
clear table<\/td>TRUNCATE TABLE t<\/td>T( :, : )=[]<\/code><\/td><\/td><\/tr>
delete rows<\/td>DELETE FROM t WHERE cond
(if WHERE is not specified, it kills all rows one by one with consistency checks. Avoid it and use TRUNCATE TABLE instead)<\/td>		T( cond, : ) = []<\/code><\/td><\/td><\/tr><\/tbody><\/table>
* I developed sophisticated tools to allow partial row insertion, but it’s not something TMW supports right out of the box. This involves overloading the default value generator<\/a> for each data type then extract the skeleton T( [], : )<\/code> to identify the data types.<\/figcaption><\/figure>\n\n\n\n

<\/p>\n\n\n\n

Core database concepts:<\/p>\n\n\n\n

Concepts<\/strong><\/td>SQL<\/strong><\/td>MATLAB (table\/dataset)<\/strong><\/td>Pandas (Dataframe)<\/strong><\/td><\/tr>
linear index<\/td>CREATE INDEX idx ON T (col)<\/td>T.idx = (1:size(T,1))'<\/code><\/td><\/td><\/tr>
group index<\/td>CREATE UNIQUE INDEX idx ON T (cols)<\/td>[~, T.idx] = sortrows(T, cols)<\/code>
(old implementation is grp2idx()<\/code>)<\/td>		<\/td><\/tr>
set operations<\/td>UNION
INTERSET<\/td>		union()
intersect()
setdiff(), setxor()<\/td>		<\/td><\/tr>
sort<\/td>ORDER BY<\/td>sortrows()<\/td><\/td><\/tr>
unique<\/td>SELECT DISTINCT<\/td>unique()<\/td><\/td><\/tr>
reduction
aggregration<\/td>		F()<\/td>@reductionFunctions<\/td><\/td><\/tr>
grouping<\/td>GROUP BY<\/td>Specifying ‘GroupingVariables’ in varfun(), rowfun(), etc.<\/td><\/td><\/tr>
partitioning<\/td>(set partition option in Table Definition<\/em>)<\/td>T1=T(:, {'key', varnames_1})<\/code>
T2=T(:, {'key', varnames_2})<\/code><\/td>		<\/td><\/tr>
joins<\/td>[type] JOIN<\/td>*join(T1, T2, ...)<\/code><\/td>df.join(df2, …)<\/td><\/tr>
cartesian product<\/a><\/td>CROSS JOIN
(misnomer, no keys)<\/td>		T_cross = [repelem(T1, size(T2,1), 1), repmat(T2, [size(T1,1), 1])]<\/code><\/td><\/td><\/tr><\/tbody><\/table>
Function programming concepts map (linear index), filter (logical index), reduce (summary & group) are heavily used with databases<\/figcaption><\/figure>\n\n\n\n

Formal databases has a Table Definition<\/em> (Column Properties<\/em>) that must be specified ahead of time and can be updated in-place<\/strong> later on (think of it as static typing). Heterogenous Data Tables can figure most of that out on the fly depending on context (think of it as dynamic typing). This impacts:<\/p>\n\n\n\n