--Lab 3, Βάση παραδείγματος σελ 32 


CREATE DATABASE [videoclub2]
GO

use [videoclub2]
GO

CREATE TABLE [dbo].[ENOIKIASH] (
	[IDPelath] [int] NOT NULL ,
	[IDKasetas] [int] NOT NULL ,
	[Apo] [datetime] NOT NULL ,
	[Ews] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[KASETA] (
	[ID] [int] NOT NULL ,
	[IDTainias] [int] NOT NULL ,
	[Typos] [char] (4) COLLATE Greek_CI_AS NULL ,
	[Posotita] [int] NULL ,
	[Timh] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PELATHS] (
	[ID] [int] NOT NULL ,
	[Onoma] [char] (30) COLLATE Greek_CI_AS NOT NULL ,
	[Thlefwno] [char] (10) COLLATE Greek_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[SYNTELESTHS] (
	[ID] [int] NOT NULL ,
	[Onoma] [char] (50) COLLATE Greek_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TAINIA] (
	[ID] [int] NOT NULL ,
	[Titlos] [char] (100) COLLATE Greek_CI_AS NULL ,
	[Xronia] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TS] (
	[IDTainias] [int] NOT NULL ,
	[IDSyntelesth] [int] NOT NULL ,
	[Rolos] [char] (30) COLLATE Greek_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ENOIKIASH] WITH NOCHECK ADD 
	CONSTRAINT [PK_ENOIKIASH] PRIMARY KEY  CLUSTERED 
	(
		[IDPelath],
		[IDKasetas],
		[Apo]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[KASETA] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[PELATHS] WITH NOCHECK ADD 
	CONSTRAINT [PK_PELATHS] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[SYNTELESTHS] WITH NOCHECK ADD 
	 PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TAINIA] WITH NOCHECK ADD 
	CONSTRAINT [PK_TAINIA] PRIMARY KEY  CLUSTERED 
	(
		[ID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TS] WITH NOCHECK ADD 
	CONSTRAINT [PK_TS] PRIMARY KEY  CLUSTERED 
	(
		[IDTainias],
		[IDSyntelesth],
		[Rolos]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[SYNTELESTHS] ADD 
	CONSTRAINT [DF__ΣΥΝΤΕΛΕΣΤ__Onoma__1B0907CE] DEFAULT (null) FOR [Onoma]
GO

ALTER TABLE [dbo].[ENOIKIASH] ADD 
	CONSTRAINT [FK_ENOIKIASH_KASETA] FOREIGN KEY 
	(
		[IDKasetas]
	) REFERENCES [dbo].[KASETA] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_ENOIKIASH_PELATHS] FOREIGN KEY 
	(
		[IDPelath]
	) REFERENCES [dbo].[PELATHS] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

ALTER TABLE [dbo].[KASETA] ADD 
	 FOREIGN KEY 
	(
		[IDTainias]
	) REFERENCES [dbo].[TAINIA] (
		[ID]
	) ON DELETE CASCADE 
GO

ALTER TABLE [dbo].[TS] ADD 
	CONSTRAINT [FK_TS_SYNTELESTHS] FOREIGN KEY 
	(
		[IDSyntelesth]
	) REFERENCES [dbo].[SYNTELESTHS] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE ,
	CONSTRAINT [FK_TS_TAINIA] FOREIGN KEY 
	(
		[IDTainias]
	) REFERENCES [dbo].[TAINIA] (
		[ID]
	) ON DELETE CASCADE  ON UPDATE CASCADE 
GO

INSERT INTO TAINIA
VALUES (1,'Rear Window', 1954)
GO

INSERT INTO TAINIA
VALUES (2, 'Psycho', 1960)
GO

INSERT INTO TAINIA
VALUES (3, 'Ben-Hur', 1959)
GO

INSERT INTO TAINIA
VALUES (4, 'Fantasy', 1961)
GO

INSERT INTO SYNTELESTHS
VALUES (1, 'Alfred Hitchcock')
GO

INSERT INTO SYNTELESTHS
VALUES (2,'Grace Kelly')
GO

INSERT INTO SYNTELESTHS
VALUES (3, 'Anthony Perkins')
GO

INSERT INTO TS
VALUES (1, 1, 'Σκηνοθέτης')
GO

INSERT INTO TS
VALUES (1, 1, 'Παραγωγός')
GO


INSERT INTO TS
VALUES (1, 2, 'Ηθοποιός')
GO

INSERT INTO TS
VALUES	(2, 3, 'Ηθοποιός')
GO

INSERT INTO TS
VALUES	(2, 3, 'Παραγωγός')
GO

INSERT INTO TS
VALUES	(4, 3, 'Κομπάρσος')
GO

INSERT INTO KASETA
VALUES (1, 1, 'VHS', 3, 2)
GO

INSERT INTO KASETA
VALUES (2, 1, 'DVD', 1, 3)
GO

INSERT INTO KASETA
VALUES (3, 2, 'VHS', 2, 2)
GO

INSERT INTO KASETA
VALUES (4, 2, 'DVD', 1, 1)
GO

INSERT INTO PELATHS
VALUES (1, 'Perkins', '246801')
GO


INSERT INTO PELATHS
VALUES (2, 'Καντακουζηνός', '246801')
GO

INSERT INTO PELATHS
VALUES (3, 'Παλαιολόγος', '987654')
GO

INSERT INTO ENOIKIASH
VALUES (1, 1, '07/10/2006', '09/10/2006')
GO

INSERT INTO ENOIKIASH
VALUES (1, 2, '09/20/2006', '11/20/2006')
GO

INSERT INTO ENOIKIASH
(IDPelath, IDKasetas, Apo)
VALUES (2, 1, '09/10/2006')
GO
