· 9 years ago · May 16, 2017, 06:04 PM
1/*
2Utwórz projekt bazy danych dla firmy hostingowej. Firma ta zatrudniać będzie pracowników oraz będzie posiadać serwerownie w różnych miejscach w polsce.
3Klienci firmy mogą posiadać wiele kont hostinowych. W bazie danych powinny znajdować się informacje o serwerach oraz ofercie kont hostinowych dla klientów.
4
5
6
7*/
8
9
10SET DATEFORMAT ymd;
11GO
12
13IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='konta_hostingowe')
14DROP TABLE konta_hostingowe;
15IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='pracownik')
16DROP TABLE pracownik;
17IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='klient')
18DROP TABLE klient;
19IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='pakiet_hostingowy')
20DROP TABLE pakiet_hostingowy;
21IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='serwer')
22DROP TABLE serwer;
23IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='miejsce')
24DROP TABLE miejsce;
25GO
26
27-- Miejsca
28CREATE TABLE miejsce (
29 id_miejsce INT PRIMARY KEY IDENTITY (1, 1),
30 ulica VARCHAR(24) NOT NULL,
31 numer VARCHAR(8) NOT NULL,
32 miasto VARCHAR(24) NOT NULL,
33 kod CHAR(6) NOT NULL,
34 telefon VARCHAR(16),
35 uwagi VARCHAR(40)
36);
37GO
38SET IDENTITY_INSERT miejsce ON
39GO
40INSERT INTO miejsce (id_miejsce, ulica, numer, miasto, kod, telefon, uwagi)
41VALUES
42(1, 'Dyrekcyjna', '6', 'Gdańsk', '82-400', '54 444 44', 'Serwerownia 1'),
43(2, 'Poznańska', '1', 'Gdynia', '74-222', '55555532', 'Serwerownia 2'),
44
45(3, 'Zielona', '1', 'Gdynia', '81-001', '500500500', 'Pracownicy'),
46
47(4, 'Warszawska', '32', 'Gdynia', '81-002', '600600600', NULL),
48(5, 'Poznańska', '1B/30', 'Gdynia', '81-003', '600500400', NULL),
49(6, 'Zielona', '7', 'Gdynia', '81-001', '500111000', NULL),
50(7, 'Bordowa', '40', 'Gdynia', '81-004', NULL, NULL),
51(8, 'Aleja Zwycięstwa', '40/3', 'Gdynia', '81-560', NULL, NULL)
52GO
53SET IDENTITY_INSERT miejsce OFF
54GO
55
56-- Pracownicy
57CREATE TABLE pracownik
58(
59 id_pracownik int NOT NULL PRIMARY KEY IDENTITY (1, 1),
60 imie VARCHAR(20) NOT NULL,
61 nazwisko VARCHAR(20) NOT NULL,
62 login VARCHAR(50) UNIQUE NOT NULL,
63 passwd VARCHAR(64) NOT NULL,
64 email VARCHAR(50) UNIQUE NOT NULL,
65 data_zatr DATETIME NOT NULL,
66 id_miejsce INT NOT NULL,
67)
68GO
69INSERT INTO pracownik (imie, nazwisko, login, email, passwd, data_zatr, id_miejsce)
70VALUES
71('Patryk', 'Chęciński', 'pchec', 'patryk.checinski@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'superhaslo1'), 2), '2015-01-01', 3),
72('Paulina', 'Niewiarowska', 'pniew', 'paulina.niewiarowska@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'ciastko123'), 2), '2017-01-01', 3),
73('Jan', 'Nowak', 'jnowa', 'jak.nowak@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'qwerty'), 2), '2014-12-03', 3),
74('Juliam', 'Tuwim', 'jtuwi', 'juliam.tuwim@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'lokomotywa'), 2), '2014-03-30', 3),
75('Stanisław', 'Trąbka', 'strab', 'stanislaw.trabka@firma.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'trabka'), 2), '2017-05-10', 3)
76GO
77
78-- Klienci
79CREATE TABLE klient
80(
81 id_klient INT NOT NULL PRIMARY KEY IDENTITY (1, 1),
82 imie VARCHAR(20) NOT NULL,
83 nazwisko VARCHAR(20) NOT NULL,
84 email VARCHAR(50) UNIQUE NOT NULL,
85 email2 VARCHAR(50) NULL,
86 passwd VARCHAR(64) NOT NULL,
87 NIP char(10) NULL CHECK (ISNUMERIC(NIP) = 1 AND LEN(NIP) = 10),
88 pesel char(11) NULL CHECK (ISNUMERIC(pesel) = 1 AND LEN(PESEL) = 11),
89 id_miejsce INT NOT NULL,
90)
91GO
92INSERT INTO klient (imie, nazwisko, email, email2, passwd, NIP, pesel, id_miejsce)
93VALUES
94('Jan', 'Nowak', 'nowaczek@wp.pl', NULL, CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 4),
95('Tomasz', 'Adamczak', 'dominator323@gmail.com', 'admin@adamczyk.pl', CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 5),
96('Piotr', 'Malczyk', 'piotrek.122@wp.pl', 'bok@superstrona.com', CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 6),
97('Paweł', 'Fiodorowicz', 'pfiodrowicz@gmail.com', NULL, CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 7),
98('Aniela', 'Dalgiewicz', 'anielka12@o2.pl', NULL, CONVERT(VARCHAR(65), HashBytes('SHA1', 'haslo'), 2), '1234567890', '12345678901', 8)
99GO
100
101-- Pakiety
102CREATE TABLE pakiet_hostingowy
103(
104 id_pakiet INT PRIMARY KEY IDENTITY (1, 1),
105 nazwa VARCHAR(20) UNIQUE NOT NULL,
106 opis TEXT NOT NULL,
107 limit_ftp INT NOT NULL,
108 limit_mail INT NOT NULL,
109 transfer_msc INT NOT NULL
110)
111GO
112INSERT INTO pakiet_hostingowy (nazwa, opis, limit_ftp, limit_mail, transfer_msc)
113VALUES
114('start2015', 'Pakiet na start', 3, 10, 300),
115('biznes2015', 'Pakiet dla klientów biznesowych', 10, 30, 900),
116('pro2015', 'Pakiet dla wymagających klientów', 30, 100, 1000),
117('start2.0', 'Nowa oferta dla zwykłych ludzi!', 5, 15, 450),
118('pro 2.0', 'Najlepsza oferta', 50, 200, 2000)
119GO
120
121-- Serwery
122CREATE TABLE serwer
123(
124 id_serwer int NOT NULL PRIMARY KEY IDENTITY (1, 1),
125 id_miejsce INT NOT NULL,
126 nazwa VARCHAR(50) NOT NULL,
127 adres_ip VARCHAR(20) NOT NULL
128)
129GO
130INSERT INTO serwer (id_miejsce, nazwa, adres_ip)
131VALUES
132(1, 's100', '195.168.11.101'),
133(1, 's101', '195.168.11.103'),
134(2, 'sd2', '196.77.45.102'),
135(2, 'sd3', '196.77.45.103'),
136(2, 'sd4', '196.77.45.104')
137
138
139CREATE TABLE konta_hostingowe
140(
141 login VARCHAR(20) PRIMARY KEY NOT NULL,
142 id_klient INT NOT NULL,
143 id_pracownik INT,
144 id_pakiet INT NOT NULL,
145 id_serwer INT NOT NULL,
146 passwd VARCHAR(65) NOT NULL,
147
148)
149GO
150
151INSERT INTO konta_hostingowe
152VALUES
153('sh200000', 1, NULL, 1, 1, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test1'), 2)),
154('sh200001', 1, 1, 2, 1, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test2'), 2)),
155('sh200002', 2, 1, 2, 2, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test3'), 2)),
156('sh200003', 3, 2, 3, 4, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test4'), 2)),
157('sh200004', 4, 2, 3, 5, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test5'), 2)),
158('sh200005', 4, 3, 4, 5, CONVERT(VARCHAR(65), HashBytes('SHA1', 'test6'), 2))
159
160SELECT * FROM klient;
161SELECT * FROM pracownik;
162SELECT * FROM pakiet_hostingowy;
163SELECT * FROM serwer;
164SELECT * FROM konta_hostingowe;
165
166
167ALTER TABLE konta_hostingowe ADD CONSTRAINT fk_konta_hostingowe_klient FOREIGN KEY(id_klient) REFERENCES klient(id_klient);
168ALTER TABLE konta_hostingowe ADD CONSTRAINT fk_konta_hostingowe_pracownik FOREIGN KEY(id_pracownik) REFERENCES pracownik(id_pracownik);
169ALTER TABLE konta_hostingowe ADD CONSTRAINT fk_konta_hostingowe_pakiet_hostingowy FOREIGN KEY(id_pakiet) REFERENCES pakiet_hostingowy(id_pakiet);
170ALTER TABLE konta_hostingowe ADD CONSTRAINT fk_konta_hostingowe_serwer FOREIGN KEY(id_serwer) REFERENCES serwer(id_serwer);
171
172ALTER TABLE pracownik ADD CONSTRAINT fk_pracownik_miejsce FOREIGN KEY(id_miejsce) REFERENCES miejsce(id_miejsce);
173ALTER TABLE serwer ADD CONSTRAINT fk_serwer_miejsce FOREIGN KEY(id_miejsce) REFERENCES miejsce(id_miejsce);
174ALTER TABLE klient ADD CONSTRAINT fk_klient_miejsce FOREIGN KEY(id_miejsce) REFERENCES miejsce(id_miejsce);