Download Table Structure for Testing - Algorithms | CMSC 351 and more Papers Algorithms and Programming in PDF only on Docsity!
Patrick Gartlan Jon Gill Fred Thomsen
Objective
Our system will provide a centralized database to store information about all surrounding
restaurants and bars. It will allow the user to quickly narrow down those restaurants to
those that suit their needs in a quick and effective manner.
Team Member Contributions
We met every week and all contributed to the design and each project phase. Fred
collected some data on various bars/restaurants since he works downtown, Jon broke it down into a more organized form suitable for the database, and Patrick developed the
PHP front end to our database.
E/R Diagram
Schema
Listing(ListingID, ListingAddress, ListingEmail, ListingPhoneNum, ListingName, LocationID) Locations(LocationID, LocationName, LocationCoords) Cat(CatID, CatName) Group(GroupID, GroupName) Attribute(AttributeID, AttributeName) CategoryListings(CatID, ListingID) CategoryGroups(CatID, GroupID) AttrGroups(GroupID, AttrID) ListingAttrs(ListingID, AttrID, Exists)
Table structure for table Listings
This table holds the information about each listing (each restaurant). It has 2 foreign keys. One is for the LocationID which references the LocationID in the Locations table. This holds generalized locations such as ‘Downtown’ and ‘University Mall’. The other foreign key is the OwnerID which references the owner of the listing.
Field Type Null Default
ListingID int(10) No
ListingName varchar(150) No
ListingStreetAddress varchar(100) No
ListingCity varchar(50) No
ListingState char(2) No
ListingZip mediumint(8) No 0
ListingPhone varchar(20) No
ListingFax varchar(20) No
ListingEmail varchar(75) No
FKListingLocationID int(10) No 0
Table structure for table Locations
This table simply holds locations. It is referenced by the Listings table.
Field Type Null Default
LocationID int(10) No
LocationName varchar(50) No
LocationStreetAddress varchar(100) No
Queries
//This gives us a list of all the bars and their addresses that have pool tables in them. 37 is the attribute ID of ‘pool tables’.
SELECTTT ListingName, ListingStreetAddress FROM ListingAttrGroups , Listings WHERE FKListingAttrGroup_AttrGroupID = 37 AND FKListingAttrGroup_ListingID = ListingID LIMIT 0 , 30
ListingName ListingStreetAddress Big Al’s/Poor Billy’s 201 N Main St Sharkey’s 220 N Main St Hokie House 322 N Main St Cabo Fish Taco 117 S Main St Top Of The Stairs 217 College Ave Oge Chi's 204 Draper Rd PK's 432 N Main St Awful Arthur's 213 Draper Rd Buffalo Wild Wings 211 Prices Fork Rd Macado's 922 University City Blvd Attitudes 900 Prices Fork Rd Rivermill 212 Draper Rd The Cellar 302 N Main St Champs 111 N Main St Abby's 1001 N Main St Mike's Grill 418 N Main St Lefty's Main Street Grille 1202 S Main St Pee Wee's Pit BBQ 109 College Ave Souvlaki 201 College Ave Bobs tackle 123 Main st Bobs Tackle Shop 123 Main St
// get all the attributes for a group. GroupID 9 = ‘Bar Amenities’
SELECTTT * FROM AttrGroups, Attrs WHERE AttrID = FKAttrGroupAttrID AND 9 = FKAttrGroupGroupID ORDER BY AttrName
AttrGroupID FKAttrGroupAttrID FKAttrGroupGroupID AttrID AttrName AttrDescription 36 26 9 26 Dance Floor
Dance Floor
37 23 9 23 Darts Dart boards available for customers to use? 35 27 9 27 Karaoke Karoke? If so, what nights?
34 22 9 22 Pool Tables
Are there Pool Tables available for customers to u... 33 24 9 24 Shuffle Board
Shuffleboard available?
32 25 9 25 Trivia Is there a trivia game?
Entity Sets
Attributes: This is a table that holds all the different types of attributes possible. Such as ‘Pool Tables’, ‘Monday Hours’, or ‘Monday Specials’
Categories: This is the ‘Category table. It tells the difference between Food & Drink.
Groups: This table is the different groups that attributes could be grouped under. Such as ‘Hours’ which will hold the different hours for each day of the week. Or Specials which will hold the specials for each day of the week.
Listings: This table holds the information about each listing (each restaurant). It has one foreign key. One is for the LocationID which references the LocationID in the Locations table. This holds generalized locations such as ‘Downtown’ and ‘University Mall’.
Locations: This table simply holds locations. It is referenced by the Listings table. Locations have a name and GPS coordinates.
Relations
Listings Belongs to Categories: This allows a listing to belong to more than one category. For example, TOTS can belong to Restaurants and Bars. PK’s might belong to Pizza Places, Bars, and Restaurants.
Listings May Contain Attributes: A listing can have many attributes associated with it. For example, TOTS can have ‘Pool Tables’ and ‘Dance Floor’ associated with it. These attributes can be associated with multiple listings. It stores a yes/no answers.
Listings Reside in Locations: A listing will reside in a location. Many listings can reside in one location. For example, TOTs and PKs are all Downtown.
