Description

company has the following initial specifications.
1. Every customer is related to only one salesperson
2. A salesperson can have many customers
3. The company needs to record the meeting of the customer and the sales person. Remarks for each meeting also needs to be recorded.
Provide the answer or do what is required on each number
1. Consider the following table:
CUSTOMER(CustomerID, LastName, FirstName, Address, City, State, ZIP, EmailAdddress, Salesperson_number, Sfirstname, Slastname, Hiredate, WageRate, ComRate, SPhone, SEmail address)
a. ______________________________________________________
2. The table above is not normalized. Normalized the data up to the third normal form.
3. Add the “meeting” table to satisfy the third specification.
4. Enumerate the normalized tables. What are the primary keys and foreign keys (If any) of each table?

a. CUSTOMER(CustomerID, LastName, FirstName, Address, City, State, ZIP, EmailAddress)

b. SALES_PERSON(Salesperson_number, Slastname, Sfirstname, Hiredate, WageRate, ComRate, SPhone, SEmailAddress)

c. MEETING(MeetingID, CustomerID, Salesperson_number, Remarks)

5. Open your MS Access, use a new database and create the 3 tables using the CREATE SQL statements. Provide the appropriate data types. Include the primary key constraints and foreign key constraints. You should also be able to use UNIQUE, NOT NULL and DEFAULT constraints. Write your queries below.

CREATE TABLE CUSTOMER(
CustomerID int NOT NULL PRIMARY KEY,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
Address varchar(255) NULL,
Phone int NULL,
City varchar(50) NULL,
State varchar(50) NULL,
ZIP varchar(12) NULL,
EmailAddress varchar(50) NOT NULL UNIQUE
);
CREATE TABLE SALES_PERSON(
Salesperson_number int NOT NULL PRIMARY KEY,
Slastname varchar(50) NOT NULL,
Sfirstname varchar(50) NOT NULL,
WageRate int NULL,
ComRate int NULL,
SPhone int NULL,
SEmailaddress varchar(50) NOT NULL UNIQUE
);
CREATE TABLE MEETING (
MeetingID int NOT NULL PRIMARY KEY,
CustomerID int,
Salesperson_number int,
Remarks varchar(255) NULL,
FOREIGN KEY (CustomerID) REFERENCES
CUSTOMER(CustomerID),
FOREIGN KEY (Salesperson_number) REFERENCES
SALES_PERSON(Salesperson_number) );

6. Using the Alter SQL statement, add another field in the contact table name it “Type”. Write the query below.

a. ALTER TABLE CUSTOMER ADD TYPE int

7. Insert 1 record using the Insert query for each table. You can enter any values you want. Write the queries below.

INSERT INTO CUSTOMER(`CustomerID`, `LastName`, `FirstName`,
`Address`, `Phone`, `City`, `State`, ZIP, `EmailAddress`,
`TYPE`) VALUES(1, ‘Machado’, ‘Leandro’, ‘.’, 0, ‘.’, ‘.’, ‘.’,
‘leandromachado@mail.com’, 0);

INSERT INTO SALES_PERSON
(`Salesperson_number`, `Slastname`, `Sfirstname`, `HireDate`,
`WageRate`, `ComRate`, `SPhone`, `SEmailaddress`)
‘guilherme.machado@mail.com’);

INSERT INTO MEETING
(`MeetingID`, `CustomerID`, `Salesperson_number`, `Remarks`) VALUES(1, 1, 1, ‘My son is awesome!!!’);

8. Update 1 record using the update query Write the query below.

UPDATE MEETING
SET `MeetingID`=1, `CustomerID`=1, `Salesperson_number`=1,
`Remarks`=’He is more than awesome!!!’;

9. Delete 1 record using the update query. Write the query below.

DELETE FROM MEETING WHERE `MeetingID`=1;

10. Establish the relationships among the tables, apply referential integrity.
11. Create a SQL query that will select all data in the Customer table. Write the SQL statement below:

SELECT * FROM CUSTOMER;

12. Create a SQL query that will select Customer ID, First Name and Last name only. Write the SQL statement below:

SELECT `CustomerID`, `LastName`, `FirstName` FROM CUSTOMER;

13. Create a SQL query that will select data from the customer table based on a condition. You can choose your own condition. Write the SQL statement below.

SELECT `CustomerID`, `LastName`, `FirstName`
FROM CUSTOMER WHERE `CustomerID` = 2;

14. Create data entry forms for all tables and enter at least one record through that method.
15. Design and create one report.
16. Submit a zipped file that includes the MS Access database and a digital copy of this paper with your answers.
6