USE master

if exists (select * from sysdatabases where name='bd_notasAlumnos')
begin
  raiserror('La base de datos existe; eliminándola....',0,1)
  DROP database bd_notasAlumnos
end
GO

raiserror('Creando base de datos bd_notasAlumnos....',0,1)
GO

CREATE DATABASE bd_notasAlumnos
GO

USE bd_notasAlumnos
GO

CREATE TABLE asignaturas
(
  id_asignatura integer primary key,
  nombre varchar(60) not null
)
GO

INSERT INTO asignaturas
       VALUES (20590, 'PROGRAMACION AVANZADA')
INSERT INTO asignaturas
       VALUES (20591, 'LAB. DE PROGRAMACION AVANZADA')
INSERT INTO asignaturas
       VALUES (30214, 'FUNDAMENTOS DE PROGRAMACION')
INSERT INTO asignaturas
       VALUES (30215, 'LAB. DE FUNDAMENTOS DE PROGRAMACION')
INSERT INTO asignaturas
       VALUES (31937, 'PROGRAMACION')
INSERT INTO asignaturas
       VALUES (31970, 'FUNDAMENTOS DE PROGRAMACION')
INSERT INTO asignaturas
       VALUES (32346, 'PROGRAMACION AVANZADA (OPTATIVA)')
INSERT INTO asignaturas
       VALUES (33693, 'PROGRAMACION VISUAL')
GO

CREATE TABLE alumnos
(
  id_alumno integer primary key,
  nombre varchar(50) not null,
)
GO

INSERT INTO alumnos
       VALUES (1234567, 'Leticia Aguirre Soriano')
INSERT INTO alumnos
       VALUES (1234568, 'Antonio Baena Sarasola')
INSERT INTO alumnos
       VALUES (1234569, 'Rosa Estrada Rios')
INSERT INTO alumnos
       VALUES (1234561, 'Isabella Dulce Bella')
INSERT INTO alumnos
       VALUES (1234562, 'Luis Gonzalez Arrutia')
INSERT INTO alumnos
       VALUES (1234563, 'Pedro Garcia Rojas')
GO

CREATE TABLE alums_asigs
(
  id_alumno integer not null,
  id_asignatura integer not null,
  nota real not null,
  primary key (id_alumno,id_asignatura)
)
GO

INSERT INTO alums_asigs
       VALUES (1234567, 20590, 2.5)
INSERT INTO alums_asigs
       VALUES (1234567, 30214, 4.5)
INSERT INTO alums_asigs
       VALUES (1234567, 31937, 6.5)
INSERT INTO alums_asigs
       VALUES (1234567, 32346, 8.5)
INSERT INTO alums_asigs
       VALUES (1234567, 33693, 9.5)
INSERT INTO alums_asigs
       VALUES (1234568, 20591, 3.5)
INSERT INTO alums_asigs
       VALUES (1234568, 30215, 5.5)
INSERT INTO alums_asigs
       VALUES (1234568, 31970, 7.5)
INSERT INTO alums_asigs
       VALUES (1234568, 32346, 8.5)
INSERT INTO alums_asigs
       VALUES (1234569, 20591, 3.5)
INSERT INTO alums_asigs
       VALUES (1234569, 30215, 5.5)
INSERT INTO alums_asigs
       VALUES (1234569, 31937, 6.5)
INSERT INTO alums_asigs
       VALUES (1234569, 31970, 7.5)
INSERT INTO alums_asigs
       VALUES (1234569, 32346, 8.5)
INSERT INTO alums_asigs
       VALUES (1234569, 33693, 9.5)
INSERT INTO alums_asigs
       VALUES (1234561, 32346, 7.5)
INSERT INTO alums_asigs
       VALUES (1234561, 33693, 7.5)
INSERT INTO alums_asigs
       VALUES (1234561, 20591, 2.5)
INSERT INTO alums_asigs
       VALUES (1234561, 30215, 6.5)
INSERT INTO alums_asigs
       VALUES (1234562, 33693, 5.5)
INSERT INTO alums_asigs
       VALUES (1234562, 20591, 9.5)
INSERT INTO alums_asigs
       VALUES (1234562, 30215, 7.5)
INSERT INTO alums_asigs
       VALUES (1234562, 31970, 4.5)
INSERT INTO alums_asigs
       VALUES (1234563, 33693, 6.5)
INSERT INTO alums_asigs
       VALUES (1234563, 30215, 7.5)
INSERT INTO alums_asigs
       VALUES (1234563, 32346, 9.5)
INSERT INTO alums_asigs
       VALUES (1234563, 31970, 8.5)

GO

ALTER TABLE alums_asigs ADD FOREIGN KEY(id_alumno) REFERENCES alumnos (id_alumno) ON UPDATE NO ACTION ON DELETE NO ACTION 
GO
ALTER TABLE alums_asigs ADD FOREIGN KEY(id_asignatura) REFERENCES asignaturas (id_asignatura) ON UPDATE NO ACTION ON DELETE NO ACTION 
GO


quit
