> The reality is that it is based on a very distorted view of relational algebra as in it is based on tables, which are not relations but bags of records. It allows nulls to represents all sorts of information and the nulls are not treated in any uniform way.
Very simply, relations are sets and sets do not have duplicate elements. SQL tables are effectively bags, where duplicates are allowed.
The only reason you see unique elements in tables is that in implementations you can define primary keys, which forces uniqueness. If there is no primary key defined then you can add as many copies as you want of any record and the system doesn't care. The default for a relation is that the primary key is every field within the entire tuple, you do not have to define a primary key as the RDBMS does that automatically for you. No duplicates can occur.
The other aspect is that nulls do not occur. The interesting thing is this forces you to design your relations carefully so that you do not have nulls occurring. You will find that there are various people who hold contra views about this and you have to look at the subject and make up your own mind. Personally, I have found that proper design and removal of nulls brings a high efficiency to the resultant database.
One lesson learnt over decades of database design and system development using SQL and SQL DBMS's, is that you have to be extremely careful in your design. I have had to redesign poorly thought out databases that had been built by those who had not put in the time and effort to understand what relational algebra meant. I spent years learning the subject and still didn't delve into the extensive depths of the subject.
Relation database theory stemmed from Codd's original papers, which you should be able to download. But this was only the start of the theory related to this subject. Various developments occurred over the next few decades. You also had various people argue against relational algebra as a useful device for creating databases.
SQL databases and DBMS were supposed to be designed on Codd's papers but essentially didn't succeed, thus leading to the mess we see today.
There have also been various personality clashes in the field, which have lead to various controversies about database technology. I simply avoided that side of things wherever I could as they were not relevant to solving the problem space problems that I was tasked to solve. There are various well-known people who proselytise their particular views about database technologies and dismiss other opposing technologies. There have been various proposals to solve the problems being faced and the reality is that for any problem you have, it is up to you to choose what works effectively for you.
If I am going to use a database as part of the solution, I still design based on relational theory as this to me is the better option. Your situation will be different and you can choose whatever is appropriate for you. However, it is beneficial to delve into relational theory as it will give you alternative ideas to play with. There is plenty of resources out there that will show you both good and bad ideas in designing any database.
My choice of DBMS is PostgreSQL and it suite of tools. I would personally stay away from Microsoft and Oracle as I have had various issues (not problems but serious issues) with each of these besides being payware. There are others like SQLite, etc which appear to be useful for small systems on the basis of feedback of other people I know. I don't have any experience in the large with these and so cannot comment on the effectiveness of these systems.
Mind you, I have been out of the thick of it for a decade or so as I am effectively retired, even though I still actively do projects I am interested in when I feel like doing such work.
> The reality is that it is based on a very distorted view of relational algebra as in it is based on tables, which are not relations but bags of records. It allows nulls to represents all sorts of information and the nulls are not treated in any uniform way.
Very simply, relations are sets and sets do not have duplicate elements. SQL tables are effectively bags, where duplicates are allowed.
The only reason you see unique elements in tables is that in implementations you can define primary keys, which forces uniqueness. If there is no primary key defined then you can add as many copies as you want of any record and the system doesn't care. The default for a relation is that the primary key is every field within the entire tuple, you do not have to define a primary key as the RDBMS does that automatically for you. No duplicates can occur.
The other aspect is that nulls do not occur. The interesting thing is this forces you to design your relations carefully so that you do not have nulls occurring. You will find that there are various people who hold contra views about this and you have to look at the subject and make up your own mind. Personally, I have found that proper design and removal of nulls brings a high efficiency to the resultant database.
One lesson learnt over decades of database design and system development using SQL and SQL DBMS's, is that you have to be extremely careful in your design. I have had to redesign poorly thought out databases that had been built by those who had not put in the time and effort to understand what relational algebra meant. I spent years learning the subject and still didn't delve into the extensive depths of the subject.
Relation database theory stemmed from Codd's original papers, which you should be able to download. But this was only the start of the theory related to this subject. Various developments occurred over the next few decades. You also had various people argue against relational algebra as a useful device for creating databases.
SQL databases and DBMS were supposed to be designed on Codd's papers but essentially didn't succeed, thus leading to the mess we see today.
There have also been various personality clashes in the field, which have lead to various controversies about database technology. I simply avoided that side of things wherever I could as they were not relevant to solving the problem space problems that I was tasked to solve. There are various well-known people who proselytise their particular views about database technologies and dismiss other opposing technologies. There have been various proposals to solve the problems being faced and the reality is that for any problem you have, it is up to you to choose what works effectively for you.
If I am going to use a database as part of the solution, I still design based on relational theory as this to me is the better option. Your situation will be different and you can choose whatever is appropriate for you. However, it is beneficial to delve into relational theory as it will give you alternative ideas to play with. There is plenty of resources out there that will show you both good and bad ideas in designing any database.
My choice of DBMS is PostgreSQL and it suite of tools. I would personally stay away from Microsoft and Oracle as I have had various issues (not problems but serious issues) with each of these besides being payware. There are others like SQLite, etc which appear to be useful for small systems on the basis of feedback of other people I know. I don't have any experience in the large with these and so cannot comment on the effectiveness of these systems.
Mind you, I have been out of the thick of it for a decade or so as I am effectively retired, even though I still actively do projects I am interested in when I feel like doing such work.