· 8 years ago · Apr 23, 2018, 07:48 AM
1-- REMOVE PREVIOUS DATABASE --
2USE [master]
3GO
4/****** Object: Database [ToyStoreDatabase] Script Date: 23.04.2018 08:37:47 ******/
5DROP DATABASE IF EXISTS [ToyStoreDatabase]
6GO
7
8-- CREATE NEW DATABASE --
9CREATE DATABASE [ToyStoreDatabase]
10 CONTAINMENT = NONE
11 ON PRIMARY
12( NAME = N'ToyStoreDatabase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ToyStoreDatabase.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
13 LOG ON
14( NAME = N'ToyStoreDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\ToyStoreDatabase_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
15GO
16ALTER DATABASE [ToyStoreDatabase] SET COMPATIBILITY_LEVEL = 130
17GO
18ALTER DATABASE [ToyStoreDatabase] SET ANSI_NULL_DEFAULT OFF
19GO
20ALTER DATABASE [ToyStoreDatabase] SET ANSI_NULLS OFF
21GO
22ALTER DATABASE [ToyStoreDatabase] SET ANSI_PADDING OFF
23GO
24ALTER DATABASE [ToyStoreDatabase] SET ANSI_WARNINGS OFF
25GO
26ALTER DATABASE [ToyStoreDatabase] SET ARITHABORT OFF
27GO
28ALTER DATABASE [ToyStoreDatabase] SET AUTO_CLOSE OFF
29GO
30ALTER DATABASE [ToyStoreDatabase] SET AUTO_SHRINK OFF
31GO
32ALTER DATABASE [ToyStoreDatabase] SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF)
33GO
34ALTER DATABASE [ToyStoreDatabase] SET AUTO_UPDATE_STATISTICS ON
35GO
36ALTER DATABASE [ToyStoreDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF
37GO
38ALTER DATABASE [ToyStoreDatabase] SET CURSOR_DEFAULT GLOBAL
39GO
40ALTER DATABASE [ToyStoreDatabase] SET CONCAT_NULL_YIELDS_NULL OFF
41GO
42ALTER DATABASE [ToyStoreDatabase] SET NUMERIC_ROUNDABORT OFF
43GO
44ALTER DATABASE [ToyStoreDatabase] SET QUOTED_IDENTIFIER OFF
45GO
46ALTER DATABASE [ToyStoreDatabase] SET RECURSIVE_TRIGGERS OFF
47GO
48ALTER DATABASE [ToyStoreDatabase] SET DISABLE_BROKER
49GO
50ALTER DATABASE [ToyStoreDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
51GO
52ALTER DATABASE [ToyStoreDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF
53GO
54ALTER DATABASE [ToyStoreDatabase] SET PARAMETERIZATION SIMPLE
55GO
56ALTER DATABASE [ToyStoreDatabase] SET READ_COMMITTED_SNAPSHOT OFF
57GO
58ALTER DATABASE [ToyStoreDatabase] SET READ_WRITE
59GO
60ALTER DATABASE [ToyStoreDatabase] SET RECOVERY FULL
61GO
62ALTER DATABASE [ToyStoreDatabase] SET MULTI_USER
63GO
64ALTER DATABASE [ToyStoreDatabase] SET PAGE_VERIFY CHECKSUM
65GO
66ALTER DATABASE [ToyStoreDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS
67GO
68ALTER DATABASE [ToyStoreDatabase] SET DELAYED_DURABILITY = DISABLED
69GO
70USE [ToyStoreDatabase]
71GO
72ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = Off;
73GO
74ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = Primary;
75GO
76ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
77GO
78ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
79GO
80ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = On;
81GO
82ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = Primary;
83GO
84ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = Off;
85GO
86ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = Primary;
87GO
88USE [ToyStoreDatabase]
89GO
90IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [ToyStoreDatabase] MODIFY FILEGROUP [PRIMARY] DEFAULT
91GO
92-- SQL_Latin1_General_CP1_CS_AS -- DEFAULT US --
93-- CRAETE TABLES --
94USE [ToyStoreDatabase]
95GO
96-- ACCOUNT TYPE TABLE --
97CREATE TABLE [dbo].[AccountType](
98 [TypeId] int IDENTITY(1,1) NOT NULL,
99 [Name] varchar(32) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
100 );
101GO
102-- USER TABLE --
103CREATE TABLE [dbo].[User](
104 [UserId] int IDENTITY(1,1) NOT NULL,
105 [AccountTypeId] int NOT NULL,
106 [Username] varchar(32) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
107 [Password] varchar(32) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
108 [FisrtName] varchar(32) NOT NULL,
109 [LastName] varchar(32) NOT NULL,
110 [Email] varchar(32) NOT NULL
111 );
112GO
113-- PRODUCT TABLE --
114CREATE TABLE [dbo].[Product](
115 [ProductId] int IDENTITY(1,1) NOT NULL,
116 [Name] varchar(32) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL,
117 [Description] text,
118 [Quantity] bigint NOT NULL,
119 [Price] decimal(10,2) NOT NULL
120 );
121GO
122-- PAYMENT TYPE TABLE --
123CREATE TABLE [dbo].[Payment](
124 [PaymentId] int IDENTITY(1,1) NOT NULL,
125 [Name] varchar(32) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
126 );
127GO
128-- PURCHASE TABLE --
129CREATE TABLE [dbo].[Purchase](
130 [PurchaseId] int IDENTITY(1,1) NOT NULL,
131 [UserId] int NOT NULL,
132 [PaymentId] int NOT NULL,
133 [Date] datetime NOT NULL
134 );
135GO
136-- QUOTATION TABLE --
137CREATE TABLE [dbo].[OrderDetails](
138 [OrderDetailsId] int IDENTITY(1,1) NOT NULL,
139 [PurchaseId] int NOT NULL,
140 [ProductId] int NOT NULL
141 );
142GO
143ALTER TABLE [dbo].[AccountType]
144 ADD CONSTRAINT PK_Type PRIMARY KEY (TypeId);
145GO
146INSERT INTO [dbo].[AccountType] VALUES('Admin');
147INSERT INTO [dbo].[AccountType] VALUES('Customer');
148GO
149ALTER TABLE [dbo].[User]
150 ADD CONSTRAINT PK_User PRIMARY KEY (UserId),
151 CONSTRAINT FK_UserAccountType FOREIGN KEY (AccountTypeId) REFERENCES [dbo].[AccountType](TypeId);
152GO
153ALTER TABLE [dbo].[Product]
154 ADD CONSTRAINT PK_Product PRIMARY KEY (ProductId);
155GO
156ALTER TABLE [dbo].[Payment]
157 ADD CONSTRAINT PK_Payment PRIMARY KEY (PaymentId);
158GO
159ALTER TABLE [dbo].[Purchase]
160 ADD CONSTRAINT PK_Purchase PRIMARY KEY (PurchaseId),
161 CONSTRAINT KF_PurchaseToUser FOREIGN KEY (UserId) REFERENCES [dbo].[User](UserId),
162 CONSTRAINT KF_PaymentMethod FOREIGN KEY (PaymentId) REFERENCES [dbo].[Payment](PaymentId);
163GO
164ALTER TABLE [dbo].[OrderDetails]
165 ADD CONSTRAINT PK_OrderDetails PRIMARY KEY (OrderDetailsId),
166 CONSTRAINT FK_WhichProduct FOREIGN KEY (ProductId) REFERENCES [dbo].[Product](ProductId),
167 CONSTRAINT FK_WhichPurchase FOREIGN KEY (PurchaseID) REFERENCES [dbo].[Purchase](PurchaseId);