7

My question is in relation to database theory.

If I am given a set of tables, is there a way to determine the base table just by looking at them?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user559142
  • 12,279
  • 49
  • 116
  • 179
  • lol - is that a serious answer? I mean if there are 5 tables in a database, how can you tell which one is the base table? – user559142 Apr 21 '11 at 18:23
  • 1
    No it's a serious answer thkala is asking for clarification as the the meaning of your question and i agree as i too do not have a clue what you are talking about. Also are you talking about relational databases or non-relational databases? – PurplePilot Apr 21 '11 at 18:26
  • Sorry i did not know that base table wasn't a universally used term within databases. The book I have, which is the so-called bible of database design, implementation and management addresses 'base tables' several times. – user559142 Apr 21 '11 at 19:39
  • And what might the title be of that so-called bible of database design ? – Erwin Smout Apr 21 '11 at 21:52
  • Database Systems - A practical approach to Design, Implementation and Management - Thomas Connolly and Carolyn Begg – user559142 Apr 22 '11 at 09:04

5 Answers5

9

It is a tenet of the relational model that there is no unnecessary distinction between a Base Table (Base Relation) and a Derived one (aka virtual relation or "view"). Both types have attributes, keys and all the other features you would expect of relations and both can be used in just the same ways. As a practical matter of implementation it is necessary that the DBMS software provide some means to differentiate them but logically speaking they are the same.

The principle that base and derived relations are similar in this way is sometimes called the Principle of Interchangeability.

Actually, I'm surprised there is so much puzzlement here about your question and surprised that your question is downvoted. Base table / relation is a very common term in relational database theory and in the SQL standard. Your question seems perfectly concise and in order to me.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • Are you certain that the OP is using the term "base table" in the same sense that you are interpreting it ? In particular, are you certain that the OP is not asking for a way to distinguish a "main" database base table from among what could be called "satellite" database base tables (e.g. as a table holding customer addresses would be a "satellite" table to the "main" customer table). – Erwin Smout Apr 21 '11 at 21:55
  • This is exactly the answer I wanted. Thanks. – user559142 Apr 22 '11 at 09:24
  • 1
    I was surprised by the apparent consensus on SO that it's OK to break 1NF in a SQL `VIEW` -- [Should a SQL VIEW always be in 1NF?](http://stackoverflow.com/q/1049062/15354) – onedaywhen Apr 27 '11 at 09:57
1

Disclaimer: I never heard of a "base table" before now

After some googling, the only instances where anyone is talking about "Base Tables", seems to be when describing the physical tables a view depends on.

Based on that, I would say all tables are base tables, with the possible exception of temporary tables.

mikerobi
  • 20,527
  • 5
  • 46
  • 42
1

According to IBM here a base table is what most would understand as "a database table", in other words the actual table itself.

But I have to say your question is a bit "quantum theory"-like - if taken literally "by just looking at them"... sorry I had to laugh because I found myself getting a headache over that one.

If I assume you meant "just by querying them" then you would have to already know the table name to write your query. Again all a bit chicken and egg.

Maybe you want to know if there is a table that contains a list of all database tables? That rather depends on the database system you are working with, and sometimes what applications. Take SAP for example. It might use an Oracle database system, and it creates a database table that contains a list of all SAP generated tables. You could think of that as the base table in the sense that it goes no further than itself.

I just thought of something else a bit cosmic: if you are implying that there must have been at least one database table at the beginning of the installation of database system, and that's what you refer to as the "base" table that you're looking for, I know of no way to determine that as a general theoretical proposition. There are going to be better people out there than me to answer that one...

...or maybe not.

T9b
  • 3,312
  • 5
  • 31
  • 50
0

I am assuming that you mean tell a regular table from a view.

It depends on what you mean by, "looking at them".

If you mean just looking at the data then no.

If you mean looking at a script that creates the table then yes. Also, if you mean by browsing through something like SQL Server Management Studio then again yes, because they break tables and views into different folders.

Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
0

If by base table you are talking about inheriting from a table, then yes you can by uniting common table fields in the base table.

For example: Base table: User(name) Tables using inheritance of the table User: Administrator, Member, etc.

But don't get confused by this inheritance, it only copies the fields, nothing else, it is not Oriented-Object stuffs.

Julio Guerra
  • 5,523
  • 9
  • 51
  • 75