Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due
Assignment #1 Instructions
CS 348 - Spring 2024 - Sections 002 & 003
Submission Instruction
This document contains instructions on how to submit Part II of Assignment #1. The School supports IBM DB2, which is what we will use for this assignment. Follow the instructions below to get started:
Get Started
DB2 is available on
ubuntu2204-002 .student .cs .uwaterloo .ca ubuntu2204-004 .student .cs .uwaterloo .ca ubuntu2204-006 .student .cs .uwaterloo .ca ubuntu2204-010 .student .cs .uwaterloo .ca ubuntu2204-012 .student .cs .uwaterloo .ca ubuntu2204-014 .student .cs .uwaterloo .ca
Every CS348 student will have an account on these servers.
First, login in by typing the following command in a terminal followed by your password:
|
softbase .cs$ ssh @linux .cs .uwaterloo .ca |
This will connect you to one of the two Ubuntu servers where you can access DB2. If you want to choose which of the six severs to connect to you can now ssh directly to (e.g., ssh ubuntu2204-002.student.cs.uwaterloo.ca) but you need not choose a particular server. If you start editing a database on one of these specific servers, you will need to reconnect to that same server or you will not see your changes. This is not a concern if you populate the database using data stored in files.
Second, set up your environment correctly: for sh, bash, et al:
|
$ source ~cs348/public/db2profile |
Then, you can start DB2 without any DB2 password:
|
$ db2 (c) Copyright IBM Corporation 1993,2007 Command Line Processor for DB2 Client 11.1.1.1 [...] For more detailed help, refer to the Online Reference Manual . db2 => |
Last, connect to the database managed by DB2 for this class by typing in DB2
|
db2 => connect to cs348 |
You can now enter SQL statements directly at the db2 => prompt. For example,
|
db2 => list tables |
You can read more examples from DB2Tutorial .pdf uploaded on Learn (under Assignment 1) or find online reference for IBM DB2 at https://www.ibm.com/support/knowledgecenter/SSEPGG_10. 1.0/com.ibm.db2.luw.kc.doc/welcome.html.
Run Test Database
Download Database . zip from Learn under Assignment 1. Transfer the unzipped files onto one of the servers which has DB2, e.g. you may use
$ scp -r Database/ @
$ scp -r Database/ @
In Database/testdb, there are several .sql scripts (connectCS348 .sql, droptables .sql, createtables .sql, populatetables.sql).
You can run .sql scripts in the following way: for example,
|
$ db2 -stvf connectCS348 .sql |
Remember to connect to CS348 (run the script above) before running other scripts. You can find the schema in createtables .sql and this script creates tables.
In Database/testdb/, there are also several text files (aircrafts .txt, employees .txt, pilots .txt, certified.txt) which are used by populatetables .sql to populate the tables. droptables .sql drops all the tables. There is a sample_query .sql file that performs a simple selection, which you can use to testif your database is loaded correctly.
Queries
In this assignment, you are required to submit 6 SQL files to answer each of the 6 questions specified in Assignment #1. Note that these query statements will be marked automatically, so you need to test if the queries work in db2 by running $ db2 -stvf 1 .sql, etc before submission
Unless otherwise specified each file should contain only a single query. You should NOT create views to answer any of the SQL questions.
You should NOT update the database since none of the questions ask you to.
Submission
You need to submit your assignment via https://marmoset.student.cs.uwaterloo.ca/.
IMPORTANT: Your submission must be a single zipped folder which includes only the 6 SQL files 1 .sql, 2 .sql, . . . , 6 .sql. This means that when you unzip the folder you should see the 6 SQL files. The files must be named exactly 1 .sql, 2 .sql, . . . , 6 .sql.
Each question has only 1 public test and many private tests. You will only see the results of the public test (whether or not you passed or failed the public test). Passing the public test does NOT guarantee that your submission will pass all of the private tests. To receive full marks your SQL must pass all of the tests. Therefore, it is important that you test your SQL yourself.
To pass a test the tuples returned by your query must match the expected output. Please use the Expected columns in each question while writing your query. The row/tuple order matters iff the question asks for a specific order.
You can re-submit until the deadline and your last submission will be used for the final grade. Do not be alarmed if marmoset is slow, marmoset can often take a rather longtime to run and grade your submission.
All of the SQL files that you submit must work in DB2 and they must NOT produce any errors/warning. Queries that produce errors/warnings will automatically fail the testcases.