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

31–40 of 177 posts

Re: Relational is more than SQL

#31

Post by jimwhite42 »

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.

Date and Darwen (spritual successors to Codd?), have a relational theory which allows nested relation values and I think it doesn't contradict the motivation behind first normal form, and it isn't hierarchical in the pre-relational database sense. I think they cleaned up Codd's ideas very effectively.

Re: Relational is more than SQL

#32

Post by iamcreasy »

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…

How would you compare prql with dbt?

Re: Relational is more than SQL

#33

Post by _a_a_a_ »

ogogmad wrote: post #30
_a_a_a_ wrote: post #26

Earlier quoted context omitted.

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.

> No one has provided convincing evidence that Codd intended to exclude nested tables entirely

Erm, my last para strongly suggests that he did?

"Under 1NF as [Codd] defined it, relation-valued attributes were “outlawed”;that is to say, a relvar having such an attribute was not in 1NF."

(but see @jimwhite42's comment)

Re: Relational is more than SQL

#34

Post by hiAndrewQuinn »

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…

This is tremendous. I'm curious to know if a CLI `prqlite3` exists which wraps around the `sqlite3` CLI many of us know and love.

Re: Relational is more than SQL

#35

Post by leeuw01 »

Timon3 wrote: post #25
danielvaughn wrote: post #20

Earlier quoted context omitted.

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.

This +1. I've been burned before when using ORMs which translate simple-looking queries to terribly inneficient SQL statements underwater.

Re: Relational is more than SQL

#36

Post by bazoom42 »

_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.…

When in doubt, check the primary source: https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

See section 1.4 about eliminating “non-simple domains” (which means nested tables) through a process of normalization.

Re: Relational is more than SQL

#37

Post by bazoom42 »

ogogmad wrote: post #30
_a_a_a_ wrote: post #26

Earlier quoted context omitted.

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.

> No one has provided convincing evidence that Codd intended to exclude nested tables entirely.

See Codds original paper (linked in a sibling comment) section 1.4.

Note that the relational algebra developed by Codd does not support querying nested tables, which would make them practically useless, even if allowed.

Re: Relational is more than SQL

#38

Post by jug »

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…

At least superficially this looks a lot like C# LINQ to me in terms of structure and database independence (as for EF Core + LINQ). It’s in my top 3 features of that language.

https://www.tutorialsteacher.com/linq/sample-linq-queries

Edit: Shortened to link due to formatting issues

Re: Relational is more than SQL

#39

Post by laerus »

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…

hey, is compile time verification of queries supported for PRQL in Rust?

Re: Relational is more than SQL

#40

Post by roenxi »

m_mueller wrote: post #19
robertlagrant wrote: post #14

On strong schemas and flexibility: 1. You still have a schema in your code. With weak schemas it's now just harder to know if every record in your database conforms to it. 2. An ORM is a great tool for prototyping. R.g. have SQLAlchemy objects in code, run a command to generate a database migration; run the migration, and you have all your data guaranteed to be compatible with your latest code, and you didn't write a…

If you program defensively you can save on certain common Schema updates in e.g. a document based data model (e.g. adding more fields). But strong schemas definitely make sense when you’re dealing with relational data from my experience. Earlier in my career I built a relational model on top of CouchDB (due to its strong replication capabilities, including on mobile devices), but it was definitely painful (and less p…

> If you program defensively you can save on certain common Schema updates in e.g. a document based data model (e.g. adding more fields)

ALTER TABLE whatever ADD COLUMN new_field type DEFAULT NULL;

I've seen a lot of people claim that they don't want to waste time clarifying their schema and I'm sure there are edge cases where that is clever. But, in the majority of cases, they are literally risking data integrity for a saving smaller than the time it takes to write a HN comment.

Making schema implicit doesn't "save" anything. The schema is still there, now just only insiders who are completely familiar with the code know what it is. And they're going to have a few extra bugs because they'll forget too.

Post reply on HN