| Author |
Topic |
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
 Posted - 2005-01-07 : 12:54:07
|
| Hi,I'm reading 'An Introduction to Database Systems' by C.J. Date.In the chapter on data types he introduces a row type which might be used like this. CREATE TABLE Users {UserID intRow Address { HouseNumber int, StreetName varchar }}..and then...UPDATE Users SET Address.HouseNumber = 22 WHERE UserID = 12Now, isn't embeding an object within a column blatantly un-relational? |
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-07 : 13:46:35
|
| Simply put, no.The question is really one of atomicity, and atomicity seems to be determined only in context. The address in this case can be considered to be atomic, as an address is composed of a house number and street name; if at all times in the logic of the application in question that address is considered only as that very combination, then we could argue that the address is atomic even though it is made up of two distinct scalar parts.Make sense? |
 |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-07 : 14:03:27
|
| Well I suppose so.So why isn't something like this used more frequently?Have you ever used a row type? |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-07 : 14:05:19
|
| It's not supported in SQL Server 2000.However, SQL Server 2005 supports CLR user-defined types, which will allow you to do this. Other products also support similar functionality -- Oracle supports Java types, Ingres supports C types, and PostgreSQL supports types written in, I believe, any number of languages. |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-07 : 15:01:51
|
| Thanks for your reply.How does one decide whether to use a row type over a relation? |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-07 : 15:07:42
|
| I think the only correct answer is, "use whatever is appropriate to your situation".You need to evaluate on a case-by-case basis and determine whether your data is atomic, how it's going to be used, and -- perhaps -- why you're putting it in a database to begin with. There is no magical formula, and most discussions I've had on this subject have ended up in confusion with no one even agreeing on what constitutes atomicity. And for that, I am quite pleased, because if this were easy then we'd all be out of work :) |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-08 : 15:13:16
|
| How about this for a rule...An entity should be a relation if it needs be referenced in its entirety by other relations otherwise is might as well be a row type. |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-08 : 15:52:53
|
| What does "referenced in its entirety" mean? |
|
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-08 : 17:19:41
|
I'm not actually sure.But if you take the table defined at the top of this thread, if another table wants to reference a user's address but without referencing the user, then the address ought to be in a seperate table where it can be referenced as an independent enitity and not as one mixed in with other user data.So I think this goes back to normalization. If an entity needs to be 'used' by more than one other entity then it should be factored into its own container otherwise it can be defined inline.quote:
You need to evaluate on a case-by-case basis and determine whether your data is atomic
Shouldn't an entity be atomic regardless of whether its a row type or a relation? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-01-08 : 18:41:32
|
| X-Factor,What edition are your reading? I've read 2nd and 7th and the difference is large. Current edition is 8th I think.I think your confusion is caused soley by terminology. You have used the terms "object", "table", "type", "entity", "row type", "column", "relation" in this thread alone. It gets confusing pretty quickly...Here's my simplest explanation:A relation is a set of tuples. A tuple is a set of columns. A column has a name, data type and ONE value.A data type can be anything, including another relation.The choice to create a "complex" data type instead of another relation is as amachanic says, context specific. But a more practical way is to ask how often I have to "unwrap" the data type to get its properties.DavidM"Always pre-heat the oven" |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-08 : 19:06:39
|
| Agreed DavidM, some of the questions to be asked in context are: Will the DBMS have to unwrap the values? Will the values within the complex type be used as keys? But even more importantly, can the DBMS natively support the type in question? Does the DBMS itself "know" that it's managing a complex type? There are a variety of implications here, including data integrity and coupling between application and database.Then there is the atomicity consideration. For instance, the address brought up by X-Factor... Let's say we have a user, and an attribute of each user is an address. If you have a type called 'address' (within the DBMS), which includes street number, street name, and postal code, you can store an address per user in that package, and every time you ask for a user's information the address can be shredded by the client code for display. However, if your requirement is to query users based on postal codes, you've now flipped your attribute relationship, and logically in those queries a postal code 'has' users. In that case, it probably no longer makes sense to store postal code as an attribute of an address, which is an attribute of a user.So in the end, it is the DA's job to understand not only what the data is conceptually, but also how it will be used within the DBMS. |
|
|
|
amachanic
SQL Server MVP
169 Posts |
|
|
X-Factor
Constraint Violating Yak Guru
392 Posts |
Posted - 2005-01-09 : 15:38:06
|
| So in SQL Server 2005, if I were to define the type of a column to be an array list (a dynamic array), would it be acceptable in the light of relational theory for the arraylist to contain a set of foreign keys? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-01-09 : 15:54:15
|
i can see it coming... posts over posts of: "wow now i can store my FK's in CSV in one row!how cool!"  Go with the flow & have fun! Else fight the flow  |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-09 : 16:09:14
|
| X-Factor, good and VERY loaded question :)I think that I can -somewhat- formally argue that, no, it still wouldn't be okay -- although given Date's nested relations, it's kind of a tricky subject.A very clear requirement for 1NF is that each column contain only a single value of a single data type, as DavidM pointed out. A "complex" datatype such as "address" can be argued to be a single value, given that the values that make it up are inherent to its existance. Stated another way, an address would not be an address without its requisite component parts, and likewise each component part would be meaningless were it not part of an address. But, e.g., a comma-delimited list is actually a collection of instances of the same type, and is NOT determined by the presence of any of its component parts, each of which can also, in the context in which a comma-delimited list is used, stand in for the list itself. And therefore, IMO, a comma-delimited list can not comply with the atomicity requirements for a complex type.I realize that's a somewhat dicey argument, and unfortunately Date's nested relations do violate that rule. I don't remember how Date gets around that one in his book. I'll have to pull that out and refresh my memory... |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-01-09 : 16:30:29
|
| X-Factor,Hmm.. a data type which holds multiple FK values... this would eliminate a many-to-many table..As far as relational theory goes, I can't see any problems with it.<SHUDDER>Damn I feel dirty now...</SHUDDER>But practically, what a nightmare. How do you enforce the FK contraint?What possible advantage would it have over the many-to-many table?Theoretically, you could represent the Northwind database with a single table and a single column.. but why would you?DavidM"Always pre-heat the oven" |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-01-09 : 16:37:26
|
| Adam,He gets "around" it by the fact the the data type has one value.. a relation value. In X-Factor's case, we have one value of type ArrayList. The internal components of the type are irrelevant to the model. That is how I understand it anyway...Dr. Codd was big on simplicity, so building a type that mimics relational operators/features is just silly.DavidM"Always pre-heat the oven" |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-09 : 16:41:08
|
| Maybe he gets around it by arguing simplicity, but I still think context has to play a part, and if a type actually represents a collection of same-typed values, that is a big red flag for me. But maybe that's where the mechanical following of rules ends and our jobs as real-world DAs begins... |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2005-01-09 : 16:53:59
|
| It's a funny thing about RDBMS...a solid theory that requires common sense to implement.100% agree on the DA's position/value. Any DA who implmented that requirement via a complex type should be shot.DavidM"Always pre-heat the oven" |
|
|
|
Sitka
Aged Yak Warrior
571 Posts |
Posted - 2005-01-09 : 17:32:23
|
| What are the best texts for education on this matter?a review of the bookFoundation for Object / Relational Databases: The Third Manifestoclaims a key element of it isSupport the relational model, and define what an "object' is and where it fits into the relational model. "Object" = Domain = Data Type.That alone makes it seem like a valueable read but other reviews weren't kind. ISBN: 0201709287; Published: May 12, 2000; Copyright 2000; Dimensions 7-3/8x9-1/4 ; Pages: 576; Edition: 2nd. Seems a little old, that often isn't a strike against the relational side of the arguement but may not give a good emphasisto the "object" world.I wish someone would start an Official XML Rant Thread. |
|
|
|
amachanic
SQL Server MVP
169 Posts |
Posted - 2005-01-09 : 19:01:55
|
| Date's _Introduction to Database Systems_ is probably still the best. _Third Manifesto_'s goal is to support development of FUTURE systems, and not really serve as a foundation text, AFAIK. |
|
|
|
Next Page
|
Object in a column