How to Create Database Table for Multivalued Attribute
Channel: Data Science Center
Date: 2021-12-11

for concept watch the link:
 https://youtu.be/4Trl5CQzu8I

For multivalued  attribute, two new relations are created.
First relation contains all of the attributes of the entity type except the multivalued attribute. 
The second relation contains Primary key of first relation and multivalue attribute.
The primary key of second relation consist of the primary key of the first relation (which also becomes a foreign key in the second relation) and the multivalued attribute.
The name of the second relation should capture the meaning of the multivalued attribute.





CREATE TABLE CUSTOMER
(
Customer_id int primary key, 
Customer_Name varchar(30), 
Customer_DOB Date, 
Stree Varchar(30), 
State varchar(26), 
Postal_Code varchar(20)
);

CREATE TABLE CUSTOMER_PHONE
(
Customer_id int, 
Phone varchar(15), 
Primary key(Customer_id , Phone ), 
foreign key(Customer_id)
REFERENCES CUSTOMER(Customer_id)
);

INSERT INTO CUSTOMER(Customer_id, Customer_Name, Customer_DOB, Stree, State, Postal_Code)
VALUES(1, 'David', '2001-11-11', '123-A', 'ABC', '223SD3');

SELECT * FROM CUSTOMER;

INSERT INTO CUSTOMER_PHONE VALUES(1, '223232323');

INSERT INTO CUSTOMER_PHONE VALUES(1, '324232323');
INSERT INTO CUSTOMER_PHONE VALUES(1, '2323222');
INSERT INTO CUSTOMER_PHONE VALUES(1, '665567777');
INSERT INTO CUSTOMER_PHONE VALUES(1, '8876544333');

SELECT * FROM customer_phone;
SELECT C.Customer_id, Customer_Name, Customer_DOB, Stree, State, Postal_Code, Phone
FROM CUSTOMER C
INNER JOIN customer_phone CP
ON C.Customer_id = CP.Customer_id;