Practice Exercises

Find a dataset from Kaggle that catches your eye. Make sure that the dataset you select is relatively small (<50 MB), and click Download. Open the data file in Excel or a text editor like Notepad. Note the kind of data included in your data file and how it is structured. What kind of data does your file include? How does your file organize individual items of data? What kind of company or organization might this data be useful for?

Imagine that you are setting up a database to support a company’s human resources function. Explain the benefits of using Access to a leadership team that is considering what software your department will need.

The foundation of a digital organization is its data platform. Choose a business that provides products or services and consider the organization’s data landscape and what types of databases are likely in use. Use evidence from the company’s website and marketing materials to support your prediction.

Build a simple database and create a query that joins information. You are currently working with information about the sales and marketing team. You want to keep track of the team members and the regions they work with. Set up a database with the following tables (remember, you would separate this data into at least two tables to make the database more efficient and easy to work with):

Table:TeamMember
TMID
TMFirstName
TMLastName
TMPhone
TMEmail
Table:SalesRegion
SalesRegionName
SalesRegionNotes
Table:SalesRecord
TMID
SalesRegion

Once you have set up the tables, create a query that returns the team member’s first and last names and the sales region they are assigned to. Add any fields you think are necessary to create a list of team members’ names with their region. You may want to try sorting to improve the query output.

Imagine that you are conducting a human resources analysis. You have a table containing all the names and personal information for a team of employees. You have another table that contains the learning records for all training and certifications that individuals have completed. Both tables have a field called EMPLID, which stores the employee ID. Design a query that returns the EMPLID, employee name, location, and all training or learning activities they have completed. After you design the query, write it in SQL. (Use Microsoft's Access SQL support page for assistance with the code.)
Table: EMPLInfo
EmplID
EmplFirstName
EmplLastName
EmplAddress
EmplCity
EmplState
EmplZip
Table: EMPLLearning
EmplID
TrainingName
TrainingDate

Importing records is an important function when working with a database like Access. Complete the following steps to create a database from scratch:

1. Create a blank database using Access and then import the colors.csv file from the LEGO Database found on Kaggle.
2. Then use your navigation buttons to do the following:
   1. Add a record with the values:
      ID: 2000
      Name: Vivid burgundy
      RGBN: 990033
      is_trans: f
   2. Modify the record to say “dragon-scale burgundy.”
   3. Delete the record.
   4. Undo the change.

LEGO has decided to stop offering the following colors: Sand Purple, Dark Green, Light Salmon, Metallic Gold, and Lavender. They have tasked you with removing these colors from their database. Using your database with the imported colors.csv file from the LEGO Database on Kaggle, locate and delete all records for the colors listed.

WorldCorp leadership would like a visually attractive list of all employees that can be used in a publication. They would like the WorldCorp logo to appear on the file and would like to display each employee’s first and last name, alphabetized by last name. Please create a report that meets these criteria using the Human Resources Data Set from the Kaggle website.

The WorldCorp Human Resources Department has asked you to create reports to support the following inquiries using the Human Resources Data Set from the Kaggle website. Before creating the reports, look at the type of data available and decide what data needs to be included or omitted. Which fields should be considered to answer these inquiries?

• Which employees have high performance scores? Low performance scores?
• Which departments are currently the most diverse?
• How has the company acquired its most diverse employees to date?
• Is pay equitable across all divisions and positions in the company?

Imagine that you are the human resources manager for the Northeast WorldCorp region. You have a dataset of employees from the region, and you would like to be able to look through employee records to identify employees who may be unhappy in their position so you can offer support and intervention. Using the Human Resources Data Set from Kaggle, import the data and create a form that will allow you to navigate between employee records to look at employee performance, satisfaction, and other data you think would be useful in determining whom you can assist.

Using the Human Resources Data Set from Kaggle (or the database you built in Exercise 1), create forms that support the following business functions:

• Evaluating employee performance (including attendance and lateness)
• Understanding employee roles (including position, department, location)
• Reviewing employee hiring information (including recruitment and position)

The internet runs on forms. Most of the websites you interact with are based on databases using forms for what you see. Look at a website you use frequently and identify which screens are likely forms that show you data from the underlying website database. Using the form you have identified, determine what the table(s) under it likely hold. Draw the table and explain your rationale, including a screen capture of the form you based your work on.