Lab Assignment 2 CMPS 182 

Lab Assignment 2 CMPS 182 
Page 1 of 5
1 Preliminaries
Before starting on this assignment, please be sure to read the General Instructions that are on Piazza
(under Resources->General Resources). If you did Lab1, you should already know how to log in to the
class PostgreSQL server. You’ll get help on Lab2 in your Discussion Section, not the Lectures, so be sure
to attend Discussion Sections.
2 Goal
The goal of the second assignment is to create a PostgreSQL data schema with 4 tables that are very
similar to the tables that you created in Lab1. The tables have the same names, attributes and data
types as the tables of Lab1, and the same primary keys but there are some UNIQUE constraints and
some restrictions on NULLs.
After you create the data schema with the 4 tables, you will be required to write some SQL statements
that use those tables. You have been given data that you can load into your tables so that you can test
the results of your queries. Testing can prove that a query is wrong, but not that it is right, so be
careful. Lab2 is due in two weeks, so you will have an opportunity to discuss the assignment during the
Discussion Section in the first week of the assignment, and to discuss issues you have had in writing a
solution to the assignment during the Discussion Section of the second week. Instructions for
submitting the assignment appear at the end of this document.
3 Lab2 Description
3.1 Create PostgreSQL Schema Lab2
You will create a Lab2 schema to set apart the database tables created in this lab from ones you will
create in future, 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 different from the general meaning. See here for
more details on PostgreSQL schemas. You create the Lab2 schema like this:
CREATE SCHEMA Lab2;
Now that you have created the schema, you want to set Lab2 to be your default schema when you use
psql. If you do not set Lab2 as the default schema, then you will have to qualify your table names with
the schema name (e.g. Lab2.Customers). To set the default schema, you modify your search path. (For
more details, see here.)
ALTER ROLE username SET SEARCH_PATH to Lab2;
You do not to have include the CREATE SCHEMA or ALTER ROLE statements in your solution.
Lab Assignment 2 CMPS 182 – Spring 2018 Due: Sunday, April 29, 11:59pm
Page 2 of 5
3.2 Create tables
You will create tables in schema Lab2 for the tables ChirpUsers, ChirpPosts, ChirpFollowers and
ChirpReads. The attributes of the 4 tables are the same as the tables of Lab1. Data types for the
attribute names in these tables are also the same as the ones specified for the tables of Lab1. The
Primary Keys are also the same. However, the tables must have the additional constraints described in
the next section.
3.2.1 Constraints
The following attributes cannot be NULL. All other attributes can be (but remember that attributes in
Primary Keys also cannot be NULL).
• In ChirpUsers: userPassword and userName
• In ChirpPosts: thePost
Also, the following must be unique for the specified table. That is, there cannot be identical rows in that
table that have exactly the same (non-NULL) values for all of those attributes (composite unique
constraint).
• In ChirpUsers: the attribute userName
• In ChirpUsers: the attribute spouseID
• In ChirpUsers: the 2 attributes joinDate and address
For example, the third constraint says that there can’t be two rows in ChirpUsers that have the same
values for both JoinData and address if both joinData and address are not NULL. Think of this as saying
that two people at the same address can’t join ChirpBase on the same date.
You will write a CREATE TABLE command for each of the 4 tables. Save the commands in the file
create.sql
Lab Assignment 2 CMPS 182 – Spring 2018 Due: Sunday, April 29, 11:59pm
Page 3 of 5
4 SQL Queries
Below are English descriptions of the five SQL queries that you need to write for this assignment, which
you will include in files queryX.sql, where X is the number of the query, e.g., your SQL statement for
Query 1 will be in the file query1.sql and so forth. Follow the directions as given. You will lose points if
you give extra tuples or attributes in your results, if you give attributes in with the wrong names or in
the wrong order, or if you have missing or wrong results.
4.1 Query 1
Find all the users whose address has the string 'West' anywhere in their address. For each such user,
your result should include just the user's name and the date that the user joined. The result should
appear sorted by join date, with the most recent join date first and the least recent join date last.
4.2 Query 2
Output the uncensored posts made by active users whose income is more than 50000. Your result should
have the user's name and the post.
4.3 Query 3
ChirpFollower tells you if user B follows user A. Sometimes user B follows user A and user A also follows
user B. Just using ChirpFollower, find pairs of userID's where B follows A and A also follows B. Yes, that
means that you'll have both A, B and B, A in your result. The two attributes in your result should be
called FirstUser and SecondUser.
4.4 Query 4
Find the name and address of Readers who have read posts written by a user whose name is 'Bill'. Your
result should not have any duplicates.
4.5 Query 5
If a row appears in ChirpsRead, then the postReader must have read the post (which is identified by
posterID and postNum) at least once. Find the userID and userName for every different user who has
read at least one post written by at least one of their followers.
Lab Assignment 2 CMPS 182 – Spring 2018 Due: Sunday, April 29, 11:59pm
Page 4 of 5
5 Testing
While your solution is still a work in progress, it is a good idea to drop all objects from the database
every time you run the script, so you can start fresh. Of course, dropping each object may be tedious,
and sometimes there may be a particular order in which objects must be dropped. The following
commands (which you can put at the top of create.sql if you want, but you don’t have to), will drop your
Lab2 schema (and all objects within it), and then create the (empty) schema again:
DROP SCHEMA Lab2 CASCADE;
CREATE SCHEMA Lab2;
Before you submit, login to your database via psql and execute your script. As you’ve learned already,
the command to execute a script is: \i <filename>.
Under Resources→Lab2, we have also provided a load script named lab2_date_loading.sql, that loads
data into the 4 tables of the database. You can execute the script with the command:
\i lab2_data_loading.sql.
You should test your 5 queries using that data. (You will have to figure out whether answers are
correct on your own.) But you may want to test your SQL statements on your own data as well.
Lab Assignment 2 CMPS 182 – Spring 2018 Due: Sunday, April 29, 11:59pm
Page 5 of 5
6 Submitting
1. Save your scripts for table creations and query statements as create.sql and query1.sql through
query5.sql You may add informative comments inside your scripts if you want (the server
interprets lines that start with two hyphens as comment lines).
2. Zip the file(s) to a single file with name Lab2_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
Lab2 should be named Lab2_1234567.zip
To generate the zip file you can use the Unix command:
 zip Lab2_1234567 create.sql query1.sql query2.sql query3.sql query4.sql query5.sql
(Of course, you use your own student ID, not 1234567.)
3. You should already know how to transfer the files from the UNIX timeshare to your local machine
before submitting to Canvas. If you are still not familiar with the process, use the instructions we
provided at the Lab1 assignment.
4. Lab2 is due by 11:59pm on Sunday, April 29. Late submissions will not be accepted, and there
will be no make-up Lab assignments.
5. You may lose credit if your queries are unnecessarily complex, even if they are correct. For
example, if you use DISTINCT but it’s not needed, you might lose half a point. Of course, you will
also lose credit if DISTINCT is needed and you omit it.
6. Be sure to follow directions about Academic Integrity that are in the Syllabus and Lecture1. If
you have any questions about those directions, please speak to the instructor as soon as possible.