UIPAB Part 2 : MVC, UIPAB Essentails and Demo

CREATE DATABASE UIPAB
go

USE UIPAB
go

CREATE TABLE Person
(
	ID		int IDENTITY(1, 1) NOT NULL,
	Firstname	varchar(30) NOT NULL,
	Surname		varchar(30) NOT NULL,
	CONSTRAINT Person_PK PRIMARY KEY (ID)
)

CREATE TABLE Address
(
	ID		int IDENTITY(1, 1) NOT NULL,
	PersonID	int NOT NULL,
	Street		varchar(50),
	Province	varchar(50),
	Country		varchar(50),
	CONSTRAINT Address_PK PRIMARY KEY (ID),
	FOREIGN KEY (PersonID) REFERENCES Person(ID)
)

CREATE TABLE Contact
(
	ID		int IDENTITY(1, 1) NOT NULL,
	PersonID	int NOT NULL,
	Number		varchar(30),
	Type		varchar(20),
	CONSTRAINT Contact_PK PRIMARY KEY (ID),
	FOREIGN KEY (PersonID) REFERENCES Person(ID)
)
go



CREATE PROC getPersons
AS
SELECT ID, Firstname, Surname
FROM   Person 
go

CREATE PROC getPerson
@id int
AS
SELECT ID, Firstname, Surname
FROM   Person 
WHERE  ID = @id
go

CREATE PROC insPerson
@firstname varchar(30),
@surname varchar(30)
AS
INSERT INTO Person
VALUES(@firstname, @surname)
SELECT SCOPE_IDENTITY()
go

CREATE PROC upPerson
@id int,
@firstname varchar(30),
@surname varchar(30)
AS
UPDATE Person
SET    Firstname = @firstname,
       Surname = @surname
WHERE  ID = @id
go


CREATE PROC getAddressesByPerson
@id int
AS
SELECT ID, PersonID, Street, Province, Country
FROM   Address
WHERE  PersonID = @id
go


CREATE PROC insAddress
@personid int,
@street varchar(30),
@province varchar(30),
@country varchar(30)
AS
INSERT INTO Address
VALUES(@personid, @street, @province, @country)
SELECT SCOPE_IDENTITY()
go

CREATE PROC upAddress
@id int,
@street varchar(30),
@province varchar(30),
@country varchar(30)
AS
UPDATE Address
SET    Street = @street,
       Province = @province,
       Country = @country
WHERE  ID = @id
go

CREATE PROC getContactsByPerson
@id int
AS
SELECT ID, PersonID, Number, Type
FROM   Contact
WHERE  PersonID = @id
go


CREATE PROC insContact
@personid int,
@number varchar(30),
@type varchar(20)
AS
INSERT INTO Contact
VALUES(@personid, @number, @type)
SELECT SCOPE_IDENTITY()
go

CREATE PROC upContact
@id int,
@number varchar(30),
@type varchar(20)
AS
UPDATE Contact
SET    Number = @number,
       Type = @type
WHERE  ID = @id
go