Help in DataBase Design (URGENT)

[Human01]

Hi Everyone,
I'm designing a database for a Motoring School and I've been given the following information:

• Pupils book either a single lesson or a course of lessons. Pupils are allocated a particular instructor when they register with the school. Sometimes, pupils ask for their instructor to be changed. A record of this should be kept.
• Occasionally a different instructor may actually take a particular lesson.
• Pupils also book mock driving tests. These are booked with local examiners, who may or may not also be instructors. Booking and paying for mock tests is treated the same as for lessons.
• Tests may be practical or theoretical. Many pupils can attend a particular theory lesson or test, while a practical session is restricted for an individual pupil. Sometimes pupils need more than one mock test which can be considered as lessons.
• Usually, the pupils pay in advance. Lessons, payments and pupils' details are entered onto a Pupil Record Sheet, when they first register. This sheet is kept at the office, and continually updated. A sample copy is attached.
• Pupils sometimes miss a lesson they have booked. If they miss the lesson with good cause and give a 48hr notification, the lesson is deferred until a later date and no payment is due. If the pupil simply fails to notify 48hr before the lesson or attend a lesson, the payment is due.

I'm stuck and confused in defining the entities in order to design the ERD and the Schema. Can someone guide me what Entities should be included and maybe little help on ERD and Schema.

I've decided to use MS-Access to create the database.

I really appreciate you help.

Thanx,
Adil

[timothybard]

People: ID, Name, Address, etc...
Students: ID, People ID (this table identifies who the students are)
Instructor: ID, People ID (this table identifies who the Instructors are)
Examiners: ID, People ID (this table identifies who the examiners are)

Please note that, with above tables, you enter the information about each person in the people table and identify if they are a student, instructor and/or examiner by adding a record to the appropriate table. This allows each person to be any combination of student, instructor and examiner.

Lessons: ID, Student ID, Instructor ID, date, Amount Due, Date Canceled (can be null) etc...
Tests: ID, Examiner ID, Test Type ID, date, etc...
Test Type: ID, Description (either practical or theoretical)
Student Attending Tests: ID, Test ID, Student ID
Student Lesson Payments: ID, Payment Amount, Payment Date, Lesson ID, etc...

Do students pay for tests as well as lessons or do they pay the examiners directly for tests?

[Human01]

Oh Timothy, I really appreciate your help.

I think you made it more clear to me now. I'll try to design the ERD and the Schema and I'll show them to you later today.

Regarding your Question, Yes the students pay for tests as well as lessons.

Thanx again,
ADil

[Human01]

Still trying to design the ERD and the Schema. Hope I can get it righ. I'll get back soon to show you the ERD and Schema for your review and guidance.

I appreciate anybody's help or comments.

Regards,
Adil

[timothybard]

I look forward to seeing what you come up with. It should be straight forward for the most part. I think the payment tracking will be the biggest issue.

[Human01]

Hi again Timothy,

I appologize as it took me along time to get back. If fact, I've been busy with other projects at the same time.

I'm abit confused with the entities. You suggested People and Students as entities. I can understand that Students is an entities, but why do we have People as entitiy? It's not mentioned in the problem description.

As you know that I should specify the right Entities so I can build upon the ERD, Schema and finally the application. I think I almost have the idea, but I need more guidance from you.

I will add the ERD after about 30 minutes for your review and comments.

Can you or anyone guide me in this?

Note: Tomorrow, 11th Nov is the deadline :confused:

Appreciate you help.

Human01

[Human01]

Sorry, Tomorrow 10th Nov is the deadline. ammmmh, so confused. :confused:

[timothybard]

There are three types of people:
Students, Instructors and Examiners.

I was suggesting that you use a people entity to track the name, address, phone number, etc. of all the people you record, whether they are Students, Instructors, Examiners or any combination of the three types. You would then use the Student entity, Instructor entity and Examiner entity to record which of the three categories each person belongs in. This allows you to record a person as an instructor and examiner (or any other combination of types) without having to record their name, address, etc in each of the three entities.

[Human01]

Hi again Timothy,

Ok, I got you. Thanks for your explanation.

I've attached a sample ERD and I hope I can have your comments and guidance.

Thanks alot,

Human01

[Human01]

Hi Timothy,

I should submit this work today. May I have your kind comments on the ERD?

I've been thinking of the tables and got a question, do you think that I should include another table for booking the lessons? (Booking Table)? If so, what attributes should I have within?

I appreciate you spot reply,

Thank alot,
Human01

[timothybard]

I reviewed the E-R Diagram and it is a good start.

I'm not sure a booking entity is needed as long as you have a booking date attribute in the test and lesson entities.

What is the Test Type entity for? When I hear the term Test Type, I would have thought that the entity would be joined to the Test entity and not the Pupil entity.

Additionally, since each lesson is taught by an instructor and test is given by an examiner, you need to have a relationship to show who is teaching each lesson and who is giving each exam. I would recommend not having the Instructor joined directly to the pupil but rather have the join go through the lesson, which the instructor will teach; same with the examiner.

Lastly, suppose a person who is an instructor is going to take an exam; how would this be recorded?

[Human01]

I really appreciate it, you made it more clear now.

I've attached another sample for the ERD. Please review it and and tell me if I can proceed with the relational schema.

Timothy, as you know I'm to submit this work today and I only have 3 hours left. I need to complete the last part which is the SQL using MS-Access. I must not use the Qeury Builder.

I have tried several times but I'm still new in SQL coding. the following are the requirements that I must do using the SQL code:

1) Produce the total number of test (practical and theoretical) and lesson sessions for October 2008.

2) Find out the unique identification number and the instructor’s full name of the most popular instructor (i.e. the one who got the highest bookings. Warning: there may be more than one popular instructor).

3) Produce a list of full names of all staff who are both instructors and examiners.

4) For any given pupil identification number, and for all sessions that were not taken by the pupil (i.e. lessons or tests), list the name of the allocated staff, the reason why the session was not taken and the method of payment for the session. The pupil ID should be passed as a parameter at run time.

5) For a given instructor for a given day, produce a report of all appointments for tests. This application will be run batch early in the morning of every working day. The input parameters should be picked from a drop down menu (e.g. Combo Box).

6) For each staff member, list the total number of tests (theoretical & practical) that they are responsible for, over a specific period of time. This application is to run on-line (i.e. produce the output on a form). The input parameters should be picked from a drop down menu (e.g. Combo Box)

Honestly, it's a matter of time as I don't have much time left. I hope I can get a fast reply and I'll remain grateful to you and to anyone offers me help and guidance.

I think point number 3 sounds easy, anyway I'll try my best to work on them all and I'll wait for your comments and guidance.

Thank you very much Timothy.

Human01

[timothybard]

Attached is the ER Diagram I would use. Please notice that any person is able to be a student, instructor, examiner or any combination of the three. Also note that my diagram does not include administrator; the only reason for this is because your original post did not mention an administrator and my diagram was based on your original post.

I'm sorry, but I cannot get you the SQL needed to perform the queries you mentioned in such a short amount of time. The problem is that I need to know your table names, the fields in your tables and the relationships between your tables before I can provide you with SQL.

If you have specific questions about how to build a query, I'd be happy to help, but I don't have time to build the queries myself.

[Human01]

Thanx for the ERD.

Ok, I understand the dificulty. I'll attach copy of the mdb file so you can have a look at the tables and the data included.

Thank you for your effort and help.

Human01

[timothybard]

Thank you for the attachment. Do you have any specific questions on how to build the queries you need? I don't mind helping, but I don't want to do the assignment for you.

Secondly, in your database, what is the purpose of the instruct_examiner table? It appears to record which people are both instructors and examiners; however, in the model I showed you, this table is not necessary. Also, since the tables determine whether the people are instructors, examiners or students, the group field in the people table is not necessary. It looks like you are very close, but things just need to be cleaned up.

[Human01]

Hi Timothy,

The deadline is passed now, but I'll have to submit it anyway.

I've included the instruct_examiner table because one of the requirements in the assignment is to produce a list of all staff who are both instructors and examiners.

For the same reason I've included the group field.

any suggestions?

I just need you to guide me to I can proceed and finish the aasignment.

Thnx,
Human01

[timothybard]

Instead of using the instruct_examiner table, I would recommend that you just add a record to the examiner table and one in the instructor table. People that appear in both tables are people who are instructors and examiners. If that information is stored in the tables, then the group field is not needed.

[Human01]

I think I'm doing well and I gain knowledge just because of your guidance :)

Thank you very much in deed.

I've cancelled the instruct_examiner table and I've added the tltle field to each examiner and instructor table. Now I got the idea.

I'll try to work on the queries now.

Thanks again

[Human01]

Hi again,

I've tried several times to work with teh queries, but I got stuck.

I've attached my mdb file, can you tell me why I'm not getting any data/records when I run the query A3?

Thankx,
Human01

[timothybard]

There are several things wrong with the query.

1) You have quotes on either side of the &. They are not needed since you are looking for the string "Examiner & Instructor"
2) In the Instructor table, the word Instructor comes before the word Examiner in each of the fields.
3) You need one criteria OR the other, not both.
4) The relationship from person to either table needs to be a 1 to many relationship.

Having said all that, making those corrections will make the query run; however, you still don't have the database structured correctly in my opinion. The i_title field and the e_title field are not needed. To see if a person is an examiner and an instructor, you simply need to see if records exist in both the examiner table and the instructor table for each person.

I have attached the database with 2 updated queries. One simply makes your query work. The second is the method that I recommend. Lastly, I added appropriate records to the instructor table and the examiner table to record things correctly. i.e. even though people_id 141 is both an examiner and an instructor, you only had him recorded in the instructor table.

[Human01]

Ahaa,, Then I'll have to re-check the table structure then.

Bye the way, I've tried the first query A1 to list the number of test(practical and theoretical) and lessons sessions for October 2008.

I used the following query:

SELECT COUNT(test_id)
FROM tests

By this I'm only getting the total number of tests in the tests table. I need to get the total number of sessions as well. those tests and sessions must be withing October 2008.

Can I say:

SELECT COUNT(test_id)
FROM tests
WHERE test_date BETWEEN 09/30/2008 AND 11/1/2008

But I don't know how to use COUNT to get the total records in two fields in October only.

Maybe I have the idea in simple English but I can implement it with coding.

Any clue?

[timothybard]

Here is the SQL for what you are trying to do in one query:

SELECT Sum(Count) AS SUMofLessonsAndTests
FROM 
(
SELECT count(lessons.lesson_date) AS Count
FROM lessons
WHERE (((lessons.lesson_date) Between #10/1/2008# And #10/31/2008#))
UNION ALL
SELECT Count(tests.test_date) as Count
FROM tests
WHERE (((tests.test_date) Between #10/1/2008# And #10/31/2008#))
)

[Human01]

Thank you Timothy, you've been very helpful and I'm learning alot from you.

Human01