USE Biblioteca;
GO

PRINT N'Creating Table [dbo].[PrestamosUsuarios]...';


GO
CREATE TABLE [dbo].[PrestamosUsuarios] (
    [IdTiquetePrestamo] INT  IDENTITY (1, 1) NOT NULL,
    [IdLibro]           INT  NULL,
    [IdUsuario]         INT  NULL,
    [FechaPrestamo]     DATE NULL,
    [FechaDevolucion]   DATE NULL,
    [EstadoPrestamo]    BIT  NULL,
    CONSTRAINT [PK_PrestamosUsuarios] PRIMARY KEY CLUSTERED ([IdTiquetePrestamo] ASC)
);


GO
PRINT N'Creating Table [dbo].[Libros]...';


GO
CREATE TABLE [dbo].[Libros] (
    [IdLibro]     INT          IDENTITY (1, 1) NOT NULL,
    [IdEditorial] INT          NULL,
    [IdIsbn]      INT          NULL,
    [IdAutor]     INT          NULL,
    [Nombre]      VARCHAR (50) NULL,
    CONSTRAINT [PK_Libros] PRIMARY KEY CLUSTERED ([IdLibro] ASC)
);


GO
PRINT N'Creating Table [dbo].[Usuarios]...';


GO
CREATE TABLE [dbo].[Usuarios] (
    [IdUsuario]       INT          IDENTITY (1, 1) NOT NULL,
    [IdTipoDocumento] INT          NULL,
    [IdEstado]        INT          NULL,
    [Cedula]          INT          NULL,
    [Nombre]          VARCHAR (30) NULL,
    [Dirección]       VARCHAR (30) NULL,
    [Telefono]        INT          NULL,
    CONSTRAINT [PK_Usuarios] PRIMARY KEY CLUSTERED ([IdUsuario] ASC)
);


GO
PRINT N'Creating Table [dbo].[Autores]...';


GO
CREATE TABLE [dbo].[Autores] (
    [IdAutor] INT          IDENTITY (1, 1) NOT NULL,
    [Nombre]  VARCHAR (50) NULL,
    CONSTRAINT [PK_Autores] PRIMARY KEY CLUSTERED ([IdAutor] ASC)
);


GO
PRINT N'Creating Table [dbo].[Isbn]...';


GO
CREATE TABLE [dbo].[Isbn] (
    [IdIsbn]      INT          IDENTITY (1, 1) NOT NULL,
    [Codigo]      VARCHAR (30) NULL,
    [Descripcion] VARCHAR (50) NULL,
    CONSTRAINT [PK_Isbn] PRIMARY KEY CLUSTERED ([IdIsbn] ASC)
);


GO
PRINT N'Creating Table [dbo].[Editoriales]...';


GO
CREATE TABLE [dbo].[Editoriales] (
    [IdEditorial] INT          IDENTITY (1, 1) NOT NULL,
    [IdCiudad]    INT          NULL,
    [Nombre]      VARCHAR (30) NULL,
    [Dirección]   VARCHAR (30) NULL,
    [Telefono]    INT          NULL,
    CONSTRAINT [PK_Editoriales] PRIMARY KEY CLUSTERED ([IdEditorial] ASC)
);


GO
PRINT N'Creating Table [dbo].[Estados]...';


GO
CREATE TABLE [dbo].[Estados] (
    [IdEstado] INT          IDENTITY (1, 1) NOT NULL,
    [Nombre]   VARCHAR (30) NULL,
    CONSTRAINT [PK_Estados] PRIMARY KEY CLUSTERED ([IdEstado] ASC)
);


GO
PRINT N'Creating Table [dbo].[TiposDocumento]...';


GO
CREATE TABLE [dbo].[TiposDocumento] (
    [IdTipoDocumento] INT          IDENTITY (1, 1) NOT NULL,
    [Nombre]          VARCHAR (30) NULL,
    CONSTRAINT [PK_TiposDocumento] PRIMARY KEY CLUSTERED ([IdTipoDocumento] ASC)
);


GO
PRINT N'Creating Table [dbo].[Ciudades]...';


GO
CREATE TABLE [dbo].[Ciudades] (
    [IdCiudad]   INT          IDENTITY (1, 1) NOT NULL,
    [IdPais]     INT          NULL,
    [Nombre]     VARCHAR (30) NULL,
    [CodigoDane] VARCHAR (30) NULL,
    CONSTRAINT [PK_Ciudades] PRIMARY KEY CLUSTERED ([IdCiudad] ASC)
);


GO
PRINT N'Creating Table [dbo].[Paises]...';


GO
CREATE TABLE [dbo].[Paises] (
    [IdPais] INT          IDENTITY (1, 1) NOT NULL,
    [Nombre] VARCHAR (30) NULL,
    CONSTRAINT [PK_Paises] PRIMARY KEY CLUSTERED ([IdPais] ASC)
);


GO
PRINT N'Creating Foreign Key [dbo].[FK_PrestamosUsuarios_Libros]...';


GO
ALTER TABLE [dbo].[PrestamosUsuarios]
    ADD CONSTRAINT [FK_PrestamosUsuarios_Libros] FOREIGN KEY ([IdLibro]) REFERENCES [dbo].[Libros] ([IdLibro]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_PrestamosUsuarios_Usuarios]...';


GO
ALTER TABLE [dbo].[PrestamosUsuarios]
    ADD CONSTRAINT [FK_PrestamosUsuarios_Usuarios] FOREIGN KEY ([IdUsuario]) REFERENCES [dbo].[Usuarios] ([IdUsuario]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Libros_Autores]...';


GO
ALTER TABLE [dbo].[Libros]
    ADD CONSTRAINT [FK_Libros_Autores] FOREIGN KEY ([IdAutor]) REFERENCES [dbo].[Autores] ([IdAutor]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Libros_Editoriales]...';


GO
ALTER TABLE [dbo].[Libros]
    ADD CONSTRAINT [FK_Libros_Editoriales] FOREIGN KEY ([IdEditorial]) REFERENCES [dbo].[Editoriales] ([IdEditorial]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Libros_Isbn]...';


GO
ALTER TABLE [dbo].[Libros]
    ADD CONSTRAINT [FK_Libros_Isbn] FOREIGN KEY ([IdIsbn]) REFERENCES [dbo].[Isbn] ([IdIsbn]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Usuarios_Estados]...';


GO
ALTER TABLE [dbo].[Usuarios]
    ADD CONSTRAINT [FK_Usuarios_Estados] FOREIGN KEY ([IdEstado]) REFERENCES [dbo].[Estados] ([IdEstado]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Usuarios_TiposDocumento]...';


GO
ALTER TABLE [dbo].[Usuarios]
    ADD CONSTRAINT [FK_Usuarios_TiposDocumento] FOREIGN KEY ([IdTipoDocumento]) REFERENCES [dbo].[TiposDocumento] ([IdTipoDocumento]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Editoriales_Ciudades]...';


GO
ALTER TABLE [dbo].[Editoriales]
    ADD CONSTRAINT [FK_Editoriales_Ciudades] FOREIGN KEY ([IdCiudad]) REFERENCES [dbo].[Ciudades] ([IdCiudad]);


GO
PRINT N'Creating Foreign Key [dbo].[FK_Ciudades_Paises]...';


GO
ALTER TABLE [dbo].[Ciudades]
    ADD CONSTRAINT [FK_Ciudades_Paises] FOREIGN KEY ([IdPais]) REFERENCES [dbo].[Paises] ([IdPais]);


GO

PRINT N'Create complete.';