4 ms·

There are other ways, like having a null with more regular semantics and better composeability (like Rust's option type). Or imagine this data: Students: Nam

There are other ways, like having a null with more regular semantics and better composeability (like Rust's option type). Or imagine this data: Students: Name: Bob Name: Alice StudentsClasses Student: Bob, class: cryptography. Student: Bob, class: databases If you can have arrays (or relations) as a field in your result set, then Students left outer join StudentsClasses Could return Student: Bob, classes: [ cryptography, databases] Student: Alice, classes: []

goto11 4y ago
This violates first normal form. So if you want to query this result further you need a more complex language than relational algebra because you need to be able to query nested structures.
- ccouzens 4y ago
  The normal forms are about how data is stored, rather than how it looks after a query. After all, most joins will violate second normal form. But yes, SQL doesn't work well with this solution.
- emmelaich 4y ago
  1nf is surprisingly tricky to define. If you consider it as a single atomic list then it is still in 1nf. It's only not in 1nf with respect to future queries IF you want to subselect from within the list values.
  - goto11 4y ago
    1NF is defined as eliminating nested relations. I assumed "[ cryptography, databases]" in the example represent a nested relation. 1NF is not defined in terms of what you want to do with the the data, it is defined in terms of domains, i.e data types. If a column allows nested tables, it violates 1NF. "Atomic" in the context of 1NF means atomic wrt. the relational algebra. Eg. if we have: Student: Bob, classes: [ cryptography, databases] Student: Alice, classes: [] Is it possible to select from this all students which has cryptography as a class, using the standard relational operators (project, filter, join)?
    - emmelaich 4y ago
      I think we're mostly in agreement! You're right, it's not possible to select from this all students which has cryptography as a class. No more than if we did concat('cryptography', ',', 'databases').

goto11 4y ago

This violates first normal form. So if you want to query this result further you need a more complex language than relational algebra because you need to be able to query nested structures.

ccouzens 4y ago
The normal forms are about how data is stored, rather than how it looks after a query. After all, most joins will violate second normal form. But yes, SQL doesn't work well with this solution.
emmelaich 4y ago
1nf is surprisingly tricky to define. If you consider it as a single atomic list then it is still in 1nf. It's only not in 1nf with respect to future queries IF you want to subselect from within the list values.
- goto11 4y ago
  1NF is defined as eliminating nested relations. I assumed "[ cryptography, databases]" in the example represent a nested relation. 1NF is not defined in terms of what you want to do with the the data, it is defined in terms of domains, i.e data types. If a column allows nested tables, it violates 1NF. "Atomic" in the context of 1NF means atomic wrt. the relational algebra. Eg. if we have: Student: Bob, classes: [ cryptography, databases] Student: Alice, classes: [] Is it possible to select from this all students which has cryptography as a class, using the standard relational operators (project, filter, join)?
  - emmelaich 4y ago
    I think we're mostly in agreement! You're right, it's not possible to select from this all students which has cryptography as a class. No more than if we did concat('cryptography', ',', 'databases').