Разработка информационной системы фильмотеки Госфильмфонда России

[ActAward] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[ActAward]([id] [bigint] IDENTITY(1,1)NOTNULL,[idActor] [bigint] NOTNULL,[idAward] [bigint] NOTNULL,CONSTRAINT [PK_ActAward] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[Actor] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Actor]([id] [bigint] IDENTITY(1,1)NOTNULL,[Surn] [varchar](50)NULL,[Name] [varchar](80)NULL,CONSTRAINT [PK_Actor] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[Audio] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Audio]([id] [bigint] IDENTITY(1,1)NOTNULL,[Name] [varchar](20)NOTNULL,[Chanel] [int] NULL,[Bitrate] [int] NULL,[Freq] [int] NULL,CONSTRAINT [PK_Audio] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[Award] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Award]([id] [bigint] IDENTITY(1,1)NOTNULL,[Capt] [varchar](80)NOTNULL,[Found] [date] NULL,CONSTRAINT [PK_Award] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[City] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[City]([id] [bigint] IDENTITY(1,1)NOTNULL,[Name] [varchar](70)NULL,[Country] [varchar](100)NULL,CONSTRAINT [PK_City] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[CityStudio] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[CityStudio]([id] [bigint] IDENTITY(1,1)NOTNULL,[idCity] [bigint] NULL,[idStudio] [bigint] NULL,[Dep] [varchar](12)NULL,CONSTRAINT [PK_CityStudio] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[Film] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Film]([id] [bigint] IDENTITY(1,1)NOTNULL,[Capt] [varchar](120)NOTNULL,[Lang] [varchar](50)NULL,[Year] [int] NULL,[Country] [varchar](100)NULL,CONSTRAINT [PK_Film] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[FilmAct] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[FilmAct]([id] [bigint] IDENTITY(1,1)NOTNULL,[idFilm] [bigint] NOTNULL,[idAtcor] [bigint] NOTNULL,CONSTRAINT [PK_FilmAct] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[FilmJanre] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[FilmJanre]([id] [bigint] IDENTITY(1,1)NOTNULL,[idFilm] [bigint] NOTNULL,[idJanre] [bigint] NOTNULL,CONSTRAINT [PK_FilmJanre] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[Jnare] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Jnare]([id] [bigint] IDENTITY(1,1)NOTNULL,[Name] [varchar](50)NOTNULL,CONSTRAINT [PK_Jnare] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[Manufact] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Manufact]([id] [bigint] IDENTITY(1,1)NOTNULL,[Capt] [varchar](120)NULL,[idFilm] [bigint] NULL,[idProd] [bigint] NULL,[idStudio] [bigint] NULL,[idVideo] [bigint] NULL,[idAudio] [bigint] NULL,[pType] [varchar](20)NULL,[Lang] [varchar](50)NULL,[Stor] [text] NULL,CONSTRAINT [PK_Manufact_1] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO/****** Object: Table [dbo].[Producer] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Producer]([id] [bigint] IDENTITY(1,1)NOTNULL,[Surn] [varchar](50)NOTNULL,[Name] [varchar](80)NULL,[Birth] [date] NULL,[Country] [varchar](100)NULL,CONSTRAINT [PK_Producer] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[Studio] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Studio]([id] [bigint] IDENTITY(1,1)NOTNULL,[Capt] [varchar](70)NOTNULL,[Found] [date] NULL,CONSTRAINT [PK_Studio] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GO/****** Object: Table [dbo].[Video] Script Date: 28.05.2023 9:02:21 ******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE [dbo].[Video]([id] [bigint] IDENTITY(1,1)NOTNULL,[Format] [varchar](30)NOTNULL,[Color] [int] NULL,[Height] [int] NULL,[Width] [int] NULL,[bitrate] [int] NULL,CONSTRAINT [PK_Video] PRIMARYKEYCLUSTERED([id] ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY])ON [PRIMARY]GOALTERTABLE [dbo].[ActAward] WITHCHECKADDCONSTRAINT [FK_ActAward_Act] FOREIGNKEY([idActor])REFERENCES [dbo].[Actor]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[ActAward] CHECKCONSTRAINT [FK_ActAward_Act]GOALTERTABLE [dbo].[ActAward] WITHCHECKADDCONSTRAINT [FK_ActAward_Award] FOREIGNKEY([idAward])REFERENCES [dbo].[Award]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[ActAward] CHECKCONSTRAINT [FK_ActAward_Award]GOALTERTABLE [dbo].[CityStudio] WITHCHECKADDCONSTRAINT [FK_CityStudio_City] FOREIGNKEY([idCity])REFERENCES [dbo].[City]([id])GOALTERTABLE [dbo].[CityStudio] CHECKCONSTRAINT [FK_CityStudio_City]GOALTERTABLE [dbo].[CityStudio] WITHCHECKADDCONSTRAINT [FK_CityStudio_Studio] FOREIGNKEY([idStudio])REFERENCES [dbo].[Studio]([id])GOALTERTABLE [dbo].[CityStudio] CHECKCONSTRAINT [FK_CityStudio_Studio]GOALTERTABLE [dbo].[FilmAct] WITHCHECKADDCONSTRAINT [FK_FilmAct_Act] FOREIGNKEY([idAtcor])REFERENCES [dbo].[Actor]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[FilmAct] CHECKCONSTRAINT [FK_FilmAct_Act]GOALTERTABLE [dbo].[FilmAct] WITHCHECKADDCONSTRAINT [FK_FilmAct_Film] FOREIGNKEY([idFilm])REFERENCES [dbo].[Film]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[FilmAct] CHECKCONSTRAINT [FK_FilmAct_Film]GOALTERTABLE [dbo].[FilmJanre] WITHCHECKADDCONSTRAINT [FK_FilmJanre_Film] FOREIGNKEY([idFilm])REFERENCES [dbo].[Film]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[FilmJanre] CHECKCONSTRAINT [FK_FilmJanre_Film]GOALTERTABLE [dbo].[FilmJanre] WITHCHECKADDCONSTRAINT [FK_FilmJanre_Janre] FOREIGNKEY([idJanre])REFERENCES [dbo].[Jnare]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[FilmJanre] CHECKCONSTRAINT [FK_FilmJanre_Janre]GOALTERTABLE [dbo].[Manufact] WITHCHECKADDCONSTRAINT [FK_Manufact_Audio] FOREIGNKEY([idAudio])REFERENCES [dbo].[Audio]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[Manufact] CHECKCONSTRAINT [FK_Manufact_Audio]GOALTERTABLE [dbo].[Manufact] WITHCHECKADDCONSTRAINT [FK_Manufact_Film] FOREIGNKEY([idFilm])REFERENCES [dbo].[Film]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[Manufact] CHECKCONSTRAINT [FK_Manufact_Film]GOALTERTABLE [dbo].[Manufact] WITHCHECKADDCONSTRAINT [FK_Manufact_Producer] FOREIGNKEY([idProd])REFERENCES [dbo].[Producer]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[Manufact] CHECKCONSTRAINT [FK_Manufact_Producer]GOALTERTABLE [dbo].[Manufact] WITHCHECKADDCONSTRAINT [FK_Manufact_Studio] FOREIGNKEY([idStudio])REFERENCES [dbo].[Studio]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[Manufact] CHECKCONSTRAINT [FK_Manufact_Studio]GOALTERTABLE [dbo].[Manufact] WITHCHECKADDCONSTRAINT [FK_Manufact_Video] FOREIGNKEY([idVideo])REFERENCES [dbo].[Video]([id])ONUPDATECASCADEONDELETECASCADEGOALTERTABLE [dbo].[Manufact] CHECKCONSTRAINT [FK_Manufact_Video]GOПриложение 2. SQLскрипт для созданиятриггеровuse [gosfilmDB]go---------------------------- Новый фильм добавляет продуктIFOBJECT_ID('OnNewFilm','TR')ISNOTNULLDROPTRIGGEROnNewFilm;-- дляперсозданияGOCREATETRIGGEROnNewFilmONdbo.FilmAFTERINSERTASIF (ROWCOUNT_BIG()=0)-- Если операцией не затронуто ни одной записиRETURN;DECLARE @idFilmINT, @CaptVARCHAR(120);SELECT @idFilm=id, @Capt=Captfrom inserted;insertintodbo.Manufact(idFilm,Capt,pType)values(@idFilm, @Capt,'оригинал');GO---------------------------- Новый продукт заполняет поля по первомуIFOBJECT_ID('OnNewManufact','TR')ISNOTNULLDROPTRIGGEROnNewManufact;-- дляперсозданияGOCREATETRIGGEROnNewManufactONdbo.ManufactAFTERINSERTASIF (ROWCOUNT_BIG()=0)-- Если операцией не затронуто ни одной записиRETURN;DECLARE @idFilmINT, @idProdINT, @idStudioINT, @idVideoINT, @idAudioINT, @pTypeVARCHAR(20);SELECT @idFilm=idFilm, @pType=pTypefrom insertedSELECTTOP (1) @idProd=idProd, @idStudio=idStudio, @idVideo=idVideo, @idAudio=idAudioFROMdbo.ManufactWHEREidFilm=@idFilmandpTypelike'оригинал'-- беремданныеизоригиналаеслитаковойестьORDERBYidFilm;if(@pTypeisNULL)SET @pType='копия';-- копияUPDATEdbo.ManufactSETidProd= @idProd,idStudio= @idStudio,idVideo= @idVideo,idAudio= @idAudio,pType= @pTypeFROMinserted-- совпадение по id из таблицы вставкиWHERE dbo.Manufact.id = inserted.id;GO