Question

USING MICROSOFT ACCESS

ABC Exterminator serves customers in Queens. The technician travels to a customer’s home to exterminate pests, such as termites, ants and rats. The technician inspects the home, and then decides which chemical to use. The customers are charged only for the chemical used.

ABC has three technicians. Each technician covers 2 ZIP code areas. Technician’s zip code areas do not overlap with the zip code areas of other technicians. The company serves only 6 Zip code areas in Queens. The company sends out bills once a month at end of month.

1. Create_ a database for the Company. Name the database Quiz5--(Your Last_Name)

2. Create five tables as follows:

Customer: CustomerID (text, Primary Key), CustomerName (text), Address (text), ZipCode (text)

Chemical: ChemicalID (text, Primary Key), UnitPrice (Currency)  

Technician: TechnicianID (text, primary key), TechnicianName (text), SSN (text)

  

AreaCoveredByTechnician: Zipcode(text, Primary Key), TechnicianID (text). ( Note this table shows which area is covered by which technician)

  

Orders: CustomerID, ChemicalID, Quantity, OrderDate (date),OrderID (Text, Primary Key)

  

Answer 1

You have not mentioned the database being used, i.e., Oracle DB, MySQL, PostGRE SQL etc. The following should work on all databases. In some of the databases replace varchar with varchar2/Text and DECIMAL(8,2) with Float/Number.

CREATE DATABASE <Your_Last_Name>;

-->Replace <Your_Last_Name> with your Last Name when executing the code.

CREATE TABLE Customer (
    CustomerID varchar(255) NOT NULL PRIMARY KEY,
    CustomerName varchar(255),
    Address varchar(255),
    ZipCode varchar(255)
);

CREATE TABLE Chemical (
    ChemicalID varchar(255) NOT NULL PRIMARY KEY,
    UnitPrice DECIMAL(8,2)
);

CREATE TABLE Technician (
    TechnicianID varchar(255) NOT NULL PRIMARY KEY,
    TechnicianName varchar(255),
    SSN varchar(255)
);

CREATE TABLE AreaCoveredByTechnician (
    Zipcode varchar(255) NOT NULL PRIMARY KEY,
    TechnicianID varchar(255) FOREIGN KEY REFERENCES Technician(TechnicianID)
);

CREATE TABLE Orders (
    OrderID varchar(255) NOT NULL PRIMARY KEY,
    CustomerID varchar(255) FOREIGN KEY REFERENCES Customer(CustomerID),
    ChemicalID varchar(255) FOREIGN KEY REFERENCES Chemical(ChemicalID),
    Quantity DECIMAL(8,2),
    OrderDate DATE
);

Now, the data can be inserted into the tables.

INSERT INTO Chemical VALUES ("Insecticide1", 276.31);
INSERT INTO Chemical VALUES ("Insecticide2", 397.70);
INSERT INTO Chemical VALUES ("Insecticide3", 411.26);
INSERT INTO Chemical VALUES ("Insecticide4", 185.54);
INSERT INTO Chemical VALUES ("Insecticide5", 245.03);

INSERT INTO Technician VALUES ("Tech001", "John King", "234234243242");
INSERT INTO Technician VALUES ("Tech002", "Larry Potter", "412094333256");
INSERT INTO Technician VALUES ("Tech003", "Don Weaseley", "789234243870");

INSERT INTO AreaCoveredByTechnician ("90001", "Tech001");
INSERT INTO AreaCoveredByTechnician ("90002", "Tech001");
INSERT INTO AreaCoveredByTechnician ("90003", "Tech002");
INSERT INTO AreaCoveredByTechnician ("90004", "Tech002");
INSERT INTO AreaCoveredByTechnician ("90005", "Tech003");
INSERT INTO AreaCoveredByTechnician ("90006", "Tech003");

INSERT INTO Customer VALUES ("Cust001", "Mary Selwyn1", "Random Street, Random House, Random Area1", "90001");
INSERT INTO Customer VALUES ("Cust002", "Mary Selwyn2", "Random Street, Random House, Random Area2", "90001");
INSERT INTO Customer VALUES ("Cust003", "Mary Selwyn3", "Random Street, Random House, Random Area3", "90002");
INSERT INTO Customer VALUES ("Cust004", "Mary Selwyn4", "Random Street, Random House, Random Area4", "90002");
INSERT INTO Customer VALUES ("Cust005", "Mary Selwyn5", "Random Street, Random House, Random Area5", "90003");
INSERT INTO Customer VALUES ("Cust006", "Mary Selwyn6", "Random Street, Random House, Random Area6", "90003");
INSERT INTO Customer VALUES ("Cust007", "Mary Selwyn7", "Random Street, Random House, Random Area7", "90001");
You can add more customers in the same way.

INSERT INTO Orders VALUES ("Order01", "Cust001", "Insecticide1", 2.1, TO_DATE('10/10/2017', 'DD/MM/YYYY');
INSERT INTO Orders VALUES ("Order01", "Cust001", "Insecticide2", 1.3, TO_DATE('10/10/2017', 'DD/MM/YYYY');
INSERT INTO Orders VALUES ("Order01", "Cust003", "Insecticide4", 10.2, TO_DATE('10/02/2018', 'DD/MM/YYYY');
INSERT INTO Orders VALUES ("Order01", "Cust005", "Insecticide1", 5.4, TO_DATE('12/10/2017', 'DD/MM/YYYY');
INSERT INTO Orders VALUES ("Order01", "Cust004", "Insecticide2", 11.2, TO_DATE('06/01/2018', 'DD/MM/YYYY');
INSERT INTO Orders VALUES ("Order01", "Cust002", "Insecticide5", 13.0, TO_DATE('28/12/2017', 'DD/MM/YYYY');
INSERT INTO Orders VALUES ("Order01", "Cust007", "Insecticide3", 4.7, TO_DATE('20/02/2018', 'DD/MM/YYYY');
You can add more orders in the same way.