CS4: Assignment 2 on Databases

Attempt All questions

PRACTICAL QUESTION ONE

(a) Using a database package create a database file ‘COMPANY’.  Design a table with the following fields; use appropriate data types and appropriate primary key. The field EmpNo should be automatically increase by 1. Enter the following records in a table, save the table as EMPLOYEES.   

1. Using all the fields in your table, create a query that will return the Gross Salary of each employee. Save the query as Gross Salary.
2. From the query Gross Salary, create a query that displays all employees who were employed before February 2012 and earn a basic salary of more than UGX 800,000. Save the query as ROYALS.
3. Create a query for those employees whose names end with letter “a” and working in the department of marketing. Save it as Dept Mar.
4. Produce a report grouped according to department that extracts the following information from the query in (b) above: EmpNo, Dept, Surname, Basic Salary and Basic Salary. Save it as Gross Report.
5. Create an input form that can be used to enter data in the table. On the form, add a company logo (use any image from computer or picture from clipart) and save the form as Data Entry Form. Include your name and personal number as page footer.
6. All employees whose Gross Salary is less than 1,200,000/= are categorized as “Form” while others are categorized as “Substance”. Use a simple if function to determine the category to which each of employee. On your form add a new field Emp Category to work out this.
7. Create another query to show years spent by employees in this company. Save it as Employee Years.

PRACTICAL QUESTION TWO

2. The table below shows filtered data for KIM INVESTMENTS employees for the year 2019. Using any database management software capture the data and save it as Kim- your name e.g.(KIMLODWAR)

1. a) Design a table using the above data and name it “KIM STAFF”
2. b) Set the STAFF NO: field as a primary key with a format of “KIM-000” using the auto number data type.

• In the above table count the number of records and total salary
• Format salary field with a UGX currency symbol

c)Design a form from the above table returning all fields, with additional field names of Age, Gross Salary (Salary*Duration), Tax (25% of Gross Salary) and use formula for each and lock your form, name the form as “KIM FORM”

1. d) Insert a footer as your name with form colour light green to the above form, a title as KIM INVESTMENTS FORM.
2. e) Design a query to return all fields as in c above and perform the necessary mathematical expressions to return the actual values. Name it KIM QUERY.
3. f) Create another query from e above and return only the following fields: Staff No, Name, Date of Birth, Salary and Tax to filter out employees who were born before 1995 and name it Before 1995.
4. g) Using the query in e, create a filtered query for employees from BURUNDI and name it as Foreigners.
5. h) Design a query called Rich for all employees whose tax rate is above 500,000 using the main query you created.
6. i) Design a report from the Foreigners query and Name it Transferred
7. j) Create a query to return names containing 5 characters. Using fields: Staff No, Name, Date of Birth, Salary, Age and Gross Salary. Save it as Five-Char.

PRACTICAL QUESTION THREE

Create a database for Jinja Bridal Car Hire Ltd saved as bridal and carry out the following tasks.

Instructions:

(a).   Design a table saved as drivers to hold the above data.

(b).  Using appropriate field, assign a primary key.

(c).  Design a form having a sky-blue background colour, footer of your name you will     use to populate the table. Save the form as Data Entry.

(d).   Design three queries that will return workers who:

(i). have no remarks against their records. Save the query as Not Appraised.

(ii).   drive Car_Type that is not a Wish. Save the query as Not Wish.

(iii). celebrate birth day in the month of January. Save the query as Born Jan.

(e). Create a report to return drivers who drive a premio Car_Type. Save the report as Premio.

(f) Create a report having all the records on one sheet. Save the report as All. The report should have the following details:

(i). A good red line boarder.

(ii). Count of the records it holds                                                                          

(iii). Group and sort your records in order of car_type

(iv). Add a title: MOTO VEHICLES INFORMATION REPORT size 24

(g) Add Header as Your Name and Your Gender as Footer on the report

(h) Add an image on your report to serve as a  logo

(h) Print your queries and report only.