Lab Assignment 2 Solution

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.






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





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.


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.








Submitting



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).



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.)




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.



Lab2 is due by 11:59pm on . Late submissions will not be accepted, and there will be no make-up Lab assignments.



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.



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.