Start 10.30 a.m.
_________________________________________________________
Bahh man i'm slack, have alot of catchign up to do this week, at the moment i'm making the database.
A few new changes have been made. Management is in a table by itself... that normalization didn't make sense ha ha. I changed the user permission PK to a composite key instead of a surrogate one.
Tables
************************************************************************
Break 2.30 p.m. - 7.00 p.m.
************************************************************************
Stored Procedures
USER
Insert
CREATE procedure [dbo].[sp_user_Insert]
@userID smallint
,@firstName varchar(30)
,@lastName varchar(30)
,@jobDescription varchar(150)
AS
INSERT INTO [dbo].[user]
(userID
,firstName
,lastName
,jobDescription)
VALUES
(@userID
,@firstName
,@lastName
,@jobDescription)
Update
CREATE procedure [dbo].[sp_user_Update]
@userID smallint
,@firstName varchar(30)
,@lastName varchar(30)
,@jobDescription varchar(150)
AS
UPDATE [dbo].[user] SET
firstName = @firstName
,lastName = @lastName
,jobDescription = @jobDescription
WHERE userID = @userID
Delete
@userID smallint
AS
DELETE FROM [dbo].[user]
WHERE userID = @userID
USER PERMISSION
Insert
CREATE procedure [dbo].[sp_userPermission_Insert]
@permissionID nvarchar(20)
,@userID smallint
AS
INSERT INTO [dbo].[userPermission]
(permissionID
,userID)
VALUES
(@permissionID
,@userID)
Update
CREATE procedure [dbo].[sp_userPermission_Update]
@permissionID nvarchar(20)
,@userID smallint
AS
UPDATE [dbo].[userPermission] SET
permissionID = @permissionID
,userid = @userID
WHERE permissionID = @permissionID and userID = @userID
Delete
create procedure [dbo].[sp_userpermission_Delete]
@permissionID nvarchar(20)
,@userID smallint
AS
DELETE FROM [dbo].[userPermission]
PERMISSION
Insert
CREATE procedure [dbo].[sp_permission_Insert]
@permissionID nvarchar(20)
AS
INSERT INTO [dbo].[permission]
(permissionID)
VALUES
(@permissionID)
Update
CREATE procedure [dbo].[sp_permission_Update]
@permissionID nvarchar(20)
AS
UPDATE [dbo].[permission] SET
permissionID = @permissionID
WHERE permissionID = @permissionID
Delete
create procedure [dbo].[sp_permission_Delete]
@permissionID nvarchar(20)
AS
DELETE FROM [dbo].[permission]
WHERE permissionID = @permissionID
JOB
Insert
CREATE procedure [dbo].[sp_job_Insert]
@jobID smallint
,@clientID nchar(6)
,@userID smallint
,@clientAddressID smallint
,@jobDescription nvarchar(120)
,@jobCompleted bit
,@jobCreatedDate datetime
,@JobCompletedDate datetime
AS
INSERT INTO [dbo].[job]
(jobID
,clientID
,userID
,clientAddressID
,jobDescription
,jobCompleted
,jobCreatedDate
,JobCompletedDate)
VALUES
(@jobID
,@clientID
,@userID
,@clientAddressID
,@jobDescription
,@jobCompleted
,@jobCreatedDate
,@JobCompletedDate)
Update
CREATE procedure [dbo].[sp_job_Update]
@jobID smallint
,@clientID nchar(6)
,@userID smallint
,@clientAddressID smallint
,@jobDescription nvarchar(120)
,@jobCompleted bit
,@jobCreatedDate datetime
,@JobCompletedDate datetime
AS
UPDATE [dbo].[job] SET
clientID = @clientID
,userID = @userID
,clientAddressID = @clientAddressID
,jobDescription = @jobDescription
,jobCompleted = @jobCompleted
,jobCreatedDate = @jobCreatedDate
,jobCompletedDate = @JobCompletedDate
WHERE jobID = @jobID
Delete
create procedure [dbo].[sp_permission_Delete]
@permissionID nvarchar(20)
AS
DELETE FROM [dbo].[permission]
WHERE permissionID = @permissionID
CLIENT ADDRESS
Insert
CREATE procedure [dbo].[sp_clientAddress_Insert]
@clientAddressID smallint
,@clientID nchar(6)
,@streetNumber nvarchar(15)
,@streetName nvarchar(30)
,@suburb nvarchar(30)
,@city nvarchar(30)
,@country nvarchar(30)
,@geocode nvarchar(50)
,@phoneNumber nvarchar(15)
,@faxNumber nvarchar(15)
AS
INSERT INTO [dbo].[clientAddress]
(clientAddressID
,clientID
,streetNumber
,streetName
,suburb
,city
,country
,geocode
,phoneNumber
,faxNumber)
VALUES
(@clientAddressID
,@clientID
,@streetNumber
,@streetName
,@suburb
,@city
,@country
,@geocode
,@phoneNumber
,@faxNumber)
Update
CREATE procedure [dbo].[sp_clientAddress_Update]
@clientAddressID smallint
,@clientID nchar(6)
,@streetNumber nvarchar(15)
,@streetName nvarchar(30)
,@suburb nvarchar(30)
,@city nvarchar(30)
,@country nvarchar(30)
,@geocode nvarchar(50)
,@phoneNumber nvarchar(15)
,@faxNumber nvarchar(15)
AS
UPDATE [dbo].[clientAddress] SET
,streetNumber = @streetNumber
,streetName = @streetName
,suburb = @suburb
,city = @city
,country = @country
,geocode = @geocode
,phoneNumber = @phoneNumber
,faxNumber = @faxNumber
WHERE clientAddressID = @clientAddressID
Delete
create procedure [dbo].[sp_clientAddress_Delete]
@clientAddressID smallint
AS
DELETE FROM [dbo].[clientAddress]
WHERE clientAddressID = @clientAddressID
CLIENT
Insert
CREATE procedure [dbo].[sp_client_Insert]
@clientID nchar(6)
,@clientName nvarchar(30)
AS
INSERT INTO [dbo].[client]
(clientID
,clientName)
VALUES
(@clientID
,@clientName)
Update
CREATE procedure [dbo].[sp_client_Update]
@clientID nchar(6)
,@clientName nvarchar(30)
AS
UPDATE [dbo].[client] SET
clientID = @clientID
,clientName = @clientName
WHERE clientID = @clientID
Delete
create procedure [dbo].[sp_client_Delete]
@clientID nchar(6)
AS
DELETE FROM [dbo].[client]
WHERE clientID = @clientID
MANAGEMENT
Insert
CREATE procedure [dbo].[sp_management_Insert]
@businessName nvarchar(30)
,@streetNumber nvarchar(15)
,@streetName nvarchar(30)
,@suburb nvarchar(30)
,@city nvarchar(30)
,@country nvarchar(30)
,@geocode nvarchar(50)
,@phoneNumber nvarchar(15)
,@faxNumber nvarchar(15)
AS
INSERT INTO [dbo].[management]
(businessName
,streetNumber
,streetName
,suburb
,city
,country
,geocode
,phoneNumber
,faxNumber)
VALUES
(@businessName
,@streetNumber
,@streetName
,@suburb
,@city
,@country
,@geocode
,@phoneNumber
,@faxNumber)
Update
CREATE procedure [dbo].[sp_management_Update]
@businessName nvarchar(30)
,@streetNumber nvarchar(15)
,@streetName nvarchar(30)
,@suburb nvarchar(30)
,@city nvarchar(30)
,@country nvarchar(30)
,@geocode nvarchar(50)
,@phoneNumber nvarchar(15)
,@faxNumber nvarchar(15)
AS
UPDATE [dbo].[management] SET
businessName = @businessName
,streetNumber = @streetNumber
,streetName = @streetName
,suburb = @suburb
,city = @city
,country = @country
,geocode = @geocode
,phoneNumber = @phoneNumber
,faxNumber = @faxNumber
WHERE businessName = @businessName
Delete
create procedure [dbo].[sp_management_Delete]
@businessName nvarchar(30)
AS
DELETE FROM [dbo].[management]
WHERE businessName = @businessName
************************************************************************
Break 9.00 p.m. - ?p.m.
************************************************************************
7 hours
No comments:
Post a Comment