· 6 years ago · Mar 26, 2019, 10:48 PM
1/*
2 * SQL Server Script
3 *
4 * In a local environment (for example, with the SQLServerExpress instance
5 * included in the VStudio installation) it will be necessary to create the
6 * database and the user required by the connection string. So, the following
7 * steps are needed:
8 *
9 * Configure the @Default_DB_Path variable with the path where
10 * database and log files will be created
11 *
12 * This script can be executed from MS Sql Server Management Studio Express,
13 * but also it is possible to use a command Line syntax:
14 *
15 * > sqlcmd.exe -U [user] -P [password] -I -i SqlServerCreateTables.sql
16 *
17 */
18
19
20 /******************************************************************************/
21 /*** PATH to store the db files. This path must exists in the local system. ***/
22 /******************************************************************************/
23 DECLARE @Default_DB_Path as VARCHAR(64)
24 SET @Default_DB_Path = N'C:\SourceCode\DataBase\'
25
26USE [master]
27
28
29/***** Drop database if already exists ******/
30IF EXISTS (SELECT name FROM sys.databases WHERE name = 'practica_test')
31DROP DATABASE [practica_test]
32
33
34USE [master]
35
36
37/* DataBase Creation */
38
39
40DECLARE @sql nvarchar(500)
41
42SET @sql =
43 N'CREATE DATABASE [practica_test]
44 ON PRIMARY ( NAME = practica_test, FILENAME = "' + @Default_DB_Path + N'practica_test.mdf")
45 LOG ON ( NAME = practica_test_log, FILENAME = "' + @Default_DB_Path + N'practica_test_log.ldf")'
46
47EXEC(@sql)
48PRINT N'Database [practica_test] created.'
49GO
50
51
52USE [practica_test]
53
54
55/* ********** Drop Table UserProfile if already exists *********** */
56
57IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[Users]') AND type in ('U'))
58DROP TABLE [Users]
59GO
60
61/*
62 * Create tables.
63 * UserProfile table is created. Indexes required for the
64 * most common operations are also defined.
65
66 * Product table is created.
67 */
68
69/* UserProfile */
70CREATE TABLE Users (
71 userId bigint IDENTITY(1,1) NOT NULL,
72 login varchar(30) NOT NULL,
73 password varchar(50) NOT NULL,
74 name varchar(30) NOT NULL,
75 lastName varchar(40) NOT NULL,
76 email varchar(60) NOT NULL
77
78 CONSTRAINT [PK_Users] PRIMARY KEY (userId),
79 CONSTRAINT [UniqueKey_Login] UNIQUE (login)
80)
81CREATE NONCLUSTERED INDEX [IX_UsersIndexByLoginName]
82ON [Users] ([login] ASC)
83PRINT N'Table Users created.'
84GO
85
86/* ********** Drop Table Products if already exists *********** */
87IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[Products]') AND type in ('U'))
88DROP TABLE [Products]
89GO
90/* Product */
91CREATE TABLE Products (
92 productId bigint IDENTITY(1,1) NOT NULL,
93 name varchar(30) NOT NULL,
94 category varchar(30) NOT NULL,
95 date DATE NOT NULL
96
97 CONSTRAINT [PK_Products] PRIMARY KEY (productId)
98)
99PRINT N'Table Products created.'
100GO
101
102/* ********** Drop Table Ratings if already exists *********** */
103IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[Ratings]') AND type in ('U'))
104DROP TABLE [Ratings]
105GO
106/* Ratings */
107CREATE TABLE Ratings (
108 ratingId bigint IDENTITY(1,1) NOT NULL,
109 vote TINYINT NOT NULL,
110 comment VARCHAR(512) NOT NULL,
111 date DATE NOT NULL
112
113 CONSTRAINT [PK_Ratings] PRIMARY KEY (ratingId)
114)
115PRINT N'Table Ratings created.'
116GO