How to Create Database Table for Multivalued Attribute

Опубликовано: 05 Октябрь 2024
на канале: Data Science Center

16,339

142

for concept watch the link:
• Transforming ER Diagrams into Relatio...

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;