1 / 11

LIS-560 Database Assignment

Kelly’s Kids Database Design. by Chrystie Kroeker Boggs. LIS-560 Database Assignment. Current Website. The Kelly’s Kids website provides online shopping, primarily for children’s clothing. Proposed changes.

zuri
Download Presentation

LIS-560 Database Assignment

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Kelly’s Kids Database Design by ChrystieKroeker Boggs LIS-560 Database Assignment

  2. Current Website The Kelly’s Kids website provides online shopping, primarily for children’s clothing

  3. Proposed changes • The current Kelly’s Kids website does not provide many options when it comes to searching for items. Currently a customer can only search by name or by category – there is no option to search for sale items, specific sizes, etc. • Proposed solution: implement an “Advanced Search” page to allow greater control over searching, using an enhanced database

  4. Database overview: Tables The ability to do advanced searching will be provided by the underlying database. This database will, in addition to the existing search capabilities, allow for filtering by sale status, sizing, and customizability (i.e., can it be monogrammed?). • The main table, “Items”, contains the basic information for each item: • Name • Pricing • Sale status • Customization status

  5. Database overview: Tables cont… • The Categories and Sizes tables provide the available item category and size options • The ItemsToCategories and ItemsToSizes tables provide the link between the main Itemstable and the Categories & Sizes tables

  6. Database overview: Relationships ItemsToSizes links a record from the Items table to one or more records in the Sizes table, allowing a single article of clothing to be linked to multiple size options. Similarly, ItemsToCategories links a record from the Items table to one or more records in the Categories table, allowing a single article to be listed under multiple categories (e.g., a swimsuit coverupmight appear both under the “dresses” and “swim” categories)

  7. Mockup: main page An “Advanced Search” option will be added to the main page

  8. Mockup: Advanced Search page

  9. Example usage: SQL Example query for searching for all items in the “7-8 Regular” size range (ID 8), sorted by name: • SELECT • "Items"."Name", • "Items"."RegularPrice", • "Items"."CurrentPrice" • FROM • "ItemsToSizes", "Items" • WHERE • "ItemsToSizes"."ItemID" = "Items"."ID" AND • "ItemsToSizes"."SizeID" = 8 AND • "Items"."IsCustomizable" = TRUE • ORDER BY "Items"."Name" ASC “Customizable items, size 7-8” query in the database file

  10. Example usage: SQL cont… Example query for searching for all items in the “Dresses” category (ID 13) that are on sale. The output includes every size available for each item: • SELECT • "Items"."Name", • "Items"."RegularPrice", • "Items"."CurrentPrice", • "Sizes"."Name" AS "Size" • FROM • "ItemsToCategories", • "Items", • "ItemsToSizes", • "Sizes" • WHERE • "ItemsToCategories"."ItemID" = "Items"."ID" AND • "ItemsToSizes"."ItemID" = "Items"."ID" AND • "ItemsToSizes"."SizeID" = "Sizes"."ID" AND • "ItemsToCategories"."CategoryID" = 13 AND • "Items"."IsOnSale" = TRUE “Dresses on sale (including available sizes)” query in the database file

  11. Sample Report Sample report for the “Dresses on Sale” query (Generated using OpenOffice’s Report wizard)

More Related