· 4 years ago · Apr 01, 2021, 10:36 AM
1-- If database NOT exists create appdatabase
2IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'appdatabase')
3 BEGIN
4 CREATE DATABASE [appdatabase]
5 COLLATE Croatian_CI_AS;
6 END
7 GO
8 USE [appdatabase]
9 GO
10-- If table NOT exists create employee table: parent table
11IF OBJECT_ID('employee', 'U') IS NULL
12BEGIN
13 CREATE TABLE dbo.employee
14 (
15 ID BIGINT NOT NULL UNIQUE,
16 name VARCHAR(100) NOT NULL,
17 surname VARCHAR(100) NOT NULL,
18 birthdate DATE,
19 employeesince DATE,
20 description VARCHAR(300),
21 username VARCHAR(30) NOT NULL UNIQUE,
22 password VARCHAR(30) NOT NULL,
23 flag CHAR(2) NOT NULL,
24 department VARCHAR(20) NOT NULL
25 CONSTRAINT PK_employee PRIMARY KEY (name, surname)
26 );
27 INSERT INTO dbo.employee (ID, name, surname, birthdate, employeesince, description, username, password, flag, department) VALUES
28 ('10123456789123', 'Jonny', 'Jones', '1982-11-30', '2001-09-11', 'Office worker since', 'jonny', 'jonnypass', 'A', 'Office'),
29 ('10021100110013', 'Leonardo', 'D Vincy', '1980-12-03', '2012-03-01', 'Good and hard working employee', 'leonardo', 'leo123', 'U', 'Office'),
30 ('15503300330033', 'Daniel', 'Viesta', '1990-11-01', '1998-07-21', 'Works since companny started', 'dviesta', '13522', 'U', 'Outourcing'),
31 ('10203982102211', 'Leonardo', 'Turtle', '1978-03-07', '1999-07-12', 'Working all day long', 'leonardo1', 'leo898', 'A', 'Main office'),
32 ('11112982102110', 'Marine', 'Sunshine', '1991-07-15', '2002-02-16', 'Nothing to write here', 'octopus', 'octopuspass', 'A', 'General building'),
33 ('11112982102120', 'Dare', 'Mc Donalds', '1991-08-15', '2000-02-06', 'Nothing to write here', 'daremc', 'daremcpass', 'U', 'General building'),
34 ('11112982102130', 'Marlin', 'Manson', '1981-11-15', '2002-04-14', 'Nothing to write here', 'marlinm', 'marlinmanson', 'U', 'General building'),
35 ('11112982102140', 'David', 'Colins', '1978-07-15', '2012-02-16', 'Nothing to write here', 'davidc', 'davidcolins', 'U', 'General building')
36
37END;
38-- If table NOT exists create userlogin table: child table REFERENCE to employee
39IF OBJECT_ID('userlogin', 'U') IS NULL
40BEGIN
41 CREATE TABLE dbo.userlogin
42 (
43 ID BIGINT NOT NULL,
44 username VARCHAR(30) NOT NULL UNIQUE
45 CONSTRAINT FK_userlogin FOREIGN KEY (ID)
46 REFERENCES dbo.employee (ID)
47 ON DELETE CASCADE
48 ON UPDATE CASCADE
49 );
50 INSERT INTO dbo.userlogin (ID, username) VALUES
51 ('10123456789123', 'jonny'),
52 ('10021100110013', 'leonardo'),
53 ('15503300330033', 'dviesta'),
54 ('10203982102211', 'leonardo1'),
55 ('11112982102110', 'octopus'),
56 ('11112982102120', 'daremc'),
57 ('11112982102130', 'marlinm'),
58 ('11112982102140', 'david')
59END