CMiREpRv

1CREATE TABLE [dbo].[Socios](
2	[codigo_socio] [int] IDENTITY(1,1) NOT NULL,
3	[nombre] [varchar](50) NOT NULL,
4	[apellidos] [varchar](50) NOT NULL,
5	[direccion] [varchar](100) NOT NULL,
6	[localidad] [varchar](50) NOT NULL,
7	[provincia] [varchar](50) NOT NULL,
8	[telefono] [varchar](15) NOT NULL,
9	[fecha_nacimiento] [datetime] NOT NULL,
10 CONSTRAINT [PK_Socios] PRIMARY KEY CLUSTERED 
11(
12	[codigo_socio] ASC
13)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
14) ON [PRIMARY]
15
16
17CREATE TABLE [dbo].[Actividades](
18	[codigo_actividad] [int] IDENTITY(1,1) NOT NULL,
19	[nombre] [varchar](100) NOT NULL,
20	[nivel] [int] NOT NULL CONSTRAINT [DF_Actividades_nivel]  DEFAULT ((2)),
21 CONSTRAINT [PK_Actividades] PRIMARY KEY CLUSTERED 
22(
23	[codigo_actividad] ASC
24)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
25) ON [PRIMARY]
26
27CREATE TABLE [dbo].[Fichas](
28	[codigo_ficha] [int] IDENTITY(1,1) NOT NULL,
29	[codigo_socio] [int] NOT NULL,
30	[codigo_actividad] [int] NOT NULL,
31	[horas_mes] [float] NOT NULL CONSTRAINT [DF_Fichas_horas_mes]  DEFAULT ((10)),
32	[horas_consumidas] [float] NOT NULL CONSTRAINT [DF_Fichas_horas_consumidas]  DEFAULT ((0)),
33	[fecha_alta] [datetime] NOT NULL,
34	[fecha_baja] [datetime] NULL,
35	[observaciones] [Nvarchar](300) NULL,
36 CONSTRAINT [PK_Fichas] PRIMARY KEY CLUSTERED 
37(
38	[codigo_ficha] ASC
39)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
40) ON [PRIMARY]
41
42IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_Actividades]') AND parent_object_id = OBJECT_ID(N'[dbo].[Actividades]'))
43ALTER TABLE [dbo].[Actividades]  WITH CHECK ADD  CONSTRAINT [CK_Actividades] CHECK  (([nivel]>=(0) AND [nivel]<=(5)))
44
45IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fichas_Actividades]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fichas]'))
46ALTER TABLE [dbo].[Fichas]  WITH CHECK ADD  CONSTRAINT [FK_Fichas_Actividades] FOREIGN KEY([codigo_actividad])
47REFERENCES [dbo].[Actividades] ([codigo_actividad])
48
49IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fichas_Socios]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fichas]'))
50ALTER TABLE [dbo].[Fichas]  WITH CHECK ADD  CONSTRAINT [FK_Fichas_Socios] FOREIGN KEY([codigo_socio])
51REFERENCES [dbo].[Socios] ([codigo_socio])
52
53IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_Fichas]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fichas]'))
54ALTER TABLE [dbo].[Fichas]  WITH CHECK ADD  CONSTRAINT [CK_Fichas] CHECK  (([horas_mes]<=(50)))
55