Stragling with homework. Could anybody help , please.

[Guest]

I know sounds fanny, but i got problem with understanding Database design in
Access. I 've read ten times tutorials but nothing helps.
This is content of homework:

All students are enrolled on courses, each student on one course only.

Students register for modules and the list of students enrolled for each
module in each semester is held in the Module Register which also holds
information about which tutorial group the student is in and the marks of the
students achieved in the coursework and examination assessment. The
Programme of Study holds information specifying which modules are core or
designated for the course.

Each course also has modules and subject lecturers for that course.

One course can contain many modules and one module can be a part of one
course only.



The text above contains information that needs to be stored in a database.
For that purpose, do the following tasks (please note that Tasks 1 and 2 are
done on paper before you even open MS Access):



1. From the text above identify the rest of the entities and their
attributes that are relevant to the University Database.

Example: Student as an entity has characteristics (attributes) such as
RegNo, LastName, FirstName, Age, Initials, DateEnrolled.



2. Entities and attributes identified in Task 1 can be stored in
various tables that share common information. The tables can be linked
through relationships.

Eg. Student has to enrol on a course,

So there is a relationship between the tables Student and Course.

Determine the relationships between the entities (tables) identified above

If anybody wants to help me , at least to give direction how to start ,i
'd be glad.

 

[Guest]

Azik,

I think it's important that you struggle with it yourself, but to get you
oriented...

First, start by not worrying about data structures, or the limiting factors,
etc. Those are details you can flesh out later. First think in terms of
Things, and Adjectives that describe these things (what your professor calls
Entities and Attributes--why academics use big words when small ones will do
is a mystery to me!). The Things will become tables, and their Adjectives
fields in the tables.

In your case, I can think of the following things:

Students
Modules
Courses
TutorialGroups

Then, attempt to flesh out the adjectives. Your prof has given you a start
below. Now, think in terms of relationships. There are 2 basic
ones--one-to-many, and many-to-many. A one-to-one normally can be
implemented simply by making a single table. There are exceptions, but they
are beyond your assignment scope. In any case, a one-to-one is really just a
special case of a one-to-many.

If you discover a many-to-many relationship, you will need another table,
because a many-to-many can be implemented by two one-to-many relationships.
For example, in an Order Entry database, there is a many-to-many relationship
between Customers and OrderDetails. Many customers can order the same
product, and each customer can order many products.

Now, as your prof has probably described to you, you in almost every case
don't want any fields in a table that are already defined in another table,
EXCEPT the one that corresponds to that table's primary key(PK), called a
foreign key (FK).

For example, a Customer table would have a customer number(primary key),
name, address, phone, etc.. To enter an order, you'd need to identify it
with a certain customer, so you would include a customer number field in your
Orders table (a foreign key), but NOT their phone, address, name, or any
other Customer field. Access is named for a reason, relational databases, by
using foreign keys, give you access to any field in a related table. If you
get this clear, you're on your way, because 25-50% of the questions on this
newsgroup relate to this concept.

In the Order Entry example above, the missing table that converts the
many-to-many to two one-to-many's is the Orders table. It would have an
order number(PK), the customer number (FK to Customers), the date the order
was placed, the code for the salesperson who took the order (FK to
Salespersons), and perhaps a Shipping Code (FK to ShippingMethods). The
OrderDetails would have a ProductID (FK to Products), a Qty, and a UnitPrice.


The UnitPrice field, by the way, is an exception to duplicating fields from
one table across another, because we have to capture the UnitPrice from the
Products table at the time of the order, otherwise the Order record would be
incorrect after we changed prices down the road.

Good luck. Hope that gets you started.
Sprinks

 

[Guest]

Hello,

I really shocked . I didn't believe , I will get any reply.
This short explanation more then enough. It's very helpful and engouraging.

Thank you very much for your help.