Stephen Taylor - PowerPoint PPT Presentation

1 / 48

About This Presentation

Title:

Stephen Taylor

Description:

Additional tables also store data regarding the crews and badges which can be ... Jota. 2005-10-14. 1. Bushdance. 2005-09-17. SELECT * FROM WalshRovers05MemberActivity ... – PowerPoint PPT presentation

Number of Views:70

Avg rating:3.0/5.0

Slides: 49

Provided by: admi1065

Category:

more less

Transcript and Presenter's Notes

Title: Stephen Taylor

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

memfirstname Memlastname
Gareth Walsh
Garth Nagle
Alison McKay
6
Restrict Using Where

SELECT
FROM WalshRovers05Fees
WHERE FeesDatepaid Is Not Null

memcrewid feesdatedue feesdatepaid feesamount
1 2005-06-05 2005-08-24 12
2 2005-06-05 2005-10-26 100
3 2005-06-05 2005-04-14 100
4 2005-06-05 2005-03-05 12
7
Projection and Where

SELECT memcrewid, feesamount
FROM WalshRovers05Fees
WHERE FeesDatepaid Is Not Null

memcrewid feesamount
1 12
2 100
3 100
4 12
5 12
6 100
7 100
8
Use Of Is Null

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
WHERE MEMKnightingDate Is Null

Memfirstname memlastname
Jenna Hall
Karina Hammond
Shaun Sproule
George Smith
9
Use Of Is Not Null

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
WHERE MEMResigneddate Is Not Null

memfirstname memlastname
Alison Mckay
Jenna Hall
10
Use Of In

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
WHERE MEMCrewID In (1,2,3)

Memfirstname memlastname
Gareth Walsh
Garth Nagle
Alison Mckay
11
Use of Not In

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
WHERE MEMCrewID Not In (1,2,3)

Memfirstname memlastname
Lloyd Schrader
Kent Palmer
Jenna Hall
12
Ordering Columns

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
SELECT MEMLastName, MEMFirstName
FROM WalshRovers05Member

Memfirstname memlastname
Gareth Walsh
Garth Nagle
Alison Mckay
memlastname Memfirstname
Walsh Gareth
Nagle Garth
Mckay Alison
13
Ordering Rows

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
ORDER BY MEMLastName

memlastname Memfirstname
Brent Butler
Jenna Hall
Karina Hammond
Tim Hammond
Russell Lyons
14
Calculation (Including As)

SELECT memcrewidfeesamount AS random_no
FROM Walshrovers05fees

random_no
12
200
400
500
600
700
800
15
Count(X)

When Counting by a specific column Null Values
are not counted
SELECT COUNT(MEMResignedDate)
FROM WalshRovers05Member

Count
2
16
Count()

Count All will count all rows which have a value
within the table
SELECT COUNT()
FROM WalshRovers05Member

Count
13
17
Use Of Average

SELECT avg(feesamount)
FROM walshrovers05fees

AVG
83.2380952380952381
18
Use of Sum

SELECT sum(feesamount) as TOTAL_FEES
FROM walshrovers05fees

total_fees
1748
19
Use of Min

SELECT min(feesamount)
FROM walshrovers05fees

min
12
20
Use of Max

SELECT max(feesamount)
FROM walshrovers05fees

max
100
21
Use of Like ()

SELECT memcrewid,memscoutid,memfirstname,memlastna
me
FROM walshrovers05member
WHERE memfirstname LIKE 'Gar'

memcrewid memscoutid memfirstname memlastname
1 136369 Gareth Walsh
2 136368 Garth Nagle
22
Use of Like (_)

SELECT memcrewid,memscoutid,memfirstname,memlastna
me
FROM walshrovers05member
WHERE memfirstname LIKE '_ar'

memcrewid memscoutid memfirstname memlastname
1 136369 Gareth Walsh
2 136368 Garth Nagle
7 987654 Karina Hammond
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

memcrewid
1
24
Insert Command

INSERT INTO WalshRovers05MemberActivity VALUES(
8,'Kiddy Moot', '2005/09/30' )

memcrewid activityname activitystartdate
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

Memfirstname Memlastname
Jenna Hall
27
Cross Product Notation

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member, WalshRovers05Position
WHERE WalshRovers05Member.MEMCrewID
WalshRovers05Position.MEMCrewID
AND PosTitle 'Secretary

Memfirstname Memlastname count
Jenna Hall 5
28
Foreign Entities and Relationships
29
One to Many Relationship

SELECT MEMFirstName,MEMLastName
FROM WalshRovers05Member
WHERE MEMCrewID 1
SELECT
FROM WalshRovers05Fees
WHERE MEMCrewID 1

Memfirstname memlastname
Gareth Walsh
memcrewid feesdatedue feesdatepaid feesamount
1 2005-06-05 2005-08-24 12
1 2004-06-05 2004-08-24 12
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

memcrewid activityname activitystartdate
1 Kiddy Moot 2005-09-30
1 Jota 2005-10-14
1 Bushdance 2005-09-17
31
Many to Many Relationship

SELECT
FROM WalshRovers05MemberActivity
WHERE ActivityName 'Bushdance'

memcrewid activityname activitystartdate
1 Bushdance 2005-09-17
2 Bushdance 2005-09-17
7 Bushdance 2005-09-17
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

memfirstname memlastname count
Garth Nagle 7
Tim Hammond 5
Gareth Walsh 7
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) gt 5

memfirstname memlastname count
Garth Nagle 7
Gareth Walsh 7
35
Sub Query Showing One Value

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
WHERE MEMCrewID (SELECT avg(MEMCrewID)
FROM WalshRovers05Member
Activity
WHERE ActivityName
'Bushdance')

memfirstname memlastname
Jenna Hall
36
Sub Query Using Min/Max

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
WHERE MEMCrewID (SELECT max(MEMCrewID)
FROM WalshRovers05Member
Activity
WHERE ActivityName
'Bushdance')

memfirstname memlastname
Brent Butler
37
Sub Query Using Any/All

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
WHERE MEMCrewID Any (SELECT MEMCrewID
FROM WalshRovers05Member
Activity
WHERE ActivityName
'Bushdance')

memfirstname memlastname
Gareth Walsh
Garth Nagle
Karina Hammond
Tim Hammond
Brent Butler
38
Sub Query Using In

SELECT MEMFirstName, MEMLastName
FROM WalshRovers05Member
WHERE MEMCrewID IN (SELECT MEMCrewID
FROM WalshRovers05Member
Activity
WHERE ActivityName
'Bushdance')

memfirstname memlastname
Gareth Walsh
Garth Nagle
Karina Hammond
Tim Hammond
Brent Butler
39
Left Outer Join

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

Memcrewid postitle
1 Crew Leader
2 Crew Leader
3 NULL
4 NULL
40
Right Outer Join

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

Memcrewid postitle
1 Crew Leader
8 Crew Leader
2 Crew Leader
5 Treasurer
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

Investature_date Squire_firstname Squire_lastname Rover_firstname Rover_Lastname
2003-04-18 Jenna Hall Brent Butler
2003-04-18 Jenna Hall Alison Mckay
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