Artigos Banco de Dados Criando um banco de dados no SQL Server - Estudo de caso

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];
GO
Listagem 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];'');
GO
Listagem 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]
GO
Listagem 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])
)
GO
Listagem 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.
Diagrama
Figura 1. Diagrama
Tipos de dados
Figura 2. Tipos de dados
Referências: