Lab Assignment 1 CMPS 180 

Lab Assignment 1 CMPS 180 
2. Goal
The goal of the first assignment is to create a PostgreSQL data schema with 5 tables. That is all that is
required in this assignment. The other Lab Assignments are much more difficult. In your Lab Sections,
you may be given information about how to load data into a table and issue simple SQL queries,
because that’s fun, but loading data and issuing queries are not required in this assignment. (That will
show up in the Lab2 assignment.)
3. Lab1 Description
3.1 Create PostgreSQL Schema Lab1
As we noted in the general instructions, you will create a Lab1 schema to set apart the database tables
created in this lab from tables you will create in future labs, as well as from tables (and other objects) in
the default (public) schema. Note that the meaning of schema here is specific to PostgreSQL, and distinct
from the general meaning of schema. See here for more details on PostgreSQL schemas. You create the
Lab1 schema using the following command:
CREATE SCHEMA Lab1;
Now that you have created the schema, you want to make Lab1 be the default schema when you use psql.
If you do not set Lab1 as the default schema, then you will have to qualify your table names with the
schema name (e.g., by writing Lab1.Customers, rather than just Customers). To set the default schema, you
modify your search path as follows. (For more details, see here.)
ALTER ROLE username SET SEARCH_PATH to Lab1;
You will need to log out and log back in to the server for this default schema change to take effect. (Students
often forget to do this.)
3.2 Tables
You’ll be creating tables for a very simplified version of the Stock Market, with tables for Exchanges,
Stocks, Customers, Trades and Quotes. Data types for the attributes in these 5 “Stock Market” tables
are described in the next section.
Important: To receive full credit, you must use the attribute names as given, and the attributes must be
in the order given. Also, the data types must match the specifications given in the next section. Follow
directions; do not do more than you’re asked to do in this assignment.
Lab Assignment 1 CMPS 180 – Winter 2019 Due: 11:59pm Wednesday Jan 23
Page 2 of 4
The underlined attribute (or attributes) identifies the primary key of the table. The tables of the schema
record Stock Market information. Each stock is traded on a stock exchange; for example, the New York
Stock Exchange (NYSE) and NASDAQ are well-known U.S. stock exchanges, but there are many others
worldwide. For example, ORCL is the symbol of a stock (Oracle) that is traded on the New York Stock
Exchange (NYSE). A timestamp consists of a date and a time. Trades occur at particular timestamps
(tradeTS) and involve customers who are buyers and sellers of a stock at a particular price and with a
particular volume. Quotes give the latest price for a stock as of a particular timestamp (QuoteTS).
In this assignment, you’ll just have to create tables with the correct table names, attributes, data types
and primary keys. (Don’t forget the primary keys!)
3.2.1 Data types
Sometimes an attribute (such as symbol, address and price) appears in more than one table. Attributes
that have the same attribute name might not have the same data type in all tables, but in our schema, they
do.
• For customerID, buyerID, sellerID and volume attributes, use integer.
• For category, which classifies types of customers, use character with fixed length 1.
• For exchangeID, use character with fixed length 6.
• For symbol, use character with fixed length 4.
• For exchangeName, stockName, custName and address attributes, use character of variable
length, with maximum length 30.
• price should be decimal, with at most 5 digits to the left of the decimal point and 2 decimal
digits after it.
• The quoteTS and tradeTS attributes should be of type timestamp.
• The isValidCustomer attribute, which indicates whether a customer is allowed to buy or sell
stocks, should be of type boolean.
You must write a CREATE TABLE statement for each of the five tables in Section 3.2. Write the
statements in the same order that the tables are listed above. Just use the data types mentioned above.
Save your statements in the file create.sql
Exchanges(exchangeID, exchangeName, address)
Stocks(exchangeID, symbol, stockName, address)
Customers(customerID, custName, address, category, isValidCustomer)
Trades(exchangeID, symbol, tradeTS, buyerID, sellerID, price, volume)
Quotes(exchangeID, symbol, quoteTS, price)
Lab Assignment 1 CMPS 180 – Winter 2019 Due: 11:59pm Wednesday Jan 23
Page 3 of 4
4. Testing
While you’re working on your solution, it is a good idea to drop all objects from the database every time
you run the create.sql script, so you can start fresh. Dropping each object in a schema may be tedious,
and sometimes there may be a particular order in which objects must be dropped. The following
command, which you should put at the top of your create.sql, will drop your Lab1 schema (and all the
objects within it), and then create the (empty) schema again:
DROP SCHEMA Lab1 CASCADE;
CREATE SCHEMA Lab1;
Before you submit your Lab1 solution, login to your database via psql and execute your create.sql script.
As you’ll learn in Lab Sections, the command to execute a script is: \i <filename>. Verify that every
table has been created by using the command: \d
Also, verify that the attributes of each table are in the correct order, and that each attribute is assigned
its correct data type using the following command: \d <table>.
5. Submitting
1. Save your script as create.sql You may add informative comments to your scripts if you want. Put
any other information for the Graders in a separate README file that you may submit.
2. Zip the file(s) to a single file with name Lab1_XXXXXXX.zip where XXXXXXX is your 7-digit
student ID. For example, if a student's ID is 1234567, then the file that this student submits for
Lab1 should be named Lab1_1234567.zip
If you have a README file (which is not required), you can use the Unix command:
 zip Lab1_1234567 create.sql README
If you don’t have a README file, to create the zip file you can use the Unix command:
 zip Lab1_1234567 create.sql
(Of course, you should use your own student ID, not 1234567.)
3. Submit the zip file on Canvas under Assignment Lab1. Please be sure that you have access to
Canvas for CMPS 180. Registered students should automatically have access; students who are
not registered in CMPS 180 will not have access. No students will be admitted to CMPS 180 after
the Lab1 due date.
If you are working on the UNIX timeshare and your zip file is located there, you will need to copy
your file to your computer so that you can upload it to Canvas through your browser. For that,
you will need an FTP (File Transfer Protocol) client to securely transfer files from the UNIX
timeshare. A widely used secure FTP client is Filezilla. Installation instructions are found in the
site of FileZilla (make sure you install the distribution suitable for your operating system). After
Lab Assignment 1 CMPS 180 – Winter 2019 Due: 11:59pm Wednesday Jan 23
Page 4 of 4
opening the Filezilla client, you will need to set the host field to unix.ucsc.edu, the username to
your CruzId and the password to your Blue password, while the port number should be set to 22
(the default port for remote login). By clicking the Quickconnect button, if your credentials are
correct, you will connect and be able to see the contents of your remote Unix folder at the right
pane (under the title “Remote site”), while the left pane (under the title “Local site”) will display
the contents of your local file system. With the mouse, you can drag the file from the Unix folder
and drop it to the desired location at your computer. This will transfer the file to your local
machine, without erasing it from its original remote location. Filezilla is only one of several
options for an FTP client. If you are finding it difficult to install the necessary tools and
successfully do file transfers, you should promptly ask for help in the Lab Sections; do not
postpone this until the deadline date. The computers at the Lab also have pre-installed SSH and
FTP clients (PuTTY and PSFTP).
Other approaches to copy files includes using SCP (Secure Copy) and using Cut-and-Paste, where
you copy the contents of the file from the unix system, and then paste contents into a file on your
computer. Cut-and-Paste may work with for small files, but that’s a hack that does not work well
for large files.
The CMPS 180 Teaching Assistants, Saloni Rane and Golam Md Muktadir, will discuss
approaches to access unix remotely (SSH for Mac/Linux and PuTTY for Windows) and to move
files to your computer (SCP for Mac/Linux and Filezilla for Windows/Mac/Linux) with you
during Lab Sections. Attend your Lab Section to ensure that you know how to handle this
correctly!
4. Lab1 is due by 11:59pm on Wednesday, January 23. Late submissions will not be accepted
(Canvas won’t take them), and there will be no make-up Lab assignments. Check to make
sure that your submission is on Canvas, and that you’ve submitted the correct file. You will
receive no credit if you accidentally submit the wrong file, even if you attempt to “prove” that
you completed the correct file on time.