SQL Assignment: Database Design and Implementation with SQL Queries

This SQL assignment solution covers various aspects of database design and implementation using SQL. The assignment includes tasks on selecting constraints, implementing missing constraints such as foreign keys and NOT NULL constraints, and creating a check constraint. It delves into triggers, including creating a sequence and a trigger to populate a SIGHTING_ID. The assignment also focuses on views, creating a view to count birds per organization and a materialized view for performance optimization. Furthermore, it includes tasks on executing queries, calculating distances using SQL, and analyzing query execution time with and without foreign keys. The solution provides SQL code snippets and output examples for each task, demonstrating a practical understanding of database concepts and SQL syntax.

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