dbsys.info

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 støtter CHECK-regler fra versjon 8.0. I systemer som ikke støtter CHECK-regler kan man i stedet bruke triggere, se kapittel 13.

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.

  1. Rolv Wesenlund spiller flere roller i Norske byggeklosser. Dette er ikke lov når FilmNr+Skuespiller er primærnøkkel.
  2. 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.
  3. 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 sykkelutleie.

Det er en trykkfeil i oppgaveteksten. Tabellen Utleie skal ha kolonner (KNr, MNr, KopiNr, TidUt, TidInn). TidUt og TidInn inneholder både dato og klokkeslett.

MS Access har ikke funksjonalitet for å utføre skriptfiler. Hver enkelt SQL-setning må utføres for seg. Kolonnen Modell.Dagpris kan gis datatype CURRENCY.


DROP TABLE IF EXISTS Utleie;
DROP TABLE IF EXISTS Kunde;
DROP TABLE IF EXISTS Sykkel;
DROP TABLE IF EXISTS Modell;

CREATE TABLE Modell
(
  MNr        INTEGER,
  Fabrikk    VARCHAR(50),
  Betegnelse VARCHAR(50),
  Kategori   VARCHAR(20),
  Dagpris    DECIMAL(8, 2),
  CONSTRAINT ModellPK PRIMARY KEY (MNr)
);

CREATE TABLE Sykkel
(
  MNr    INTEGER,
  KopiNr SMALLINT,
  Ramme  SMALLINT,
  Farge  VARCHAR(20),
  CONSTRAINT SykkelPK PRIMARY KEY (MNr, KopiNr),
  CONSTRAINT ModellFK FOREIGN KEY (MNr) REFERENCES Modell(MNr)
);

CREATE TABLE Kunde
(
  KNr       INTEGER AUTO_INCREMENT,
  Fornavn   VARCHAR(20),
  Etternavn VARCHAR(50),
  Mobil     VARCHAR(15),
  CONSTRAINT KundePK PRIMARY KEY (KNr)
);

CREATE TABLE Utleie
(
  KNr    INTEGER,
  MNr    INTEGER,
  KopiNr SMALLINT,
  TidUt  DATETIME,
  TidInn DATETIME,
  CONSTRAINT UtleiePK PRIMARY KEY (MNr, KopiNr, DatoUt, KlSlettUt),
  CONSTRAINT KundeFK FOREIGN KEY (KNr) REFERENCES Kunde(KNr),
  CONSTRAINT SykkelFK FOREIGN KEY (MNr, KopiNr) REFERENCES Sykkel(MNr, KopiNr)
);

INSERT INTO
  Modell(MNr, Fabrikk, Betegnelse, Kategori, Dagpris)
VALUES
  (1, 'Avante', 'Birken classic', 'Terreng', 75.00);

INSERT INTO
  Modell(MNr, Fabrikk, Betegnelse, Kategori, Dagpris)
VALUES
  (2, 'Trailo', 'Askeladden', 'Hybrid', 55.00);
  
INSERT INTO
  Sykkel(MNr, KopiNr, Ramme, Farge)
VALUES
  (1, 1, 25, 'grå');
  
INSERT INTO
  Sykkel(MNr, KopiNr, Ramme, Farge)
VALUES
  (1, 2, 28, 'rød');
  
INSERT INTO
  Sykkel(MNr, KopiNr, Ramme, Farge)
VALUES
  (2, 1, 25, 'blå');
  
INSERT INTO
  Sykkel(MNr, KopiNr, Ramme, Farge)
VALUES
  (2, 2, 27, 'svart');
  
INSERT INTO
  Sykkel(MNr, KopiNr, Ramme, Farge)
VALUES
  (2, 3, 27, 'grå');

INSERT INTO Kunde(Fornavn, Etternavn, Mobil)
VALUES ('Varg', 'Virrum', '99887766');

INSERT INTO Kunde(Fornavn, Etternavn, Mobil)
VALUES ('Trude', 'Stein', '44556677');

INSERT INTO
  Utleie(KNr, MNr, KopiNr, TidUt, TidInn)
VALUES
  (1, 1, 1, '2019-08-20 12:31:45', '2019-08-21 15:01:58');
  
INSERT INTO
  Utleie(KNr, MNr, KopiNr, TidUt, TidInn)
VALUES
  (2, 1, 2, '2019-08-22 09:15:41', '2019-08-22 14:07:19');

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', 2016, 22000, 'Kasko',    50, 4500.00),
  (1, 'AD33445', 2001, 11000, 'Ansvar',   70, 2300.00),
  (2, 'KZ99887', 2014, 18000, 'Kasko',    20, 7800.00),
  (3, 'PN29298', 2014, 25000, 'Delkasko', 50, 5200.00);
  
INSERT INTO
  Skadesak(ForsNr, RegDato, SkadeType)
VALUES
  (1, '2018-02-10', 'Kollisjon'),
  (2, '2018-12-16', 'Brannskade'),
  (2, '2019-07-30', 'Kollisjon'),
  (2, '2019-10-01', 'Tyveri'),
  (3, '2019-10-30', 'Glassrute');