The school has asked you to produce a simple system to test students and you are asked to create a data model for it. Dean, Prof Daniel Chase, scheduled a formal meeting to go over the requirements with you.

You: Hi, professor. Can you please describe what you are looking for?

Dan: Hi. Sure. I have a team of web developers that are going to work on the front end. What I need from you is to help them with data modeling, considering your experience. So, we need a simple database, that can store information about our students, quiz bank, and test results.

You: I will be glad to help. What information do you keep for students?

Dan: That part is easy, we have a list of students in a file that is supported by my team. It has the student ID, full name, email address, school, and major. I also have a flag showing if the student is in a graduate, undergraduate, or post-graduate program. And lastly, we store enrollment dates.

You: How do you identify students?

Dan: Funny story with that. I thought the student id was supposed to be unique, however, I have recently learned that student id is unique only with an enrollment year. The IT team has assured me that the email address is unique, but I am not sure anymore.

You: I this it is safe to assume that email is unique, I guess. Talk to me about the quiz bank.

Dan: We keep a list of questions and possible multiple-choice answers.

You: Do you have any other questions, like open-ended or feel-in-blank type?

Dan: No, keep it simple. All questions will have 2-6 answers, with the possibility of more than one correct answer. The question is chosen from a topic which is always populated. It has a question code and text. I can also see the total possible answers and total correct answers for each question. I realize that if we add more answers to the question these values will and may change correspondently. The test bank is also available in a file, but I do not think you can use it to load it, so we may end up adding all questions manually.

You: I will see if we can automate it. Can you please tell me more about the quiz?

Dan. Sure. Each student may take as many quizzes as they would like. The student will pick a topic or multiple topics and the number of tests he/she wants to take. The application will then randomly generate a list of questions from the bank based on the preferences. We record the date when the quiz was taken, and the number of correct answers at the end of it.

The following requirement is optional. If you complete it you will get Extra Points (5 points).

Dan. One more thing. It would be awesome if I can go back to see which student answered what question, whether it was correct, and when it happened.

Create a conceptual and logical data models that captures all the requirements above.

Build an initial (non-normalized) conceptual and logical data models for the case above.

Identify all entities

Identify attributes. For each attribute provide what type of attribute it is. (Simple, derived, multi-values etc)

Identify the relationships between those entities

Identify the Candite Key.

Submit ER diagram (Visio, word, or excel files, a picture of an ER diagram on paper, or ER diagram extract from any tool you use)

Submit a text/word document that provides answers for each 5 deliverables above.