Untitled

-- High Distinction, Autumn 2018
--Created by Zachary Francis, zachary.francis@student.uts.edu.au
--The following is a database which describes Scouts Australia.
--It enables tracking of membership details for individual members including badges earnt and attendance to camps.
--Scout camp, Scout group and Scout Hall details are also tracked within.
--This database was inspired by https://scouts.com.au/
--All information provided is real, except for human details which was populated randomly.

DROP table Scout_Halls CASCADE;
DROP table Groups CASCADE;
DROP table Members CASCADE;
DROP table Camps CASCADE;
DROP table Camp_Participants CASCADE;
DROP table Badge_List CASCADE;
DROP table Badges_Earnt CASCADE;
DROP table Patrol_Group CASCADE;

Create table Scout_Halls
(
    Location        TEXT,
    Size            integer,
    CONSTRAINT Scout_HallsPK PRIMARY KEY (Location),
    CONSTRAINT di_table_Scout_Halls_Size CHECK
        (Size > 0)
);

Create table Groups
(
    GroupID     integer,
    GroupName   TEXT    NOT NULL,
    Location    TEXT,

    CONSTRAINT GroupsPK PRIMARY KEY (GroupID),

    CONSTRAINT GroupsFK FOREIGN KEY
            (Location) REFERENCES Scout_Halls
);

Create table Members
(
    MemberID    integer     NOT NULL,
    Name        TEXT        NOT NULL,
    Gender      TEXT,
    BirthYear   integer     NOT NULL,
    BirthMonth  integer     NOT NULL,
    BirthDay    integer     NOT NULL,
    division    TEXT,
    GroupID     integer,

        CONSTRAINT MembersPK PRIMARY KEY (MemberID),

        CONSTRAINT MembersFK FOREIGN KEY
            (GroupID) REFERENCES Groups,

        CONSTRAINT di_table_Members_MemberID CHECK
                    (MemberID >= 1),

        CONSTRAINT di_table_Members_BirthYear CHECK
                    (BirthYear >= 1900 AND BirthYear <= 2018),

        CONSTRAINT di_table_Members_BirthMonth CHECK
                    (BirthMonth >= 1 AND BirthMonth <= 12),

        CONSTRAINT di_table_Members_BirthDay CHECK
                    (BirthDay >= 1 AND BirthDay <= 31),

        CONSTRAINT di_table_Members_division CHECK(division IN(
                    'Joey',
                    'Cub',
                    'Scout',
                    'Venturer',
                    'Rover',
                    'Leader'))
);


Create table Camps
(
    CampID      integer,
    CampName    TEXT,
    CampDate        DATE,
    CampLocation    TEXT,

   CONSTRAINT CampsPK PRIMARY KEY (CampID)
);

Create table Camp_Participants
(
    CampID      integer     NOT NULL,
    MemberID    integer     NOT NULL,

  CONSTRAINT Camp_ParticipantsPK PRIMARY KEY (CampID,MemberID),

  CONSTRAINT Camp_ParticipantsFK FOREIGN KEY
            (CampID) REFERENCES Camps,

  CONSTRAINT Camp_ParticipantsFK2 FOREIGN KEY
            (MemberID) REFERENCES Members
);

Create table Badge_List
(
    BadgeID     integer,
    BadgeName   TEXT,

    CONSTRAINT Badge_ListPK PRIMARY KEY (BadgeID)
);

Create table Badges_Earnt
(
    BadgeID     integer     NOT NULL,
    MemberID    integer     NOT NULL,

    CONSTRAINT Badges_EarntPK PRIMARY KEY (BadgeID, MemberID),

    CONSTRAINT Badges_EarntFK FOREIGN KEY
                (BadgeID) REFERENCES Badge_List,

    CONSTRAINT Badges_EarntFK2 FOREIGN KEY
                (MemberID) REFERENCES Members
);

Create table Patrol_Group
(
    MemberIDA   integer     NOT NULL,
    MemberIDB   integer     NOT NULL,
    Patrol_Name TEXT        NOT NULL,

    CONSTRAINT Patrol_GroupPK PRIMARY KEY (MemberIDA, MemberIDB),

    CONSTRAINT Patrol_GroupFK FOREIGN KEY
                (MemberIDA) REFERENCES Members(MemberID),
    CONSTRAINT Patrol_GroupFK2 FOREIGN KEY
                (MemberIDB) REFERENCES Members(MemberID)
);

INSERT INTO Scout_Halls VALUES('Panorama Parade, Berkeley Vale NSW 2261', 100);
INSERT INTO Scout_Halls VALUES('Baden Powell Park, Nirvana St, Long Jetty NSW 2261', 90);
INSERT INTO Scout_Halls VALUES('145 Maidens Brush Rd, Wyoming NSW 2250', 140);
INSERT INTO Scout_Halls VALUES('Willoughby Rd & Terrigal Drive, Terrigal NSW 2260', 122);
INSERT INTO Scout_Halls VALUES('Levitt St, Wyong NSW 2259', 70);
INSERT INTO Scout_Halls VALUES('5 Centenary Dr, Mosman NSW 2088', 170);
INSERT INTO Scout_Halls VALUES('Adelaide St, Tumbi Umbi NSW 2261', 165);
INSERT INTO Scout_Halls VALUES('3C Avenue Rd, Mosman NSW 2088', 125);


INSERT INTO Groups VALUES(1,'1st Berkeley Vale Scout Group','Panorama Parade, Berkeley Vale NSW 2261');
INSERT INTO Groups VALUES(2,'1st Tumbi Scout Group','Adelaide St, Tumbi Umbi NSW 2261');
INSERT INTO Groups VALUES(3,'1st Tuggerah Lakes Scouts','Baden Powell Park, Nirvana St, Long Jetty NSW 2261');
INSERT INTO Groups VALUES(4,'2nd Tuggerah Lakes Sea Scouts','Baden Powell Park, Nirvana St, Long Jetty NSW 2261');
INSERT INTO Groups VALUES(5,'1st Wyong Scout Group','Levitt St, Wyong NSW 2259');
INSERT INTO Groups VALUES(6,'Wyoming Scouts','145 Maidens Brush Rd, Wyoming NSW 2250');
INSERT INTO Groups VALUES(7,'1st Mosman Scout Group','3C Avenue Rd, Mosman NSW 2088');
INSERT INTO Groups VALUES(8,'3rd Mosman Sea Scouts','5 Centenary Dr, Mosman NSW 2088');


INSERT INTO Members VALUES(1,'Austin Irwin','Male',1990,2,7,'Leader',1);
INSERT INTO Members VALUES(2,'Debbie Silvester','Female',2009,3,,'Cub',1);
INSERT INTO Members VALUES(3,'Horace Monette','Male',2010,7,30,'Cub',1);
INSERT INTO Members VALUES(4,'Hector Deschamps','Male',2009,6,24,'Cub',1);
INSERT INTO Members VALUES(5,'Newt Bryce','Male',2009,10,26,'Cub',1);
INSERT INTO Members VALUES(6,'Bentley Pearce','Male',2009,3,13,'Cub',1);
INSERT INTO Members VALUES(7,'Kelly Maynard','Female',2011,1,5,'Cub',1);
INSERT INTO Members VALUES(8,'Clarinda Garrett','Female',2005,10,4,'Scout',1);
INSERT INTO Members VALUES(9,'Blaine Howland','Male',2006,2,3,'Scout',1);
INSERT INTO Members VALUES(10,'Erma Whittle','Female',2006,4,8,'Scout',1);
INSERT INTO Members VALUES(11,'Imogen Huff','Female',2004,7,28,'Scout',1);
INSERT INTO Members VALUES(12,'Libby Ansel','Female',2004,9,25,'Scout',1);
INSERT INTO Members VALUES(13,'Rebecca Parish','Female',2005,12,1,'Scout',1);
INSERT INTO Members VALUES(14,'Chadwick Ross','Male',2007,2,21,'Scout',1);
INSERT INTO Members VALUES(15,'Millie Roy','Female',2007,3,26,'Scout',1);
INSERT INTO Members VALUES(16,'Estella Fabron','Female',2006,1,4,'Scout',1);
INSERT INTO Members VALUES(17,'Quinton Garrard','Male',1974,4,27,'Leader',1);
INSERT INTO Members VALUES(18,'Tristan Edison','Male',1969,7,18,'Leader',2);
INSERT INTO Members VALUES(19,'Jeannie Morris','Female',1947,3,17,'Leader',3);
INSERT INTO Members VALUES(20,'Phillip Eaton','Male',1975,5,8,'Leader',4);
INSERT INTO Members VALUES(21,'Katey Bonney','Female',1994,1,5,'Leader',5);
INSERT INTO Members VALUES(22,'Prosper Bourdillon','Male',1991,8,8,'Leader',6);
INSERT INTO Members VALUES(23,'Colleen Durant','Female',1958,6,7,'Leader',7);
INSERT INTO Members VALUES(24,'Richard Evered','Male',1959,11,30,'Leader',8);


INSERT INTO Camps VALUES(1, 'Jamboree 2018', 2018-09-30, 'Newington Armory, Sydney Olympic Park');
INSERT INTO Camps VALUES(2, 'Beginners Abseiling', 2018-07-13, 'Heaton - Watagan State Forest');


INSERT INTO Camp_Participants VALUES(1,1);
INSERT INTO Camp_Participants VALUES(2,1);
INSERT INTO Camp_Participants VALUES(3,1);
INSERT INTO Camp_Participants VALUES(4,1);
INSERT INTO Camp_Participants VALUES(5,1);
INSERT INTO Camp_Participants VALUES(20,1);
INSERT INTO Camp_Participants VALUES(9,1);
INSERT INTO Camp_Participants VALUES(10,1);
INSERT INTO Camp_Participants VALUES(11,1);
INSERT INTO Camp_Participants VALUES(12,1);
INSERT INTO Camp_Participants VALUES(14,1);


INSERT INTO Badge_List VALUES(1,'Outdoors');
INSERT INTO Badge_List VALUES(2,'Initiative');
INSERT INTO Badge_List VALUES(3,'Leadership');
INSERT INTO Badge_List VALUES(4,'Environment');
INSERT INTO Badge_List VALUES(5,'Expedition');
INSERT INTO Badge_List VALUES(6,'Citizenship');
INSERT INTO Badge_List VALUES(7,'Expressions');


INSERT INTO Badges_Earnt VALUES(1,2);
INSERT INTO Badges_Earnt VALUES(4,2);
INSERT INTO Badges_Earnt VALUES(5,2);
INSERT INTO Badges_Earnt VALUES(6,2);
INSERT INTO Badges_Earnt VALUES(7,2);


INSERT INTO Patrol_Group VALUES(2,2,'Eagle');
INSERT INTO Patrol_Group VALUES(2,3,'Eagle');
INSERT INTO Patrol_Group VALUES(2,4,'Eagle');
INSERT INTO Patrol_Group VALUES(2,5,'Eagle');
INSERT INTO Patrol_Group VALUES(2,6,'Eagle');
INSERT INTO Patrol_Group VALUES(8,8,'Emu');
INSERT INTO Patrol_Group VALUES(8,9,'Emu');
INSERT INTO Patrol_Group VALUES(8,10,'Emu');
INSERT INTO Patrol_Group VALUES(8,11,'Emu');