Looking for Help

[lance7tour]

Am new to Access Database Design and having trouble understanding the concept of this assignment and if I am on the right track.



These are the headings that are part of the excel spreadsheet:

InstructorName, LocationName, LocationAddress, LocationCity, LocationState, LocationZip, DateInstructor, AssignedToLocation, CourseNumber, CourseName, CourseSection, Number, Credits, SemesterOffered

These are the business rules associated with the headings:

1 Each location can have more than one instructor
2 Each instructor can teach at more than one location.
3 Each course has one name
4 A course is always worth the same number of credits
5 A course can have many sections
6 Each section has a unique section number. These numbers are never reused.
7 Each location offers many course sections
8 Each course section is offered at a single location
9 Each location has one address
10 Course sections are assigned to locations, not instructors

These are the instructions that I was given:

"You are only supposed to write out the DBDL plan for the database, not actually make any tables. Your finished product should be similar to the sample I just sent you. n this class, it's all about the planning part. In the next class, DB Apps, is where you build the database. So, look over the sample data and the business rules and give it a shot."

This is what she sent me as examples:

tblStudents (StudentID, FName, LName, MI, SSN, BirthDate)

tblClasses (ClassID, Name, CatNum, Credits, Description)
SK Name

tblClassSections (ClassSectionID, Location, Semester, Instructor, ClassID)

tblStudentClassSections (StudentID, ClassSectionID, DateEnrolled, Grade, Notes)

This is what I came up with from the above and havent gotten any feedback from other students of the instructor:

1. tbl1 (LocationName, Instructor Name)
2. tbl2 (InstructorName, LocationName)
3. tbl3 (CourseName, CourseNumber, CourseSectionNumber)
4. tbl4 (CourseNumber, CourseName, CourseSectionNumber, Credits)
5. tbl5 (CourseNumber, CourseName, CourseSectionNumber)
6. tbl6 (CourseSectionNumber, CourseNumber, CourseName)
7. tbl7 (LocationName, CourseSectionName, CourseNumber, CourseName)
8. tbl8 (CourseSectionName, CourseNumber, CourseName, LocationName)
9. tbl9 (LocationName, LocationAddress)
10. tbl10 (CourseSectionNumber, CourseNumber, CourseName, LocationName)

any help would be greatly appreciated.

[ssanfu]

Do you know about normalization? Here are 3 sites to read:
http://www.sitepoint.com/database-design-management/
http://www.jpmartel.com/bu12_c.htm
http://databases.about.com/od/specif...malization.htm


So let's look at what your instructor sent you:

tblStudents (StudentID, FName, LName, MI, SSN, BirthDate)

This table holds info about students, with [StudentID] as the PK.
---------------

tblClasses (ClassID, Name, CatNum, Credits, Description)
SK Name

This table holds info about classes, with[ClassID] as the PK
---------------

ONE Class can have MANY Sections. ONE Section can have only ONE Class.
This is a 1 to many relationship. So...

tblClassSections (ClassSectionID, Location, Semester, Instructor, ClassID)

This table has [ClassSectionID] as the PK and [ClassID] (from tblClasses) as a FK.

---------------
Looking at students, ONE student can have MANY class sections, and ONE class section can have MANY students.
This is a MANY to MANY relationship. So we need a table that links students and class sections:

tblStudentClassSections (StudentID, ClassSectionID, DateEnrolled, Grade, Notes)

Here [StudentID] is a FK to tblStudents and [ClassSectionID] is a FK to tblClassSections.

--------------
In my dBs, I have an Autonumber field as the PK in every table. In the case of a junction table, I still have the autonumber field, but use a compound key as the PK. In your example, in tblStudentClassSections, the compound PK would be [StudentID] & [ClassSectionID].

So read the sites on normalization, then try and apply those concepts to your assignment. Try and use phrases like

"ONE XXXX can have MANY YYYY."