SSCI 582: Spatial Databases Project 1
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
SSCI 582: Spatial Databases
Project 1
PROJECT 1 – SQL IN MS ACCESS
ASSIGNMENT DESCRIPTION
In Project 1, you will explore the world of Structured Query Language, aka SQL (pronounced as “see- qual” or “ess-que-el”). You will complete a basic online SQL course at codecademy.com, review SQL fundamentals through a LinkedIn Learning course, and then on your own use what you just learned to write SQL statements to query a database in MS Access.
Structured Query Language (SQL) is a basic language that works through simple statements. SQL was originally created to help us manage data stored in relational databases, like Microsoft Access and SQL Server, among many. It is widely used across spatial sciences, in that it is used today in the backend of many frameworks you will run across, including database management systems (DBMS) and even desktop and web programming applications. It is a core piece of learning how to manage spatial as well as aspatial data.
MS Access is an aspatial database management system (DBMS) that combines Microsoft Jet Database Engine with a graphic user interface (GUI). While Access databases can no longer be connected with ESRI ArcGIS (as the architecture of the latter does not support personal geodatabases -- we will learn about that later), its user-friendly environment makes it an easy entry to write SQL statements and is still commonly used in government, business and some research fields to store and manage datasets.
LEARNING OBJECTIVES
• Connect to SSI Server via VMware
• Experiment with SQL by writing basic SQL queries
• Understand tables and relationships in MS Access
INSTRUCTIONS
1. Complete the Learn SQL course in codecademy (https://www.codecademy.com/learn/learn-sql). Signup for a free account and complete the Lessons only. You are not expected to complete the quizzes or projects, which require a subscription (~ 3 hrs).
Note: When signing up for codecademy, do NOT enter any credit card information if you are prompted to use ‘codecademy Pro’ . For example, you can click back in the browser to go back to the main page, then click the options button in the upper right corner, choose Catalog then click Catalog Menu, and lastly under Languages click on SQL and you will be able to start the course.
I strongly recommend you to type (rather than copy & paste) all of the SQL statements in this interactive online environment. The purpose of this exercise is for you to become familiar with writing the language. Take notes and/or screenshots if you want.
2. Watch the videos assigned below in the SQL Essential Training (with Bill Weinman) in Linkedin Learning (accessed through initial USC Blackboard login):
• Chapter 2: SQL Overview (~26 mins)
• Chapter 3: Fundamental Concepts (~ 37 mins)
• Chapter 4: Relationships (~11 mins)
To access Linkedin Learning, log into Blackboard and navigate to the ‘log in to the Linkedin Learning button under ‘Helpful Resources’ menu in the Home tab (Not the 582 course page; you may need to scroll down to see it in the lower left side of the page):
|
|
Note: Since Lynda.com was acquired by LinkedIn, which is now owned by Microsoft (since 2016), I found its user interface continues to evolve. At this time when I tested, after you click the Linkedin Learning button in the Blackboard Home page, a new Browser tab should automatically open with you logged in. If you have any issues accessing LinkedIn Learning via your USC account (i.e. you should never have to pay for anything you want to learn in Linkedin Learning), take a screen capture showing the issue and email me, ASAP.
3. Create, populate and query a simplified ‘World’ database in MS Access using SQL queries.
Data: Text files in WorldData1.zip (in the Week 2 folder on D2L (and the same data is in the H:\582\Week 2 folder in the SSI Server)
Platform: Microsoft Access 2019 in SSI Server via VMWare (or in your own PC/Mac, if you have MS Office Professional 2007 or later).
This part of the exercise provides you a real-world practice of SQL in a commonly used relational database environment.
• Log into SSI Server via VMWare (The instructions to access SSI Server are available in “Using SSI Server under the menu “Other Course Documents” in our 582 D2L). Upload the unzipped WorldData1.zip to your own G:\582 drive.
• In SSI Server, start MS Access (2019 is installed on the Server) and create a new (choose blank) database called ‘Project1’. Save the database in your G:\582\Project1 folder (If you get a popup asking to close open documents say OK; and if you get a Security warning asking if you want to make this a Trusted document say No.)
• Import each of the three text files into tables in your database (External Data tab > Text File) Examine these tables to make sure field names (first row in the text file) are properly populated and not used as a part of data.
• In Design View for the ‘COUNTRY” table, make the Name field its primary key instead of the ID field. (The little “key” icon will jump from ID to Name, confirming this.)
• In Datasheet mode (right-click on COUNTRY in left panel and choose Open), perform the following database maintenance/update operations:
a. To the COUNTRY table, add ‘Turkey’ with a population of 69660559, an area 300618.16 sq. mi., a literacy rate of 81.3 % and GDP of 385,123,000,000. (NOTE: be careful thatyou have the right number of0’s here. The database carries GDP in millions.)
b. To the CITY table, add ‘Ankara’, the capital of Turkey, with population 3,180,000; also add ‘Detroit’ in the U.S., with population 3,775,000.
c. To the LAKE table, add a new column ‘Continent’; then for all the relevant rows, enter “North America” in the cell at this column.
• Create a Many-to-1 (∞:1) relationship from CITY.In_Country to COUNTRY.Name, and also from LAKE.In_Country to COUNTRY.Name using the ‘Relationships’ function accessed from the Database Tools tab. In each case, set the Referential Integrity checkbox.
• From the Design tab, create a Relationship Report for your database; export this diagram as a pdf document. You can use File > Print > Microsoft Print to PDF, or click the PDF or XPS button to publish your PDF. Be sure to save it to your G: drive.
• NOTE: Adis Abeba is an alternative spelling for the Addis Ababa, the capital of Ethiopia.
|
|
4. Write and execute SQL queries in your World database to answer the following questions using the Simple Query Wizard with a combination of Design and SQL Views in MS Access. Each query can be accomplished in a single SQL statement. Save the query separately as username_query#, i.e. jswift_query1. Do not make manual edits to the database.
a. List everything (*) about countries whose population is less than 100,000. Your results should look something like this:
|
|
b. List the names, areas, and depths of all the lakes in North America, in order of declining area. What might a negative depth possible mean?
c. List the country, capital city, area, and GDP of countries whose literacy rate is less than 55%. Exclude countries for which the capital is not reported.
d. What are the name and population of the capital city in the country where Lake Baikal is located? Does the answer make sense, historically?
e. What is the average population of the capital cities and the non-capital cities? Display the answer in one table, rounded to integer (i.e. 0 decimal place). Hint: Use a “Group by” clause.
f. For each capital city, find the average depth of the lakes in the affected country. For example, after identifying “Washington DC” as the capital of the U.S., find the average depth of all the U.S. lakes.
Hints: Avoid lakes with an unknown depth (-999); use a “Group by” clause.
5. Write up a short report in MS Word; save it with a naming convention “LastName_Project1.docx” with the following contents. You should write out prose in complete sentences for each section (except for #3), not just use bullet points.
#1. (0.5 point) List 2 functionality and the associated syntax (SQL commands or statements) that are new and/or relevant to you in this Project.
#2. (0.5 point) Add a screen capture of your relationship report PDF showing your
relationship diagram in MS Access.
#3. (5 points total: 0.5 points each for the questions 4.a-b; 1 point each for the questions 4.c- f) Copy & paste SQL query statements used to answer the questions a-f above directly in the Word doc (so I can read it easily) with screen capture for each corresponding resulting table.
#4. (2 points) Write a short paragraph (e.g. < 150 words) about what parts of this homework assignment you found to be most helpful in learning SQL.
Submit your report into the Project 1 link by the above-listed due date.
1. If you are not familiar with MS Access, you may want to browse through the Linkedin Learning course Access 2019 Essential Training with Adam Wilbert, Ch. 1-5 are recommended; reaming chapters 6-8 are optional, recommended only if you think you might use MS Access a lot in the future.
2. w3schools.com is another good resource to learn more about SQL commands, including simple examples.
2022-09-08