Løsningsforslag til kapittel 3

Oppgave 1a + 2a + 2b + 2c + 2d

Tabelldefinisjoner og valideringsregler.


CREATE TABLE Kommune
(
  KommuneNr   CHAR(4),
  KommuneNavn VARCHAR(50) UNIQUE NOT NULL,
  CONSTRAINT  KommunePN PRIMARY KEY ( KommuneNr )
);

CREATE TABLE Bilmodell
(
  Bilmerke   VARCHAR(50),
  Bilmodell  VARCHAR(50),
  Gruppe     CHAR(15),
  AntallHK   SMALLINT NOT NULL,
  CONSTRAINT BilmodellPN PRIMARY KEY (Bilmerke, Bilmodell ),
  CONSTRAINT RegelBilgruppe CHECK 
    ( Gruppe IN ( 'personbil', 'varebil', 'lastebil', 'buss' ) ),
  CONSTRAINT RegelHestekrefter CHECK
    ( AntallHK BETWEEN 1 AND 299 )
);

CREATE TABLE Bilsalg
(
  Bilmerke   VARCHAR(50),
  Bilmodell  VARCHAR(50),
  KommuneNr  CHAR(4),
  År         SMALLINT,
  Måned      SMALLINT,
  Antall     SMALLINT NOT NULL,
  CONSTRAINT BilsalgPN PRIMARY KEY
    (Bilmerke, Bilmodell, KommuneNr, År, Måned ),
  CONSTRAINT KommuneFN
    FOREIGN KEY (KommuneNr) REFERENCES Kommune (KommuneNr),
  CONSTRAINT BilmodellFN FOREIGN KEY (Bilmerke, Bilmodell)
    REFERENCES Bilmodell (Bilmerke, Bilmodell),
  CONSTRAINT RegelÅr CHECK
    ( År BETWEEN 1980 AND 2100 ),
  CONSTRAINT RegelMåned CHECK
    ( Måned BETWEEN 1 AND 12 )
);

MS Access må settes opp med støtte for SQL Server syntaks for å kunne bruke CHECK-regler. Med standardoppsett må reglene i stedet legges inn som valideringsregler ved tabelldefinisjon i utformingsvisning.

MySQL godtar syntaksen for CHECK, men utfører ikke kontrollen. Man kan i stedet bruke triggere, se kapittel 13.

MariaDB har ikke støtte for CHECK og gir feilmelding. Igjen er løsningen å bruker triggere.

I følge oppgaveteksten skulle man sikre at KommuneNavn ikke skulle inneholde repetisjoner. Teknisk gjøres dette med UNIQUE som vist i koden over. I praksis er det en dårlig idé, fordi det faktisk finnes flere kommuner med samme navn.

Oppgave 1b

Registrere en ny bilmodell.


INSERT INTO Bilmodell(Bilmerke, Bilmodell, Gruppe, AntallHK)
VALUES ('Toyota', 'Avensis', 'personbil', 90);

Oppgave 1c

Endre antall hestekrefter for bilmodellen (fra 1b).


UPDATE Bilmodell
SET AntallHK = 95
WHERE Bilmerke = 'Toyota' AND Bilmodell = 'Avensis'

Oppgave 1d

Slette bilmodellen (fra 1b).


DELETE FROM Bilmodell
WHERE Bilmerke = 'Toyota' AND Bilmodell = 'Avensis'

Oppgave 2e

Bruk av fremmednøkler for å begrense lovlige verdier.

Definer en ny tabell Gruppe med en enkelt kolonne Betegnelse. Fyll tabellen med fire rader (personbil, varebil, lastebil og buss), og la Bilmodell.Gruppe være fremmednøkkel mot den nye tabellen. Alternativt kan man innføre et løpenummer Gruppe.GruppeNr som primærnøkkel, og lagre dette nummeret i Bilmodell (Bilmodell.Gruppe må da få en talldatatype).

Oppgave 3

Primærnøkkel for RolleIFilm.

Alle de tre forslagene til primærnøkkel blir for strenge. Løsningen er å definere FilmNr+Rollenavn+Skuespiller som primærnøkkel.

Rolv Wesenlund spiller flere roller i Norske byggeklosser. Dette er ikke lov når FilmNr+Skuespiller er primærnøkkel.
Sylvester Stallone spiller Rocky i både Rocky I, Rocky II og Rocky III. Det er ikke lov når Rollenavn+Skuespiller er primærnøkkel.
I en film der man bruker én skuespiller til å spille en rollefigur som barn og en annen for å spille rollefiguren som voksen blir primærnøkkel FilmNr+Rollenavn for streng. Med rollenavn av typen "Tarzan som liten" kan det imidlertid fungere med FilmNr+Rollenavn som primærnøkkel.

Oppgave 4

Skriptfil for videobutikken Blanca.

MS Access har ikke funksjonalitet for å utføre skriptfiler. Hver enkelt SQL-setning må utføres for seg.


DROP TABLE Utleie;
DROP TABLE Eksemplar;
DROP TABLE Film;
DROP TABLE Kunde;

CREATE TABLE Kunde
(
  KNr        INTEGER,
  Fornavn    VARCHAR(50),
  Etternavn  VARCHAR(50),
  CONSTRAINT KundePN PRIMARY KEY (KNr)
);

CREATE TABLE Film
(
  FNr        INTEGER,
  Tittel     VARCHAR(100),
  År         SMALLINT,
  Land       VARCHAR(100),
  Sjanger    VARCHAR(50),
  Alder      SMALLINT,
  Tid        SMALLINT,
  Pris       DECIMAL(8,2),
  CONSTRAINT FilmPN PRIMARY KEY (FNr)
);

CREATE TABLE Eksemplar
(
  FNr        INTEGER,
  EksNr      SMALLINT,
  Format     CHAR(3),
  CONSTRAINT EksemplarPN PRIMARY KEY (FNr, EksNr),
  CONSTRAINT FilmFN FOREIGN KEY (FNr)
  REFERENCES Film (FNr)
);

CREATE TABLE Utleie
(
  FNr        INTEGER,
  EksNr      SMALLINT,
  Dato       DATE,
  KlSlett    TIME,
  KNr        INTEGER,
  CONSTRAINT UtleiePN PRIMARY KEY (FNr, EksNr, Dato, KlSLett),
  CONSTRAINT UtleieFN1 FOREIGN KEY (KNr)
    REFERENCES Kunde (KNr),
  CONSTRAINT UtleieFN2 FOREIGN KEY (FNr, EksNr)
    REFERENCES Eksemplar (FNr, EksNr)
);

INSERT INTO Kunde(KNr, Fornavn, Etternavn)
VALUES (1, 'Ola', 'Hansen');

INSERT INTO Kunde(KNr, Fornavn, Etternavn)
VALUES (2, 'Lise', 'Pettersen');

INSERT INTO Film(FilmNr, Tittel, År, Land, Sjanger, Alder, Tid, Pris)
VALUES (1, 'Casablanca', 1942, 'USA', 'Drama', 15, 102, 149.00)

INSERT INTO Eksemplar(FNr, EksNr, Format)
VALUES (1, 1, 'DVD');

INSERT INTO Eksemplar(FNr, EksNr, Format)
VALUES (1, 2, 'Blue-ray');

INSERT INTO Utleie(FNr, EksNr, Dato, KlSlett, KNr)
VALUES (1, 1, '02.01.2015', '14:45', 1);

INSERT INTO Utleie(FNr, EksNr, Dato, KlSlett, KNr)
VALUES (1, 2, '02.01.2015', '14:53', 1);

Det er naturlig å vurdere autonummerering for Kunde.KNr og Film.FNr.

I MS Access kan Film.Pris gis datatype CURRENCY.

Oppgave 5

Skriptfil for forsikringsselskap.

Vi bruker her en mer kompakt måte å skrive INSERT-setninger, der flere rader settes inn uten å gjenta kolonnenavnene for hver gang. Dette fungerer bla. i MySQL.


DROP TABLE IF EXISTS Skadesak;
DROP TABLE IF EXISTS Forsikring;
DROP TABLE IF EXISTS Kunde;

CREATE TABLE Kunde
(
  KundeNr     INT AUTO_INCREMENT,
  FødselsDato DATE,
  Fornavn     VARCHAR(30),
  Etternavn   VARCHAR(50),
  CONSTRAINT  KundePK PRIMARY KEY (KundeNr)
);

CREATE TABLE Forsikring
(
  ForsNr     INT AUTO_INCREMENT,
  KundeNr    INT NOT NULL,
  RegNr      CHAR(7) UNIQUE NOT NULL,
  RegAar     INT NOT NULL,
  KmPrAar    INT NOT NULL,
  ForsType   VARCHAR(10) NOT NULL,
  Bonus      INT,
  AarsPremie DECIMAL(8, 2),
  CONSTRAINT ForsikringPK PRIMARY KEY (ForsNr),
  CONSTRAINT ForsikringKundeFK FOREIGN KEY (KundeNr) REFERENCES Kunde(KundeNr)
);

CREATE TABLE Skadesak
(
  SaksNr      INT AUTO_INCREMENT,
  ForsNr      INT NOT NULL,
  RegDato     DATE,
  SkadeType   VARCHAR(30),
  CONSTRAINT  SkadesakPK PRIMARY KEY (SaksNr),
  CONSTRAINT  SkadesakForsikringFK FOREIGN KEY (ForsNr) REFERENCES Forsikring(ForsNr)
);

INSERT INTO
  Kunde(FødselsDato, Fornavn, Etternavn)
VALUES
  ('1988-07-17', 'Ola',    'Hansen'),
  ('1991-11-04', 'Kari',   'Mo'),
  ('1970-02-28', 'Anette', 'Lien');

INSERT INTO
  Forsikring(KundeNr, RegNr, RegAar, KmPrAar, ForsType, Bonus, AarsPremie)
VALUES
  (1, 'LY12345', 2012, 22000, 'Kasko',    50, 4500.00),
  (1, 'AD33445', 1997, 11000, 'Ansvar',   70, 2300.00),
  (2, 'KZ99887', 2010, 18000, 'Kasko',    20, 7800.00),
  (3, 'PN29298', 2010, 25000, 'Delkasko', 50, 5200.00);
  
INSERT INTO
  Skadesak(ForsNr, RegDato, SkadeType)
VALUES
  (1, '2014-02-10', 'Kollisjon'),
  (2, '2014-12-16', 'Brannskade'),
  (2, '2015-07-30', 'Kollisjon'),
  (2, '2015-10-01', 'Tyveri'),
  (3, '2015-10-30', 'Glassrute');