CISC 3810 Assignment 6

  • 0. Print the GIFTS table from Assignment 6 in PK order. Label your output as HW6 step 0.
  • 1a. Create a DONATES table (give the CREATE TABLE statement you use) for information about gifts that people have donated. The table will contain the name of a person giving a gift (the donor), the name of a person getting the gift( the receiver), and the name of a gift. A person is allowed to give one or more gifts; a person is allowed to receive one or more gifts; but any one person is allowed to donate to any other just once. Each gift mentioned should appear in the GIFTS table. (What is the PK for this table?) Label your output as HW6step 1a.
  • 1b. Enter at least 20 rows of (valid) data into the table. Show one INSERT statement. Label your output as HW6step 1b.
  • 1c. Print the table three ways (give the SQL for each method of printing):
    • i - ordered by donor (desc) and then receiver (also desc).
    • ii - ordered by receiver and then donor (both asc)
    • iii - ordered by gift, then donor, then receiver
    • Label your output as HW6step 1c. You should have THREE outputs.
  • 2. Make sure you have at least one row (and preferably two) satisfying each query below: In each part, print whatever field is asked for, plus any other relevant fields.
    • Give the name of each donor and the number of gifts that person donated.
    • Give the name of each receiver and the number of gifts that person received.
    • Give the names of people who donated a gift and received a gift.
    • Give the names of people who donated a gift but did not receive any gift.
    • Give the names of people who received a gift but did not donate any gift.
    • Give the names of people who donated a gift to themselves.
    • Give the names of people who donated a gift to themselves and at least one gift to another person.
    • Give the names of people who donated and received the same gift, either to themselves or to someone else.
    • Give the names of people who donated and received the same gift, but the donation was to someone else and the gift was from someone else.
    • Give the names of people who donated the same gift more than one time.
    • Label your output as HW6 step 2(a) thru 2(j) as appropriate
  • 3. Print each part below ordered by donor's name and then receiver's name:
    • For each gift donation, give donor, receiver, gift name, retail and sales price of the gift.
    • Find the person who donated the gift with the highest retail price. Print all of the relevant information.
    • Repeat part b, but this time do the gift with the lowest sales price.
    • Label your output as HW6 step 3(a) thru 3(c) as appropriate
  • 4. List the rows in DONATES for which the name of the gift appears in DONATES but the name of the gift does not appear in GIFTS. You should verify that there are no rows in this query (why?). This type of check is called referential integrity. Label your output as HW6 step 4
  • 5. Add new rows to DONATES which will violate referential integrity. That is, add several new rows to the table which will satisfy the query from Part 4. (Don’t show the SQL INSERT commands.)
    • Print the entire DONATES table with the new rows circled. Label your output as HW6 step 5a
    • Rerun the query from Part 4. Show the results. Label your output as HW6 step 5b
    • Delete the new rows (so you are back to the original DONATES table). Give the SQL commands to delete the rows. Print the DONATES table again after the deletions (so you are back to the original table). Label your output as HW6 step 5c
  • 6a. Using the ALTER TABLE command, add a constraint to the DONATES table. The new constraint should say that the gift field in DONATES is a foreign key, referencing the gift field in the GIFTS table. Label your output as HW6 step 6a
  • b. Try to repeat step 5. What happens? Label your output as HW6 step 6b
  • 7. Using the sys.objects table, give the name, object_id, type, create_date, and type_desc columns for all rows which you created yourself. By hand, circle any rows that were added in this assignment. Label your output as HW6 step 7
Academic Honesty!
It is not our intention to break the school's academic policy. Posted solutions are meant to be used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly complete the form. Please provide a valid email address and we will get back to you within 24 hours. Payment is through PayPal, Buy me a Coffee or Cryptocurrency. We are a nonprofit organization however we need funds to keep this organization operating and to be able to complete our research and development projects.