Your team

This project will expand the database schema of your Project 1, Part 3. So ideally you will work with the same teammate as for Project 1. In this case, your TA mentor will be the same TA as for Project 1.

However, you are welcome to switch teammates if you wish. In this case, please be considerate and notify your Project 1 teammate immediately. You and your new teammate will have to decide whether to expand your Project 1, Part 3 schema or, alternatively, your new teammate's Project 1, Part 3 schema. The project that you will work on will "inherit" the TA mentor from Project 1.

In either case, you will carry out this project in teams of two. Both students in a team will receive the same grade for Project 2. Please check the Collaboration Policy web page for important information on what kinds of collaboration are allowed for projects, and how to compute the available number of project grace late days for a team. You can use project grace late days normally for this project.

Project description

In this project, you will learn about some of the advanced features—object-relational and otherwise—of the PostgreSQL database management system, and you will expand your final Project 1, Part 3 database schema—not your web front-end, even if you followed the Web Front-End Option for Part 3 of Project 1—to incorporate some of these features. Specifically, for this project you will expand your final database schema from Project 1 by adding to the schema your choice of 3 out of the 4 items below:

• Add to your schema at least one text attribute, over which you can do full-text search, as described here. (You can focus on Sections 12.1 and 12.2.) You can either add the new text attribute to one of your existing tables using ALTER TABLE or you can create a new table altogether that contains one text attribute (and other attributes, of course). In either case, this text attribute should hold document-style data (i.e., a paragraph or more of natural language text, such as in a news article); the text attribute should not hold regular strings. For example, it is OK for the new attribute to correspond to, say, the text of a product review by a user; it is not OK for the new attribute to correspond to a product name or a userid, which would be more naturally modeled with a regular VARCHAR attribute. Text search functions and operators in PostgreSQL are documented here. Note: You should use single quotes for inserting text values.
• Add to your schema at least one array attribute. You can either add the new array attribute to one of your existing tables using ALTER TABLE or you can create a new table altogether that contains one array attribute (and other attributes, of course). Array functions and operators in PostgreSQL are documented here. Note: PostgreSQL uses a "one-based" numbering convention for arrays (i.e., an array of n elements will have them numbered from 1 through n by default).
• Define a new composite type using CREATE TYPE and create a new table in your schema of that type (see the "CREATE TABLE employees OF employee_type..." example here).
• Define a trigger in your database using CREATE TRIGGER. PostgreSQL requires that the "action" part of the trigger be carried out by a user-defined function, so you will have to also write such a user-defined function using CREATE FUNCTION. For our project, you should write this function using PL/pgSQL, PostgreSQL's "SQL Procedural Language." You can find a discussion on how to define trigger procedures in this simple language here.

For this project, you should:

1. Expand your final Project 1 schema on our PostgreSQL server with your choice of 3 out of the 4 items above.
2. Add data to your database so that (a) each new table that you created has at least 10 tuples and (b) each new attribute that you added to existing tables has meaningful values for all tuples in the tables.
3. Write a thorough, revealing README file following the instructions below.

Needless to say, your additions to your schema—including the definition of a trigger, if you choose to add a trigger—should be a meaningful, integrated addition to your Project 1 database. Your final schema should include all the relations and constraints from Part 3 of Project 1, plus the additional features specified above. You will not demonstrate or expand any web front-end for your database, but rather just expand your database schema. You are welcome and encouraged to discuss your additions to your Project 1, Part 3 schema with your TA mentor.

Important Note 1: The additions to your schema described above indeed need to be additions, not already present in your Project 1 schema. For example, if your Project 1 schema already included a text attribute, you cannot use this attribute to count as "an addition" for Project 2. Instead, you need to follow the guidelines above to truly expand your Project 1 schema with new features not already present in it.

Important Note 2: If your Project 1 schema has an attribute that would be most naturally modeled as having a text type, then it is OK for you to change the type of the attribute to a text type for Project 2 and have this changed attribute count as one of your additions for the project. (The same comment applies to array.) However, you should only do this if the new type, text (or array), is indeed a good, natural choice for the attribute according to the guidelines above.

What to submit and when

You will submit this part of the project on Gradescope. The deadline is Friday, December 8, at 5 p.m. ET. Just as for Project 1, you should submit your project exactly once per team, rather than once per student. (Click on "Add Group Member" after one of you has submitted your project.) You should submit a single (uncompressed) README file (.txt, .pdf, or .md) containing:

1. The name and UNI of both teammates. If you changed teammates with respect to Project 1, please indicate whose Project 1 project you have expanded for Project 2 and who the TA mentor for the project was.
2. The name of the PostgreSQL account where your database is on our server (i.e., specify which teammate's UNI we should use to identify the database for your team). This is the database on which we will base our grading.
3. A thorough explanation of the three items above with which you expanded your project. Explain carefully your rationale behind your modifications to the schema and how these modifications fit within your overall project.
4. The SQL statements that you used to create each of the three items (i.e., the CREATE TYPE, CREATE TRIGGER, CREATE FUNCTION, ALTER TABLE statements with which you added the three items to your schema); you don't need to include the SQL statements for adding tuples to your database.
5. If you added a trigger, explain carefully what it is meant to achieve and why. Also include in your README file a real example of an "event" (i.e., an insertion, deletion, or update of a relation in your database, as specified in your trigger definition) that causes the trigger to be executed, together with a clear explanation of what the trigger does as a result of the event, including listing clearly any modifications to the database that happen as part of the trigger. Your description should be detailed enough so that we can recreate on your PostgreSQL database the execution of the trigger exactly as you describe it, and part of your grade will be based on the quality and accuracy of this description.
6. Substantial, meaningful queries involving the new attributes and tables in your schema, with a sentence or two per query explaining what the query is supposed to compute. If one of your three added items is a trigger, then you need to submit two queries (in addition to the trigger information in the previous bullet); otherwise, you need to submit three queries. All your new attributes and tables should appear at least once in one of the queries that you submit. For a text attribute, make sure at least one of your queries uses full-text search, as described here. For an array attribute, make sure at least one of your queries accesses elements in the array. Overall, your queries should work over your PostgreSQL database as submitted. We will run them against your database and part of your grade will be based on them, so please choose your queries carefully. We strongly suggest that you submit well formed queries that run without problems, so please make sure that you have tested your queries by running them on your database exactly as submitted (use copy and paste).

Grading

You will be graded on:

• The quality of your expansion of your Project 1 design with three advanced features, as specified above; to get full credit, you should add such features to your Project 1 design in a natural and meaningful way.
• The quality of the real-world (or at least realistic) data that you added to the database—expanding on the data that is already in your database from Project 1, which you should keep—to use the new features, as specified in Item 2 in the "Project description" above.
• The quality of your README file, including the descriptions, queries, and other features specified in the "What to submit and when" section above.