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