Project Assignment 3

Logistics and General Information

• Turning in your work: Unless otherwise specified, this lab assignment and all subsequent ones will be turned in electronically. We will use submit. Submit is accessed from your cats account. For information on using submit, enter "submit -m" from the console. Please keep in mind that the deadlines for submitting lab assignments are separate from the deadlines for written homework assignments. The following late policy applies to this and subsequent lab assignments:

  Lab assignment work must be submitted electronically by midnight at the end of the day that it is due. Programming work submitted after the deadline but before the "late deadline" -- midnight 48 hours after the deadline -- will be accepted but penalized 50%. No lab assignment work will be accepted after the late deadline.

• PostgreSQL (lack of) backups: PostgreSQL is not backed up, so anything you need long-term should be saved in the cats file system. Some suggestions for general management of your database application are given below. CATS-Instructional Computing warns: "Although system backups are performed, YOUR STUDENTS MUST BE SURE TO PERFORM THEIR OWN BACKUPS, as I will not restore anything from my own backups except in case of emergency and/or complete system failure." `

• SQL on PostgreSQL: If you decide to test your solutions to the SQL homework assignment exercises using PostgreSQL, or experiment with running some SQL queries on your database, note that PostgreSQL's implementation of SQL may differ somewhat from the SQL standard as covered in the textbook and class. See PostgreSQL feature compatibility list from MySQL's crash-me program.

• PostgreSQL for CS180: Students wishing to use PostgreSQL must connect with their CATS accounts to 'db.ic.ucsc.edu'. Students must use ssh (both versions 1 and 2 will work), as telnet is not enabled. SSH is an encrypted (safer) method of connecting to a remote host, as opposed to telnet which transmits and receives data unencrypted. Free and legitimate SSH software is available for almost all computing platforms. Once connected to db.ic.ucsc.edu, students will have full access to their CATS home directories via AFS, much the same way as when logged onto teach.ic.ucsc.edu and hawking.ic.ucsc.edu.

PostgreSQL

Note: For system maintenance reasons, you are not allowed to create a new database by using the createdb command. A database has already been created for you with the same name as your user-name.

You can access your database with the command:

psql -a

The assignment

Part A

Description: This part comprises of the following questions:

1. Write down all non-trivial Functional Dependencies (FDs) that hold on your schema.
2. Pick one FD that holds on a specific table, say R. Provide two instances of R, one that violates the FD, and one that satisfies it. Then, write an SQL query which allows you to detect whether the FD holds or not, and run in over the two instances.
   Notes:
   • If you pick a key FD, then PostgreSQL will already do the dirty work for you: it will check for the uniqueness of the key, and it will not let you insert a duplicate tuple. Besides that, you will not be able to load an instance of R that violates the FD. You should, therefore, pick a non-key FD for this question.
   • You will need to delete all tuples from R before inserting the data of each case.
   • The query should produce some output that indicates whether the FD is violated or not. In what you turn in, you should indicate how the output of the query is interpreted in each case.
3. Given the FDs of question 1, check whether you schema is in BCNF. If not, then provide a BCNF decomposition of your tables.
4. Is your new schema in 3NF?

What to turn in: Please see Recording Your Session below for a guide to preparing output to be submitted for this and subsequent project parts. In this and all subsequent project parts, the material you turn in should be clearly formatted and delineated, and should include comments for any aspects that are not crystal clear. Poorly assembled or documented material will not receive full credit, even if it is correct. You also will not receive full credit if you turn in your entire large data files (or large query results in later assignments) when we ask for small samples. Other than comments, truncation, and simple formatting, it is Academic Dishonesty to edit scripts before turning them in.

For this part of the assignment the following files should be turned in electronically using your cats account and the submit program:

README	Please give your name, cats account, project part number, date, a list and description of the files you are submitting, and any other information that will be useful for the grader.
answers.txt	A text file containing the answers to questions 1-4. For question 1, you should list each functional dependency in a separate line, using the notation R: X-->Y, where R is the table name, and X and Y are attribute sets from R. For question 2, indicate which functional dependency you will examine, then list the checking SQL query and explain how its output indicates whether the FD is violated or not. For question 3, if your schema is not in BCNF, then list the violating functional dependencies. For question 4, if your schema is not in 3NF, then list the violating functional dependencies.
fd_data_ok.script	The COPY command which loads the instance of R that does not violate the FD.
fd_data_violate.script	The COPY command which loads the instance of R that violates the FD.
fd_query.script	The script file that contains the query to check for the FD of question 1. Make sure to explain how the query works in the answers.txt file.
fd_query_violate.log	The log file showing the execution of the checking query on the instance that violates the FD.
fd_query_ok.log	The log file showing the execution of the checking query on the instance that satisfies the FD.
bcnf_schema.script	The script file with the CREATE TABLE commands for the BCNF schema. If your schema is already in BCNF, then just insert the commands to create the original schema.

To submit these files use the syntax:

submit cmps180-np.w04 proj3-part1 README answers.txt fd_data_ok.script fd_data_violate.script fd_query.script fd_query_violate.log fd_query_ok.log bcnf_schema.script

or proj3-part1-late if your project part is late.

Part B

Description: In this part, you will need to insert constraints in your schema that will check for the integrity of your data. Before tackling this part, it will be useful to look at the PostgreSQL documentation regarding constraints.

Read this before attempting to define any constraints in PostgreSQL.

• The following constraints are given to you by El Cheapo:
  1. For any song, a higher bitrate should always be charged more.
  2. A client should not be allowed to have more than five credit cards.
  3. The expiration date on the credit card of any customer should always be in the future. Note: You might want to check out the documentation on retrieving the current date.
  4. The genre(s) of a song should appear in the genres for the artist(s) of the song.
  5. A rating can only take one out of 4 values. Note: The actual values depend on whether you represent ratings as strings or integers.
  6. An e-mail address should consist of a string, followed by '@', followed by another string. Note: The documentation on pattern matching might prove useful.
• For each constraint, insert a suitable CHECK clause in the schema definition of the corresponding table. Note: In order to reload your schema, you will have to DROP all the existing tables. Once your new schema is created, you will also need to reload your test data. You will have to make sure that the data does not violate any of the constraints that you have specified.
• For each constraint, provide an INSERT or UPDATE statement that violates the constraint.

What to turn in: Please see Recording Your Session below for a guide to preparing output to be submitted for this and subsequent project parts. In this and all subsequent project parts, the material you turn in should be clearly formatted and delineated, and should include comments for any aspects that are not crystal clear. Poorly assembled or documented material will not receive full credit, even if it is correct. You also will not receive full credit if you turn in your entire large data files (or large query results in later assignments) when we ask for small samples. Other than comments, truncation, and simple formatting, it is Academic Dishonesty to edit scripts before turning them in.

For this part of the assignment the following files should be turned in electronically using your cats account and the submit program:

README	Please give your name, cats account, project part number, date, a list and description of the files you are submitting, and any other information that will be useful for the grader.
constraint_tables.script	The script file with the updated CREATE TABLE commands for your schema. These commands should include the CHECK clauses that implement the constraints.
constraint_data.script	The script file with the COPY commands to populate your database. You can include the same test data as in Project Assignment 2, as long as they do not violate the constraints.
constraint_tables.log	An execution log showing that the execution of constraint_tables.script and constraint_data.script.
constraint_violates.script	The script file with the INSERT/UPDATE statements that violate the constraints.
constraint_violates.log	An execution log of the constraint_violates.script script.

To submit these files use the syntax:

submit cmps180-np.w04 proj3-part2 README constraint_tables.script constraint_tables.log constraint_data.script constraint_violates.script constraint_violates.log

or proj3-part2-late if your project part is late.

Part C

Description: For this part of the assignment, you will create a logical schema for a "special" group of users.

During your 120th meeting with El Cheapo executives, you are told that the system should support two different groups of users: on-line customers, who will browse the music store and buy songs, and market researchers, who will perform an analysis of the customers' buying trends. Since the second group of users are not SQL savvy, the executives would like to create a simplified logical schema for them, so that they can write simple SQL queries to get their answers. More specifically, an analyst needs to ask for the following information:

• The total amount of dollars paid for songs of a specific genre.
• The total amount of dollars paid from customers of a specific age (or range of ages), and for songs of a specific genre.
• The total number of purchases, and the total amount of purchases for a specific artist.
• For customers of a specifc age (or range of ages), the number of purchases for songs that belong to one of the customers' favorite genres.
• For a specific song title, its total rating, and its total amount of purchases.

Your task is to define a logical schema (i.e., a set of views) which will make this information available to the analysts. You also need to show to the executives how the previous questions can be answered by SQL queries over the logical schema. Keep in mind that an analyst does not know what a join is, and the most complex SQL query that he/she can write can only involve selection predicates! In order to make the demonstration more believable, you should alter your test data so that each view contains at least five tuples in total.

What to turn in: Please see Recording Your Session below for a guide to preparing output to be submitted for this and subsequent project parts. In this and all subsequent project parts, the material you turn in should be clearly formatted and delineated, and should include comments for any aspects that are not crystal clear. Poorly assembled or documented material will not receive full credit, even if it is correct. You also will not receive full credit if you turn in your entire large data files (or large query results in later assignments) when we ask for small samples. Other than comments, truncation, and simple formatting, it is Academic Dishonesty to edit scripts before turning them in.

For this part of the assignment the following files should be turned in electronically using your cats account and the submit program:

README	Please give your name, cats account, project part number, date, a list and description of the files you are submitting, and any other information that will be useful for the grader.
logical_data.script	A script file with the necessary COPY commands, which will load the test data in the database.
logical_data.log	The execution log for logical_data.script. Note that you should delete all existing tuples from your database prior to loading this new test data.
logical_schema.script	The script file with the CREATE VIEW commands that create the logical schema for analysts.
logical_schema.log	The execution log for logical_schema.script.
logical_queries.script	The queries over the logical schema.
logical_queries.log	An execution log showing the execution and results of the queries in logical_queries.script.

To submit these files use the syntax:

submit cmps180-np.w04 proj3-part3 README logical_data.script logical_data.log logical_schema.script logical_schema.log logical_queries.log

or proj3-part3-late if your project part is late.

Maintaining your databases

drop table T;

If you want to get rid of all tuples in T without deleting the table itself, issue the command:

delete from T;

Recording Your Session

    script [ -a ] [ filename ]

    exit

Creating and running script files from psql

The execution script file you create can consist of most any series of commands which you could enter following the psql prompt. This includes all of the SQL commands which you will be using to create, modify and test your data base. Examples include the CREATE TABLE and SELECT commands. Just like when using the psql command line interface you must terminate each SQL command in your script file with a semicolon. \i and \q do not need semicolons.

If you are recording your session using the script command described above into a script log then it is useful to start psql using the -a option so that all commands included in your execution script file will be echoed to the console and thus to your script log file. Here is an example script file that creates a table (relation) named products:

	CREATE TABLE products (
	  productID INT,
	  name VARCHAR(80)
          price NUMERIC(10,2),
          retailPrice NUMERIC(10,2)
        );
      

	COPY products FROM stdin USING DELIMITERS '|';
	1419|American Greetings CreataCard Gold V4.0|21.49|25.24
	1424|Barbie(R) Nail Designer(TM)|20.74|25.99
	1427|Panzer Commander|21.99|30.24
	1431|Riven: The Sequel to Myst|31.99|40.24
	\.
      

Sample Script Files

createbeers.script

-- Sample Script file to Create and

-- Populate a BEERS DB

-- print out the current time

SELECT timeofday();

-- Create and Populate Tables

CREATE TABLE Beers (
  name VARCHAR(30),
  manf VARCHAR(50)
);

COPY Beers FROM stdin USING DELIMITERS '|';
Coors|Adolph Coors
Coors Lite|Adolph Coors
Miller|Miller Brewing
Miller Lite|Miller Brewing
MGD|Miller Brewing
Bud|Anheuser-Busch
Bud Lite|Anheuser-Busch
Michelob|Anheuser-Busch
Anchor Steam|Anchor Brewing
\.

CREATE TABLE Bars (
  name VARCHAR(30),
  addr VARCHAR(50),
  license VARCHAR(50)
);

COPY Bars FROM stdin USING DELIMITERS '|';
Joe's|123 Any Street|B7462A
Sue's|456 My Way|C5473S
\.

CREATE TABLE Sells (
  bar VARCHAR(20),
  beer VARCHAR(30),
  price REAL
);

COPY Sells FROM stdin USING DELIMITERS '|';
Joe's|Coors|2.50
Joe's|Bud|2.50
Joe's|Bud Lite|2.50
Joe's|Michelob|2.50
Joe's|Anchor Steam|3.50
Sue's|Coors|2.00
Sue's|Miller|2.00
\.

CREATE TABLE Drinkers (
  name VARCHAR(30),
  addr VARCHAR(50),
  phone CHAR(16)
);

COPY Drinkers FROM stdin USING DELIMITERS '|';
Bill Jones|180 Saint St.|831-459-1812
Kelly Arthur|180 Alto Pl.|650-856-2002
Fred|1234 Fifth St.|831-426-1956
\.

CREATE TABLE Likes (
  drinker VARCHAR(30),
  beer VARCHAR(30)
);

COPY Likes FROM stdin USING DELIMITERS '|';
Bill Jones|Miller
Bill Jones|Michelob
Kelly Arthur|Anchor Steam
Fred|MGD
\.

CREATE TABLE Frequents (
  drinker VARCHAR(30),
  bar VARCHAR(30)
);

COPY Frequents FROM stdin USING DELIMITERS '|';
Bill Jones|Joe's
Bill Jones|Sue's
Kelly Arthur|Joe's
\.

-- Execute some SELECT queries--

SELECT * FROM Bars;
SELECT * FROM Drinkers;

-- print out the current time
SELECT timeofday();

dropbeers.script

DROP TABLE Beers;
DROP TABLE Bars;
DROP TABLE Sells;
DROP TABLE Likes;
DROP TABLE Frequents;
DROP TABLE Drinkers;