Es muy importante saber el uso del lenguaje SQL, pues es con el que todos los Sistemas Gestores de Bases de Datos funcionan. Y es que la creación y manipulacionde las bases de datos se hacen desde el lenguaje SQL.
Diseño de la base de datos.
CLIENTES (Cod_Clientes, Nombre, Apellidos, Carta Laboral, Cod_Propiedades1, Cod_Contrato1)
SEDES (Cod_Sedes, Nombre, Dirección, Cod_Admin, Cod_Propiedades 1, Cod_Clientes1)
ADMINISTRADOR (Cod_Admin, Nombre, Apellido)
PROPIEDADES (Cod_Propiedades, num_habitaciones, Tipo: casa/apartamento Area, Dirección, Cod_Dueños)
FIADORES (Cod_Fiadores, Nombre, Apellidos, Cod_Prop_R1)
DUEÑOS (Cod_Dueños, Nombre, Apellidos)
CONTRATOS (Cod_Contrato, Fecha_Inicio, Fecha caducidad, Valor, Cod_Fiadores1)
PROPIEDAD DE RESPALDO (Cod_Pro_Res, Dirección)
SCRIPT DE LA BASE DE DATOS
USE [master]
GO
/****** Object: Database [Arrendamientos_Muriel] Script Date: 08/11/2018 03:18:23 p.m. ******/
CREATE DATABASE [Arrendamientos_Muriel]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Arrendamientos_Muriel', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Arrendamientos_Muriel.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Arrendamientos_Muriel_log', FILENAME = N'C:\Program Files (x86)\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\Arrendamientos_Muriel_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Arrendamientos_Muriel] SET COMPATIBILITY_LEVEL = 120
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Arrendamientos_Muriel].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Arrendamientos_Muriel] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET ARITHABORT OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Arrendamientos_Muriel] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [Arrendamientos_Muriel] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET DISABLE_BROKER
GO
ALTER DATABASE [Arrendamientos_Muriel] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Arrendamientos_Muriel] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET RECOVERY SIMPLE
GO
ALTER DATABASE [Arrendamientos_Muriel] SET MULTI_USER
GO
ALTER DATABASE [Arrendamientos_Muriel] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Arrendamientos_Muriel] SET DB_CHAINING OFF
GO
ALTER DATABASE [Arrendamientos_Muriel] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [Arrendamientos_Muriel] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
ALTER DATABASE [Arrendamientos_Muriel] SET DELAYED_DURABILITY = DISABLED
GO
USE [Arrendamientos_Muriel]
GO
/****** Object: Table [dbo].[Administradores] Script Date: 08/11/2018 03:18:24 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Administradores](
[Cod_Admin] [smallint] NOT NULL,
[Nombre] [varchar](50) NOT NULL,
[Apellido] [varchar](50) NOT NULL,
CONSTRAINT [PK_Administradores] PRIMARY KEY CLUSTERED
(
[Cod_Admin] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Clientes] Script Date: 08/11/2018 03:18:24 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Clientes](
[Cod_Clientes] [smallint] NOT NULL,
[Nombre] [varchar](50) NOT NULL,
[Apellidos] [varchar](50) NOT NULL,
[Carta_laboral] [varchar](50) NOT NULL,
[Cod_Propiedades1] [smallint] NOT NULL,
[Cod_Contrato1] [smallint] NOT NULL,
CONSTRAINT [PK_Clientes] PRIMARY KEY CLUSTERED
(
[Cod_Clientes] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Contratos] Script Date: 08/11/2018 03:18:24 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contratos](
[Cod_Contrato] [smallint] NOT NULL,
[Fecha_Caducidad] [smalldatetime] NOT NULL,
[Valor] [money] NOT NULL,
[Cod_Fiadores1] [smallint] NOT NULL,
CONSTRAINT [PK_Contratos] PRIMARY KEY CLUSTERED
(
[Cod_Contrato] 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].[Dueño] Script Date: 08/11/2018 03:18:24 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Dueño](
[Cod_Dueños] [smallint] NOT NULL,
[Nombre] [varchar](50) NOT NULL,
[Apellido] [varchar](50) NOT NULL,
CONSTRAINT [PK_Dueño] PRIMARY KEY CLUSTERED
(
[Cod_Dueños] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Fiadores] Script Date: 08/11/2018 03:18:24 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Fiadores](
[Cod_Fiadores] [smallint] NOT NULL,
[Nombre] [varchar](50) NOT NULL,
[Apellidos] [varchar](50) NOT NULL,
[Cod_Prop_Res1] [smallint] NOT NULL,
CONSTRAINT [PK_Fiadores] PRIMARY KEY CLUSTERED
(
[Cod_Fiadores] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Propiedades] Script Date: 08/11/2018 03:18:24 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Propiedades](
[Cod_Propiedades] [smallint] NOT NULL,
[Direccion] [varchar](50) NOT NULL,
[Cod_Dueños1] [smallint] NOT NULL,
[Casa] [bit] NOT NULL,
[Area_M2] [smallint] NOT NULL,
[Num_Habitaciones] [smallint] NOT NULL,
CONSTRAINT [PK_Propiedades] PRIMARY KEY CLUSTERED
(
[Cod_Propiedades] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Propiedades de Respaldo] Script Date: 08/11/2018 03:18:24 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Propiedades de Respaldo](
[Cod_Pro_Res] [smallint] NOT NULL,
[Direccion] [varchar](50) NOT NULL,
CONSTRAINT [PK_Propiedades de Respaldo] PRIMARY KEY CLUSTERED
(
[Cod_Pro_Res] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[Sedes] Script Date: 08/11/2018 03:18:24 p.m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Sedes](
[Cod_Sedes] [smallint] NOT NULL,
[Nombre] [varchar](50) NOT NULL,
[Direccion] [varchar](50) NOT NULL,
[Cod_Admin1] [smallint] NOT NULL,
[Cod_Propiedades1] [smallint] NOT NULL,
[Cod_Clientes1] [smallint] NOT NULL,
CONSTRAINT [PK_Sedes] PRIMARY KEY CLUSTERED
(
[Cod_Sedes] 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
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Clientes] WITH CHECK ADD CONSTRAINT [FK_Clientes_Contratos] FOREIGN KEY([Cod_Contrato1])
REFERENCES [dbo].[Contratos] ([Cod_Contrato])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Clientes] CHECK CONSTRAINT [FK_Clientes_Contratos]
GO
ALTER TABLE [dbo].[Clientes] WITH CHECK ADD CONSTRAINT [FK_Clientes_Propiedades] FOREIGN KEY([Cod_Propiedades1])
REFERENCES [dbo].[Propiedades] ([Cod_Propiedades])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Clientes] CHECK CONSTRAINT [FK_Clientes_Propiedades]
GO
ALTER TABLE [dbo].[Contratos] WITH CHECK ADD CONSTRAINT [FK_Contratos_Fiadores] FOREIGN KEY([Cod_Fiadores1])
REFERENCES [dbo].[Fiadores] ([Cod_Fiadores])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Contratos] CHECK CONSTRAINT [FK_Contratos_Fiadores]
GO
ALTER TABLE [dbo].[Fiadores] WITH CHECK ADD CONSTRAINT [FK_Fiadores_Propiedades de Respaldo] FOREIGN KEY([Cod_Prop_Res1])
REFERENCES [dbo].[Propiedades de Respaldo] ([Cod_Pro_Res])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Fiadores] CHECK CONSTRAINT [FK_Fiadores_Propiedades de Respaldo]
GO
ALTER TABLE [dbo].[Propiedades] WITH CHECK ADD CONSTRAINT [FK_Propiedades_Dueño] FOREIGN KEY([Cod_Dueños1])
REFERENCES [dbo].[Dueño] ([Cod_Dueños])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Propiedades] CHECK CONSTRAINT [FK_Propiedades_Dueño]
GO
USE [master]
GO
ALTER DATABASE [Arrendamientos_Muriel] SET READ_WRITE
GO
No hay comentarios:
Publicar un comentario