First: - Create the following tables: 1.CREATE TABLE access_control_group ( access_control_group_id number(10) primary key NOT NULL, access_control_group_name varchar2(255) NOT NULL, allowed_areas varchar2(255) DEFAULT NULL ); 1.CREATE TABLE cards ( card_id number(10) primary key NOT NULL, status number(3) NOT NULL, created_at date NOT NULL, suspended_at date DEFAULT NULL NULL, last_used_at date DEFAULT NULL NULL, last_used_location varchar2(255) DEFAULT NULL ); 2. CREATE TABLE departments ( department_id number(10) primary key NOT NULL, department_name varchar2(255) NOT NULL ); 3. CREATE TABLE employees ( employee_id number(10) primary key NOT NULL, name varchar2(255) NOT NULL, gender varchar2(255) NOT NULL, date_of_birth date NOT NULL, position varchar2(255) NOT NULL, department_id number(10) NOT NULL REFERENCES departments(department_id), card_id number(10) NOT NULL REFERENCES cards(card_id) ); 4.CREATE TABLE access_log ( access_log_id number(10) primary key NOT NULL, card_id number(10) NOT NULL REFERENCES cards(card_id), timestamp date NOT NULL, status varchar2(100) NOT NULL, where_used varchar2(255) NOT NULL );

- Then insert the following data in the previous tables: INSERT INTO access_control_group VALUES (1, 'All Employees', 'Main Entrance, Elevators, Cafeteria '); INSERT INTO access_control_group VALUES (2, 'IT', 'Server Room, IT Department'); INSERT INTO access_control_group VALUES (3, 'Finance', 'Finance Department'); INSERT INTO access_control_group VALUES (4, 'Security', 'Main Entrance, Elevators, Cafeteria, Server Room, IT Department, Finance Department'); INSERT INTO access_control_group VALUES (5, 'Visitors', NULL); INSERT INTO cards values(57, 1, to_date('2018-01-01 15:01:20', 'yyyy-mm-dd HH24:MI:SS'), NULL, to_date('2018-12-21 17:00:00', 'yyyy-mm-dd HH24:MI:SS'), 'Server Room'); INSERT INTO cards values(58, 1, to_date('2018-01-01 15:03:30', 'yyyy-mm-dd HH24:MI:SS'), NULL, to_date('2018-12-21 17:00:00', 'yyyy-mm-dd HH24:MI:SS'), 'Elevators'); INSERT INTO cards values(62, 1, to_date('2018-01-01 15:19:15', 'yyyy-mm-dd HH24:MI:SS'), NULL, to_date('2018-12-21 17:13:00', 'yyyy-mm-dd HH24:MI:SS'), 'Cafeteria'); INSERT INTO cards values(65, 1, to_date('2018-01-04 08:30:13', 'yyyy-mm-dd HH24:MI:SS'), NULL, to_date('2018-12-21 17:00:00', 'yyyy-mm-dd HH24:MI:SS'), 'Main Entrance'); INSERT INTO cards values(66, 1, to_date('2018-01-04 08:30:13', 'yyyy-mm-dd HH24:MI:SS'), NULL, to_date('2018-12-21 17:00:00', 'yyyy-mm-dd HH24:MI:SS'), 'Main Entrance'); INSERT INTO cards values(69, 1, to_date('2018-05-25 15:18:39', 'yyyy-mm-dd HH24:MI:SS'), NULL, NULL, ''); INSERT INTO cards values(70, 1, to_date('2018-05-25 15:18:39', 'yyyy-mm-dd HH24:MI:SS'), NULL, NULL, ''); INSERT INTO departments (department_id, department_name) SELECT 1, 'Finance' FROM dual UNION ALL SELECT 2, 'IT' FROM dual UNION ALL SELECT 3, 'HR' FROM dual INSERT INTO employees (employee_id, name, gender, date_of_birth, position, department_id, card_id) SELECT 1, 'Jasmine Barnes', 'Female', to_date('1980-12-22', 'yyyy-mm-dd'), 'Manager', 3, 58 FROM dual UNION ALL SELECT 2, 'Kimberly Lewis', 'Female', to_date('1981-03-12', 'yyyy-mm-dd'), 'Recruiter', 3, 57 FROM dual UNION ALL SELECT 3, 'Megan Welch', 'Female', to_date('1982-11-06', 'yyyy-mm-dd'), 'Graphic Designer', 2, 62 FROM dual UNION ALL SELECT 4, 'Coby Rojas', 'Male', to_date('1979-05-25', 'yyyy-mm-dd'), 'Auditor', 1, 65 FROM dual UNION ALL

SELECT 5, 'Shawn Gardner', 'Male', to_date('1986-05-25', 'yyyy-mm-dd'), 'Security Guard', 2, 62 FROM dual UNION ALL SELECT 6, 'John Jones', 'Male', to_date('1990-12-11', 'yyyy-mm-dd'), 'Project Manager', 1, 65 FROM dual UNION ALL SELECT 7, 'Krystal Prince', 'Female', to_date('1987-11-01', 'yyyy-mm-dd'), 'Social Media Specialist', 2, 57 FROM dual UNION ALL SELECT 8, 'Stephen Johnson', 'Male', to_date('1978-12-12', 'yyyy-mm-dd'), 'Operations Coordinator', 1, 58 FROM dual UNION ALL SELECT 9, 'Michael Davis', 'Male', to_date('1985-12-14', 'yyyy-mm-dd'), 'System Administrator', 2, 65 FROM dual UNION ALL SELECT 10, 'Evan Martinez', 'Male', to_date('1986-01-23', 'yyyy-mm-dd'), 'System Administrator', 3, 58 FROM dual; INSERT INTO access_log (access_log_id, card_id, timestamp, status, where_used) SELECT 1, 57, to_date('2018-12-01 13:32:13', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Server Room' FROM dual UNION ALL SELECT 2, 58, to_date('2018-12-04 22:58:35', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Cafeteria' FROM dual UNION ALL SELECT 3, 58, to_date('2018-12-10 05:43:55', 'yyyy-mm-dd HH24:MI:SS'), ' 'approved', 'Finance Department' FROM dual UNION ALL SELECT 4, 57, to_date('2018-12-14 01:36:23', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 5, 57, to_date('2018-12-17 23:42:41', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Cafeteria' FROM dual UNION ALL SELECT 6, 57, to_date('2018-12-21 17:00:00', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Server Room' FROM dual UNION ALL SELECT 7, 58, to_date('2018-12-01 15:44:40', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Main Entrance' FROM dual UNION ALL SELECT 8, 58, to_date('2018-12-05 21:06:26', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Main Entrance' FROM dual UNION ALL SELECT 9, 58, to_date('2018-12-10 06:24:12', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Main Entrance' FROM dual UNION ALL SELECT 10, 58, to_date('2018-12-14 07:38:58', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Main Entrance' FROM dual UNION ALL SELECT 11, 58, to_date('2018-12-18 13:09:03', 'yyyy-mm-dd HH24:MI:SS'), 'denied', 'Server Room' FROM dual UNION ALL SELECT 12, 58, to_date('2018-12-21 17:00:00', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 13, 57, to_date('2018-12-02 18:07:00', 'yyyy-mm-dd HH24:MI:SS'), 'denied', 'IT Department' FROM dual UNION ALL SELECT 14, 57, to_date('2018-12-05 21:51:20', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Cafeteria' FROM dual UNION ALL SELECT 15, 57, to_date('2018-12-10 22:33:03', 'yyyy-mm-dd HH24:MI:SS'), 'denied', 'Finance Department' FROM dual UNION ALL SELECT 16, 57, to_date('2018-12-14 20:39:24', 'yyyy-mm-dd HH24:MI:SS'), 'denied', 'IT Department' FROM dual UNION ALL SELECT 17, 57, to_date('2018-12-18 13:34:30', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 18, 58, to_date('2018-12-21 17:00:00', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 19, 62, to_date('2018-12-02 22:23:10', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 20, 65, to_date('2018-12-06 01:36:48', 'yyyy-mm-dd HH24:MI:SS'), 'denied', 'Finance Department' FROM dual UNION ALL SELECT 21, 62, to_date('2018-12-11 12:52:33', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'IT Department' FROM dual UNION ALL

SELECT 22, 62, to_date('2018-12-14 23:21:21', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Cafeteria' FROM dual UNION ALL SELECT 24, 62, to_date('2018-05-16 14:25:32', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Elevators' FROM dual UNION ALL SELECT 25, 62, to_date('2018-12-03 09:38:52', 'yyyy-mm-dd HH24:MI:SS'), 'denied', 'Finance Department' FROM dual UNION ALL SELECT 26, 65, to_date('2018-12-06 08:41:22', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Main Entrance' FROM dual UNION ALL SELECT 27, 65, to_date('2018-12-11 13:33:57', 'yyyy-mm-dd HH24:MI:SS'), 'approved', 'Main Entrance' FROM dual UNION ALL SELECT 28, 65, to_date('2018-12-15 06:40:05', 'yyyy-mm-dd HH24:MI:SS'), 'denied', 'Finance Department' FROM dual

Q5. Create a view name Allowed_Area to display the allowed area for each of the access Control groups, if the access control group does not have any areas, the following Message should be displayed 'No Areas'. Q6. Create a view name all Employees to display the name and gender of all employees. Sort the result in descending order based on their gender, if the two or more gender Have the same gender, sort by name in ascending order. Q7. Create a view name Last_Access that displays when the last time the finance Department was successfully accessed. Name the column Last Access Time'. Q8. Create a view name No_Emps_Per_Depts that display the number of employees per Department if the department has no less than two employees. 9. Create non-unique index on the foreign key column (card_id) in the access_log table. 10. Display the indexes and uniqueness that exist in the data dictionary for the Cards table