Projeto de banco de dados para um exame

Fácil no bigint e nvarchar(MAX)

Table Student:

ID int (primary key, identity)
Name nvarchar(800)

Table Question:

ID smallint (primary key, identity)
TextOfTheQuestion nvarchar(800)
CorrectAns tinyint FK (to AnswerNum.Num)  
tested and can make a composite FK to Answer  QuestionID, AnsNum   
I was not sure could do this but it would mean you have to create the question  
Then populate Answer  
Then come back and edit question for CorrectAns so may not be the way you want to go

Table Answer:

QuestionID smalling PK FK (to Question.ID) 
AnswerNum tinyint   PK FK (to AnswerNum.Num)
TextOfTheAnswer nvarchar(800)


Table StudentChoice:

StudentID int       (primary key, foreign key to Student.ID)
QuestionID smallint (primary key)
AnswerNum tinyint 
(QuestionID, AnswerNum) REFERENCES Answer (QuestionID, AnswerNum)

Table AnswerNum:

Num tinyint PK 
-- just values 1, 2, 3
-- this way you can change number of questions in the future 

É assim que você classifica
Quando a consulta principal sai limpa, é sinal de um bom design de banco de dados

  select Student.Name, count(*) as score 
  from student
  left join StudentChoice
         on Student.ID = StudentChoice.StudentID 
  left join Question
         on Question.QuestionID = StudentChoice.QuestionID 
        and Question.CorrectAns = StudentChoice.AnswerNum   
            -- CorrectAns in Question makes this a whole lot easier  
  group by Student.Name 

Acho que você acertou em cheio no seu esquema inicial.

No entanto, como estamos lidando com um cenário MCQ, você não precisa de uma tabela "Respostas" - a resposta pode ser incluída na pergunta (junto com as duas respostas incorretas - veja abaixo). Isso simplificará muito as coisas. Outra alteração menor que eu recomendaria é ter um campo Question_ID PRIMARY KEYna tabela Question.

Preparei um esquema abaixo - é MySQL, infelizmente, não uso o Microsoft SQL Server com muita frequência e não tenho uma instância disponível. Não deve ser muito difícil de "traduzir".

Alguns comentários.

Em vez de usar apenas "ID" como nome de campo em todas as tabelas, recomendo que você use "Table_Name_ID" - isso torna seu SQL mais claro e também ajuda na depuração - se você receber uma mensagem dizendo algo como "... erro com ID on insert..." - você não sabe a qual ID da tabela a mensagem está se referindo, enquanto que se você nomeá-los explicitamente, a mensagem de erro se torna significativa.

CREATE TABLE Student (Student_ID int, Name VARCHAR(25));
CREATE TABLE Question (Question_ID int, Name VARCHAR(25), Question_Text 
                       VARCHAR(64000), Answer1 VARCHAR(4096), 
                       Answer2 VARCHAR(4096), Answer3 VARCHAR(4096),
                       Correct_Answer TINYINT);
CREATE TABLE Student_Response (Student_ID int, Question_ID int,
                               Student_Answer TINYINT);

Se você tiver perguntas formuladas de forma que sejam marcadas como 'A', 'B' ou 'C', será necessário modificar TINYINT para CHAR(1).

Você notará que eu uso nomes relativamente longos e (espero) fáceis de entender para meus FOREIGN KEYs & c. Há duas razões para isso:

• Os programadores não precisam vasculhar a documentação para descobrir o que é uma entidade - ela é autoexplicativa,

• Absolutamente essencial para depuração - que é onde a grande maioria dos aplicativos passa a maior parte do tempo. Receber uma mensagem de que "... a restrição SYS00003433 foi violada..." não é exatamente útil. O Oracle é ótimo para dar a restrições sem nome seus próprios nomes "especiais" gerados pelo sistema.

Você também notará que eu uso nomes de tabelas no singular. Eu recomendo que você faça o mesmo - assim você nunca terá que pensar em "Hmmm... é aluno ou alunos?" De qualquer forma, escolha um padrão e cumpra -o.

Se você pesquisar " práticas recomendadas de design de banco de dados" no Google , obterá vários sites - reserve um tempo e se dê ao trabalho de ler alguns deles e ver qual é o consenso - se muitas pessoas acham que é uma boa ideia, provavelmente é !

ALTER TABLE Student_Response 
  ADD CONSTRAINT fk_sr_student FOREIGN KEY (Student_ID) 
    REFERENCES Student (Student_ID);
ALTER TABLE Student_Response 
  ADD CONSTRAINT fk_sr_question FOREIGN KEY (Question_ID) 
    REFERENCES Question (Question_ID);

Essa restrição única é para eliminar a possibilidade de duas respostas para a mesma pergunta.

ALTER TABLE Student_Response 
  ADD CONSTRAINT uq_sr_student_question 
    UNIQUE (Student_ID, Question_ID);

Mesmo que você o exclua especificamente, deve ser relativamente fácil adicionar uma tabela de exames (mesmo exame, horários diferentes) ou possivelmente uma tabela de "tentativa" para isso e, em seguida, ter uma tabela de exames para exames diferentes - ou seja, databases101, databases201.

Talvez você deva considerar uma data ou mesmo um campo datetime associado à tentativa - fazer o mesmo exame de manhã e à tarde?

No final do dia, pode-se projetar até que as vacas voltem para casa. Evite o transtorno de design compulsivo - com isso quero dizer escolha (com antecedência) um ponto em que você ficará satisfeito com a funcionalidade do seu sistema e pare por aí. Eu sei como é fácil cair na armadilha de adicionar continuamente "bits and bobs" e não fazer nenhum trabalho real.

A solução de tarefa de exame "Projetando banco de dados" provavelmente deve mostrar como um aluno pode expressar regras de negócios de domínio por meio de ferramentas de integridade de dados de banco de dados (PK, FK).

Sua solução cobre a maioria deles. As regras que faltam são:
(1) Apenas uma resposta é a solução para uma pergunta. O sinalizador de bit não ajuda sem gatilhos ou código de aplicativo extra.
(2) Um Aluno pode escolher apenas uma Resposta para uma Pergunta.

Table Students:

ID int (primary key, identity)
Name nvarchar(200)

Table Questions:

ID int (primary key, identity)
TextOfTheQuestion nvarchar(4000)
SolutionID int
foreign key (SolutionID) to Answers(ID)

Eu adicionei SolutionID, desta forma, Answer tem exatamente uma solução.

Table Answers:

ID int (primary key, identity)
QuestionID int (foreign key to Questions.ID)
TextOfTheAnswer nvarchar(4000)

Para atender (2), o PK do StudentChoices deve ser (StudentID,QuestionID), o que significa que o aluno respondeu à pergunta e sua resposta foi ... .

Table StudentChoices:

StudentID int (primary key, foreign key to Students.ID)
QuestionID int (primary key)
AnswerID int 

Agora surge um problema, AnswerID e QuestionID não devem se contradizer, ou seja, AnswerID deve pertencer à pergunta referenciada por QuestionID. Podemos alcançá-lo declarando FK em StudentChoices:

foreign key (QuestionID, AnswerID) to Answers(QuestionsID, ID)

Para poder segmentar Answers(QuestionsID, ID) no FK acima, este par de atributos deve ser declarado Único em Answers:

unique (QuestionID,ID) -- target of FK from Questions, StudentChoices

É absolutamente seguro declarar tal Unique porque cada superconjunto de PK é único. Usando o mesmo exclusivo, também garantimos que SolutionID pertença à resposta exatamente a esta pergunta, substituindo FK em Questions por

foreign key (ID,SolutionID) to Answers(QuestionsID, ID)

Ao revisar a estrutura que você tem agora, eu alteraria as colunas de identidade de BigInt para Int. Sugiro que você revise quantos Alunos, Perguntas, Respostas e Opções você espera e selecione os tipos de dados com intervalos de tamanho apropriados.

Eu também substituiria nvarchar(max) por um campo de tamanho mais razoável. Como existem limitações com funções em nvarchar(max). Só usaria como último recurso.

Quanto à própria estrutura da tabela, eu faria alterações na tabela de opções do aluno.

1. Eu adicionaria uma coluna de identidade como uma chave primária para identificar exclusivamente uma única linha. Isso facilita a vida das aplicações.

2. Eu adicionaria uma coluna de ID da pergunta. Para que você possa ver o Aluno A, para a Questão B selecione a Resposta C.

3. Opcional: se você adicionou um índice exclusivo na ID do aluno e na ID da pergunta, os alunos só podem selecionar 1 resposta por pergunta.

Outras coisas para se pensar: Este banco de dados será usado para muitos exames diferentes? (considere o id do exame) Ou um aluno pode fazer o mesmo exame duas vezes? Como você lidaria com isso?

Como outros mencionaram, você deve usar tipos de dados razoáveis, por exemplo, não BIGINTpara alunos/pergunta (nunca haverá mais de 2 bilhões de alunos), provavelmente não NVARCHAR(MAX)para perguntas/respostas. Você também deve adicionar NOT NULLrestrições quando possível.

Um modelo de dados lógico limpo resultaria nisso:

Aluno de Mesa

StudentID int (primary key, system assigned?)
-- of course there will be an official registration number 
-- issued by the univerity which is probably not based on a sequence
Name nvarchar(200) not null
-- of course there will be lots of other columns like last_name/first_name/birthdate/...

Questão de Tabela

QuestionID smallint (primary key, system assigned?) 
TextOfTheQuestion nvarchar(4000) not null

CorrectAnswer tinyint not null check (CorrectAnswer between 1 and 3)
-- for a 100% clean model you might:
-- drop the check, remove the NOT NULL 
-- and do a foreign key to Answers(QuestionID, AnswerID) instead
-- But then you got a circular reference and this is hard to load

Tabela Resposta

QuestionID int (foreign key to Questions(QuestionID))
AnswerID tinyint check (AnswerID between 1 and 3)
TextOfTheAnswer not null nvarchar(4000)
Primary key (QuestionID, AnswerID) 

Tabela AlunoEscolha

StudentID int (foreign key to Students.StudentID)
QuestionID smallint (foreign key to Answers.AnswerID)
AnswerID tinyint -- NULLable as student might not have answered question (yet)?
Primary key (StudentID, QuestionID)

Em seguida, uma consulta para obter as perguntas respondidas (corretas) ficará assim:

select s.Name, 
   count(sc.AnswerID) as AnsweredQuestions
   count(q.AnswerID) as CorrectAnswers
from student as s
left join StudentChoice as sc
       on s.StudentID = sc.StudentID 
left join Question as q
       on q.QuestionID = sc.QuestionID 
      and q.CorrectAns = sc.AnswerId
group by Student.Name 

Claro que isso pode ficar cada vez mais complicado, por exemplo, em relação às questões reais (em ordem crescente de complexidade):

• São exatamente 10 perguntas?
• Ou há vários conjuntos de 10 perguntas?
• Ou 10 perguntas são escolhidas aleatoriamente de um conjunto maior de perguntas?

Em vez de abordar o aspecto específico do modelo de dados da questão (já existem cinco respostas que fazem um bom trabalho), abordarei o objetivo declarado desta questão (afinal, o contexto é importante).

Este é o meu projeto pessoal para fins de aprendizagem. Estou tentando aprender Entity framework + C# por conta própria.

Agora, eu entendo que pode muito bem haver outros fatores e detalhes que não foram compartilhados que tornam a busca por esse modelo de dados específico o curso de ação mais apropriado. No entanto, só sei o que foi compartilhado (a declaração acima), portanto, com isso em mente:

SE você estiver fazendo este modelo apenas para aprender Entity Framework (EF), C# e possivelmente MVC / ASP.NET / etc, talvez seja melhor considerar uma das seguintes abordagens:

1. Dado que você já fez várias perguntas sobre modelagem de dados, usar o modelo de dados real a que essas perguntas se referem pode facilitar o foco no aprendizado do material .NET. O modelo de dados nesta questão específica é uma situação artificial e, portanto, não foi testado com dados reais e usuários reais; carece de certas complexidades que surgem à medida que um sistema começa a ser usado. Portanto, você poderia gastar um pouco de tempo repensando vários aspectos desse modelo após sua concepção inicial, quando deveria se concentrar em como usar o Entity Framework para interagir com as regras de negócios do mundo real com as quais você já está familiarizado/confortável com.

2. Se você realmente precisa/deseja um sistema para gerenciar alunos fazendo um exame, não há necessidade de reinventar a roda quando muitos desses sistemas já existem e podem ser estudados. Há toda uma classe de software chamada Learning Management Systems (LMSs) e pelo menos um deles é de código aberto e é bastante popular, então eles já passaram pelas dores iniciais de encontrar pequenas coisas que não funcionaram. E você certamente não precisa do modelo de dados completo, pois ele lida com muito mais do que você precisa para aprender o EF; portanto, basta pegar as partes necessárias para começar a criar um aplicativo .NET em torno dele.

   O sistema que estou pensando chama-se Moodle e pode ser encontrado no GitHub em:
   https://github.com/moodle/moodle/blob/master/lib/db/install.xml
   Esse link vai direto para o modelo de dados deles. Na verdade, é uma descrição do modelo de dados que eles renderizam em um dos 6 ou mais dialetos RDBMS diferentes. Mas é tão fácil de ler que você pode ver facilmente as tabelas, índices, relacionamentos, etc. Você pode até instalar o Moodle para que ele crie a versão SQL Server desse modelo. O código deles é todo em PHP, então você pode precisar baixá-lo separadamente, mas eles têm um binário para Windows (leia a seção "Qual versão eu escolho?" à esquerda).

Mais uma vez, entendo que você pode ter um ou mais motivos para precisar manter o modelo de dados sobre o qual está perguntando nesta pergunta. Portanto, esses são apenas pontos a serem considerados, talvez para o futuro, se não agora, ou talvez para outras pessoas que pesquisam "modelos de dados de exames / testes" que podem desconhecer as opções de código aberto, como o Moodle.

MAS, independentemente de qual caminho específico você escolher, você definitivamente precisará aprender mais sobre ajuste de desempenho, os prós e contras dos vários tipos de dados, etc., já que você está usando um ORM (ou seja, Entity Framework). Certifique-se de procurar "plan cache bloat" e lembre-se de que o EF realmente tem a opção, para cada objeto e ação, de chamar um procedimento armazenado em vez de gerar dinamicamente o SQL :-).