SQL Queries for CS340 project
Requirements:
1) Add capability for all entities
2) Delete and update capability for any entity
3) Add to all relationships
4) Add and remove to at least one many-to-many relationship
5) Search using text or filter using dynamically populated list of properties to filter on, present for at least one entity (either players on player screen or structures in industry)
QUERIES BY Requirement
1) Add capability for all entities
Add to the player entity: Add a new player on the player page.
Add to the player entity: Design a new item use on the industry page.
Add to the structures entity: Invent a new item structure on the industry page.
Add to the locations entity: Chart a wormhole on the out in space page.
2) Delete and update capability for any entity
Delete a player entity record: delete a player on the player page.
Update a
player entity record: Switch ships in the space station page.
OR
Update an item emtity record: Unpackage an object in the space station page.
3) Add to all relationships
Add to the "item structure HAS item use" 1-to-1 relationship: design an item use in the industry view. When you create an item use record, you must also assign it to an item with the selection box.
Add to the Cargo Space entity: Unpackaging and naming a cargo space in the space station view adds a record to the Cargo Space table.
Add to the "cargo space contains" 1-to-many relationship: move an object into a cargo space on space station page.
Add to the "cargo space NESTS IN cargo space" many-to-one relationship: move a cargo space into another cargo space on space station page
OR
Dock at a station while out in space view. This simply moves the ship cargo space into the space station cargo space.
Add to the "cargo space OCCUPIES location" one-to-many relationship: Create a new player on the player page. Doing so creates a new ship in a new space station in a set location.
OR
Unpackage an object corresponding to a cargo space on a space station page. Doing so also creates a new cargo space in that space station's location.
Add to the "location CONNECTS TO location" many-to-many relationship: Chart a wormhole on the out in space page. This links a new location to an old location and vice versa (two new mirror image records on the table).
Add to the "player PILOTS cargo space" one-to-one relationship: change ships on space station page.
OR
Create a new character on the player page. This starts them piloting a ship.
4) Add and remove to at least one many-to-many relationship
Adding to a many-to-many relationship is already included above: chart a wormhole.
Remove a record from a many-to-many relationship: destroying a wormhole link is yet to be implemented.
5) Search using text or filter using dynamically populated list
Sort players by name or location: Under construction. Sort players by name or location.
OR
Sort objects and cargo spaces by column: Table headings on the space station page act as hyperlinks to sort tables by that heading, toggling between ascending and descending.
QUERIES BY View
-- Players View
INSERT to create a new player by a form
SELECT to list players by id (hidden), name, piloting container, location for main player list and selection box
DELETE a player by ID
-- Out in space View
UPDATE to dock: move NESTS IN entry of cargo space by session player -> container_piloting, location id in dropdown
UPDATE* to travel: change cargo space location and all subcargospace recursively
SELECT&DELETE* to jettison a cargo space: recursively remove subcargospace and contained objects
DELETE to jettison an object: removes just an object entity
INSERT to chart a wormhole: add a new location by a form and link it to your current location
-- Space Station View
UPDATE to undock: new CS nests in set to NULL
SELECT to view objects in a specific cargo space
SELECT* to generate a tree of cargo spaces recursively
SELECT to populate object selection input: trash an object
SELECT to populate players selection input: give an object to another player
SELECT to populate container input: move an object into another cargo space
INSERT to unpackage a cargo space's corresponding object and create the space
DELETE to repackage a cargo space's corresponding object and delete the space
INSERT to move objects into cargo spaces
DELETE to move objects out of cargo spaces
SELECT&DELETE&INSERT* to empty cargo space contents recursively
UPDATE to change piloting ships
-- Industry View
INSERT to invent an item structure
INSERT to design an item use
INSERT to produce objects
SELECT to get item structure types
SELECT to get item use scales
SELECT to get item structure list