Transportation Employee and Department MS Access Assignment

/in /by

RUSTAQ COLLEGE OF EDUCATION
COMP2002 INTRODUCTION TO COMPUTERS II – MS ACCESS ASSIGNMENT GR: 150
P a g e 1 | 2
Note: Copying is strictly not allowed. If any student found copied answers from other student will get
ZERO marks.
INSTRUCTIONS: Do the following questions related to MS ACCESS.
1. Create a database with the name “Company”. Save the file with the name “XXXXCompany.accdb”,
where XXXX is your name. Send this file to me on or before 7-5-2020.
2. Create 2 tables with the name “Employee” and “Department” using design view.
3. The fields in the Employee table are as follows. You need to appropriately select the data type and
size for each field. Also you need to identify the primary key of the table. 3M
Field_Name Data Type Size Remarks
emp_id ? ? ID of the employee
emp_name ? ? Name of the employee
job_name ? ? What job he/she doing
manager_id ? ? The manager under which he/she working
hire_date ? ? Date of joining the company
salary ? ? The salary of employee
commission ? ? Apart from salary if he/she gets extra money as
commission
dep_id ? ? In which department the employee is working.
4. Enter the following data into the table.
emp_id | emp_name | job_name | manager_id | hire_date | salary | commission | dep_id
——–+———-+———–+————+————+———+————+——–
8319 | HAAROON | PRESIDENT | | 1991-11-18 | 6000.00 | | 100
6928 | AHMED | MANAGER | 8319 | 1991-05-01 | 2750.00 | | 300
7832 | SALIM | MANAGER | 8319 | 1991-06-09 | 2550.00 | | 100
5646 | SAIRA | MANAGER | 8319 | 1991-04-02 | 2957.00 | | 200
7858 | ABDULLAH | ANALYST | 5646 | 1997-04-19 | 3100.00 | | 200
9062 | FATMA | ANALYST | 5646 | 1991-12-03 | 3100.00 | | 200
3679 | AALEYAAH | CLERK | 9062 | 1990-12-18 | 900.00 | | 200
4989 | KHALID | SALESMAN | 6928 | 1991-02-20 | 1700.00 | 300.00 | 300
5271 | SARA | SALESMAN | 6928 | 1991-02-22 | 1350.00 | 600.00 | 300
6564 | MAZOON | SALESMAN | 6928 | 1991-09-28 | 1350.00 | 1700.00 | 300
8454 | EKHLAS | SALESMAN | 6928 | 1991-09-08 | 1600.00 | 100.00 | 300
8736 | YOUSUF | CLERK | 7858 | 1997-05-23 | 1200.00 | | 200
9000 | ANWAAR | CLERK | 6928 | 1991-12-03 | 1050.00 | | 300
9324 | MARYAM | CLERK | 7832 | 1992-01-23 | 1400.00 | | 100
5. The fields in the Department table are as follows. You need to appropriately select the data type and
size for each field. Also you need to identify the primary key of the table. 2M
Field_Name Data Type Size Remarks
dep_id ? ? ID of the department
dep_name ? ? Name of the department
dep_location ? ? Location of the department
RUSTAQ COLLEGE OF EDUCATION
COMP2002 INTRODUCTION TO COMPUTERS II – MS ACCESS ASSIGNMENT GR: 150
P a g e 2 | 2
6. Enter the following data into the table.
dep_id | dep_name | dep_location
——–+————+————–
100 | FINANCE | MUSCAT
200 | AUDIT | RUSTAQ
300 | MARKETING | SOHAR
400 | PRODUCTION | SALALAH
7. Make the RELATIONSHIP between Employee and Department tables. 2M
8. Do the following QUERIES using query design.
Single table queries:
a. Display all employee whose salary is greater than 3000. 1M
b. Display the names of employee who are getting commission less than 500. 1M
c. Display names and jobs of department 300 employees. 1M
d. Display all the employees in descending order of their names. 1M
Multiple table queries:
e. Display the names and salary of all employees who are working in FINANCE department. 3M
f. Display the names and jobs of all employees who are working in RUSTAQ. 3M
9. Create a tabular FORM for Employee and Department tables respectively using form wizard. 4M
(Use appropriate designs of your choice wherever needed)
10. Create the following REPORTS using report wizard.
(Use appropriate designs of your choice wherever needed)
a. All employees’ information along with their job, salary and department IDs. 2M
b. All department’ information in ascending order of their locations. 2M
END OF ASSIGNMENT