aDvp6M6m

1-- -- Initialising the database ---
2-- --------------------------------
3-- *** *** *** Drop the table if it already exists *** *** ***
4IF OBJECT_ID('dbo.fishImage', 'U') IS NOT NULL
5ALTER TABLE dbo.fishImage DROP CONSTRAINT fishFK_image_caught
6IF OBJECT_ID('dbo.fishImage', 'U') IS NOT NULL
7DROP TABLE dbo.fishImage
8GO
9IF OBJECT_ID('dbo.fishCaught', 'U') IS NOT NULL
10ALTER TABLE dbo.fishCaught DROP CONSTRAINT fishFK_caught_user
11ALTER TABLE dbo.fishCaught DROP CONSTRAINT fishFK_caught_body
12ALTER TABLE dbo.fishCaught DROP CONSTRAINT fishFK_caught_method
13ALTER TABLE dbo.fishCaught DROP CONSTRAINT fishFK_caught_type
14IF OBJECT_ID('dbo.fishCaught', 'U') IS NOT NULL
15DROP TABLE dbo.fishCaught
16GO
17IF OBJECT_ID('dbo.fishUser', 'U') IS NOT NULL
18DROP TABLE dbo.fishUser
19GO
20IF OBJECT_ID('dbo.fishBody', 'U') IS NOT NULL
21DROP TABLE dbo.fishBody
22GO
23IF OBJECT_ID('dbo.fishMethod', 'U') IS NOT NULL
24DROP TABLE dbo.fishMethod
25GO
26IF OBJECT_ID('dbo.fishType', 'U') IS NOT NULL
27DROP TABLE dbo.fishType
28GO
29
30
31-- *** *** *** Creating the tables *** *** ***
32
33CREATE TABLE dbo.fishUser
34(
35    userID INT NOT NULL IDENTITY PRIMARY KEY,
36    -- primary key column
37    userName NVARCHAR(255) NOT NULL,
38    userEmail NVARCHAR(255) NOT NULL,
39    userPassword NVARCHAR(255)
40);
41GO
42
43CREATE TABLE dbo.fishMethod
44(
45    methodID INT NOT NULL IDENTITY PRIMARY KEY,
46    -- primary key column
47    methodName NVARCHAR(255) NOT NULL
48);
49GO
50
51CREATE TABLE dbo.fishType
52(
53    typeID INT IDENTITY NOT NULL PRIMARY KEY,
54    typeName NVARCHAR (255)
55);
56GO
57
58CREATE TABLE dbo.fishBody
59(
60    bodyID INT IDENTITY NOT NULL PRIMARY KEY,
61    bodyName NVARCHAR (255)
62);
63GO
64
65CREATE TABLE dbo.fishCaught
66(
67    fishID INT IDENTITY NOT NULL PRIMARY KEY,
68    fishLength NVARCHAR (50),
69    fishWeight NVARCHAR (50),
70
71    FK_userID INT,
72    FK_bodyID INT,
73    FK_methodID INT,
74    FK_typeID INT,
75
76    CONSTRAINT fishFK_caught_user FOREIGN KEY (FK_userID) REFERENCES dbo.fishUser (userID),  
77    CONSTRAINT fishFK_caught_body FOREIGN KEY (FK_bodyID) REFERENCES dbo.fishBody (bodyID),
78    CONSTRAINT fishFK_caught_method FOREIGN KEY (FK_methodID) REFERENCES dbo.fishMethod (methodID),
79    CONSTRAINT fishFK_caught_type FOREIGN KEY (FK_typeID) REFERENCES dbo.fishType (typeID),
80);
81GO
82
83CREATE TABLE dbo.fishImage
84(
85    imageID INT IDENTITY NOT NULL PRIMARY KEY,
86    imageURL NVARCHAR (255),
87    geoLocation NVARCHAR (255),
88    FK_fishID INT,
89
90    CONSTRAINT fishFK_image_caught FOREIGN KEY (FK_fishID) REFERENCES dbo.fishCaught (fishID),
91);
92GO
93
94-- Users
95-- note: all passwords are "kodeord"
96INSERT INTO dbo.fishUSER
97(userName, userEmail, userPassword)
98VALUES
99('Denlangeline', 'JanRasmusen@mail.com', '$2a$10$0wW6B/97p1bVg6jE01Z2ouI2K3mJZOAsYEntCrCWn00XtiAkTFP3u'),
100('Fiskemund', 'Henrik112@mail.com', '$2a$10$0wW6B/97p1bVg6jE01Z2ouI2K3mJZOAsYEntCrCWn00XtiAkTFP3u'),
101('Lystfiskarn', 'Svendfisk@mail.com', '$2a$10$0wW6B/97p1bVg6jE01Z2ouI2K3mJZOAsYEntCrCWn00XtiAkTFP3u'),
102('TunaCant', 'Eriklaursen@mail.com', '$2a$10$0wW6B/97p1bVg6jE01Z2ouI2K3mJZOAsYEntCrCWn00XtiAkTFP3u'),
103('JeBait', 'Aquaman10@mail.com', '$2a$10$0wW6B/97p1bVg6jE01Z2ouI2K3mJZOAsYEntCrCWn00XtiAkTFP3u')
104GO
105
106INSERT INTO dbo.fishType
107(typeName)
108VALUES
109('Regnbueørred'),
110('Gedde'),
111('Torsk'),
112('Aborre'),
113('Skælkarpe'),
114('Ål'),
115('Tun'),
116('Laks'),
117('Havkat'),
118( 'Havørred')
119GO
120
121INSERT INTO dbo.fishBody
122(bodyName)
123VALUES
124('Sø'),
125('Å'),
126('Fjord'),
127('Hav')
128GO
129
130INSERT INTO dbo.fishMethod
131(methodName)
132VALUES
133('Medefiskeri'),
134('Fluefiskeri'),
135('Spinnefiskeri'),
136('Pirkefiskeri'),
137('Dørgring'),
138('C4-fiskeri')
139GO
140
141
142INSERT INTO dbo.fishCaught
143(FK_typeID, FK_bodyID, FK_methodID, fishWeight, fishLength, FK_userID)
144VALUES
145('1', '1', '1', '6', '70', '1'),
146('9', '4', '4', '5', '70', '2'),
147('6', '2', '1', '1,5', '40', '3'),
148('8', '3', '3', '5,4', '84', '4'),
149('3', '4', '4', '2,4', '43', '5'),
150('7', '4', '1', '290', '265', '2')
151GO
152
153/* INSERT INTO dbo.fishImage
154(imageURL, geoLocation, FK_fishID)
155VALUES
156('http://facebook.com', '100, 100', '1')
157GO */
158
159SELECT * FROM dbo.fishCaught