Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Relational is more than SQL

fauna.com

Post reply on HN
Print view View original

21–30 of 177 posts

Re: Relational is more than SQL

#21

Post by ghusbands »

_a_a_a_ wrote: post #13
ghusbands wrote: post #10

> Most importantly, SQL databases made supporting highly consistent ACID transactions easy. The default transaction isolation level for every major database is not ACID. Enabling the required serializability tends to make performance terrible, and so most don't.

> Enabling the required serializability... is trivial, no? > ...tends to make performance terrible I've heard this a lot but never seen any figures - anyone have any numbers/experience? (edit: and most apps I've worked with didn't need serialisability, either because they were working with a snapshot of data or absolutely precise answers weren't needed)

I've heard multiple accounts of people being taken by surprise by this and by how transaction isolation actually works in databases and not finding it at all easy to correct it. A famous one is https://blog.codinghorror.com/deadlocked/

(On your edit: The problem is not knowing when you're being hit by it. Even just maintaining a limit on total size of uploaded files or such, for example, is nontrivial under default isolation levels.)

Re: Relational is more than SQL

#22

Post by bazoom42 »

_a_a_a_ wrote: post #16
bazoom42 wrote: post #7

Earlier quoted context omitted.

A relation is by definition a set of tuples (informally called a table where the tuples are the rows). Codds relational database model adds the further constraint that nested tables are not allowed (first normal form), instead representing relationships through foreign keys. Codds motivation for disallowing nested tables is that it makes query languages much simpler. He develops relational algebra which is the founda…

Out of my depth here (no experience) but "Codds relational database model adds the further constraint that nested tables are not allowed" may be wrong. He allowed nested stuff, it's just that SQL didn't support it. Can anyone elucidate? Please don't shout that I'm wrong because there was something there in his first paper.

No, he explicitly disallows nested relations. This is the definition of first normal form.

Hierarchical databases (which predate relational) can be understood as nested relations, and Codds first example of normalization is how to extract the nested relations in such a database into seperate tables and instead express the relationships through foreign keys.

Re: Relational is more than SQL

#23

Post by _a_a_a_ »

ghusbands wrote: post #21
_a_a_a_ wrote: post #13

Earlier quoted context omitted.

> Enabling the required serializability... is trivial, no? > ...tends to make performance terrible I've heard this a lot but never seen any figures - anyone have any numbers/experience? (edit: and most apps I've worked with didn't need serialisability, either because they were working with a snapshot of data or absolutely precise answers weren't needed)

I've heard multiple accounts of people being taken by surprise by this and by how transaction isolation actually works in databases and not finding it at all easy to correct it. A famous one is https://blog.codinghorror.com/deadlocked/ (On your edit: The problem is not knowing when you're being hit by it. Even just maintaining a limit on total size of uploaded files or such, for example, is nontrivial under default i…

On your first para I'm not remotely sympathetic. They are using a complex, sophisticated tool without understanding it. They got what they deserved. Particularly telling is this ridiculous quote "I'm a little disappointed that SQL Server treats our silly little web app like it's a banking application". Edit: so, they wanted even more lax integrity constraints by default? FFS

And, BTW, deadlocks can most definitely happen using the default Read Committed isolation level.

Per your 2nd para, I simply don't understand, can you clarify?

Re: Relational is more than SQL

#24

Post by gregjor »

ghusbands wrote: post #8
gregjor wrote: post #6

Earlier quoted context omitted.

> there’s nothing in [Codd’s] principles that require tables/rows of tuples. Have you read Codd’s Rules #1 and #2? Pretty clear on this point. https://en.wikipedia.org/wiki/Codd%27s_12_rules Technically the relational model uses the term relation to refer to an unordered set of tuples, where every tuple has a key (one or more elements) to uniquely identify it, and every tuple has the same number of items, of the same…

> > there’s nothing in [Codd’s] principles that require tables/rows of tuples. You're misquoting; is that intentional? The post you're replying to says the principles that inspired/predate Codd don't require tables/tuples. Thus, the details of Codd's relational model are irrelevant.

If I misunderstood the principles you refer to I apologize. Codd's relational rules and designs for relational databases developed from relational algebra theory, which was introduced by... Edgar F. Codd.

https://en.m.wikipedia.org/wiki/Relational_algebra

If you meant to refer to some other principles can you name them or give a link or something?

Re: Relational is more than SQL

#25

Post by Timon3 »

danielvaughn wrote: post #20
snthpy wrote: post #12

Disclaimer: I'm a core contributor to PRQL [1] and post about it a lot on HN. Apologies for jumping in on other people's threads, but for people interested in the headline, PRQL might be of interest. At PRQL[1] we believe that SQL is a combination of two things: 1. Relational Algebra, which is eternal because it's just maths, and 2. A language designed in the 70s that looks like COBOL. When people say that SQL will n…

That first PRQL code sample is wonderfully readable.

It is! One suggestion to make it even more convincing: I'd love to see the SQL statement it compiles to.

Re: Relational is more than SQL

#26

Post by _a_a_a_ »

bazoom42 wrote: post #22
_a_a_a_ wrote: post #16

Earlier quoted context omitted.

Out of my depth here (no experience) but "Codds relational database model adds the further constraint that nested tables are not allowed" may be wrong. He allowed nested stuff, it's just that SQL didn't support it. Can anyone elucidate? Please don't shout that I'm wrong because there was something there in his first paper.

No, he explicitly disallows nested relations. This is the definition of first normal form. Hierarchical databases (which predate relational) can be understood as nested relations, and Codds first example of normalization is how to extract the nested relations in such a database into seperate tables and instead express the relationships through foreign keys.

Thanks for a polite disagreement, but I believe you are wrong (not saying you are!). IIRC Codd defined relation valued attributes and also associated operators Group and Ungroup. https://www.oreilly.com/library/view/sql-and-relational/9781...

also https://shark.armchair.mb.ca/~erwin/RA_Intro.htm

"

Relations are, themselves, values too, and relation attributes can therefore be declared to be of another relation type. Such attributes are called 'Relation-valued attributes' (RVA's for short).

In the RA, two operators are available that allow us to manipulate relations in connection with RVA's : GROUP and UNGROUP

"

Like I said, I'm a bit out of my depth here so take the above as evidence rather than proof that such things existed, but I'm pretty sure I saw this, hand-drawn, in one of Codd's original papers.

.

Edit: you are right

"Codd proposed a normal form thathe called first normal form (1NF), and he included a requirement for 1NF in his definitions for 2NF,3NF, and subsequently BCNF. Under 1NF as he defined it, relation-valued attributes were “outlawed”;that is to say, a relvar having such an attribute was not in 1NF."

https://fliphtml5.com/qprz/cxon/basic/201-235

Re: Relational is more than SQL

#27

Post by bhattid »

ghusbands wrote: post #17
iudqnolq wrote: post #11

Earlier quoted context omitted.

that's presumably why the author said "made supporting... easy", not "is"?

Most people believe that databases are ACID by default, so it's worth bringing up when an article even subtly implies otherwise.

Damn, this is news to me. Seems I have some reading to do, thanks for pointing that out.

Re: Relational is more than SQL

#28

Post by frogulis »

snthpy wrote: post #12

Disclaimer: I'm a core contributor to PRQL [1] and post about it a lot on HN. Apologies for jumping in on other people's threads, but for people interested in the headline, PRQL might be of interest. At PRQL[1] we believe that SQL is a combination of two things: 1. Relational Algebra, which is eternal because it's just maths, and 2. A language designed in the 70s that looks like COBOL. When people say that SQL will n…

Pretty cool, your description got my click. I particularly enjoy that a filter is a filter before and after grouping.

One thing, the "showcase" section is not usable for me on mobile. The code box does not fit on the screen horizontally and I can't scroll right to see the remainder of it.

Re: Relational is more than SQL

#29

Post by continuational »

snthpy wrote: post #12

Disclaimer: I'm a core contributor to PRQL [1] and post about it a lot on HN. Apologies for jumping in on other people's threads, but for people interested in the headline, PRQL might be of interest. At PRQL[1] we believe that SQL is a combination of two things: 1. Relational Algebra, which is eternal because it's just maths, and 2. A language designed in the 70s that looks like COBOL. When people say that SQL will n…

In that first example, is the last line superfluous? It doesn't seem to be used.

Re: Relational is more than SQL

#30

Post by ogogmad »

_a_a_a_ wrote: post #26
bazoom42 wrote: post #22

Earlier quoted context omitted.

No, he explicitly disallows nested relations. This is the definition of first normal form. Hierarchical databases (which predate relational) can be understood as nested relations, and Codds first example of normalization is how to extract the nested relations in such a database into seperate tables and instead express the relationships through foreign keys.

Thanks for a polite disagreement, but I believe you are wrong (not saying you are!). IIRC Codd defined relation valued attributes and also associated operators Group and Ungroup. https://www.oreilly.com/library/view/sql-and-relational/9781... also https://shark.armchair.mb.ca/~erwin/RA_Intro.htm " Relations are, themselves, values too, and relation attributes can therefore be declared to be of another relation type.…

No, it doesn't mean he's right. The "normal forms" could merely be suggestions for a database designer, not a technical limitation enforced by the software itself.

No one has provided convincing evidence that Codd intended to exclude nested tables entirely. People seem to be conflating i) good database design, as suggested by Codd ii) the feature-set of a DBMS, also as suggested by Codd.

Post reply on HN