1 / 48

Analysis of the Gareth Walsh Rovers Database

Analysis of the Gareth Walsh Rovers Database. Stephen Taylor Anthony Ung. Database Purpose and Contents.

barrett-horn
Download Presentation

Analysis of the Gareth Walsh Rovers 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. Analysis of the Gareth Walsh Rovers Database Stephen Taylor Anthony Ung

  2. Database Purpose and Contents • This database is designed to catalogue the admission of new members into the Australian Scouting organisation the Rovers. The database stores information regarding the new members as well as which group they have joined. • Additional tables also store data regarding the crews and badges which can be earned by the various members. • The activities table also keeps track of all Rover related activities.

  3. Entity Relationship Diagram

  4. SQL Queries on a Single Entity/ Table

  5. Projection SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member;

  6. Restrict Using Where SELECT * FROM WalshRovers05Fees WHERE FeesDatepaid Is Not Null;

  7. Projection and Where SELECT memcrewid, feesamount FROM WalshRovers05Fees WHERE FeesDatepaid Is Not Null;

  8. Use Of Is Null SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member WHERE MEMKnightingDate Is Null;

  9. Use Of Is Not Null SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member WHERE MEMResigneddate Is Not Null;

  10. Use Of In SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member WHERE MEMCrewID In (1,2,3);

  11. Use of Not In SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member WHERE MEMCrewID Not In (1,2,3);

  12. Ordering Columns SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member; SELECT MEMLastName, MEMFirstName FROM WalshRovers05Member;

  13. Ordering Rows SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member ORDER BY MEMLastName;

  14. Calculation (Including As) SELECT memcrewid*feesamount AS random_no FROM Walshrovers05fees ;

  15. Count(X) • When Counting by a specific column Null Values are not counted SELECT COUNT(MEMResignedDate) FROM WalshRovers05Member;

  16. Count(*) • Count All will count all rows which have a value within the table SELECT COUNT(*) FROM WalshRovers05Member;

  17. Use Of Average SELECT avg(feesamount) FROM walshrovers05fees;

  18. Use of Sum SELECT sum(feesamount) as TOTAL_FEES FROM walshrovers05fees;

  19. Use of Min SELECT min(feesamount) FROM walshrovers05fees ;

  20. Use of Max SELECT max(feesamount) FROM walshrovers05fees ;

  21. Use of Like (%) SELECT memcrewid,memscoutid,memfirstname,memlastname FROM walshrovers05member WHERE memfirstname LIKE 'Gar%';

  22. Use of Like (_) SELECT memcrewid,memscoutid,memfirstname,memlastname FROM walshrovers05member WHERE memfirstname LIKE '_ar%';

  23. Use of Distinct • Distinct Ensures no rows of identical values are displayed • The same crewid appears many times in the activity table but distinct displays it only once SELECT DISTINCT memcrewid FROM walshrovers05memberactivity WHERE memcrewid = 1;

  24. Insert Command INSERT INTO WalshRovers05MemberActivity VALUES( 8,'Kiddy Moot', '2005/09/30' );

  25. Foreign Keys and Natural Joins

  26. Natural Join SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member NATURAL JOIN WalshRovers05Position WHERE PosTitle = 'Secretary' GROUP BY MEMFirstName, MEMLastName;

  27. Cross Product Notation SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member, WalshRovers05Position WHERE WalshRovers05Member.MEMCrewID =WalshRovers05Position.MEMCrewID AND PosTitle = 'Secretary‘;

  28. Foreign Entities and Relationships

  29. One to Many Relationship SELECT MEMFirstName,MEMLastName FROM WalshRovers05Member WHERE MEMCrewID = 1; SELECT * FROM WalshRovers05Fees WHERE MEMCrewID = 1;

  30. Many to Many Relationship • There is a many-to-many relationship between the members and activities table which is separated into two one-to-many relationships. There can be many members in each activity and each member can be in many activities. SELECT * FROM WalshRovers05MemberActivity WHERE MEMCrewID = 1;

  31. Many to Many Relationship SELECT * FROM WalshRovers05MemberActivity WHERE ActivityName = 'Bushdance';

  32. Group By's, Sub-Queries and Complex Joins

  33. Group By SELECT MEMFirstName, MEMLastName, Count(ActivityName) FROM WalshRovers05MemberActivity NATURAL JOIN WalshRovers05Member NATURAL JOIN WalshRovers05Position WHERE PosTitle = 'Crew Leader' GROUP BY MEMFirstName, MEMLastName;

  34. Group By Having SELECT MEMFirstName, MEMLastName, Count(ActivityName) FROM WalshRovers05MemberActivity NATURAL JOIN WalshRovers05Member NATURAL JOIN WalshRovers05Position WHERE PosTitle = 'Crew Leader' GROUP BY MEMFirstName, MEMLastName HAVING Count(ActivityName) > 5;

  35. Sub Query Showing One Value SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member WHERE MEMCrewID = (SELECT avg(MEMCrewID) FROM WalshRovers05MemberActivity WHERE ActivityName = 'Bushdance');

  36. Sub Query Using Min/Max SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member WHERE MEMCrewID = (SELECT max(MEMCrewID) FROM WalshRovers05MemberActivity WHERE ActivityName = 'Bushdance');

  37. Sub Query Using Any/All SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member WHERE MEMCrewID = Any (SELECT MEMCrewID FROM WalshRovers05MemberActivity WHERE ActivityName = 'Bushdance');

  38. Sub Query Using In SELECT MEMFirstName, MEMLastName FROM WalshRovers05Member WHERE MEMCrewID IN (SELECT MEMCrewID FROM WalshRovers05MemberActivity WHERE ActivityName = 'Bushdance');

  39. Left Outer Join SELECT WalshRovers05Member.memcrewid, Postitle FROM WalshRovers05Member Left Join WalshRovers05Position Using (memcrewid);

  40. Right Outer Join SELECT WalshRovers05Member.memcrewid, Postitle FROM WalshRovers05Member Right Join WalshRovers05Position Using (memcrewid);

  41. Self Join SELECT squires.MEMSquiringDate as investature_date , squires.MEMFirstName AS Squire_FirstName, squires.MEMLastName AS Squire_LastName, rovers.MEMFirstName AS Rover_FirstName, rovers.MEMLastName AS Rover_LastName FROM WalshRovers05Member squires, WalshRovers05Member rovers WHERE squires.MEMSquiringDate = rovers.MEMKnightingDate;

  42. Data Integrity With SQL

  43. Creation of a Foreign Key Create TABLE WalshRovers05Member ( MEMCrewID INTEGER, CONSTRAINT WalshRovers05Member_PK PRIMARY KEY (MEMCrewID), Create TABLE WalshRovers05Position ( MEMCrewID INTEGER, CONSTRAINT WalshRovers05Position_MEMCrewID_FK FOREIGN KEY (MEMCrewID) REFERENCES WalshRovers05Member ON DELETE RESTRICT

  44. Check Statement CONSTRAINT ValidGender CHECK ( MEMGender = 'M' OR MEMGender = 'F'), CONSTRAINT ValidDOB CHECK ( MEMDOB BETWEEN '1900-01-01' AND '2005-01-01'), CONSTRAINT ValidMEMJoiningDate CHECK ( MEMJoiningDate BETWEEN '1900-01-01' AND '2006-01-01'), CONSTRAINT ValidMEMSquiringDate CHECK ( MEMSquiringDate BETWEEN '1900-01-01' AND '2006-01-01'), CONSTRAINT ValidMEMKnightingDate CHECK ( MEMKnightingDate BETWEEN '1900-01-01' AND '2006-01-01')

  45. On Delete Restrict CONSTRAINT WalshRovers05Fees_MEMCrewID_FK FOREIGN KEY (MEMCrewID) REFERENCES WalshRovers05Member ON DELETE RESTRICT

  46. On Delete Cascade CONSTRAINT WalshRovers05Badge_MEMCrewID_FK FOREIGN KEY (MEMCrewID) REFERENCES WalshRovers05Member ON DELETE CASCADE

  47. Normalization (removed from this case study, as normalization was not done well.)

  48. Creation of a View CREATE VIEW fees1 (memcrewid, FeesDatedue, FeesDatePaid, FeesAmount) AS SELECT * FROM WalshRovers05Fees; SELECT * FROM Fees1;

More Related