How to define multi-valued and composite attribute

Hello All,
I want to implement an address field in a table. But,
this address field is a multi-valued attribute. Also, I want to make it
as a composite field(so that all the info viz, city,st,zip,tel,email
etc can be tied together)
Can anyone tell me how to implement this using T-SQL?I sounds as though you want to store the entire address in a single
table column. You COULD just use a long string, say varchar(255) for
example, but do that and you will never be able to query any of the
individual parts without driving yourself mad in the process. Do you
really want to give up being able to ask for just the addresses in
California? Or just the phone numbers in a specific area code?
The proper way to do this in a database is with multiple columns. One
(two is better) for street address, one for city, one for state, one
for zip, one for phone, one for email. If you need to string them
together that is easy to do when you retrieve them.
Roy Harvey
Beacon Falls, CT
On 3 Mar 2006 10:45:35 -0800, "juventus" <saurabh.kotkar@.gmail.com>
wrote:

>Hello All,
> I want to implement an address field in a table. But,
>this address field is a multi-valued attribute. Also, I want to make it
>as a composite field(so that all the info viz, city,st,zip,tel,email
>etc can be tied together)
>Can anyone tell me how to implement this using T-SQL?

How to define composite key?

Hello, everyone:
I need to define composite PK and FK for a ERD. Could someone offer the methods that work with,
1. T-SQL
2. ERD
Thanks a lot.
ZYTThe question is more complex than it seems, so the answer will be a bit "long winded".

1) You define PK and FK using a contraint within Transact SQL. The constraint types are PRIMARY KEY and FOREIGN KEY. For example:
CREATE TABLE composite (
compositeId INT NOT NULL
CONSTRAINT XPKcomposite
PRIMARY KEY (compositeId)
, name VARCHAR(50) NOT NULL
)

CREATE TABLE component (
componentId INT NOT NULL
CONSTRAINT XPKcomponent
PRIMARY KEY (componentId)
, name VARCHAR(50) NOT NULL
)

CREATE TABLE membership (
compositeId INT NOT NULL
CONSTRAINT XFK01membership
FOREIGN KEY (compositeId)
REFERENCES composite (compositeId)
, componentId INT NOT NULL
CONSTRAINT XFK02membership
FOREIGN KEY (componentId)
REFERENCES component (componentId)
CONSTRAINT XPKmembership
PRIMARY KEY (compositeId, componentId)
)This allows you to have many composites (packages), made up of many components (parts), and allows each component to appear in as many packages as needed (because the membership relationship is separate from both the component and the composite).

The ERD diagram questions are a bit more complex. Using IDE1FX, any attribute "above the line" is part of the primary key. You can optionally tag the foreign key attributes with an (FK) designator. Using "crows foot" notation, any attribute "above the line" is also part of the primary key, but there is no standard way to denote foreign keys. Using the various GUI tools for UML, the rules vary.

-PatP|||USE Northwind
GO

CREATE TABLE myTable00(Col4 int NOT NULL PRIMARY KEY)

CREATE TABLE myTable99 (
Col1 int IDENTITY(1,1)
, Col2 char(1)
, Col3 datetime DEFAULT GetDate()
, Col4 int
, PRIMARY KEY (Col1, Col2)
, FOREIGN KEY (Col4) REFERENCES myTable00(Col4)
)
GO

DROP TABLE myTable99
DROP TABLE myTable00
GO|||Hello, Pat and Brett:

Thanks a lot for the posts. Let's share this code I got it from email.

create table addresses (
houseNum INT,
telNo INT,
constraint pk_address primary key (houseNum, telNo)
)|||I'm not clear on why you are sharing that. What would you like us to do with it?

-PatP