Question

Sub-view/External Schema 4 Complaints and Responses Any consumer may file a complaint against a food establishment. A complaint may be about a foodborne illness (Illness) contracted at the establishment, the sanitary conditions (Sanitary Conditions) of the establishment, or the conduct of an employee at a facility (Employee Conduct). If the complaint is related to a foodborne illness, it must be one of the types listed in the Common Foodborne Illness table shown in the table at the following link: https:// www douglas ountyhealth.com images/stones food/FOCUS%20On%20FOOd%20Safety pdf. The county only needs to track the Causative Pathogen, Incubation Time, Length of Illness, and Common Symptoms for each type of illness shown. For each complaint, the county needs to record the name, address, phone, and e-mail address of the person filing the complaint, the date of the complaint, the establishment for which the complaint applies, the type of complaint (Illness, Sanitary Conditions, Employee Conduct, Other), and for foodborne ilnesses, the Causative Pathogen 1. Each complaint is filed by one person and is about a single facility. 2. A single person can file multiple complaints about different establishments/facilities. 3. A facility may have many complaints filed against it.

Using SQL to design Sub-view/External Schema 1: Restaurants/Facilities

Answer 1

First, we need to identify the entities for our Schema.

As per the given information, complaint can be of different type, so we need a Complaint Type entity that will hold the types of complaints. Below will be the schema:

ComplaintType (ComplaintTypeID, ComplaintType) where ComplaintTypeID is the primary key.

We can create the SQL as:

CREATE TABLE ComplaintType(

     ComplaintTypeID VARCHAR(10) PRIMARY KEY,

     ComplaintType VARCHAR(50) NOT NULL

);

If it is regarding food borne illness, we need to track the illness type. So IllnessType would be next entity that will hold attributes as per the below schema

IllnessType (IllnessTypeID, CausativePathogen, IncubationTime, IllnessLength, CommonSymptoms)

We can create the SQL as:

CREATE TABLE IllnessType(

     IllnessTypeID VARCHAR(10) PRIMARY KEY,

     CausativePathogen VARCHAR(50),

     IncubationTime NUMBER NOT NULL,

     IllnessLength NUMBER NOT NULL,

     CommonSymptoms VARCHAR(100) NOT NULL

);

Next would be the Illness entity that will be as per the below schems

Illness (IllnessID, Illness, IllnessTypeID) where IllnessTypeID is the foreign key that refers to the above entity.

We can create the SQL as:

CREATE TABLE Illness(

     IllnessID VARCHAR(10) PRIMARY KEY,

     Illness VARCHAR(50) NOT NULL,

     IllnessTypeID VARCHAR(10) NOT NULL,

     FOREIGN KEY fk_IlTyp(IllnessTypeID)

          REFERENCES IllnessType(IllnessTypeID)

);

Now, the person who is filing the complaint would be the next entity called Complainant which will have the below schema.

Complainant (ComplainantID, Name, Address, Phone, email)

We can create the SQL as:

CREATE TABLE Complainant(

     ComplainantID VARCHAR(10) PRIMARY KEY,

     Name VARCHAR(30) NOT NULL,

     Address VARCHAR(100),

     Phone VARCHAR(15) NOT NULL,

     Email VARCHAR(20) NOT NULL

);

The complaint which is filed against the establishment will be the next entity called Facility which will have the below schema

Facility (FacilityID, FacilityName, Address)

We can create the SQL as:

CREATE TABLE Facility(

     FacilityID VARCHAR(10) PRIMARY KEY,

     FacilityName VARCHAR(20) NOT NULL,

     Address VARCHAR(100)

);

Now, the final entity would be the Complaint that will be files by the Complainant against the Facility. Below will be the schema

Complaint (ComplaintID, ComplaintDate, FacilityID, ComplainantID, ComplaintTypeID, IllnessID)

Where FacilityID is related to Facility entity, CompainantID is related to Complainant entity, ComplaintTypeID is related to ComplaintType and IllnessID is related to Illness entity and these all are represented here as foreign keys.

We can create the SQL as:

CREATE TABLE Complaint(

     ComplaintID VARCHAR(20) PRIMARY KEY,

     ComplaintDate DATE NOT NULL,

     FacilityID VARCHAR(10) NOT NULL,

     ComplainantID VARCHAR(10) NOT NULL,

     ComplaintTypeID VARCHAR(10) NOT NULL,

     IllnessID VARCHAR(10),

     FOREIGN KEY Comp_Fac(FacilityID)

          REFERENCES Facility(FacilityID),

     FOREIGN KEY Comp_Comp(ComplainantID)

          REFERENCES Complaint(ComplainantID),

     FOREIGN KEY Comp_CompType(ComplaintTypeID)

          REFERENCES ComplaintType(ComplaintTypeID),

     FOREIGN KEY Comp_Ill(IllnessID)

          REFERENCES Illness(IllnessID)

);

Let me know if you have any query.

Thanks!