Lab1_BD

GO
USE master
GO
IF EXISTS (SELECT * from sys.databases where name='moedata')
        DROP DATABASE moedata
GO
CREATE DATABASE moedata
GO
ALTER DATABASE moedata SET MULTI_USER
GO
USE moedata
GO
if OBJECT_ID('f1','function') is not null
    drop function dbo.f1
go
create function dbo.f1(@chislo int)
returns bit
as
    begin
        declare @flag bit=0
        declare @a nvarchar(10)
        set @a=Convert(nvarchar(10),@chislo)
        if @a like '%0%'
            set @flag=1
            return @flag
    end
go
if OBJECT_ID('f2','function') is not null
    drop function dbo.f2
go
create function dbo.f2(@phone nvarchar(9))
returns bit
as
  begin
    declare @flag bit='false'
    declare @sum int=0
    set @sum=@sum+SUBSTRING(@phone,1,1)+SUBSTRING(@phone,2,1)+SUBSTRING(@phone,3,1)+
    +SUBSTRING(@phone,5,1)+SUBSTRING(@phone,6,1)+SUBSTRING(@phone,8,1)+SUBSTRING(@phone,9,1)
    set @sum=@sum%2
    if (@sum)=1
        set @flag='true'
    return @flag
  end
go
if OBJECT_ID('f3','function') is not null
    drop function dbo.f3
go
create function dbo.f3(@fam nvarchar(25))
returns bit
as
  begin
    declare @flag bit='false'
    declare @i int=1
    declare @cc int=0
    declare @dfg nvarchar(1)
    while @i<25
    begin
        set @dfg=substring(@fam,@i,1)
        if(substring(@fam,@i,1) like'[ёуеэоаяию]')
        begin
            set @cc=@cc+1
        end
        set @i=@i+1
    end
    if(@cc<6)
        set @flag='true'
    return @flag
  end
go
if OBJECT_ID('f4','function') is not null
    drop function dbo.f4
go
create function dbo.f4(@fam nvarchar(7))
returns bit
as
  begin
    declare @flag bit='false'
    declare @i int=1
    declare @cc int=0
    while @i<=LEN(@fam)
    begin
        if(substring(@fam,@i,1) like'/')
         set @cc=@cc+1
         set @i=@i+1
    end
    if @cc<2
        set @flag='true'
    return @flag
  end
go
if OBJECT_ID('f5','function') is not null
    drop function dbo.f5
go
create function dbo.f5(@fam nvarchar(7))
returns bit
as
  begin
    declare @flag bit='false'
    declare @i int=1
    declare @cc int=0
    while @i<=LEN(@fam)
    begin
        if(substring(@fam,@i,1) like'[^0-9]')
         set @cc=@cc+1
         set @i=@i+1
    end
    if @cc=0
        set @flag='true'
    return @flag
  end
go
if OBJECT_ID('f6','function') is not null
    drop function dbo.f6
go
create function dbo.f6(@fam nvarchar(7))
returns bit
as
  begin
    declare @flag bit='false'
    declare @i int=1
    declare @cc int=0
    while @i<=LEN(@fam)
    begin
        if(substring(@fam,@i,1) like'[0-9]')
         set @cc=@cc+1
         set @i=@i+1
    end
    if @cc=LEN(@fam)-1 and substring(@fam,LEN(@fam),1) like'[а-я]'
        set @flag='true'
    return @flag
  end
go
CREATE TABLE Street
(StreetId int NOT NULL identity(1,1)PRIMARY KEY,
Name nvarchar(25) NOT NULL)
GO
CREATE TABLE Fam
(FamId int NOT NULL identity(1,1) PRIMARY KEY,
Name nvarchar(25) NOT NULL check(dbo.f3(Name)='true'))--Name not like '%[яиюэоауеё]%[яиюэоауеё]%[яиюэоауёе]%[яиюэёоауе]%[яиёюэоауе]%[яиюэоаёуе]%'))
GO
INSERT INTO Fam(Name)
         VALUES ('Яблочных')
INSERT INTO Fam(Name)
         VALUES ('Апельсинов')
INSERT INTO Fam(Name)
         VALUES ('Виноградов')
INSERT INTO Fam(Name)
         VALUES ('Грушев')
INSERT INTO Fam(Name)
         VALUES ('Арбузова')
insert into Fam(Name) values('eeeeeее')
GO
CREATE TABLE Subscriber
(SubscriberId int NOT NULL identity(1,1) Primary Key,
PhoneNumber nvarchar(9) check(PhoneNumber like'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]' and dbo.f2(PhoneNumber)='true'),
FamId int NOT NULL,
Iniz nvarchar(4),
Indeks nvarchar(5),
StreetId int NOT NULL,
HouseNumber nvarchar(7) check (dbo.f4(HouseNumber)='true' or dbo.f5(HouseNumber)='true' or dbo.f6(HouseNumber)='true'),-- check(not like '%/%/%' or like'[0-9][0-9][0-9][0-9]' or like''),
FlatNumber int check(dbo.f1(FlatNumber)='true'),
Sex nvarchar(1),
FOREIGN KEY (FamID) REFERENCES Fam(FamId),
FOREIGN KEY (StreetId) REFERENCES Street(StreetId))
GO
INSERT INTO Street(Name)
                 VALUES ('Пушкинская')
INSERT INTO Street(Name)
                 VALUES ('Лермонтовская')
INSERT INTO Street(Name)
                 VALUES ('Чуковская')
INSERT INTO Street(Name)
                 VALUES ('Толкиенская')

go
INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES ('777-77-77',1, 'А.А.','61005',2, '13а',30,'М')
INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES ('999-99-99',2, 'Р.В', '61005', 1,'1/2',40,'М')
INSERT INTO Subscriber(PhoneNumber, FamId, Iniz,Indeks,StreetId, HouseNumber, FlatNumber, Sex)
         VALUES ('555-55-55',1, 'Р.В', '61005', 1,'1',405,'М')
go
select * from Subscriber
go