I was very excited to learn what was on the agenda for this past week: Information retrieval!
Up to now, we haven’t done many things that I’d consider “hands on” — in the first few days of the course, Professor Schedlbauer excited us by naming various industry technologies (such as MySQL, SQL, R, etc.) that we’d learn later in the course.
That time is now — this past week, we learned SQL syntax, and how to implement a relational database schema in Microsoft Access.
Up to this Tuesday, we had just been practicing diagramming entity relationships, using conventions such as IE and UML. We then converted these entity relationship diagrams to relational database schemas, which were basically tables (for each entity) which had columns for its various fields.
This week, we learned how to actually implement these relational database schemas in a piece of sofware used in industry — we translated a relational database schema created in a previous week (as a homework assignment) into a Microsoft Access Database.
My first impressions of Microsoft Access were that it was not very user-friendly at all. There were tons of options and words that sounded like they were powerful at the top of the window, with a massive grid in the middle.
However, we quickly learned how to do the most basic thing in Access — creating a table. We created various tables (whose information was drawn from the same previous assignment), and then create relationships between various tables in the relationship view.
I had to manually create a join table to create a specific many to many relationship; figuring out how to do so initially was difficult but after I learned how to do so integrating it into the rest of the database was relatively easy.
In my opinion, by far the coolest part of Microsoft Access is that it automatically generates SQL queries for you, provided you create a query using the GUI. Creating queries using the GUI was actually pretty easy; the hardest part was creating a query that utilized a join table.
Another reason why I found this automatic SQL generation in Microsoft Access so neat is that it helps the user learn a bit of programming / SQL, even if they wren’t particularly trying to learn. It’s merely an option that Access provides.
PCMag wrote an article about top tips for Microsoft Access — a few of their top ones involved publishing an Access database to the internet. This would be pretty neat to try out sometime; if your Access database were on the internet, you could access it from any of your devices, or show people on the go.
Curious about the alternatives to Microsoft Access, I did some googling, and read up a bit about Microsoft SQL Server. While for academic purposes I believe that Microsoft Access is more than adequate (2gb per database, 1 core for processing), learning Microsoft SQL Server would be valuable for the future (10gb, 4 cores). Also, for efficiency, Microsoft SQL Server is optimized to be run on a machine that isn’t the machine that’s being used by the user.