Description
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
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
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 . 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
.5. Submitting1. Save your script as create.sql You may add informative comments to your scripts if you want. Putany 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-digitstudent ID. For example, if a student’s ID is 1234567, then the file that this student submits forLab1 should be named Lab1_1234567.zipIf you have a README file (which is not required), you can use the Unix command:zip Lab1_1234567 create.sql READMEIf 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 toCanvas for CMPS 180. Registered students should automatically have access; students who arenot registered in CMPS 180 will not have access. No students will be admitted to CMPS 180 afterthe Lab1 due date.If you are working on the UNIX timeshare and your zip file is located there, you will need to copyyour 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 UNIXtimeshare. A widely used secure FTP client is Filezilla. Installation instructions are found in thesite of FileZilla (make sure you install the distribution suitable for your operating system). AfterLab Assignment 1 CMPS 180Page 4 of 4opening the Filezilla client, you will need to set the host field to unix.ucsc.edu, the username toyour 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 arecorrect, you will connect and be able to see the contents of your remote Unix folder at the rightpane (under the title “Remote site”), while the left pane (under the title “Local site”) will displaythe contents of your local file system. With the mouse, you can drag the file from the Unix folderand drop it to the desired location at your computer. This will transfer the file to your localmachine, without erasing it from its original remote location. Filezilla is only one of severaloptions for an FTP client. If you are finding it difficult to install the necessary tools andsuccessfully do file transfers, you should promptly ask for help in the Lab Sections; do notpostpone this until the deadline date. The computers at the Lab also have pre-installed SSH andFTP clients (PuTTY and PSFTP).Other approaches to copy files includes using SCP (Secure Copy) and using Cut-and-Paste, whereyou copy the contents of the file from the unix system, and then paste contents into a file on yourcomputer. Cut-and-Paste may work with for small files, but that’s a hack that does not work wellfor large files.The CMPS 180 Teaching Assistants, Saloni Rane and Golam Md Muktadir, will discussapproaches to access unix remotely (SSH for Mac/Linux and PuTTY for Windows) and to movefiles to your computer (SCP for Mac/Linux and Filezilla for Windows/Mac/Linux) with youduring Lab Sections. Attend your Lab Section to ensure that you know how to handle thiscorrectly!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 makesure that your submission is on Canvas, and that you’ve submitted the correct file. You willreceive no credit if you accidentally submit the wrong file, even if you attempt to “prove” thatyou completed the correct file on time.
