===============================================
USE MASTER
GO
CREATE DATABASE bdPrueba
ON PRIMARY
(NAME = 'bdPrueba', FILENAME = 'E:\BD\bdPrueba.mdf', SIZE = 10MB, MAXSIZE = Unlimited, FILEGROWTH = 2MB)
LOG ON
(NAME = 'bdPrueba_log', FILENAME ='E:\BD\bdPrueba_log.ldf', SIZE = 10MB, MAXSIZE = 30MB, FILEGROWTH = 10%)
GO
USE bdPrueba
GO
CREATE TABLE Lector(
IdLector int identity(1,1) not null,
ApellidoLector varchar(80) not null,
NombreLector varchar(80) not null,
TelefonoLector varchar(30) not null,
CONSTRAINT PK_IdLector Primary key (IdLector)
)
GO
CREATE TABLE Prestamo(
NroPrestamo int identity(1,1) not null,
CantLibrosPrestados int not null,
FechaPrestamo datetime not null,
IdLector int not null,
CONSTRAINT PK_Prestamo PRIMARY KEY(NroPrestamo),
CONSTRAINT FK_IdLector FOREIGN KEY (IdLector) REFERENCES Lector(IdLector)
)
CREATE TABLE Autor(
IdAutor int identity(1,1) not null,
ApellidoAutor varchar(60) not null,
NombresAutor varchar (60) not null,
PaisAutor varchar(30) not null
CONSTRAINT PK_IdAutor PRIMARY KEY (IdAutor)
)
CREATE TABLE Editorial(
IdEditorial int identity not null,
NombreEditorial varchar(80) not null
CONSTRAINT PK_IdEditorial PRIMARY KEY (IdEditorial)
)
CREATE TABLE Libro(
IdLibro int identity not null,
PrecioRef money null,
Titulo varchar(80) not null,
IdEditorial int not null,
CONSTRAINT PK_Idlibro PRIMARY KEY (IdLibro),
CONSTRAINT FK_IdEditorial FOREIGN KEY (IdEditorial) REFERENCES Editorial(IdEditorial)
)
CREATE TABLE Libro_Autor(
IdLibro int not null,
IdAutor int not null,
Capitulos int not null,
CONSTRAINT PK_Libro_Autor PRIMARY KEY (IdLibro,IdAutor),
CONSTRAINT FK_IdLibro FOREIGN KEY (IdLibro) REFERENCES Libro(IdLibro),
CONSTRAINT FK_IdAutor FOREIGN KEY (IdAutor) references Autor(IdAutor)
)
go
sp_helpconstraint Libro_Autor
CREATE TABLE Detalle_Prestamo(
IdLibro int not null,
NroPrestamo int not null,
NroCopia int not null,
CONSTRAINT PK_IdLibro_NroPrestamo PRIMARY KEY (IdLibro,NroPrestamo),
CONSTRAINT FK_IdLibro_DetallePrestamo FOREIGN KEY (IdLibro) REFERENCES Libro(IdLibro),
CONSTRAINT FK_NroPrestamo FOREIGN KEY (NroPrestamo) REFERENCES Prestamo(NroPrestamo)
)
GO
-- Añadir Campo a tabla lector
ALter table Lector
ADD DNI char(8) not null,
Direccion varchar(80) not null
GO
-- Añadir Constraint UNIQUE a tabla Lector
ALTER TABLE Lector
ADD CONSTRAINT UNQ_DNI
UNIQUE (DNI)
GO
SP_HELPCONSTRAINT Lector
GO
