(I) FAQ for Lab 3

•         (1) The username and password in your email are for the database, NOT cs460.bu.edu server.
                  Use your BU username and password to log on cs460.bu.edu server.
•         (2) When importing data in PostgreSQL on cs460.bu.edu server, the data file should be on the server, NOT on your own machine.
                  The cs460.bu.edu server cannot read the file in your local machine.
•         (3) You can use PostgreSQL either on your own machine or on cs460.bu.edu server. But do NOT mix them together. There is no synchronization between the server and your machine.
                  (a) If you are using your own machine, the data file should be in your machine.
                  (b) If you are using cs460.bu.edu server, make sure the data file should be on the server.
•         (4) Your home folder is shared by all servers in CS department.
                  (a) In windows machines, the "Z" drive is your home folder. The files in "Z" drive is the same as the files in your linux home folder.
                  (b) The files in the home folder on csa2 or csa3 server is the same as the files in the home folder on cs460 server.
                  (c) You can put the files in "Z" drive and read the files in Linux terminal.
                  (d) You can upload the files to csa2 or csa3 server and read the files on cs460 server.
•         (5) Do NOT use Linux commands in PostgreSQL database shell. Vice versa.
                  (a) For example: "ls", "head", "tail", "more", "less" and "cat" are limux commands. Do not use them in PostgreSQL database shell.
                  (b) For example: "/copy", "select", and "create" are PostgreSQL database commands. Do not use them in Linux terminal.
•         (6) Use semicolon ";" at the end of each statement in PostgreSQL database shell.
                  (a) If you see "=>", it means you are typing a new statement in PostgreSQL database shell.
                  (b) If you see "->", it means you continue to type the previous statement.
•         (7) If you are using Macs to install PostgreSQL Database, maybe you will see an error regarding to the maximal memory issue.
                  (a) Please read the README file shown in error message and change the configuration file on your Mac.
                  (b) You should change kern.sysv.shmmax to a larger number (larger than 32M).

(II) Copy or Download Files to Server

•         (1) Use wget to download the files to your home folder on CS servers.
                  For example, after you log on to csa2 or csa3 server, type wget http://files.hubwaydatachallenge.org/hubway_2011_07_through_2013_11.zip in terminal to download the data file for Lab 3.
•         (2) Use scp to copy the files from your Mac or Linux machine to CS server.
                  For example, in your Mac or Linux machines, open a terminal and go to the directory of your file to be copied. Type scp [filename] [username]@csa2.bu.edu:~ in terminal to copy the files.
•         (3) Use FileZilla FTP software to upload the files from your Windows machine to CS server.
                  (a) Download the FileZilla client and install it on your own machine.
                  (b) Open the FileZilla client.
                  (c) Type sftp://csa2.bu.edu in Host.
                  (c) Type your BU username and BU password.
                  (d) Type 22 in Port.
                  (e) Connect the server and upload your files.

(III) Linux commands

•         (1) man : Displays the on-line manual page
                  (a) The most important command.
                  (b) If you do not know how to use a command, read its manual first.
                  (c) For example, type man ls to show the manual of command ls .
•         (2) ls : List files in the current directory.
•         (3) cd : Change the directory.
•         (4) mkdir : Make a directory.
•         (5) rmdir : Remove an empty directory.
•         (6) rm : Remove files.
•         (7) cp : Copy files.
•         (8) mv : Move files.
•         (9) head : Show first several lines of a file.
•         (10) tail : Show last several lines of a file.
•         (11) more : Display a file.
•         (12) pwd : Show the path of the current directory.
•         (13) wget : Download files.
•         (14) scp : Copied files between different hosts.
•         (15) unzip : Unzip a zip file. Similar commands include tar, gzip and bzip2

•         * You can find more about linux commands here .
•         ** Use linux commands on csa2 and csa3 servers.

(IV) PostgreSQL commands

•         (1) Use \d to display all relations in the database.
•         (2) Use \d [table_name] to display the schema of a specified relation.
•         (3) Use \copy to import and export relations.
•         (4) Use \h to show help manual.


•         (5) Use CREATE TABLE to define a new table. Read CREATE TABLE manual
•         (6) Use DROP TABLE to delete a table. Read DROP TABLE manual
•         (7) Use ALTER TABLE to change a table. Read ALTER TABLE manual


•         * Statements end with semicolons. Use ; at the end of each statement.

(V) Relational Algebra

•         (1)     $\sigma$     Selection, e.g. $\sigma_{firstname=John}(students)$
•         (2)     $\pi$     Projection, e.g. $\pi_{name, age}(students)$, it only returns distinct rows.
•         (3)     $\times$     Cross Product
•         (4)     $-$     Set Difference
•         (5)     $\cup$     Set Union
•         (6)     $\rho$     Renaming, e.g. $\rho_{sid \rightarrow studentId}(students)$
•         (7)     $\cap$     Set Intersection
•         (8)     $\Join$     Natural Join
•         (9)     $\Join_{\theta}$     Conditional/Theta Join, $R\Join_{\theta}S = \sigma_\theta(R\times S)$
•         (10)     $/$     Division, $A/B = \left\lbrace \langle x \rangle \mid \forall \langle y \rangle \in B, \exists \langle x, y \rangle \in A \right\rbrace$

•         * You can type relational algebra in Word (Insert Equation).
•         ** $\Join$ is \bowtie , $\cup$ is \cup , $\cap$ is \cap and others are Greek symbols.
•         *** You can try Google Docs, if you do not have Word.

(VI) Solution to Exercise of Lab 3

• 
                  CREATE TABLE trips (
                      seq_id integer,
                      hubway_id integer,
                      status varchar(20),
                      duration integer,
                      start_date timestamp,
                      strt_statn integer,
                      end_date timestamp,
                      end_statn integer,
                      bike_nr char(6),
                      subsc_type varchar(20),
                      zip_code varchar(6),
                      birth_date integer,
                      gender varchar(6)
                  );
• 
                  \copy trips from '~/Downloads/hubway_trips_sample.csv' CSV HEADER;