Databasteknik: Exercise 7 - Microsoft Access

Objectives

• To get some hands-on experience with Microsoft Access.

Scenario

Preparations

• Introduktion till Microsoft Access (section 32.8 in the course book, chapter 27 in the first edition)
• As an alternative, you can study the (rather similar) chapter Introduktion till Microsoft Access in the web course about databases.
• A web course at Microsoft about a newer version, Microsoft Office Access 2007. The new version is similar to the old ones − except that they have changed the user interface, so everything looks completely different.
• Two web courses about Microsoft Office Access 2013: What's new in Access 2013 and Make the switch to Access 2013

Exercise

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.)