General database question 2

[Andrzejek]

First, the boring part:

From: [URL unfurl="true"]http://en.wikipedia.org/wiki/Relational_database[/url]
A relational database is a database that has a collection of tables of data items, all of which is formally described and organized according to the relational model.

In the relational model, each table schema must identify a column or group of columns, called the primary key, to uniquely identify each row. Rows in one table can relate to rows in another table by establishing a foreign key, a column or group of columns in one table that points to the primary key of another table. The relational model offers various levels of refinement of table organization and reorganization called database normalization.

From: [URL unfurl="true"]http://en.wikipedia.org/wiki/Table_(database)[/url]
In relational databases and flat file databases, a table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows, the cell being the unit where a row and column intersect. A table has a specified number of columns, but can have any number of rows. Each row is identified by the values appearing in a particular column subset which has been identified as a unique key index.

Table is another term for relations; although there is the difference in that a table is usually a multiset (bag) of rows whereas a relation is a set and does not allow duplicates. Besides the actual data rows, tables generally have associated with them some metadata, such as constraints on the table or on the values within particular columns.

Now, the question:
If all (so called) ‘tables’ in the database ignore and break pretty much all the rules of being the “relational databases’ tables” (no PK, no FK, no constrains, no normalization, etc.), can they still be called ‘tables’?
And going even further, if those are NOT tables, do we still have a “relational database” or even just a database?

In other words, if we have a data in unrelated (for the lack of a better word) spreadsheets, where you can add or update any row with anything, even insert whole empty rows (all fields can be NULLs) – is it still called relational database?


Have fun.

---- Andy

 

[CajunCenturion]

==> can they still be called ‘tables’?
Yes, they are tables. The relational model requires that tables meet a set of normalization criteria, but it's not those criteria that make them tables, it's those criteria that make them relational.

==> if those are NOT tables, do we still have a “relational database” or even just a database?
As stated above, they are still tables; however, such a collection of tables does not form a relational database. They still form a database, but it's not a relational database. Your spreadsheet is a database, but it's not a relational database.


==> Table is another term for relations
In this context, I think you have that backwards. The relational database model is mathematically based on the theory of relations. (Remember the difference between a function [f(x) = y] and a relation [f=x]?) A mathematical relation can be represented by a table and in the relational database model, all the tables are representations of mathematical relations.

--------------
Good Luck
To get the most from your Tek-Tips experience, please read
FAQ181-2886
Wise men speak because they have something to say, fools because they have to say something. - Plato

 

[djj55]

By-the-way, do not believe everything you read on the internet. CajunCenturion can be believed.


djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[Olaf Doschke]

>Table is another term for relations
This comes from the context:

Table is another term for relations; although there is the difference in that a table is usually a multiset (bag) of rows whereas a relation is a set and does not allow duplicates.

You can see, that here the term relation is not used in the database terminology of being meta data about how two tables are related to each other.

Another quote from Relational model:

The fundamental assumption of the relational model is that all data is represented as mathematical n-ary relations, an n-ary relation being a subset of the Cartesian product of n domains.

Here you get more mathematical terms involved and overlapping with database terminology. You have to keep this separated from each other. For example domain here is the mathematical term for type.

The main point distinguishing a database table from a tabular schema of rows and column with anything in it is, that it's set of n-tuples has n predefined domains, types of values. And while you may represent each of the well known database column types with a char type, that is restricting a database table.

Also, as a side note: Even after strict normalization a table doesn't necessarily have a FK, it can be an leaf node in terms of relations defined by PK/FK and such a leaf table doesn't need FKs, it's head data only other tables relate to. And this time I am not talking of mathematical relations, of course. Normalisation also does seldom characterise each single table of a database, even if for example repeated values in a single table are a sign of it's bad normalisation. If you monitor your weight in a table id, datetime, weight you don't normalize the weight column, like you also never normalize dates or datetimes, even though they are actually always candidates for normalisation in regard of the theory.

But now for your final question Cajun didn't address:

Sheets are no database tables simply because they break the concept of a column being strictly typed. Even lacking a primary key is not a main concern here, you don't have n-tuples (d1,...dN) with each dn being of a certain data domain (type). You can use a sheet as a table, so if you restrict yourself, Excel could be used as relational database, but so could paper.

Bye, Olaf.

 

[Andrzejek]

Thank you CC, that makes sense.
So it looks like I have to deal with the database with tables, they are just not ‘relational’ tables which makes the database not ‘relational’, either. Too bad, with all the money spent on latest and greatest versions of Oracle, and all what I have is a step up from paper. :-(

So, apart from nightmare to maintain it, loosing records due to lack of data integrity (‘orphan’ records), entering the same data into multiple tables, etc. What else do I have to anticipate? Slower and slower performance (I already have some Selects that take up to 12 minutes)? Crash and burn at any time?


Have fun.

---- Andy

 

[Olaf Doschke]

You're making a vague description, combining this with your original post "if we have a data in unrelated (for the lack of a better word) spreadsheets", are you saying you a developer left you with an Oracle database in such a bad design, no PK, no FK, most NULLABLE fields, no referential integrity, no rules or constraints?

I am not an Oracle expert, but you could build up on the situation, if the tables are Oracle tables, at least the database server allows improvements.

Or are you just addressing a part of the database, which is perhaps generated for reports? Or are you really talking about Excel spreadsheets?

In my 15 experience about databases I also have seen some bad designs, but some tables turned out to be temp data. Surely you can handle that better, but is the whole database this way, really? Did you analyize all of it and all related code?

Bye, Olaf.

 

[Andrzejek]

Yes Olaf, I do have to work with the tables set up like that, my (Oracle) DBA calls them 'spreadsheets' because that's what they are. But those ARE tables, just not ‘relational’ tables. And they are not for reports, and they are not temp tables. I am not ‘left with an Oracle database in such a bad design’, I work with it every day as a programmer. I know, pretty sad. I am just the programmer and cannot do any DB design, the 'person in charge' does not allow anybody to do any tables' set up. I did that to a several tables with PK, FK, constrains, defaults, the whole think was set up right. I was told not to do it any more. It drives me up the wall. And the development continues with whole bunch of new tables set up exactly like the ones we already have.

I am just trying to find out what am I to expect, what’s coming up.



Have fun.

---- Andy

 

[ChrisHunt]

Sounds like a classic case for The Daily WTF. What you should expect is disaster. What you should do is start looking for a better job, so you can get out of there before it happens.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd