SCHEMA VIDEO CLUB

VideoClub2.sql

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PELICULAS_ACTORES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PELICULAS] DROP CONSTRAINT FK_PELICULAS_ACTORES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PELICULAS_DIRECTORES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PELICULAS] DROP CONSTRAINT FK_PELICULAS_DIRECTORES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PELICULAS_GENEROS]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PELICULAS] DROP CONSTRAINT FK_PELICULAS_GENEROS
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SOCIOS_LOCALIDADES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SOCIOS] DROP CONSTRAINT FK_SOCIOS_LOCALIDADES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_UNIDADES_PELICULAS]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UNIDADES] DROP CONSTRAINT FK_UNIDADES_PELICULAS
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ALQUILERES_SOCIOS]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ALQUILERES] DROP CONSTRAINT FK_ALQUILERES_SOCIOS
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ALQUILERES_UNIDADES]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ALQUILERES] DROP CONSTRAINT FK_ALQUILERES_UNIDADES
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ACTORES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ACTORES]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ALQUILERES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ALQUILERES]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DIRECTORES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DIRECTORES]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GENEROS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GENEROS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LOCALIDADES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LOCALIDADES]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PELICULAS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[PELICULAS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SOCIOS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SOCIOS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UNIDADES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UNIDADES]
GO

CREATE TABLE [dbo].[ACTORES] (
	[CODIGO] [numeric](1, 0) NOT NULL ,
	[NOMBRE] [char] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ALQUILERES] (
	[Socio] [numeric](6, 0) NOT NULL ,
	[Pelicula] [numeric](6, 0) NOT NULL ,
	[Unidad] [numeric](1, 0) NOT NULL ,
	[Fecha] [smalldatetime] NOT NULL ,
	[Costo] [numeric](7, 2) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DIRECTORES] (
	[CODIGO] [numeric](1, 0) NOT NULL ,
	[NOMBRE] [char] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[GENEROS] (
	[CODIGO] [numeric](1, 0) NOT NULL ,
	[NOMBRE] [char] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LOCALIDADES] (
	[CODIGO] [numeric](4, 0) NOT NULL ,
	[NOMBRE] [char] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PELICULAS] (
	[Codigo] [numeric](6, 0) NOT NULL ,
	[Nombre] [char] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
	[Actor] [numeric](1, 0) NOT NULL ,
	[Genero] [numeric](1, 0) NOT NULL ,
	[Director] [numeric](1, 0) NOT NULL, 
	[Duración] [numeric](3, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SOCIOS] (
	[NUMERO] [numeric](6, 0) NOT NULL ,
	[NOMBRE] [char] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
	[DOMICILIO] [char] (30) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
	[LOCALIDAD] [numeric](4, 0) NOT NULL ,
	[FECINGR] [smalldatetime] NOT NULL ,
	[CATEGORIA] [char] (1) COLLATE Traditional_Spanish_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UNIDADES] (
	[Codigo] [numeric](6, 0) NOT NULL ,
	[Unidad] [numeric](1, 0) NOT NULL ,
	[Estado] [char] (1) COLLATE Traditional_Spanish_CI_AS NOT NULL ,
	[Valor] [numeric](3, 0) NOT NULL,
	[Formato] [char](1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ACTORES] WITH NOCHECK ADD 
	CONSTRAINT [PK_ACTORES] PRIMARY KEY  NONCLUSTERED 
	(
		[CODIGO]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ALQUILERES] WITH NOCHECK ADD 
	CONSTRAINT [PK_ALQUILERES] PRIMARY KEY  NONCLUSTERED 
	(
		[Socio],
		[Pelicula],
		[Unidad],
		[Fecha]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[DIRECTORES] WITH NOCHECK ADD 
	CONSTRAINT [PK_DIRECTORES] PRIMARY KEY  NONCLUSTERED 
	(
		[CODIGO]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[GENEROS] WITH NOCHECK ADD 
	CONSTRAINT [PK_GENEROS] PRIMARY KEY  NONCLUSTERED 
	(
		[CODIGO]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[LOCALIDADES] WITH NOCHECK ADD 
	CONSTRAINT [PK_LOCALIDADES] PRIMARY KEY  NONCLUSTERED 
	(
		[CODIGO]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[PELICULAS] WITH NOCHECK ADD 
	CONSTRAINT [PK_PELICULAS] PRIMARY KEY  NONCLUSTERED 
	(
		[Codigo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[SOCIOS] WITH NOCHECK ADD 
	CONSTRAINT [PK_SOCIOS] PRIMARY KEY  NONCLUSTERED 
	(
		[NUMERO]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[UNIDADES] WITH NOCHECK ADD 
	CONSTRAINT [PK_UNIDADES] PRIMARY KEY  NONCLUSTERED 
	(
		[Codigo],
		[Unidad]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[ALQUILERES] ADD 
	CONSTRAINT [FK_ALQUILERES_SOCIOS] FOREIGN KEY 
	(
		[Socio]
	) REFERENCES [dbo].[SOCIOS] (
		[NUMERO]
	),
	CONSTRAINT [FK_ALQUILERES_UNIDADES] FOREIGN KEY 
	(
		[Pelicula],
		[Unidad]
	) REFERENCES [dbo].[UNIDADES] (
		[Codigo],
		[Unidad]
	)
GO

ALTER TABLE [dbo].[PELICULAS] ADD 
	CONSTRAINT [FK_PELICULAS_ACTORES] FOREIGN KEY 
	(
		[Actor]
	) REFERENCES [dbo].[ACTORES] (
		[CODIGO]
	),
	CONSTRAINT [FK_PELICULAS_DIRECTORES] FOREIGN KEY 
	(
		[Director]
	) REFERENCES [dbo].[DIRECTORES] (
		[CODIGO]
	),
	CONSTRAINT [FK_PELICULAS_GENEROS] FOREIGN KEY 
	(
		[Genero]
	) REFERENCES [dbo].[GENEROS] (
		[CODIGO]
	)
GO

ALTER TABLE [dbo].[SOCIOS] ADD 
	CONSTRAINT [FK_SOCIOS_LOCALIDADES] FOREIGN KEY 
	(
		[LOCALIDAD]
	) REFERENCES [dbo].[LOCALIDADES] (
		[CODIGO]
	)
GO

ALTER TABLE [dbo].[UNIDADES] ADD 
	CONSTRAINT [FK_UNIDADES_PELICULAS] FOREIGN KEY 
	(
		[Codigo]
	) REFERENCES [dbo].[PELICULAS] (
		[Codigo]
	)
GO