1 / 18

yellowpages.au

yellowpages.com.au. By Rosalinda Pritchard. Database Description. yellowpages.com.au is an Australian business directory. It provides search functions for local or interstate businesses. It allows a search for business types such as electricity or business names such as TRU energy .

colby-reed
Download Presentation

yellowpages.au

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. yellowpages.com.au By Rosalinda Pritchard

  2. Database Description yellowpages.com.au is an Australian business directory. It provides search functions for local or interstate businesses. It allows a search for business types such as electricity or business names such as TRU energy. It is a place where businesses can inform the public of their names, contact details and the nature of their business.

  3. ListingCategory CustomerListing listingID*catID* customerID*listingID* Entity-Relationship Diagram m 1 m 1 1 1 m m

  4. ListingCategory listingID*catID* Single One-to-Many Relationship m 1 Foreign key A listing can be listed in one or more category listing. Primary key

  5. ListingCategory listingID*catID* Single Many-to-Many Relationship m 1 m 1 Primary key Foreign key Primary key Foreign key

  6. Single Many-to-Many Relationship A business such as 'Rhodes Docherty & Co' may list this business name under many business categories such as 'Accountants & Auditors', 'Financial Planning', 'Taxation Consultants' and so on. A business category such as 'Accountants & Auditors' can have many businesses listed under its name, businesses that offers accounting and auditing services. So the relationship between listings and categories is that of many-to-many.

  7. A Simple Query Example:Find all Rhodes Docherty’s business listing. SQL Query: select * from listing where listingBusinessName like '%Rhodes Docherty%'; Results:

  8. Natural Join Example:Find all Rhodes Docherty’s business listing. SQL Query: SELECT listingBusinessName, description FROM listing NATURAL JOIN listingCategory NATURAL JOIN category; Results: listingbusinessname | description---------------------------------------------------------------+------------------------ Institute of Chartered Accountants In Australia The | Accountants & AuditorsChartered Accountants In Aust The Institute Of | Accountants & AuditorsRhodes Docherty & Co | Accountants & AuditorsA J Rhodes Docherty & Co | Financial PlanningA J Rhodes Docherty & Co | Taxation Consultants(5 rows)

  9. Cross Product Example:Find all Rhodes Docherty’s business listing using cross product method. SQL Query: SELECT listing.listingBusinessName, category .description FROM listing, listingCategory, category WHERE listing.listingID = listingCategory.listingID AND listingCategory. catID = category.catID; Results: listingbusinessname | description---------------------------------------------------------------+------------------------ Institute of Chartered Accountants In Australia The | Accountants & AuditorsChartered Accountants In Aust The Institute Of | Accountants & AuditorsRhodes Docherty & Co | Accountants & AuditorsA J Rhodes Docherty & Co | Financial PlanningA J Rhodes Docherty & Co | Taxation Consultants(5 rows)

  10. Group by with HAVING Example:Show all categories that has more than 1 business listings. SQL Query: SELECT description, count(*) FROM category, ListingCategory WHERE category.catID = ListingCategory.catID GROUP BY description HAVING count(*) > 1; Results: description | count---------------------------------+------- Accountants & Auditors | 3(1 row)

  11. Query with a Sub-Query Example:Show all categories that has no business listings. SQL Query: SELECT description FROM category WHERE catID NOT IN (select catID from ListingCategory); Results: description----------------------------------- Vending Equipment & Services Soft Drink Mfrs &/or Distributors Confectionery--Wsalers & Mfrs Take Away Food Hotel, Restaurant & Club Supplies Confectionery--Wsalers & Mfrs Electricity Retailers Electricity Suppliers Gas Suppliers(9 rows)

  12. Self Join Description:Self join allows you to work in a table, joining it to itself. Example:Display all business names or listings that share a phone number. SQL Query: SELECT l1.listingBusinessName, l1.listingPhoneNo, l2.listingBusinessName, l2.listingPhoneNo FROM listing l1, Listing l2 WHERE l1.listingPhoneNo = l2.listingPhoneNo AND l1.listingBusinessName > l2.listingBusinessName; Results: (see next slide)

  13. Self Join Results: listingbusinessname | listingphoneno | listingbusinessname | listingphoneno --------------------------------------------------------------+----------------+---------------------------------------------------+---------------- Institute of Chartered Accountants In Australia The | 0292901344 | Chartered Accountants In Aust The Institute Of | 0292901344 Rhodes Docherty & Co | 0299884033 | A J Rhodes Docherty & Co | 0299884033 Rhodes Docherty & Co | 0299884033 | A J Rhodes Docherty & Co | 0299884033 (3 rows) The above results shows Rhodes Docherty & Co twice because it has the same phone number as A J Rhodes Docherty & Co which has two entries in the system.

  14. CHECK Statements Example:Validate or check the postcode to ensure it consist of 4 digits. SQL Statement: CREATE TABLE listing ( ... CONSTRAINT listing_listingBusinessPostCode CHECK ((listingBusinessPostCode >= 1000) AND (listingBusinessPostCode <= 9999)));

  15. Action Statements Example:Action statements protects data from being corrupted when a user modifies or deletes records from a table. Action Statement 1: CREATE TABLE customerListing ( ... CONSTRAINT customerListingPK PRIMARY KEY (customerID, listingID), CONSTRAINT customerListingFK_customer FOREIGN KEY (customerID) REFERENCES customerON DELETE CASCADE, When a record is deleted from customer’s table, the record that is linked to customerListing is automatically deleted, because of the ‘on delete cascade’ action statement.

  16. Action Statements CREATE TABLE listingCategory ( ... CONSTRAINT listingCategoryFK_category FOREIGN KEY (catID) REFERENCES categoryON DELETE CASCADE Action Statement 2: When a record is deleted from the category’s table, the record that is linked to listingCategoryis automatically deleted, because of the ‘on delete cascade’ action statement.

  17. View CREATE VIEW BusListing ASselect Customer.customerID, CustomerListing.listingID,listingPhoneNo, listingBusinessName, listingBusinessAddress, listingBusinessSuburb, listingBusinessState, Category.catID, description FROMcustomer, customerListing, listing, listingCategory, category WHEREcustomer.customerID = customerListing.customerID AND customerListing.listingID = listing.listingID AND listing.listingID = listingCategory.listingID AND listingCategory.catID = category.catID; Create View:

  18. View select listingBusinessName from BusListing; Results: View BusListing: listingbusinessname---------------------------------------------------- Institute of Chartered Accountants In Australia The Chartered Accountants In Aust The Institute Of Rhodes Docherty & Co A J Rhodes Docherty & Co A J Rhodes Docherty & Co(5 rows)

More Related