trhv7eHP

1DROP TABLE IF EXISTS CERTIFICATE;
2DROP TABLE IF EXISTS WORKHISTORY;
3DROP TABLE IF EXISTS ASSIGNMENT;
4DROP TABLE IF EXISTS CANDIDATEQUALIFICATION;
5DROP TABLE IF EXISTS QUALIFICATIONS;
6DROP TABLE IF EXISTS COMPANY;
7DROP TABLE IF EXISTS CANDIDATE;
8
9
10CREATE TABLE CANDIDATE(
11  candidate_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
12  candidate_surname VARCHAR(30) NOT NULL,
13  candidate_lastname VARCHAR(30) NOT NULL,
14  candidate_phone INTEGER NOT NULL
15);
16
17CREATE TABLE COMPANY(
18  company_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
19  company_name VARCHAR(30) NOT NULL,
20  company_phone INTEGER NOT NULL,
21  company_email VARCHAR(30) NOT NULL
22);
23
24CREATE TABLE QUALIFICATIONS(
25  qualification_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
26  qualification_name VARCHAR(30) NOT NULL
27);
28
29CREATE TABLE CANDIDATEQUALIFICATION(
30  candidate_id INTEGER NOT NULL,
31  qualification_id INTEGER NOT NULL,
32  FOREIGN KEY(candidate_id) REFERENCES CANDIDATE(candidate_id),
33  FOREIGN KEY(qualification_id) REFERENCES QUALIFICATIONS(qualification_id),
34  CONSTRAINT candidatequalification_pk PRIMARY KEY (candidate_id, qualification_id)
35);
36
37CREATE TABLE ASSIGNMENT(
38  assignment_id INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
39  qualification_id INTEGER,
40  company_id INTEGER NOT NULL,
41  candidate_id INTEGER,
42  start_date DATE NOT NULL,
43  end_date DATE NOT NULL,
44  total_hours INTEGER,
45  FOREIGN KEY(company_id) REFERENCES COMPANY(company_id),
46  FOREIGN KEY(candidate_id) REFERENCES CANDIDATE(candidate_id),
47  FOREIGN KEY(qualification_id) REFERENCES QUALIFICATIONS(qualification_id)
48);
49
50CREATE TABLE WORKHISTORY(
51  workhistory_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
52  candidate_id INTEGER NOT NULL,
53  assignment_id INTEGER NOT NULL,
54  total_hours INTEGER,
55  FOREIGN KEY(assignment_id)REFERENCES ASSIGNMENT(assignment_id),
56  FOREIGN KEY(candidate_id)REFERENCES CANDIDATE(candidate_id)
57);
58
59CREATE TABLE CERTIFICATE(
60  certificate_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
61  workhistory_id INTEGER NOT NULL,
62  candidate_id INTEGER NOT NULL,
63  FOREIGN KEY(candidate_id)REFERENCES CANDIDATE(CANDIDATE_ID),
64  FOREIGN KEY(workhistory_id) REFERENCES WORKHISTORY(workhistory_id)
65);
66
67
68INSERT INTO CANDIDATE VALUES(DEFAULT, 'Erlend', 'Sundoy', '90909090');
69INSERT INTO CANDIDATE VALUES(DEFAULT, 'Snorre', 'Strand', '80808080');
70INSERT INTO CANDIDATE VALUES(DEFAULT, 'Oscar', 'Skaug', '70707070');
71
72INSERT INTO COMPANY VALUES(DEFAULT, 'SpekkMekk', '80000000', 'spekk@mekk.no');
73INSERT INTO COMPANY VALUES(DEFAULT, 'Kanyla', '70000000', 'kanyla@firma.no');
74INSERT INTO COMPANY VALUES(DEFAULT, 'Bambyla', '60000000', 'bambyla@jobb.no');
75
76INSERT INTO QUALIFICATIONS VALUES(DEFAULT, 'Spise vaffler');
77INSERT INTO QUALIFICATIONS VALUES(DEFAULT, 'Ta bilder');
78INSERT INTO QUALIFICATIONS VALUES(DEFAULT, 'Kaste stein');
79
80INSERT INTO CANDIDATEQUALIFICATION VALUES('1', '1'); -- Erlend kan spise vaffler
81INSERT INTO CANDIDATEQUALIFICATION VALUES('1', '2'); -- Erlend kan ta bilder
82INSERT INTO CANDIDATEQUALIFICATION VALUES('2', '3'); -- Snorre kan kaste stein :)
83                                                     -- Oscar kan NULL!
84
85INSERT INTO ASSIGNMENT VALUES(DEFAULT, NULL, '1', '3', '20170110', '20170124', '40'); -- ingen kvalikk, Oscar, SpekkMekk, fra 10 - 24, totalt 40 timer.
86INSERT INTO ASSIGNMENT VALUES(DEFAULT, '1', '2', '1', '20170112', '20170114', '10'); -- må kunne spise vaffler, Kanyler, Erlend, fra 12 - 14, totalt 10 timer.
87INSERT INTO ASSIGNMENT VALUES(DEFAULT, '3', '3', '2', '20170110', '20170110', '1'); -- må kunne kaste stein, Bambyler, Snorre, Den 10.01 - totalt 1 time.
88
89INSERT INTO WORKHISTORY VALUES(DEFAULT, '1', '2', '10'); -- increment(1), Erlend(1), Assignment(2), 10 timer.
90INSERT INTO WORKHISTORY VALUES(DEFAULT, '2', '3', '1'); -- increment(2), Snorre(2), Assignment(3), 1 time.
91INSERT INTO WORKHISTORY VALUES(DEFAULT, '3', '1', '40'); -- increment(3), Oscar(3), Assignment(1), 40 timer.
92
93INSERT INTO CERTIFICATE VALUES(DEFAULT, '1', '1'); -- Erlends sertifikat
94INSERT INTO CERTIFICATE VALUES(DEFAULT, '2', '2'); -- Snorres sertitikat
95INSERT INTO CERTIFICATE VALUES(DEFAULT, '3', '3'); -- Oscars sertifikat
96
97
98-- OPPGAVE B)
99-- 1 ) Enkelte fremmednøkler kan være NULL. Objekter kan referere til ingen objekter. For eksempel kan kvalifikasjoner være null.
100
101-- 2 ) Jeg kunne for eksmpel Laget en primærnøkkel i CANDIDATEQUALIFICATIONS, inkrementert den og brukt den som nøkkel på assignment.
102-- for å få sjekket at kvalifikasjonene blir møtt. slik det er nå må man manuelt lese av tabellene for å sjekke kravene møtes.
103
104
105
106-- OPPGAVE D)
107
108-- 1) Lag en liste over alle bedriftene. Navn, telefon og epost til bedriften skal skrives ut.
109SELECT company_name, company_phone, company_email FROM COMPANY;
110
111-- 2) Lag en liste over alle oppdragene.
112-- Om hvert oppdrag skal du skrive ut oppdragets nummer samt navn og telefonnummer til bedriften som tilbyr oppdraget.
113SELECT assignment_id, company_name, company_phone FROM ASSIGNMENT NATURAL JOIN COMPANY;
114
115-- 3) Lag en liste over kandidater og kvalifikasjoner.
116-- Kandidatnavn og kvalifikasjonsbeskrivelse skal med i utskriften i tillegg til løpenumrene som identifiserer kandidat og kvalifikasjon.
117SELECT candidate_id, candidate_surname, candidate_lastname, qualification_id, qualification_name
118FROM CANDIDATE NATURAL JOIN QUALIFICATIONS NATURAL JOIN CANDIDATEQUALIFICATION;
119-- Har ikke løpenummer i CANDIDATEQUALIFICATIONS.
120
121-- 4) Som oppgave 3), men få med de kandidatene som ikke er registrert med kvalifikasjoner.
122SELECT CANDIDATE.candidate_id, candidate_surname, candidate_lastname, qualification_name
123FROM CANDIDATE LEFT JOIN CANDIDATEQUALIFICATION ON(CANDIDATE.candidate_id = CANDIDATEQUALIFICATION.candidate_id)
124LEFT JOIN QUALIFICATIONS ON(CANDIDATEQUALIFICATION.qualification_id = QUALIFICATIONS.qualification_id);
125
126-- 5) Skriv ut jobbhistorikken til en bestemt vikar, gitt kandidatnr.
127-- Vikarnavn, sluttdato, oppdragsnr og bedriftsnavn skal med.
128SELECT CANDIDATE.candidate_id, CANDIDATE.candidate_surname, ASSIGNMENT.end_date, ASSIGNMENT.assignment_id, COMPANY.company_name
129  FROM CANDIDATE NATURAL JOIN ASSIGNMENT NATURAL JOIN COMPANY;
130
131
132
133-- RELASJONSMODELLEN:
134
135-- CANDIDATE(candidate_id, candidate_surname, candidate_lastname, candidate_phone)
136
137-- COMPANY(company_id, company_name, company_phone, company_email)
138
139-- QUALIFICATION(qualification_id, qualification_name)
140
141-- CANDIDATEQUALIFICATION(candidatequalification_pk, candidate_id *, qualification_id *)
142
143-- ASSIGNMENT(assignment_id, qualification_id *, company_id *, candidate_id *, start_date, end_date, total_hours)
144
145-- WORKHISTORY(workhistory_id, candidate_id *, assignment_id *, total_hours)
146
147-- CERTIFICATE(certificate_id, workhistory_id *, candidate_id *)