Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

General database question

General database question

General database question

First, the boring part:

From: http://en.wikipedia.org/wiki/Relational_database
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: http://en.wikipedia.org/wiki/Table_(database)
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

RE: General database question

==> 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(y)=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: How can I maximize my chances of getting an answer?
Wise men speak because they have something to say, fools because they have to say something. - Plato

RE: General database question

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

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

RE: General database question

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

Quote (Wikipedia)

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.

RE: General database question

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. sad

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

RE: General database question

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.

RE: General database question

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close