Many-to-Many (N:M) Relationship: Create a relational database design from the fo
ID: 3708851 • Letter: M
Question
Many-to-Many (N:M) Relationship:Create a relational database design from the following data model:
This includes the CUSTOMER, SEMINAR, and CONTACT entities and relationships in the E-R diagram from assignment Ch4-HSDProject.
Database Design:
? Write the definitions of the tables using the format TABLE (Column1, Column2, ...).
? Underline the primary key in each table, and italicize the foreign keys.
Write the referential integrity check for each foreign key.
This is what i have but What is that special thing that is needed to implement a many-to-many relationship?
SEMINAR(SeminarID,SeminarDate,SeminarTime,Location,SeminarTitle)
CUSTOMER(EmailAddress,LastName,FirstName,Phone,StreetAddress,City,State,Zip)
CONTACT(EmailAddress,ContactDate,ContactNumber,ContactType,SeminarID)
SeminarID in SEMINAR must exist in SeminarID in SEMINAR_CUSTOMER.
Explanation / Answer
Answer)
SEMINAR(SeminarID,SeminarDate,SeminarTime,Location,SeminarTitle)
SEMINAR table- SeminarID is the primary key
CUSTOMER(EmailAddress,LastName,FirstName,Phone,StreetAddress,City,State,Zip)
CUSTOMER table - EmailAddress is the primary key
CONTACT(EmailAddress,ContactDate,ContactNumber,ContactType,SeminarID)
CONTACT table - EmailAddress is the primary key, SeminarID is the foreign key which references SEMINAR(SeminarID)
Apart from the above table format, we have to implement a many-to-many relationship.
Thus here we have to create a new table called SEMINAR_CUSTOMER for many-to-many relationship between SEMINAR and CUSTOMER.
Definition of SEMINAR_CUSTOMER would be:
SEMINAR_CUSTOMER (SeminarID, CustomerEmailAddress)
Here in the above table SEMINAR_CUSTOMER, SeminarID is the foreign key references SEMINAR(SeminarID) and CustomerEmailAddress is the foreign key which references CUSTOMER(EmailAddress). Composite primary key is : (SeminarID, CustomerEmailAddress).
This this is the thing that is needed to implement a many-to-many relationship.