In Microsoft Access create and populate the following three

In Microsoft Access, create and populate the following three database tables, including foreign keys, using SQL. PLEASE USE SQL to populate the tables. Save all queries in the database. Assume the ID for each table is the primary key. Assume all fields are required. Assume all fields that contain only numbers are numeric type. Save the database as Personnel.accdb. Once the tables have been created, encrypt the database with a password. Be sure the password is strong. It should be at least 16 characters in length, and contain at least one of each of the following: upper-case letters, lower-case letters, numbers, and special characters. Submit the Personnel.accdb file. Submit the password in the comments section of the assignment submission area.

Employees

Managers

Departments

Employee Employee LastName EmployeeFirstName EmployeeDeptID Employee MgrID 1 101 Ward 202 Debbie 102 203 Ables 4 103 201 Simmons Brenda 5 104 202 Turner Lou 2 105 Phil 205 Conwell

Solution

/************ SQL to Create Tables ************/
CREATE TABLE employees
(
employeeID integer NOT NULL,
employeeLastName varchar(100) NOT NULL,
employeeFirstName varchar(100) NOT NULL,
employeeDeptID integer NOT NULL,
employeeManagerID integer NOT NULL,

PRIMARY KEY (employeeID),
FOREIGN KEY (employeeDeptID) REFERENCES departments(departmentID),
FOREIGN KEY (employeeManagerID) REFERENCES managers(managerID)
)

CREATE TABLE managers
(
managerID integer NOT NULL,
managerLastName varchar(100) NOT NULL,
managerDeptID integer NOT NULL,

PRIMARY KEY (managerID),
FOREIGN KEY (managerDeptID) REFERENCES departments(departmentID)
)

CREATE TABLE departments
(
departmentID integer NOT NULL,
departmentName varchar(50) NOT NULL,
departmentLocation varchar(255) NOT NULL,

PRIMARY KEY (departmentID)
)

/************ Insert Values based on tables ************/
Employees:
-----------------
INSERT INTO employees(employeeID, employeeLastName, employeeFirstName, employeeDeptID, employeeManagerID)
VALUES (101,\'Ward\',\'Debbie\',202,1);
INSERT INTO employees(employeeID, employeeLastName, employeeFirstName, employeeDeptID, employeeManagerID)
VALUES (102,\'Ables\',\'Joe\',203,3);
INSERT INTO employees(employeeID, employeeLastName, employeeFirstName, employeeDeptID, employeeManagerID)
VALUES (103,\'Simmons\',\'Brenda\',201,4);
INSERT INTO employees(employeeID, employeeLastName, employeeFirstName, employeeDeptID, employeeManagerID)
VALUES (104,\'Turner\',\'Lou\',202,5);
INSERT INTO employees(employeeID, employeeLastName, employeeFirstName, employeeDeptID, employeeManagerID)
VALUES (105,\'Conwell\',\'Phil\',205,2);

Managers:
---------------
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (1,\'Bunn\',202);
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (2,\'Norman\',205);
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (3,\'Kritz\',203);
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (4,\'Washington\',201);
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (5,\'Muller\',202);

Departments:
-------------------
INSERT INTO departments(departmentID, departmentName, departmentLocation)
VALUES (201,\'Accounting\',\'Suite 315\');
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (202,\'Customer Service\',\'Suite 303\');
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (203,\'IT\',\'Floor 2\');
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (204,\'Marketing\',\'Suite 322\');
INSERT INTO managers(managerID, managerLastName, managerDeptID)
VALUES (205,\'Human Resources\',\'Suite 301\');

/****************** To set Password for Database ******************/

MS Access
If the .accdb file is located on desktop under Database folder. like Desktop->Database->Personnel.accdb, process the following steps to create password for database.
Step1: Open MS Access 2013
Step2: Then click File->Open->Computer. Then click \'Browse\' in order to open the file. That is select a database file eg:Personnel.accdb, which you want to encrypt with password.
Step3: Select a file from \'Browse\' window. Then click \'Open Exclusive\' under \'Open\' menu item (Click \'arrow\' which is near to \'Open\').
Step4: After that go to \'File\' menu then click Info->Encrypt with password option. Here you have an option to set password for your database. Set the password as per your wish.