SQL Assignment: Constraints, Triggers, Views, and Queries

The provided assignment content is a SQL assignment that consists of five tasks: TASK 1, TASK 2, TASK 3, TASK 4, and TASK 5. TASK 1 involves selecting all constraints from the user_constraints table, while TASK 2 deals with implementing missing foreign key constraints. TASK 3 focuses on creating views, specifically a materialized view and a regular view. TASK 4 calculates the distance between sightings using the Haversine formula, and TASK 5 selects sighting data based on a specific spotter ID.

sql assignment
STUDENT NAME:
25 October 2016

Contents
TASK 1....................................................................................................................................................2
PART A: SELECTING ALL CONSTRAINTS..............................................................................................2
PART B: IMPLEMENTING THE MISSING CONSTRAINTS......................................................................2
TASK 2: TRIGGERS..................................................................................................................................3
PART A...............................................................................................................................................3
PART C...............................................................................................................................................4
PART D...............................................................................................................................................4
TASK 3: VIEWS.......................................................................................................................................4
PART A...............................................................................................................................................4
PART B...............................................................................................................................................5
PART C: EXECUTION TIME..................................................................................................................6
TASK 4....................................................................................................................................................8
PART A...............................................................................................................................................8
TASK 5....................................................................................................................................................9
PART A...............................................................................................................................................9
PART B.............................................................................................................................................10
1 | P a g e

TASK 1
PART A: SELECTING ALL CONSTRAINTS
ANSWER:
SELECT * FROM USER_CONSTRAINTS;
PART B: IMPLEMENTING THE MISSING CONSTRAINTS
ANSWER: The statements to implement the missing constraints are:
Alter table spotters add constraint FK_ORG_ID_TO_ORG_ID foreign key(organisation_id) references
organisations(organisation_id);
Alter table sightings add constraint FK_SPOTTER_ID_TO_SPOTTER_ID foreign key(spotter_id)
references spotters(spotter_id);
Alter table sightings add constraint FK_BIRD_ID_TO_BIRD_ID foreign key(bird_id) references
birds(bird_id);
alter table organisations add constraint NN_ORGANISATION_NAME check(organisation_name is
NOT NULL);
2 | P a g e

alter table SPOTTERS add constraint NN_SPOTTER_NAME CHECK(SPOTTER_name IS not null);
alter table BIRDS add constraint NN_BIRD_NAME CHECK(BIRD_name IS not null);
alter table sightings add constraint CK_SIGHTING_DATE CHECK (EXTRACT(YEAR FROM
SIGHTING_DATE) <= 2016);
TASK 2: TRIGGERS
PART A
CREATE SEQUENCE SEQ_SIGHTINGS
START WITH 300000
INCREMENT BY 1;
CREATE OR REPLACE TRIGGER SEQ_SIGHTINGS
BEFORE INSERT ON SIGHTINGS
FOR EACH ROW
BEGIN
IF :new.SIGHTING_ID IS NULL THEN
SELECT SEQ_SIGHTINGS.nextval INTO :new.SEQ_SIGHTINGS FROM DUAL;
END IF;
END;
3 | P a g e

PART C
OUTPUT:
PART D
Select * from sightings where sighting_date = '09-MAR-2016';
Output:
TASK 3: VIEWS
PART A
create view V_ORGANISATION_BIRD_COUNT as select o.organisation_name, count(s.bird_id) as
no_of_birds from sightings s, spotters s2, organisations o where o.organisation_id =
s2.organisation_id and s2.spotter_id = s.spotter_id group by o.organisation_name;
commit;
select * from V_ORGANISATION_BIRD_COUNT;
4 | P a g e

PART B
CREATE MATERIALIZED VIEW MV_ORGANISATION_BIRD_COUNT
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM V_ORGANISATION_BIRD_COUNT;
COMMIT;
select * from MV_ORGANISATION_BIRD_COUNT;
5 | P a g e

PART C: EXECUTION TIME
6 | P a g e

7 | P a g e

TASK 4
PART A
SELECT SIGHTING_ID, SQRT(POWER((-28 + 151), 2) + POWER((LATITUDE + LONGITUDE), 2)) FROM
SIGHTINGS;
OUTPUT:
8 | P a g e

TASK 5
PART A
select s1.sighting_id, s2.spotter_name, s1.sighting_date from sightings s1, spotters s2 where
s1.spotter_id = s2.spotter_id and s1.spotter_id = 1255;
output:
9 | P a g e

PART B
alter table sightings drop constraint FK_SPOTTER_ID_TO_SPOTTER_ID;
alter table spotters drop primary key;
select s1.sighting_id, s2.spotter_name, s1.sighting_date from sightings s1, spotters s2 where
s1.spotter_id = s2.spotter_id and s1.spotter_id = 1255;
10 | P a g e

The query executed after dropping primary and foreign keys is that it will take more time to execute.
This is so because the tables are not relate. Hence each column’s value will be matched and then the
result will gathered and then the result is returned. Hence this plan takes more time execute.
11 | P a g e