What tables do i need to create?

[fergie223]

alright im new here and i have just started using oracle. i have a oracle database to create and i am having problems choosing what tables to create. the database i am designing is explained as follows:

A database system is required to control the way in which exam paper moderation is carried out in the computing department. The current system involves the staff members who teach on the module setting an exam paper. This is then passed to another member of staff with knowledge of the area who checks it for any errors or problems with the level of question set. The moderator writes a short report which is handed back to the setter. Any changes to the paper are then made and the setters record any changes made in response to the moderator. If the paper requires an external examiner it is posted off to another University. All papers that require an external examiner are sent in a single batch by a date specified by the University. There is normally one external examiner per course (i.e. one for the BSc, another for the AB, MSc etc.) It then comes back a few weeks later, and may have suggestions for alterations/corrections from the external examiner. Finally after this stage, it is submitted to the exams office ready to be printed in time for the examination period.
Each of the stages above has to be recorded, i.e. the progress of each paper through the process is kept up-to-date, usually with the date that the paper passes each stage recorded, but as indicated above at various stages short text reports are compiled.
For many modules there is one member of staff who sets the paper, but there are a number of modules shared between two or more staff members.
[N.B. external examiners are normally required to see all award bearing papers - which normally means final year papers on a particular course, although he can request to see more]


thanks in advance for any help. i just need to make sure i choose the right tables and be sent in the right direction before i start creating the database. cheers.

[tsboles]

Database design often starts off with the cited requirements and then you develop the derived requirements. These are the cited requirements that I see within your paragraph.

Staff members associated with module.
Exam paper associated with module and staff member.
Staff member is either setting an exam paper (setter) or checking an exam paper (examiner).
Moderator associated with exam paper and a short paper.
Exam paper updated by setter from comments in short paper.
Setter communicates changes to moderator.
Possible external examiners from other University exam paper.
Batch assignment by date of all papers that require an external examiner.
One external examiner per course (i.e. one for the BSc, another for the AB, MSc etc.)
Possible suggestions for alterations/corrections from the external examiner.
Exam submitted to the exams office
Each of the stages above has to be recorded.

You might see more but that is what I see at first glance.
You now need tables for the objects in these requirements. Within these tables could be derived requirements which I will mark with DEV. I will even go through a couple of tables in the form of: FIELD DATATYPE NULLABLE

PERSON_TABLE
PERSON_ID number NO (This is unique and the primary key on table)
NAME varchar2 NO
POSITION varchar2 NO (ie. STAFF, EXTERNAL EXAMINER)
TELEPHONE varchar2 YES (DEV)
ADDRESS varchar2 YES (DEV)
EMAIL varchar2 YES (DEV)
DEPARTMENT varchar2 YES (DEV)

PAPER_TABLE
PAPER_ID number NO (This is unique and the primary key on this table)
PAPER_TYPE number NO (This refers to EXAM, SHORT_PAPER, EXTERNAL_COMMENTS or similar paper types)
PAPER_NAME varchar2 NO (DEV)
PAPER_LOCATION varchar2 NO (DEV)
PAPER_SETTER NO YES (This refers to PERSON_TABLE but since we are recording several different papers it goes can be nullable)

EXAM_PROCESS
PAPER_ID number NO (This is a foreign key relating to PAPER_TABLE)
CURRENT_PROCESS number NO (This will refer to another table with with process IDs.)

......I am going to end this here. You can make it very complicated or simple depending on what your future vision. If you think that more processes can be added later, then you need a process table which can be added to and referenced. If you think you can have different types of examiners added then you will need an examiner table that can be referenced. I hope this helps you get on track.

Tim Boles