1 / 15

LEGO COLLECTION DATABASE

LEGO COLLECTION DATABASE. Monica Ham Assignment 5 SLIS 511. Project Description

june
Download Presentation

LEGO COLLECTION DATABASE

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. LEGO COLLECTION DATABASE Monica Ham Assignment 5 SLIS 511

  2. Project Description The goal of this project is to design an database for a family of Lego set collectors. Some collectors have so many sets they have trouble remembering details about all the sets they have. Another problem faced by collectors is losing track of assembly manuals. This database will help collectors to keep better track of the sets they own. Also who bought which set when, and how much they paid for it. Information about the minifigures that come with each set and assembly manuals will also be included.

  3. MINIFIG M ER Diagram (1,1) 1 (0,N) SET 1 1 (0,N) (1,1) purchases 1 M BUYER (1,N) REBUILDS (1,1)

  4. PK

  5. Query 1: List all sets owned ordered by theme. select theme AS "Theme", setname AS "Set Name" from set order by theme ASC, setname ASC;

  6. Query 2: List number of sets from the Harry Potter theme that are part of the collection select count(*) from set where theme='Harry Potter';

  7. Query 3: List names of distinct mini figures (collectors may have many repeat minifigures).  select distinct minifigname from minifig;

  8. Query 4: List all sets and the buyer/owner of each set SELECT set.setname, buyer.buyername, purchaseyear FROM set FULL JOIN buyer ON set.set_id=buyer.set_id ORDER BY buyername ASC;

  9. Query 5: List the rebuilds associated with each set name and organize by theme. SELECT set.theme, set.setname,rebuilds.rebuildname, set.digitalmanual, rebuilds.manualpart FROM set RIGHT JOIN rebuilds ON set.set_id=rebuilds.set_id ORDER BY theme ASC, setname ASC;

  10. Query 6: List total amount of money spend on Lego sets by each buyer. SELECT buyername,SUM(pricepaid) FROM buyer GROUP BY buyername;

  11. Query 7: List the number of sets purchased and the total amount spent on those sets by year. SELECT purchaseyear, count(distinct set_id) AS "number of sets", SUM(pricepaid)AS "total expenditure" FROM buyer GROUP BY purchaseyear;

  12. Query 8: List all minifigures of humans and their associated sets in alphabetical order. SELECT minifig.minifigname, set.setname FROM minifig INNER JOIN set ON minifig.set_id=set.set_id WHERE minifig.minifigtype='human' ORDER BY set.setname ASC, minifig.minifigname ASC;

  13. Query 9: List the set name and price of sets that cost more than the average set cost. SELECT set.setname, buyer.pricepaid FROM set INNER JOIN buyer ON set.set_id=buyer.set_id WHERE buyer.pricepaid>(SELECT avg(pricepaid) FROM buyer);

  14. Query 10: List the name of each buyer/owner and the percent of the collection they own. SELECT buyername, count(*) * 100 / (SELECT count(*) FROM buyer) AS "percent" FROM buyer GROUP BY buyername;

  15. Query 11: List sets that have rebuilds and their associated themes. SELECT DISTINCT set.setname, set.theme FROM set RIGHT JOIN rebuilds ON set.set_id=rebuilds.set_id ORDER BY theme ASC, setname ASC;

More Related