Sql: Multi Valued Attributes

how to insert values to multivalued attribute in sql with a primary key attribute in the table?

create table Additional_room_info(
    roomInfoId BIGINT AUTO_INCREMENT PRIMARY KEY,
    roomId varchar(5),
    features varchar(20),
    constraint fk_room_add_ftrs foreign key(roomID) references Room(roomID)
);

create table for multi valued attribute SQL

Looks OK; although, I'd also create foreign key constraints from emp_ser to employee and service:

SQL> create table Emp_ser (
  2    Emp_id    number,
  3    Ser_id    number,
  4    primary key (Emp_id, Ser_id),
  5    constraint fk_es_emp foreign key (emp_id) references employee (emp_id),
  6    constraint fk_es_ser foreign key (ser_id) references service (ser_id)
  7  );

Table created.

SQL>

SQL Database multiple values for same attribute - Best practices?

I think this is rather n:m-related. You'd need one table Person holding ID, name and other person's details. Another table Accessory with ID, name and more accessory's details. And a third table PersonAccessory to store pairs of PersonID and AccessoryID (this is called mapping table)

Working example (SQL-Server syntax)

CREATE TABLE Person(ID INT IDENTITY PRIMARY KEY,Name VARCHAR(100));
INSERT INTO Person VALUES('John'),('Jim');

CREATE TABLE Accessory(ID INT IDENTITY PRIMARY KEY,Name VARCHAR(100));
INSERT INTO Accessory VALUES('Scarf'),('Mask');

CREATE TABLE PersonAccessory(PersonID INT NOT NULL FOREIGN KEY REFERENCES Person(ID)
                            ,AccessoryID INT NOT NULL FOREIGN KEY REFERENCES Accessory(ID));
INSERT INTO PersonAccessory VALUES(1,1),(2,1),(2,2);

SELECT p.Name
      ,a.Name 
FROM PersonAccessory AS pa
INNER JOIN Person AS p ON pa.PersonID=p.ID
INNER JOIN Accessory AS a ON pa.AccessoryID=a.ID;
GO

--DROP TABLE PersonAccessory;
--DROP TABLE Accessory;
--DROP TABLE Person

The result

John    Scarf
Jim     Scarf
Jim     Mask

How to create a multivalued attribute on SQL

There are two ways:

1. have the four attributes in the table directly:

   • fornecedor: id, nome, rua, n_porta, cod_postal, localidade, email, telefone.

2. have an address table:

   • endereço: id, rua, n_porta, cod_postal, localidade.
   • fornecedor: id, nome, id_endereço, email, telefone.

You would decide for the latter, if you have multiple fornecedores using the same address. Otherwise simply use the first solution and have the address fields in the fornecedor table.

sql query for multi valued attributes

Assuming you don't have duplicate attributes per GUID you can achieve the desired result without a JOIN:

SELECT "GUID" FROM T
WHERE ( "ATTR_SUBTYPE" = 'location' AND "ATTR_VAL" = 'US' )
OR    ( "ATTR_SUBTYPE" = 'owner' AND "ATTR_VAL" = 'himanshu' )
GROUP BY "GUID"
HAVING COUNT(*) = 2 -- <-- keep only GUID have *both* attributes

See http://sqlfiddle.com/#!4/80900/2

Whats the best way to implement a database with multivalued attributes?

That is a typical n to m relation. It works like this

persons table
------------
id
name
address

interests table
---------------
id
name

person_interests table
----------------------
person_id
interest_id

person_interests contains a record for each interest of a person. To get the interests of a person do:

select i.name
from interests i
join person_interests pi on pi.interest_id = i.id
join persons p on pi.person_id = p.id
where p.name = 'peter'

You could create also tables for hobbies. To get the hobbies do the same in a separate query. To get both in one query you can do something like this

select p.id, p.name, 
       i.name as interest, 
       h.name as hobby
from persons p    
left join person_interests pi on pi.person_id = p.id
left join interests i on pi.interest_id = i.id
left join person_hobbies ph on ph.person_id = p.id
left join hobbies h on ph.hobby_id = h.id
where p.name = 'peter'

---

Related Topics