Final Version - Portfolio 4

/* Em primeiro lugar, deveremos abrir o PostgreSQL e digitar cada sentença ou comando.
A linha seguinte vai criar o banco de dados vazio, denominado "faculdade", onde deverao ser armazenadas as tabelas, ou entidades. */

CREATE DATABASE faculdade ENCODING 'utf8';

/* Em seguida, passamos a criar as tabelas Aluno, Endereco, Curso, Professor, Disciplina e Matricula */

CREATE TABLE  aluno   (id SERIAL PRIMARY KEY NOT NULL,
    nome   VARCHAR(200) NOT NULL,
    fone   VARCHAR(15) NULL,
    cpf   VARCHAR(14) NULL,
    data_nasc   DATE NOT NULL,
    sexo   CHAR(1) NOT NULL
    );
CREATE TABLE endereco  (id   SERIAL PRIMARY KEY NOT NULL,
rua   VARCHAR(100) NOT NULL,
numero   INT NULL,
    bairro   VARCHAR(100) NULL,
    cidade   VARCHAR(100) NOT NULL,
    estado   CHAR(2) NOT NULL,
    outros   CHAR(200) NULL,
    pessoa_id   INT NOT NULL
  );
CREATE TABLE  curso   (id   SERIAL PRIMARY KEY NOT NULL,
    nome   VARCHAR(200) NOT NULL,
    semestre   VARCHAR(6) NOT NULL
    );
CREATE TABLE professor  (id   SERIAL PRIMARY KEY NOT NULL,
    nome   VARCHAR(200) NOT NULL,
    graduacao   VARCHAR(100) NOT NULL,
    fone   VARCHAR(15) NULL
    );
CREATE TABLE disciplina (id   SERIAL PRIMARY KEY NOT NULL,
    nome   VARCHAR(200) NOT NULL,
    professor_id INT NOT NULL,
    curso_id   INT NOT NULL
  );
CREATE TABLE matricula  (id   SERIAL PRIMARY KEY NOT NULL,
    aluno_id   INT NOT NULL,
    curso_id   INT NOT NULL,
    data_matricula DATE NOT NULL
    );

/* Estabeleceremos a partir de agora os relacionamentos, conforme sequencia abaixo.
Na tabela endereco, a chave estrangeira pessoa_id faz referencia a chave primaria id na tabela aluno.
Na tabela disciplina, a chave estrangeira professor_id faz referencia a chave primaria id na tabela professor.
Na tabela disciplina, a chave estrangeira curso_id faz referencia a chave primaria id na tabela curso.
Na tabela matricula, a chave estrangeira aluno_id faz referencia a chave primaria id na tabela aluno.
Na tabela matricula, a chave estrangeira curso_id faz referencia a chave primaria id na tabela curso.
*/

ALTER TABLE endereco ADD CONSTRAINT fk_aluno FOREIGN KEY(pessoa_id) REFERENCES aluno(id) ON DELETE RESTRICT;
ALTER TABLE disciplina ADD CONSTRAINT fk_professor FOREIGN KEY(professor_id) REFERENCES professor(id) ON DELETE RESTRICT;
ALTER TABLE disciplina ADD CONSTRAINT fk_curso FOREIGN KEY(curso_id) REFERENCES curso(id) ON DELETE RESTRICT;
ALTER TABLE matricula ADD CONSTRAINT fk_aluno FOREIGN KEY(aluno_id) REFERENCES aluno(id) ON DELETE RESTRICT;
ALTER TABLE matricula ADD CONSTRAINT fk_curso FOREIGN KEY(curso_id) REFERENCES curso(id) ON DELETE RESTRICT;

/* Agora povoando cada tabela com quatro dados.
Adicionando primeiramente na tabela aluno */

INSERT INTO  aluno VALUES (1,
    'JOYCE VIEIRA',
    '(88) 99624-0845',
    '123.456.789-01',
    '1999-02-28',
    'F'
    );
INSERT INTO  aluno VALUES (2,
    'ELIAS SANTANA',
    '(88) 99902-9575',
    '123.456.789-02',
    '1995-02-27',
    'M'
    );
INSERT INTO  aluno VALUES (3,
    'ADAIL SILVA',
    '(88) 99467-1677',
    '123.456.789-03',
    '1994-02-26',
    'M'
    );
INSERT INTO  aluno VALUES (4,
    'LUCIVALDO SILVA',
    '(85) 99945-6584',
    '123.456.789-04',
    '1974-02-25',
    'M'
    );

/* Na tabela endereco */

INSERT INTO endereco VALUES  (1,
    'RUA MENINO JESUS DE PRAGA',
    200,
    'CIDADE PEDRO MENDES CARNEIRO',
    'SOBRAL',
    'CE',
    'Sem ressalvas',
    2
    );
INSERT INTO  endereco VALUES  (2,
    'RUA C',
    500,
    'JUREMA',
    'CAUCAIA',
    'CE',
    'Mora bem distante do CED',
    4
    );
INSERT INTO endereco VALUES  (3,
    'RUA DR. PAULO SANFORD',
    1350,
    'ALTO DA EXPECTATIVA',
    'SOBRAL',
    'CE',
    'Sem ressalvas',
    3
    );
INSERT INTO  endereco VALUES  (4,
    'TRAVESSA VICENTE ANDRE',
    30,
    'NOSSA SENHORA DE FATIMA',
    'MASSAPE',
    'CE',
    'Mora em outro municipio diverso do CED',
    1
    );

/* Na tabela curso */

INSERT INTO curso VALUES  (1,
    'CURSO DE BANCO DE DADOS DO CED',
    '2015/2'
    );
INSERT INTO curso VALUES  (2,
    'ANDROID DAS MAIS DIVERSAS FORMAS E SABORES',
    '2015/2'
    );
INSERT INTO curso VALUES  (3,
    'CRIANDO CONSULTAS SQL EM BANCO DE DADOS USANDO POSTGRESQL',
    '2014/1'
    );
INSERT INTO curso VALUES  (4,
    'INFORMATICA BASICA USANDO WINDOWS 7 E WORD 2013',
    '2015/1'
    );

/* Na tabela professor */

INSERT INTO professor VALUES  (1,
    'LIVIA SOUSA',
    'TECNOLOGIA DA INFORMACAO',
    '(88) 99934-5254'
    );
INSERT INTO professor VALUES  (2,
    'FRANCISCO ANTONIO BRITO',
    'ENGENHARIA DE PRODUCAO',
    '(88) 99625-9141'
    );
INSERT INTO professor VALUES  (3,
    'LINCOLN FREIRE AGUIAR',
    'ENGENHARIA CIVIL',
    '(88) 99356-3426'
    );
INSERT INTO  professor VALUES  (4,
    'MARCONI PAULINO',
    'COMPUTACAO',
    '(85) 98925-7688'
    );

/* Na tabela disciplina */

INSERT INTO  disciplina VALUES  (1,
    'CURSO AVANCADO DE ALGEBRA E REGRAS LOGARITMICAS',
    2,
    3
    );
INSERT INTO  disciplina VALUES  (2,
    'INFORMATICA AVANCADA',
    1,
    1
    );
INSERT INTO  disciplina VALUES  (3,
    'BANCO DE DADOS',
    3,
    4
    );

INSERT INTO  disciplina VALUES (4,
    'TRIGONOMETRIA BASICA',
    4,
    2
    );

/* Na tabela matricula */  

INSERT INTO  matricula VALUES  (1,
    2,
    1,
    '2015-11-19'
    );
INSERT INTO  matricula VALUES  (2,
    3,
    4,
    '2015-10-29'
    );
INSERT INTO  matricula VALUES  (3,
    4,
    2,
    '2015-05-30'
    );
INSERT INTO  matricula VALUES  (4,
    1,
    3,
    '2015-11-19'
    );

/* Realizando consultas, conforme segue.
- Na tabela aluno, liste todos os registros onde id for igual a 2;
- Na tabela professor, liste todos os registros cujo id for 3;
- Na tabela matricula, liste todos os registros onde aluno_id (chave estrangeira) for 4;
- Na tabela curso, liste todos os registros onde semestre seja 2015/2.
*/

SELECT * FROM aluno WHERE id = 2;
SELECT * FROM professor WHERE id = 3;
SELECT * FROM matricula WHERE aluno_id = 4;
SELECT * FROM curso WHERE semestre = '2015/2';