CIS2002: Clive Finkelstein’s techniques - Database Assignment

Internal Code: MAS3605

Database Assignment:

You are designing a database for multiple choice questions in an online test module of the student information systems for a campus. The online test module stores general profile information about all the students and records student responses and test results. All information regarding the tests including all the questions and options of the multiple choice questions are recorded in the module. A test is recorded with a name, description, start date, end date and must belong to one of two types: General Test or Academic Test. These two types of test are recorded in an entity called TEST TYPE. For Academic Test, you also need to store the Field of Research (FoR) code. A test contains a minimum of 10 questions. Each question can be listed in multiple tests as well. For a question, we store its identifier, the question text and the category for the question. A separate list of all category names are stored in an entity called CATEGORY. A question also has a minimum of four options where one of the options must be the correct answer. All the options for each question are stored with an identifier, option text and a flag to determine whether the option is the correct answer or not. Student numbers and email addresses are imported from a legacy application and stored in the STUDENT entity. The imported student number is the identifier of the student entity and the email address is the username for login. Other details stored for a student include name, password, account joined date and account expiry date. Questions: a) An ER diagram of the system. Show all entities, relationships, cardinalities and m optionalities. Also, include all intersection entities. You must use the Finkelstein methodology as per the study book and tutorials. b) A list of relations (equivalent to Finkelstein entity list). Produce complete relations with all entities and attributes. Show all primary and foreign keys. Include all attributes that are specifically mentioned and all key attributes. You may need to create primary and foreign keys that are not specifically mentioned. You must use the Finkelstein methodology as per the study book and tutorials c) An Oracle SQL table create statement for the relation that you think is most critical in this system. This relation must have a primary key and at least one foreign key.