SQL Server Database Assignment: Kware Database Implementation

This document provides a comprehensive solution to a MS SQL Server database assignment, focusing on the implementation of a Kware database. The assignment covers various aspects of database design and management, including the installation of SQL Server, the use of SQL Server Configuration Manager, and a comparison of SQL Server's architecture to MS Access. The solution includes detailed work instructions with screenshots for the SQL Server installation, along with explanations of the Kware script and its functionality. The core of the assignment revolves around creating table statements, defining data types, and implementing primary, foreign, and check constraints for the Kware database. The solution demonstrates the creation of tables like Supplier, PurchaseOrder, and PurchaseOrderProduct, along with the application of constraints to ensure data integrity. Additionally, the document explores advanced SQL concepts such as print statements, select statements, string concatenations, and date calculations. It includes homework solutions with complex SQL queries involving joins, functions, and subqueries. The document further delves into database mail, cursors, stored procedures, triggers, and SQL jobs, providing code examples and explanations for each. The design choices are discussed in the context of database normalization, and the document touches upon topics like transactions, security, and application front-end considerations. Overall, the document serves as a valuable resource for students learning about SQL Server database design, implementation, and advanced SQL techniques.

Running head: MS SQL SERVER
MS SQL Server
Name of the Student:
Name of the University:
Author Note

1
MS SQL SERVER
Week 1 - Getting started
SQL Server dev Version installation
The screenshots for the step by step installation of the SQL Server dev version has been
described below:

2
MS SQL SERVER

3
MS SQL SERVER

4
MS SQL SERVER

5
MS SQL SERVER

6
MS SQL SERVER
Introduction to Server configuration manager
The tools associated with the SQL Severs are being managed with the help of the SQL
Server configurations manager. The network protocols associated with the server and the server
itself is configured with the help of this tool.

7
MS SQL SERVER
All the instances of the servers are available for the users to view and manage with the
help of this tool.
Architecture of SQL Server versus Access
The MS access database utilizes the file servers design where the details of the database
can be adjusted within a single file. On the other hand, the SQL Server provides the option of a
client server architecture, which provide the option of a centralized database.

8
MS SQL SERVER
Kware Script explanation
The Kware Script runs without any errors and the Kware database is created. However, it
is seen that Kware database only has 4 table. The customer, Products, SalesOrder,
SalesOrderProduct tables are the ones which has been created.

9
MS SQL SERVER
Create table statements, Data types
Supplier Table is created
USE [Kware]
GO
/****** Object: Table [dbo].[Supplier] Script Date: 2/15/2020 1:07:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Supplier](
[SID] [int] NOT NULL,
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED
(

10
MS SQL SERVER
[SID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Purchase Order table is created
USE [Kware]
GO
/****** Object: Table [dbo].[PurchaseOrder] Script Date: 2/15/2020 1:08:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PurchaseOrder](
[POID] [int] NOT NULL,

11
MS SQL SERVER
[SID] [int] NOT NULL,
[Numbr] [numeric](18, 0) NOT NULL,
[Date] [datetime] NOT NULL,
[FullPrice] [money] NOT NULL,
[Status] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_ProductOrder] PRIMARY KEY CLUSTERED
(
[POID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PurchaseOrder] WITH CHECK ADD CONSTRAINT
[FK_ProductOrder_Supplier] FOREIGN KEY([SID])
REFERENCES [dbo].[Supplier] ([SID])
GO