Tables & Relationships (1 Viewer)

[darth sidious]

Hi

I have attached a Scenario that I am looking at devising a prototype database for. What I am interested in is how many tables would be needed and the relationships based on the scenario attached.

Also, what type of keys/compound keys etc


Scenario

Vicky Wilkinson is the performing arts teacher. She produces performances at various times during the year. Some of the performances are used to generate evidence for students studying A-level performing arts, others are purely for fun. The next production will be The Wizard of Oz and it is a performance for fun. It will be performed for the general public over a number of dates.

Every performance needs students to take a range of production jobs, for example sound crew, directing crew and actors. Students specify the production job they would like to have. They keep the same job on all productions they are involved in.

Each production has characters, which only actors can be assigned to play. For example, in The Wizard of Oz there is a character called Dorothy. Only one actor is assigned to a title role, such as Dorothy. Up to ten actors can be required for a bit part character, such as winged monkeys.

Currently Vicky keeps handwritten records of students and productions. She is finding this stressful as it is hard to make sure production jobs have been filled and all characters have actors assigned to them. Vicky knows you are studying ICT and thinks you could develop a more efficient IT-based solution to register students and manage the productions. In the first instance it has been agreed that you will design and build a prototype relational database system that will focus on three tasks.

Registering students, which involves:

· ensuring their name, gender and preferred production job are present
· generating a unique student number
· storing the student details if all requirements are met
· providing a suitable message for Vicky, so that she knows what is happening.
Assigning students to characters and other jobs for The Wizard of Oz, using one
form only, which involves:

· selecting the production
· indicating what type of production job the student is to be assigned to
· if it is an actor then:
· selecting the character
· displaying details for this character
· calculating the number of actors still needed
· ensuring only a suitable actor can be assigned to that character
· storing the character assignment if all requirements are met
· updating any records as necessary.
· if it is another production job:
· allowing the job to be selected
· allowing only a suitable student to be assigned to that job
· storing the job assignment if all requirements are met.

Many thanks

Dart Sidious

 

[namliam]

Looks like school work to me, non the less, simply start by normalizing your data.
This process starts in the zero normal form, simply start by writing down all the information you need, like you started doing but a bit more geared towards databases.

Student Name
Date of Birth
Student ID
etc....

Then going thru steps of normalization of 1,2,3 untill you get to your required design.