Assessment Brief
Database Design and Management I Summative Assessment 1
ASSESSMENT DETAILS
| Course Title: | Database Design and Management I |
| Course Code: | |
| Course Leaders: | |
| Level: | |
| Assessment Title: | Summative 1: Set computational exercises |
| Assessment Number: | |
| Assessment Type: | Set computational exercises |
| Restrictions on Time/Length: | |
| Individual/Group: | Individual |
| Assessment Weighting: | 60% |
| Issue Date: | 04 July 2022, 08:00 |
| Hand in Date: | 12 August 2022, 23:59 |
| Planned Feedback Date: | 09 September 2022, 17:30 |
| Mode of Submission: | Canvas |
| Anonymous Marking: | YES |
When completed, please upload Summative 1 Assessment as a PDF file by the date shown in the table above.
Do not include your name in the file. ASSESSMENT TASK
In this assignment you will be required to retrieve and process information from a database called ‘Auction’ using Oracle Live SQL.
Information on how to access and use Oracle Live SQL is shown in Module 2. Summative 1 Assessment
It is recommended to become familiar with Oracle Live SQL before attempting to answer the questions in this assignment.
The ‘Auction’ database script (AUCTION_OLS.sql) can be downloaded from the Syllabus
page in the left-navigation in Canvas.
You will need to upload the script into Oracle Live SQL via the My Scripts tab when you are logged in. Note that the next time you log into Oracle Live SQL, the ‘Auction’ database
script should still reside in the My Scripts tab so that you can rerun it, and then continue with the assignment.
The figure below shows 6 tables that reside inside the ‘Auction’ database. The ‘Table’ column contains the names of the 6 tables inside the database. The ‘Attributes’ column contains the names of the attributes (columns) inside each respective table. Any attribute in bold is a primary key; any attribute in italics is a foreign key.
| Table | Attributes |
| Bidder | BidderID, FirstName, LastName, Address, City, Zip, InPartyOf, PaymentAccount. |
| Bids | ItemID, BidderID, BidAmount. |
| Donor | DonorID, Name, Address, City, Zip. |
| ItemCategories | CategoryID, CategoryName |
| Items | ItemID, PackageID, CategoryID, Name, Value, DonorID, Description. |
| Tickets | BidderID, TicketType, Quantity. |
For each question, you must submit the following:
- The answer to the question generated by querying the database.
o If the answer is a table, please limit the output to 15 rows (unless the question specifies an actual limit).
- The full SQL statement employed in Oracle Live SQL to generate the respective answer.
- A clear explanation of what actions the SQL keywords and SQL clauses within the SQL statement are performing, for example if there is a specific join or a subquery taking place and how the data is being processed (e.g. only values above a specific amount are being retained).
- A short justification of why you chose that particular SQL statement.
- List the top six categories based on the number of items they contain.
- For each donor, list the item(s) and the corresponding retail value.
- List the first 20 item names by category along with the number of each of these item names in each category.
- For example you may find that there are 3 Crystal Clear items inside the Art Category.
- List the first 15 bidders by full name (in alphabetical order) that actually purchased an item (or items), what items they purchased, and the amount that was bid for that item.
- How much did each successful bidder spend, including tickets, each priced at £3? Summative 1 Assessment
- List the first 15 bidders by name (in alphabetical order) when showing the amount bid, ticket spend, and corresponding total spend for each successful bidder.
- For bidders that belonged to a group, e.g. a collection of friends or a family, how much did each member in that respective group spend on bids? What was the total spend on bids of each group? What was the total spend on bids of all groups?
- How much money was raised in total by the event?
- This includes the total item purchases and the total ticket sales.
- List the first 15 items by name (in alphabetical order) that did not get sold. o
There should not be repeated item names.
- List all the full names of the bidders (in alphabetical order) who went away
“empty-handed”.
- Who were the top 10 bidders in terms of the amount bid, and the number of items they each purchased?
- What was the average winning bid for each category?
- Of the items that were sold, list the first 20 items (in alphabetical order) along with the item price, their category, bid amounts, and the average bid in that category.
Get expert help for Database Design and Management I and many more. 24X7 help, plag free solution. Order online now!
