SQL Server é um sistema gerenciador de banco de dados relacional criado pela empresa Microsoft. Ele foi criado em a parceria com a Sybase, que durou de 1988 até 1994 (Wikipedia, 2013). Esse produto é um servidor de banco de dados abrangente com suporte às cargas de trabalho corporativas, altos níves de desempenho, disponibilidade e segurança (Microsoft, 2013).
Revisão Bibliográfica
Database
É um arquivo que contém objetos SQL. Um banco de dados é constituído por schemas, usuários, tabelas, visões, procedimentos e outras estruturas. O database armazena dados de forma relacional (aprendendodotnet, 2013).
Schema
Schemas são uma coleção de objetos em um banco de dados, são utilizados para organizar os objetos e são bastante utilizados em padrões de sistema de banco de dados, muito úteis para garantir performance e segurança (Almeida, 2013).
Estudo de Caso
Nesse estudo de caso vamos construir o banco de dados, os schemas, os usuários e as tabelas de um sistema para supermercados.
Lista de objetos que serão criados
Database
- DB_SuperMarket.
Schemas
- Person;
- Production;
- Sales;
- Shopping.
Tabelas
- SalesMan
- Employee
- EmployeeInfo
- Customer
- CustomerInfo
- Product
- Sale
- ItemSale
- Purchase
- PurchaseItem
- Taxes
- PointOfSale
- PaymentType
- MethodOfPayment
- Department
- Post
- Provider
- ProviderInfo
- CustomerInfo
- EmployeeInfo
Usuários
- adminSuperMarket;
- appSuperMarket.
Código
CREATE DATABASE [DB_SuperMarket];
GOListagem 1. Criando o banco de dados
IF NOT EXISTS (SELECT [NAME] FROM SYS.SCHEMAS WHERE NAME = ''Person'')
EXEC(''CREATE SCHEMA [Person];'');
IF NOT EXISTS (SELECT [NAME] FROM SYS.SCHEMAS WHERE NAME = ''Production'')
EXEC(''CREATE SCHEMA [Production];'');
IF NOT EXISTS (SELECT [NAME] FROM SYS.SCHEMAS WHERE NAME = ''Sales'')
EXEC(''CREATE SCHEMA [Sales];'');
IF NOT EXISTS (SELECT [NAME] FROM SYS.SCHEMAS WHERE NAME = ''Shopping'')
EXEC(''CREATE SCHEMA [Shopping];'');
GOListagem 2. Criando os schemas
IF NOT EXISTS (SELECT LOGINNAME FROM MASTER.DBO.SYSLOGINS WHERE NAME = ''adminSuperMarket'' AND
DBNAME = ''DB_SuperMarket'')
CREATE LOGIN [adminSuperMarket] WITH PASSWORD=N''adminSuperMarket'',
DEFAULT_DATABASE=[DB_SuperMarket], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
IF NOT EXISTS (SELECT LOGINNAME FROM MASTER.DBO.SYSLOGINS WHERE NAME = ''appSuperMarket'' AND
DBNAME = ''DB_SuperMarket'')
CREATE LOGIN [appSuperMarket] WITH PASSWORD=N''appSuperMarket'',
DEFAULT_DATABASE=[DB_SuperMarket], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
IF NOT EXISTS (SELECT [NAME] FROM SYSUSERS WHERE NAME = ''adminSuperMarket'')
CREATE USER [adminSuperMarket] FOR LOGIN [adminSuperMarket]
GO
IF NOT EXISTS (SELECT [NAME] FROM SYSUSERS WHERE NAME = ''appSuperMarket'')
CREATE USER [appSuperMarket] FOR LOGIN [appSuperMarket]
GOListagem 3. Criando os usuários
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''SalesMan'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[SalesMan]
GO
CREATE TABLE [Person].[SalesMan](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[Nickname] NVARCHAR(30) NOT NULL
,[IdEmployee] BIGINT NOT NULL
,[Percent] MONEY NOT NULL
,[SalesManType] INT NOT NULL CHECK([SalesManType] = 1 OR [SalesManType] = 2)--1 -
Internal / 2 - External
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Department'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[Department]
GO
CREATE TABLE [Person].[Department](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[Description] NVARCHAR(70) NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Post'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[Post]
GO
CREATE TABLE [Person].[Post](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[Description] NVARCHAR(70) NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Place'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[Place]
GO
CREATE TABLE [Person].[Place](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[ZipCode] NVARCHAR(20) NOT NULL
,[Street] NVARCHAR(150) NOT NULL
,[District] NVARCHAR(70) NOT NULL
,[City] NVARCHAR(70) NOT NULL
,[State] NVARCHAR(70) NOT NULL
,[Country] NVARCHAR(70) NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Employee'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[Employee]
GO
CREATE TABLE [Person].[Employee](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdDepartment] BIGINT NOT NULL
,[IdPost] BIGINT NOT NULL
,[FirstName] NVARCHAR(70) NOT NULL
,[LastName] NVARCHAR(70) NOT NULL
,[Birthdate] DATETIME NOT NULL
,[Document] NVARCHAR(11) NOT NULL
,[Salary] DECIMAL(38, 12) NOT NULL
,PRIMARY KEY([Id])
,CONSTRAINT [UQ_Employee_Document] UNIQUE ([Document])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''EmployeeInfo'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[EmployeeInfo]
GO
CREATE TABLE [Person].[EmployeeInfo](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdPlace] BIGINT NOT NULL
,[Phone1] NVARCHAR(20) NOT NULL
,[Phone2] NVARCHAR(20) NOT NULL
,[Phone3] NVARCHAR(20) NOT NULL
,[Mail1] NVARCHAR(150) NOT NULL
,[Mail2] NVARCHAR(150) NOT NULL
,PRIMARY KEY([Id])
)
GO
CREATE TABLE [Person].[Provider](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[CorporateName] NVARCHAR(70) NOT NULL
,[FancyName] NVARCHAR(70) NOT NULL
,[Birthdate] DATETIME NOT NULL
,[Document] NVARCHAR(11) NOT NULL
,[Salary] DECIMAL(38, 12) NOT NULL
,PRIMARY KEY([Id])
,CONSTRAINT [UQ_Provider_Document] UNIQUE ([Document])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''ProviderInfo'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[ProviderInfo]
GO
CREATE TABLE [Person].[ProviderInfo](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdPlace] BIGINT NOT NULL
,[Phone1] NVARCHAR(20) NOT NULL
,[Phone2] NVARCHAR(20) NOT NULL
,[Phone3] NVARCHAR(20) NOT NULL
,[Mail1] NVARCHAR(150) NOT NULL
,[Mail2] NVARCHAR(150) NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Customer'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[Customer]
GO
CREATE TABLE [Person].[Customer](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[FirstName] NVARCHAR(70) NOT NULL
,[LastName] NVARCHAR(70) NOT NULL
,[Birthdate] DATETIME NOT NULL
,[Document] NVARCHAR(11) NOT NULL
,PRIMARY KEY([Id])
,CONSTRAINT [UQ_Customer_Document] UNIQUE ([Document])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''CustomerInfo'' AND O.[type] in (N''U'') AND S.name = ''Person'')
DROP TABLE [Person].[CustomerInfo]
GO
CREATE TABLE [Person].[CustomerInfo](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdPlace] BIGINT NOT NULL
,[Phone1] NVARCHAR(20) NOT NULL
,[Phone2] NVARCHAR(20) NOT NULL
,[Phone3] NVARCHAR(20) NOT NULL
,[Mail1] NVARCHAR(150) NOT NULL
,[Mail2] NVARCHAR(150) NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Product'' AND O.[type] in (N''U'') AND S.name = ''Production'')
DROP TABLE [Production].[Product]
GO
CREATE TABLE [Production].[Product](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[ccode] NVARCHAR(15) NOT NULL
,[Description] NVARCHAR(150) NOT NULL
,[Stock] INT NOT NULL
,[CostPrice] DECIMAL(38, 12) NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Sale'' AND O.[type] in (N''U'') AND S.name = ''Sales'')
DROP TABLE [Sales].[Sale]
GO
CREATE TABLE [Sales].[Sale](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdPaymentType] BIGINT NOT NULL
,[IdMethodOfPayment] BIGINT NOT NULL
,[IdCustomer] BIGINT NOT NULL
,[DeliveryDate] DATETIME NOT NULL
,[SaleDate] DATETIME NOT NULL
,[ValueTotal] DECIMAL(38, 12) NOT NULL
,[QtyTotal] INT NOT NULL
,[Obs] NVARCHAR(MAX) NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''ItemSale'' AND O.[type] in (N''U'') AND S.name = ''Sales'')
DROP TABLE [Sales].[ItemSale]
GO
CREATE TABLE [Sales].[ItemSale](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdProduct] BIGINT NOT NULL
,[Value] DECIMAL(38, 12) NOT NULL
,[Qty] INT NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Purchase'' AND O.[type] in (N''U'') AND S.name = ''Shopping'')
DROP TABLE [Shopping].[Purchase]
GO
CREATE TABLE [Shopping].[Purchase](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdPaymentType] BIGINT NOT NULL
,[IdMethodOfPayment] BIGINT NOT NULL
,[IdProvider] BIGINT NOT NULL
,[DeliveryDate] DATETIME NOT NULL
,[PurchaseDate] DATETIME NOT NULL
,[ValueTotal] DECIMAL(38, 12) NOT NULL
,[QtyTotal] INT NOT NULL
,[Obs] NVARCHAR(MAX) NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''PurchaseItem'' AND O.[type] in (N''U'') AND S.name = ''Shopping'')
DROP TABLE [Shopping].[PurchaseItem]
GO
CREATE TABLE [Shopping].[PurchaseItem](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdProduct] BIGINT NOT NULL
,[Value] DECIMAL(38, 12) NOT NULL
,[Qty] INT NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''PointOfSale,'' AND O.[type] in (N''U'') AND S.name = ''Shopping'')
DROP TABLE [Shopping].[PointOfSale]
GO
CREATE TABLE [Shopping].[PointOfSale](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[IdEmployee] BIGINT NOT NULL
,[Place] NVARCHAR(150) NOT NULL
,[StartDateOfWork] DATETIME NULL
,[EndDateOfWork] DATETIME NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''Taxes'' AND O.[type] in (N''U'') AND S.name = ''dbo'')
DROP TABLE [dbo].[Taxes]
GO
CREATE TABLE [dbo].[Taxes](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[Description] NVARCHAR(150) NOT NULL
,[Value] MONEY NOT NULL
,PRIMARY KEY([Id])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''PaymentType'' AND O.[type] in (N''U'') AND S.name = ''dbo'')
DROP TABLE [dbo].[PaymentType]
GO
CREATE TABLE [dbo].[PaymentType](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[Description] NVARCHAR(150) NOT NULL
,PRIMARY KEY([Id])
,CONSTRAINT [UQ_PaymentType_Description] UNIQUE ([Description])
)
GO
IF EXISTS (SELECT O.* FROM sys.objects O JOIN sys.schemas S ON S.[schema_id] = O.[schema_id]
WHERE O.[name] = ''MethodOfPayment '' AND O.[type] in (N''U'') AND S.name = ''dbo'')
DROP TABLE [dbo].[MethodOfPayment]
GO
CREATE TABLE [dbo].[MethodOfPayment](
[Id] BIGINT IDENTITY(1, 1) NOT NULL
,[Description] NVARCHAR(150) NOT NULL
,[Qty] INT NOT NULL
,PRIMARY KEY([Id])
,CONSTRAINT [UQ_MethodOfPayment_Description] UNIQUE ([Description])
)
GOListagem 4. Criando as tabelas
Explicação
Abaixo temos uma breve explicação dos comandos que foram utilizados (MSDN, 2013).
- CREATE DATABASE: Cria um novo banco de dados e os arquivos usados para armazenar o banco de dados. Cria um instantâneo de banco de dados, ou atribui um banco de dados a partir dos arquivos destacadas de um banco de dados previamente criado.
- EXISTS: Especifica uma subconsulta para testar a existência de linhas.
- EXEC: Executa uma sequência de comando ou cadeia de caracteres dentro de um lote Transact-SQL, ou um dos seguintes módulos: procedimento armazenado do sistema definido pelo usuário, procedimento armazenado com valor escalar definido pelo usuário, função ou procedimento armazenado estendido.
- CREATE SCHEMA: Cria um esquema no banco de dados atual. A transação também pode criar tabelas e exibições no novo esquema, e definir GRANT, DENY ou REVOKE nesses objetos.
- CREATE LOGIN: Cria um login novo SQL Server.
- CREATE USER: Cria um usuário no banco de dados atual.
- DROP TABLE: Remove uma ou mais definições de tabela e todos os dados, índices, gatilhos, restrições e especificações de permissão para essas tabelas.
- CREATE TABLE: Cria uma nova tabela no SQL Server.
- CHECK: A restrição CHECK é usada para limitar o intervalo de valores que podem ser colocados em uma coluna.
- PRIMARY KEY: A restrição de chave primária identifica exclusivamente cada registro em uma tabela de banco de dados.
- IDENTITY: Indica que a coluna será do tipo auto-incremento.
- UNIQUE: Indica que a coluna não poderá receber valores repetidos.
