Question

You are asked to build a database to track surveys that will be sent out to customers and their responses to those surveys. Customers can respond to a survey only once.

List the table names as well as the primary and foreign keys in those tables. No other columns other than the keys need to be listed.

Example expected output style:

[table1name]
column1name (PK)

[table2name]
column1name (PK)
column2name (PK)
column3name (FK)

Answer 1

-- Below is queries for creating db and corresponding tables.

Create database Survey;

create table surveydetails (id int primary key, option1 varchar(20), option2 varchar(20), option3 varchar(20));

--above is survey details table having survey details. currently i have added only 3 options and can be increased based on servey.

Create table customer (SSN varchar(20) Primary key, firstName varchar(20),Lastname varchar(20),PhoneNumber varchar(20),SurveyId INT, foreign key (surveyid) references surveydetails(id));

--Above is customer tabke which store basic information of customer and unique idenfied by SSN which will have only one per customer. And also contain one survey id to refer survey table.

We need to make sure customer to enter SSN so he can only do survey only once. we also can use email address if required as unique identifier filed.