Databasteknik: Exercise 7 - Microsoft Access

This is an exercise. You don't have to show or hand in anything, but if you have any problems or questions, you are welcome to ask your teaching assistant.

Objectives

Scenario

You're finally fed up with Mimer, and you've decided to join Bill Gates in his quest for world domination. Therefore, you will now try out Microsoft Access.

Preparations

Read about Microsoft Access, for example:

Exercise

Start Microsoft Access, create a database, and do the following things in that database:
  1. Create a table Employee, with a suitable schema, and put some example rows in it.
  2. Create a table Department, with a suitable schema, and put some example rows in it.
  3. Create a reference attribute that refers from Employee to Department, indicating which department each employee works on. Define it as a relationship in Access. (Click on Verktyg, and then choose Relationer.)
  4. Create a query that shows the name of each employee, along with the name of the department that that employee works on.
  5. Create a text file with data about some employees, and import the data in that file into the Employee table. (Hint: Arkiv -> Hämta externa data -> Importera. Put data for one employee on each row in the text file, and use semi-colon to separate the fields.
  6. Create links to some tables in your Mimer database, for example the tables dept and store. (Hint: Arkiv -> Hämta externa data -> Länka externa tabeller. Choose ODBC Databases as file format. Any Mimer database that you have defined as an ODBC data source on this machine should be listed under the tab Machine Data Source.)
  7. Now that you have access to your Mimer database from inside Access, use Access to build a query that involves at least two tables in the Mimer database. For example, you can list the name of each department (dept) that exists in a store in San Francisco. (Hint: Start by defining a relationship between the two tables.)


Thomas Padron-McCarthy (thomas.padron-mccarthy@oru.se), December 3, 2020