Opdracht 1.sql

-- Zet de server output aan
SET SERVEROUTPUT ON

-- Maak de landen tabel
CREATE TABLE Country
(
  ID NUMBER PRIMARY KEY,
  Code VARCHAR2(3) UNIQUE NOT NULL,
  Name VARCHAR2(50) UNIQUE NOT NULL
);

-- Maak de universiteiten tabel
CREATE TABLE University
(
  ID NUMBER PRIMARY KEY,
  Code VARCHAR2(4) UNIQUE NOT NULL,
  CountryID NUMBER NOT NULL,
  Name VARCHAR2(100) NOT NULL,
  SNL NUMBER NOT NULL,
  
  FOREIGN KEY (CountryID) REFERENCES Country(ID)
);

-- Voeg wat landen toe
INSERT INTO Country VALUES (1, 'NL', 'The Netherlands');
INSERT INTO Country VALUES (2, 'FR', 'France');
INSERT INTO Country VALUES (3, 'DE', 'Germany');
INSERT INTO Country VALUES (4, 'GB', 'United Kingdom');
INSERT INTO Country VALUES (5, 'BE', 'Belgium');
INSERT INTO Country VALUES (6, 'US', 'United States of America');
INSERT INTO Country VALUES (7, 'CA', 'Canada');

-- Universiteiten in Gelderland
INSERT INTO University VALUES (0, 'TUA',   1, 'Theologische Universiteit Apeldoorn', 8);
INSERT INTO University VALUES (1, 'RUN',   1, 'Radboud Universiteit Nijmegen', 10);
INSERT INTO University VALUES (2, 'WUR',   1, 'Wageningen Universiteit', 10);

-- Universiteiten in Groningen
INSERT INTO University VALUES (3, 'RUG',   1, 'Rijksuniversiteit Groningen', 7);

-- Universiteiten in Limburg
INSERT INTO University VALUES (4, 'UM',    1, 'Universiteit Maastricht', 8);
INSERT INTO University VALUES (5, 'OU',    1, 'Open Universiteit', 7);
INSERT INTO University VALUES (6, 'MSM',   1, 'Maastricht School of Management', 10);

-- Universiteiten in Noord-Brabant
INSERT INTO University VALUES (7, 'TUE',   1, 'Technische Universiteit Eindhoven', 9);
INSERT INTO University VALUES (8, 'TIU',   1, 'Universiteit van Tilburg', 9);

-- Universiteiten in Noord-Holland
INSERT INTO University VALUES (9, 'UVA',   1, 'Universiteit van Amsterdam', 10);
INSERT INTO University VALUES (10, 'VU',   1, 'Vrije Universiteit', 9);

-- Universiteiten in Overijssel
INSERT INTO University VALUES (11, 'PTHU', 1, 'Protestantse Theologische Universiteit vestiging Kampen', 7);
INSERT INTO University VALUES (12, 'TUK',  1, 'Theologische Universiteit Kampen', 6);
INSERT INTO University VALUES (13, 'UT',   1, 'Universiteit Twente', 7);

-- Universiteiten in Utrecht
INSERT INTO University VALUES (14, 'UU',   1, 'Universiteit Utrecht', 8);
INSERT INTO University VALUES (15, 'NBU',  1, 'Nyenrode Business Universiteit', 10);
INSERT INTO University VALUES (16, 'UVH',  1, 'Universiteit voor Humanistiek', 6);
INSERT INTO University VALUES (17, 'KTU',  1, 'Katholieke Theologische Universiteit', 6);
INSERT INTO University VALUES (18, 'TIAS', 1, 'TiasNimbas Business School', 7);

-- Universiteiten in Zeeland
INSERT INTO University VALUES (19, 'UCR',  1, 'University College Roosevelt', 7);

-- Universiteiten in Noord-Holland
INSERT INTO University VALUES (20, 'TUD',  1, 'Technische Universiteit Delft', 9);
INSERT INTO University VALUES (21, 'UL',   1, 'Universiteit Leiden', 10);
INSERT INTO University VALUES (22, 'RUR',  1, 'Erasmus Universiteit Rotterdam', 10);

-- University of Phoenix, USA
INSERT INTO University VALUES (23, 'UOPX',  6, 'University of Phoenix', 30);

-- Een functie om een International Student Identification Number (ISIN) te genereren
create or replace FUNCTION generateISIN(
  countryCode Country.Code%TYPE,
  universityCode University.Code%TYPE,
  studentNumber VARCHAR2
)
RETURN VARCHAR2
AS
  newStudentNumber VARCHAR2(50) := '';
  countLoop INTEGER;
  
  TYPE typeNumberArray IS TABLE OF VARCHAR2(4);
  numberArray typeNumberArray := typeNumberArray();
  
  modRes INTEGER := 0;
  modSecond VARCHAR(50);
  restSN VARCHAR(50);
BEGIN
  newStudentNumber := convertToNumbers(universityCode) || convertToNumbers(countryCode) || studentNumber;

  countLoop := 0;
  
  WHILE countLoop < CEIL(LENGTH(newStudentNumber) / 4)
  LOOP
    countLoop := countLoop + 1;
  
    numberArray.extend;
    
    -- Er is maar een item
    IF countLoop = 1 THEN
      numberArray(countLoop) := SUBSTR(newStudentNumber, 1, 4);
    ELSE
      numberArray(countLoop) := SUBSTR(newStudentNumber, 1 + (4 * (countLoop - 1)), 4);
    END IF;
  END LOOP;
  
  -- Reset
  newStudentNumber := '';
  
  -- De array omdraaien
  WHILE countLoop > 0
  LOOP
    newStudentNumber := newStudentNumber || numberArray(countLoop);
    
    countLoop := countLoop - 1;
  END LOOP;
  
  restSN := newStudentNumber;
  modRes := MOD(substr(restSN, 1, 4), 62);
  modSecond := LPAD(modRes, 2, '0');
  restSN := modSecond || substr(restSN, 5);
  
  -- Modulo berekenen
  WHILE LENGTH(restSN) >= 4
  LOOP
    modRes  := MOD(substr(restSN, 1, 4), 62);
    modSecond := LPAD(modRes, 2, '0');
    restSN := modSecond || substr(restSN, 5);
  END LOOP;
  
  modRes := MOD(restSN, 62);
  
  -- De 0 voor de restwaarde zetten indien de lengte van de restwaarde 1 is
  IF LENGTH(modRes) < 2 THEN
    newStudentNumber := studentNumber || '0' || modRes;
  ELSE
    newStudentNumber := studentNumber || modRes;
  END IF;
 
  -- Add space after every 4th char
  newStudentNumber := RTRIM(regexp_replace(newStudentNumber, '(....)', '\1 ' ));
  
  newStudentNumber := countryCode || ' ' || newStudentNumber || ' ' || universityCode;

  RETURN newStudentNumber;
END;
/

CREATE OR REPLACE FUNCTION convertToNumbers(
  stringToConvert VARCHAR2
)
RETURN NUMBER
AS
  convertedNumbers NUMBER;
  countLoops NUMBER := 0;
  charToConvert VARCHAR2(50);
BEGIN
  WHILE countLoops < LENGTH(stringToConvert)
  LOOP
    charToConvert := SUBSTR(UPPER(stringToConvert), countLoops + 1, 1);
    
    convertedNumbers := convertedNumbers || ASCII(charToConvert) - 49;
    
    countLoops := countLoops + 1;
  END LOOP;
  
  RETURN convertedNumbers;
END;
/

-- Een functie die controleert of een gegeven ISIN voldoet aan de eisen
create or replace FUNCTION checkForCorrectness (
  ISIN VARCHAR2
)
RETURN INTEGER
AS
  countryCode VARCHAR2(50);
  universityCode VARCHAR2(50);
  studentNumber VARCHAR2(50);
  
  lengthOfIsin INTEGER;
  lengthUniversityCode INTEGER;
  
  countryId INTEGER;
  UniversityCountryId INTEGER;
  
  INVALID_STUDENT EXCEPTION;
BEGIN
  countryCode := SUBSTR(ISIN, 1, INSTR(ISIN, ' ') - 1);
  universityCode := REGEXP_SUBSTR(ISIN, '[^ ]+$');
  studentNumber := REGEXP_REPLACE(SUBSTR(ISIN, LENGTH(countryCode) + 1, lengthOfIsin), '\s');

  lengthOfIsin := LENGTH(ISIN) - (LENGTH(countryCode) + LENGTH(universityCode));
  
  -- Toegestane lengte
  SELECT snl
  INTO lengthUniversityCode
  FROM university
  WHERE code = universityCode;
  
  -- Zonder de restwaarde
  IF (LENGTH(studentNumber) - 2) != lengthUniversityCode THEN
    RAISE INVALID_STUDENT;
  END IF;
  
  -- Id van de stad ophalen
  SELECT id
  INTO countryId
  FROM country
  WHERE code = countryCode;
  
  -- Id van de stad ophalen waar de universiteit zich bevind
  SELECT countryId
  INTO UniversityCountryId
  FROM university
  WHERE code = universityCode;

  IF UniversityCountryId != countryId THEN
    RAISE NO_DATA_FOUND;
  END IF;

  RETURN 1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN

    RETURN 0;
  WHEN INVALID_STUDENT THEN

    RETURN 0;
END;
/

-- Een hulp functie die bij de test cases wordt gebruik
CREATE OR REPLACE PROCEDURE ASSERT_EQUALS (
  actual VARCHAR2,
  expected VARCHAR2
)
AS
BEGIN
  IF (NVL(actual, -1) ^= NVL(expected, -2)) THEN
    RAISE_APPLICATION_ERROR(-20000, 'ASSERT FAILS. ' || actual || ' != ' || expected);
  END IF;
END;
/

-- Test 1a
SET SERVEROUTPUT ON
BEGIN
  ASSERT_EQUALS(generateISIN('NL','TUA','98162670'),'NL 9816 2670 60 TUA');
  ASSERT_EQUALS(generateISIN('NL','RUN','1889924721'),'NL 1889 9247 2130 RUN');
  ASSERT_EQUALS(generateISIN('NL','WUR','8760503442'),'NL 8760 5034 4210 WUR');
  ASSERT_EQUALS(generateISIN('NL','RUG','7948372'),'NL 7948 3725 2 RUG');
  ASSERT_EQUALS(generateISIN('NL','UM','10392781'),'NL 1039 2781 20 UM');
  ASSERT_EQUALS(generateISIN('NL','OU','2551716'),'NL 2551 7166 0 OU');
  ASSERT_EQUALS(generateISIN('NL','MSM','5337149774'),'NL 5337 1497 7446 MSM');
  ASSERT_EQUALS(generateISIN('NL','TUE','543595679'),'NL 5435 9567 926 TUE');
  ASSERT_EQUALS(generateISIN('NL','TIU','498476766'),'NL 4984 7676 634 TIU');
  ASSERT_EQUALS(generateISIN('NL','UVA','5389537253'),'NL 5389 5372 5343 UVA');
  ASSERT_EQUALS(generateISIN('NL','VU','658372658'),'NL 6583 7265 832 VU');
  ASSERT_EQUALS(generateISIN('NL','PTHU','7688668'),'NL 7688 6682 7 PTHU');
  ASSERT_EQUALS(generateISIN('NL','TUK','429859'),'NL 4298 5932 TUK');
  ASSERT_EQUALS(generateISIN('NL','UT','7279553'),'NL 7279 5534 5 UT');
  ASSERT_EQUALS(generateISIN('NL','UU','04692710'),'NL 0469 2710 10 UU'); -- ging fout
  ASSERT_EQUALS(generateISIN('NL','NBU','1100500646'),'NL 1100 5006 4613 NBU'); -- ging fout
  ASSERT_EQUALS(generateISIN('NL','UVH','862176'),'NL 8621 7619 UVH');
  ASSERT_EQUALS(generateISIN('NL','KTU','903151'),'NL 9031 5123 KTU');
  ASSERT_EQUALS(generateISIN('NL','TIAS','7294147'),'NL 7294 1472 8 TIAS');
  ASSERT_EQUALS(generateISIN('NL','UCR','7187419'),'NL 7187 4195 8 UCR');
  ASSERT_EQUALS(generateISIN('NL','TUD','548194600'),'NL 5481 9460 022 TUD');
  ASSERT_EQUALS(generateISIN('NL','UL','7906541256'),'NL 7906 5412 5643 UL');
  ASSERT_EQUALS(generateISIN('NL','RUR','6396609648'),'NL 6396 6096 4834 RUR');
END;

-- Test 1b
SET SERVEROUTPUT ON
BEGIN
  --testcases die fout moeten gaan
  --lengte gegeven studentnummer (3216 = lengte 4) komt niet overeen met die voor gegeven landcode NL en universiteitscode TUE nl. 9 (zie tabel University)
  ASSERT_EQUALS(checkForCorrectness('NL 3216 02 TUE'), 0);

  --testcases die goed moeten gaan
  ASSERT_EQUALS(checkForCorrectness('NL 4633 4809 KTU'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 4954 2537 7808 MSM'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 8051 5891 4351 NBU'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 0346 7021 0 OU'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 1483 5380 9 PTHU'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 8838 4630 6 RUG'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 0520 3256 8940 RUN'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 4755 8038 7646 RUR'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 9258 7513 6 TIAS'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 5663 9350 540 TIU'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 0161 4530 10 TUA'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 6358 9764 150 TUD'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 4528 9748 456 TUE'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 9762 6150 TUK'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 6634 7173 0 UCR'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 2735 6509 1355 UL'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 2045 2808 14 UM'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 6341 6170 3 UT'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 0440 2982 10 UU'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 7246 8587 8923 UVA'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 1714 3453 UVH'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 2487 5959 114 VU'),1);
  ASSERT_EQUALS(checkForCorrectness('NL 1087 6196 1850 WUR'),1);
END;

-- Test 1c
SET SERVEROUTPUT ON
BEGIN
  ASSERT_EQUALS(generateISIN('US','UOPX','301522821691826471941782343133'),'US 3015 2282 1691 8264 7194 1782 3431 3322 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','523595200069997478468516090574'),'US 5235 9520 0069 9974 7846 8516 0905 7432 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','582252573807395057043924916248'),'US 5822 5257 3807 3950 5704 3924 9162 4822 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','885212525407281230171639341071'),'US 8852 1252 5407 2812 3017 1639 3410 7108 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','246082509850885817941238878858'),'US 2460 8250 9850 8858 1794 1238 8788 5854 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','254813395865332527223319330076'),'US 2548 1339 5865 3325 2722 3319 3300 7614 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','650175233613662342022245787007'),'US 6501 7523 3613 6623 4202 2245 7870 0740 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','769930711156115905628621556591'),'US 7699 3071 1156 1159 0562 8621 5565 9130 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','944832934567761037045517968994'),'US 9448 3293 4567 7610 3704 5517 9689 9414 UOPX');
  ASSERT_EQUALS(generateISIN('US','UOPX','856563611608570565634652119720'),'US 8565 6361 1608 5705 6563 4652 1197 2018 UOPX');
END;
/
BEGIN
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','026279244656716148547036319831')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','758639609786562659471273765725')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','312712592806580254274361527720')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','537761976881240512166851835336')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','517042222499518545121566489856')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','931235450434319987693601579102')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','470334193389226488274554674331')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','608824797474155295355678232067')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','767526170940715899949762111372')),1);
  ASSERT_EQUALS(checkForCorrectness(generateISIN('US','UOPX','518038916173926003367541064107')),1);
END;

-- Test cases
-- Zie meegeleverde testscripts
--DECLARE
--BEGIN
--END;
--/

-- Drop alle tables, functies en procedures weer
DROP TABLE University;
DROP TABLE Country;
DROP FUNCTION generateISIN;
DROP FUNCTION checkForCorrectness;
DROP PROCEDURE ASSERT_EQUALS;