Создание базы данных "Жилищно-эксплуатационная контора"

Старцева А.В. 335 группа Билет № 40

Физическая модель

CREATE TABLE HOUSE (

ID INT PRIMARY KEY NOT NULL IDENTITY (1, 1),

ADRESS VARCHAR(100)

)

CREATE TABLE FLAT (

ID INT PRIMARY KEY NOT NULL IDENTITY (1, 1),

ID_HOUSE INT NOT NULL,

FLAT_NUMBER INT,

CLIENT_ACCOUNT VARCHAR(16),

FOREIGN KEY(ID_HOUSE) REFERENCES dbo.HOUSE(ID)

)

CREATE TABLE PUBLIC_SERVICE

(

ID INT PRIMARY KEY NOT NULL IDENTITY (1, 1),

ID_FLAT INT NOT NULL,

TYPE_SERVICE VARCHAR(30),

FOREIGN KEY(ID_FLAT) REFERENCES dbo.FLAT(ID)

)

CREATE TABLE LODGER

(

ID INT PRIMARY KEY NOT NULL IDENTITY (1, 1),

ID_FLAT INT NOT NULL,

NAME VARCHAR(50),

FOREIGN KEY(ID_FLAT) REFERENCES dbo.FLAT(ID)

)

CREATE TABLE PAYMENT

(

ID INT PRIMARY KEY NOT NULL IDENTITY (1, 1),

ID_TYPE_SERVICE INT NOT NULL,

TARIFS INT,

MONTH_PAY INT,

PAID INT

FOREIGN KEY(ID_TYPE_SERVICE) REFERENCES dbo.PUBLIC_SERVICE(ID)

)    

DROP TABLE PAYMENT

GO

DROP TABLE HOUSE

GO

DROP TABLE FLAT

GO

DROP TABLE LODGER

GO

DROP TABLE PUBLIC_SERVICE

GO

INSERT INTO HOUSE (ADRESS) VALUES ('QWRTAN')

INSERT INTO HOUSE (ADRESS) VALUES ('ASDFG')

INSERT INTO HOUSE (ADRESS) VALUES ('FGHJ')

INSERT INTO FLAT (ID_HOUSE,FLAT_NUMBER,CLIENT_ACCOUNT) VALUES (1,34,'qwer12')

INSERT INTO FLAT (ID_HOUSE,FLAT_NUMBER,CLIENT_ACCOUNT) VALUES (1,36,'qwer145')

INSERT INTO FLAT (ID_HOUSE,FLAT_NUMBER,CLIENT_ACCOUNT) VALUES (1,37,'qwer12345')

INSERT INTO FLAT (ID_HOUSE,FLAT_NUMBER,CLIENT_ACCOUNT) VALUES (2,56,'qyui44')

INSERT INTO FLAT (ID_HOUSE,FLAT_NUMBER,CLIENT_ACCOUNT) VALUES (2,4,'qwer1256')

INSERT INTO LODGER (ID_FLAT,NAME) VALUES (1,'asddddf')

INSERT INTO LODGER (ID_FLAT,NAME) VALUES (2,'asddddfdf')

INSERT INTO LODGER (ID_FLAT,NAME) VALUES (3,'asf')

INSERT INTO LODGER (ID_FLAT,NAME) VALUES (4,'asddgf')

INSERT INTO LODGER (ID_FLAT,NAME) VALUES (5,'asdddgff')

INSERT INTO PUBLIC_SERVICE (ID_FLAT,TYPE_SERVICE) VALUES (1,'gas')

INSERT INTO PUBLIC_SERVICE (ID_FLAT,TYPE_SERVICE) VALUES (1,'water')

INSERT INTO PUBLIC_SERVICE (ID_FLAT,TYPE_SERVICE) VALUES (2,'water')

INSERT INTO PUBLIC_SERVICE (ID_FLAT,TYPE_SERVICE) VALUES (2,'gas')

INSERT INTO PAYMENT(ID_TYPE_SERVICE,TARIFS,MONTH_PAY,PAID) VALUES (1, 123, 1, 246)

INSERT INTO PAYMENT(ID_TYPE_SERVICE,TARIFS,MONTH_PAY,PAID) VALUES (2, 23, 1, 46)

INSERT INTO PAYMENT(ID_TYPE_SERVICE,TARIFS,MONTH_PAY,PAID) VALUES (1, 123, 2, 0)

SELECT * FROM HOUSE

SELECT * FROM FLAT

SELECT * FROM LODGER

SELECT * FROM PUBLIC_SERVICE

SELECT * FROM PAYMENT

SELECT * FROM PUBLIC_SERVICE WHERE ID_FLAT=1

CREATE FUNCTION GET_PUBLIC_SERVICE (    )

RETURNS TABLE

AS

RETURN

(

  SELECT * FROM [dbo].[PUBLIC_SERVICE]

)

GO

USE [ÆÝÊ]

SELECT * FROM [dbo].[GET_PUBLIC_SERVICE] ()

GO

CREATE TRIGGER [trigger]

   ON  [dbo].[PUBLIC_SERVICE]

   AFTER INSERT

AS

PRINT 'Izmena'

INSERT INTO PUBLIC_SERVICE VALUES (1,'gas')