Databasteknik: Exercise 7 - Microsoft

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

To get some hands-on experience with either Microsoft Access or Microsoft SQL Server.

Scenario

You're finally fed up with Mimer, so you've decided to join Bill Gates in his quest for world domination. Therefore, you will now try out one of Microsoft's datbase management systems, either Microsoft Access or Microsoft SQL Server.

SQL Server can be downloaded as a test version and installed on your computer. I have not found a version of Access that doesn't cost money.

Preparations

Read about Microsoft SQL Server, for example:

Introduktion till Microsoft SQL Server (chapter 31 in the course book, section 29.10 in the first edition)
Joel Murach, Bryan Syverson: Murach's SQL Server 2019 for Developers

Or, read about Microsoft Access, for example:

Introduktion till Microsoft Access (section 32.8 in the course book, chapter 27 in the first edition)
The (much longer) chapter Introduktion till Microsoft Access in the web course about databases. It is about Microsoft Access 2000. Yes, it is a very old version, but Access hasn't changed much − except that they have changed the user interface, so everything looks different.

Exercise

Download and install SQL Server 2019 Express, or another test version of SQL Server. Link: https://www.microsoft.com/en-us/sql-server/sql-server-downloads

If your version of SQL Server doesn't already contain Micrsoft SQL Server Management Studio, which is the user interface for managing and working with SQL Server databases, you need to download an install it. Link: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Then create some tables, insert some data, and run some queries.

Or, start Microsoft Access, create a database, and do the following things in that database:

Create a table Employee, with a suitable schema, and put some example rows in it.
Create a table Department, with a suitable schema, and put some example rows in it.
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.)
Create a query that shows the name of each employee, along with the name of the department that that employee works on.
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.
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.)
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), March 22, 2021