sql代写-CSE 182-Assignment 3
时间:2022-05-16
Lab Assignment 3 CSE 182 - Spring 2022 Due: 11:59pm Tuesday, May 17
Page 1 of 8
1 Preliminaries
In this lab, you will work with a SpotMe database schema similar to the schema that you used in Lab2. We’ve
provided a lab3_create.sql script for you to use (which is similar to, but not quite the same as the create.sql in
our Lab2 solution), so that everyone can start from the same place. Please remember to DROP and CREATE
the Lab3 schema before running that script (as you did in previous labs), and also execute:

ALTER ROLE yourlogin SET SEARCH_PATH TO Lab3;

so that you’ll always be using the Lab3 schema without having to mention it whenever you refer to a table.
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.)
Soon, we’ll also provide a lab3_data_loading.sql script that will load data into your tables. You’ll need to run
that script before executing Lab3. The command to execute a script is: \i
In Lab3, you will be required to combine new data (as explained below) into one of the tables. You will need
to add some new constraints to the database and do some unit testing to see that the constraints are followed.
You will also create and query a view, and create an index.
New goals for Lab3:
1. Perform SQL to “combine data” from two tables
2. Add foreign key constraints
3. Add general constraints
4. Write unit tests for constraints
5. Create and query a view
6. Create an index
There are lots of parts in this assignment, but none of them should be difficult. Lab3 will be discussed during
the Lab Sections before the due date, Tuesday, May 17. The due date of Lab3 is 3 weeks after the due date
of Lab2 because students want to study for the Midterm, and also because we didn’t cover all of the Lab3
topics before the Midterm.

Lab Assignment 3 CSE 182 - Spring 2022 Due: 11:59pm Tuesday, May 17
Page 2 of 8
2. Description
2.1 Tables with Primary Keys for Lab3
The primary key for each table is underlined. The attributes are the same ones from Lab2, but there’s one table
that you haven’t seen before.

In the lab3_create.sql file that we’ve provided under Resources→Lab3, the first 6 tables are similar to the
tables were in our Lab2 solution, except that the NULL and UNIQUE constraints from Lab2 are not
included. Also, lab3_create.sql does not have the three Foreign Key Constraints on the Horses table
that were in our Lab2 solution. (You’ll create new variations of those constraints in Lab3.)
In practice, primary keys, unique constraints and other constraints are almost always entered when tables are
created, not added later. lab3_create.sql handles some constraints for you, but, you will be adding some
additional constraints to these tables in Lab3, as described below.
Note also that there is an additional table, ChangeRacingPersons, in the lab3_create.sql file that has most (but
not all) of the attributes that are in the RacingPersons table. We’ll say more about ChangeRacingPersons
below.
Under Resources→Lab3, you’ll also be given a load script named lab3_load_data that loads tuples into the 7
tables of the schema. You must run both lab3_create.sql and lab3_load_data.sql before you run the
parts of Lab3 that are described below.

RacingPersons(personID, personName, registryDate, canBeJockey, canBeTrainer)
Stables(stableID, stableName, address, stableOwnerID)
Horses(horseID, horseName, horseBreed, birthDate, stableID, trainerID, horseOwnerID)
Racetracks(racetrackID, trackName, address, trackDistance)
Races(racetrackID, raceDate, raceNum, raceStartTime, winningPrize)
HorseRaceResults(racetrackID, raceDate, raceNum, horseID, jockeyID, finishPosition,
raceFinishTime)


ChangeRacingPersons(personID, personName, registryDate)
Lab Assignment 3 CSE 182 - Spring 2022 Due: 11:59pm Tuesday, May 17
Page 3 of 8
2.2 Combine Data
Write a file, combine.sql (which should have multiple SQL statements that are in a Serializable transaction)
that will do the following. For each tuple in ChangeRacingPersons, there might already be a tuple in the
RacingPersons table that has the same primary key (that is, the same value for personID). If there isn’t a tuple
in RacingPersons with the same primary key, then this is a new racing person that should be inserted into
RacingPersons. If there already is a tuple in RacingPersons with that primary key, then this is an update of
information about that racing person. So here are the effects that your transaction should have:
• If there isn’t already a tuple in the RacingPersons table which has that personID, then you should insert a
tuple into the RacingPersons table corresponding to all the attribute values in that ChangeRacingPersons
tuple. Your insert should also set canBeJockey to TRUE and canBeTrainer to TRUE.
• If there already is a tuple in the RacingPersons table which has that personID, then update the tuple in
RacingPersons that has that personID. Update personName and registryDate for that existing
RacingPersons tuple to equal the corresponding attribute values in the ChangeRacingPersons tuple. Also,
set the value of canBeTrainer in the existing RacingPersons tuple to TRUE. Don’t change the value of
canBeJockey in the existing RacingPersons tuple.
Your transaction may have multiple statements in it. The SQL constructs that we’ve already discussed in class
are sufficient for you to do this part (which is one of the hardest parts of Lab3).

2.3 Add Foreign Key Constraints
Important: Before running Sections 2.3, 2.4 and 2.5, recreate the Lab3 schema using the lab3_create.sql
script, and load the data using the script lab3_data_loading.sql. That way, any database changes that you’ve
done for Combine won’t propagate to these other parts of Lab3.
Here’s a description of the Foreign Keys that you need to add for this assignment. (Foreign Key Constraints are
also referred to as Referential Integrity constraints.) The lab3_create.sql file that we’ve provided for Lab3
includes only some of the Referential Integrity constraints that were in the Lab2 solution, but you’re asked to
use ALTER to add additional constraints to the Lab3 schema.
The load data that you’re provided with should not cause any errors when you add these constraint. Just add the
constraints listed below, exactly as described, even if you think that additional Referential Integrity constraints
should exist. Note that (for example) when we say that every stable (stableID) in the Horses table must appear
in the Stables table, that means that the stableID attribute of the Horses table is a Foreign Key referring to the
Primary Key of the Stables table (which also is stableID).
• Each stable (stableID) in the Horses table must appear in the Stables table as a Primary Key (stableID).
o If a tuple in the Stables table is deleted, and there are Horses tuples that correspond to that
stable, then that Stables tuple deletion should be rejected .
o If the Primary Key stableID of a Stables tuple is updated, then all corresponding horses in
Horses should also be updated to the new stableID value .


Lab Assignment 3 CSE 182 - Spring 2022 Due: 11:59pm Tuesday, May 17
Page 4 of 8
• Each trainer (trainerID) that appears in the Horses table must also appear in the RacingPersons table as
a Primary Key (personID).
o If a tuple in the RacingPersons table is deleted, then all corresponding Horses tuples which
have that person as their trainerID should have their trainerID set to NULL.
o If the Primary Key (personID) of a RacingPersons tuple is updated, and there are tuples in
Horses which have that person as their trainerID, then all corresponding Horses tuples which
have that person as their trainerID should also be updated to have the new personID value as
their trainerID.

• Each horse owner (horseOwnerID) that appears in the Horses table must also appear in the
RacingPersons table as a Primary Key (personID).
o If a tuple in the RacingPersons table is deleted, then all corresponding Horses tuples which
have that person as their horseOwnerID should also be deleted.
o If the Primary Key (personID) of a RacingPersons tuple is updated, and there are tuples in
Horses which have that person as their horseOwnerID, then that RacingPersons tuple update
should be rejected

Write commands to add foreign key constraints in the same order that the foreign keys are described above.
Your foreign key constraints should have names, but you may choose any names that you like. Save your
commands to the file foreign.sql

2.4 Add General Constraints
General constraints for Lab3 are:
1. In RaceTracks, trackDistance must be positive. (You all should know that 0 isn’t positive.) Please give a
name to this constraint when you create it. We recommend that you use the name positiveTrackDistance, but
you may use a different name if you’d like.
2. In Horses, the trainer of the horse and the owner of the horse must not be the same person. (But it’s okay for
either or both to be NULL.) Please give a name to this constraint when you create it. We recommend that you
use the name notBothOwnerTrainer, but you may use a different name if you’d like.
3. In Races, if raceDate is December 25, 2021 then winningPrize must be more than 12000. Please give a name
to this constraint when you create it. We recommend that you use the name bigChristmasPrize, but you may
use a different name if you’d like.
Write commands to add general constraints in the order the constraints are described above, and save your
commands to the file general.sql. Note that the values TRUE and UNKNOWN are okay for a CHECK
constraint is okay, but FALSE isn’t.

Lab Assignment 3 CSE 182 - Spring 2022 Due: 11:59pm Tuesday, May 17
Page 5 of 8
2.5 Write Unit Tests
Unit tests are important for verifying that your constraints are working as you expect. We will require tests for
just a few common cases, but there are many more unit tests that are possible.
For each of the 3 foreign key constraints specified in section 2.3, write one unit test:
o An INSERT command that violates the foreign key constraint (and elicits an error). You must violate
that specific foreign key constraint, not any other constraint.
Also, for each of the 3 general constraints, write 2 unit tests, with 2 tests for the first general constraint,
followed by 2 tests for the second general constraint, followed by 2 tests for the third general constraint.
o An UPDATE command that meets the constraint.
o An UPDATE command that violates the constraint (and elicits an error).
Save these 3 + 6 = 9 unit tests in the order specified above in the file unittests.sql.
Lab Assignment 3 CSE 182 - Spring 2022 Due: 11:59pm Tuesday, May 17
Page 6 of 8
2.6 Working with a View
Important: Before doing this section, recreate the Lab3 schema once again using the lab3_create.sql script, and
load the data using the script lab3_data_loading.sql. That way, any changes that you’ve done for previous parts
of Lab3 (e.g., Unit Test) won’t affect results in this section of Lab3.
2.6.1 Create a view
A horse finishes a race in last place if its finishPosition is greater than or equal to the finishPosition of all the
horses in that race. (It is possible that more than one horse finishes last in a race, since there can be ties. One
of the UNIQUE constraints in Lab2 would have prevented that, but we don’t have that constraint in Lab3.)
Create a view called LastPlaceHorsesView that has five attributes, horseID, racetrackID, raceDate, raceNum,
finishPosition. This view should have a tuple for each horse that finished last in the (racetrackID, raceDate,
raceNum) horse race. Your view should have no duplicates in it.
Save the script for creating that view in a file called createview.sql


Lab Assignment 3 CSE 182 - Spring 2022 Due: 11:59pm Tuesday, May 17
Page 7 of 8
2.6.2 Query a View
For this part of Lab3, you’ll write a script called queryview.sql that contains a query that uses
LastPlaceHorsesView and (possibly) some tables. In addition to that query, you must also include some
comments inside your queryview.sql script; we’ll describe those necessary comments below.
For each horse, we’d like to find out the number of races in which that horse finished last. Write and run a SQL
query (which we’ll call “CountLastPlaces” that uses LastPlaceHorsesView to determines for each horse the
number of races in which that horse finished last. You may want to use some tables to write this query, but be
sure to use the view.
The output of your “CountLastPlaces” query should be the horseID, the name of the horse, the number of races
in which it finished last and the maximum finishPlace value for the races in which it finished last. The result
attributes should appear as horseID, horseName, lastCount and maxLastPlaceFinish. But you should include a
horse in your result only if its maxLastPlaceFinish is 3 or more. No duplicates should appear in your result.
Then write the results of the “CountLastPlaces” query in a comment. The comment should contain the
complete output when you execute that query.
Next, write commands that delete just the tuples that have the following Primary Keys from the
HorseRaceResults table:
• The HorseRaceResults tuple whose Primary Key is (3008, DATE ‘2022-02-26’, 2 ,555).
• The HorseRaceResults tuple whose Primary Key is (3001, DATE ‘2021-08-11’, 1, 551).
Run the “CountLastPlaces” query once again after those deletions. Write the output of the query in a second
comment. Do you get a different answer?
You need to submit a script named queryview.sql containing your “CountLastPlaces” query on the view … and
more. In that file you should include all of the following, in order:
• Your “CountLastPlaces” query.
• A first comment with the output of the “CountLastPlaces” query on the load data before the deletions.
• The SQL statements that delete the two tuples indicated above,.
• The same “CountLastPlaces” query again.
• And a second comment with the second output of the same “CountLastPlaces” query on the load data
after the deletions.
It probably was a lot easier to write the “CountLastPlaces” query using the LastPlaceHorsesView view than it
would have been if you hadn’t had it!

Lab Assignment 3 CSE 182 - Spring 2022 Due: 11:59pm Tuesday, May 17
Page 8 of 8
2.7 Create an Index
Indexes are data structures used by the database to improve query performance. Locating the tuples in the
HorseRaceResults table for a particular horseID and finishPosition might be slow if the database system has to
search the entire HorseRaceResults table (if the number of HorseRaceResults was very large). To speed up that
search, create an index named HorsePositionTester over the horseID and finishPosition columns (in that order)
of the HorseRaceResults table. Save the command in the file createindex.sql.
Of course, you can run the same SQL statements whether or not this index exists; having indexes just changes
the performance of SQL statements. But this index could make it faster to determine the horse races in which a
horse finished in a particular position. (For example, find all the horse races which a particular horse won.) It
could also make it faster to get information about all the races in which a particular horse raced.
For this assignment, you need not do any searches that use the index, but if you’re interested, you might want to
do searches with and without the index, and look at query plans using EXPLAIN to see how queries are
executed. Please refer to the documentation of PostgreSQL on EXPLAIN that’s at
https://www.postgresql.org/docs/12/sql-explain.html

3 Testing
Before you submit, login to your database via psql and execute the provided database creation and load scripts,
and then test your seven scripts (combine.sql foreign.sql general.sql unittests.sql createview.sql queryview.sql
createindex.sql). Note that there are two sections in this document (both labeled Important) where you are
told to recreate the schema and reload the data before running that section, so that updates you performed
earlier won’t affect that section. Please be sure that you follow these directions, since your answers may be
incorrect if you don’t.

4 Submitting
1. Save your scripts indicated above as combine.sql foreign.sql general.sql unittests.sql createview.sql
queryview.sql createindex.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 files to a single file with name Lab3_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 Lab3 should be
named Lab3_1234567.zip To create the zip file you can use the Unix command:

zip Lab3_1234567 combine.sql foreign.sql general.sql unittests.sql createview.sql queryview.sql
createindex.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.
4. Lab3 is due on Canvas by 11:59pm on Tuesday, May 17. Late Submissions will not be accepted, and
there will be no make-up Lab assignments.


essay、essay代写