[nooch]

Database Design Quote | Reply

--------------------------------------------------------------------------------


I'm having difficulty linking some tables together. Basically the database is being hosted on MS SQL, but is still in design stage so any alterations can still be made. I need to link the following tables:


Students - This table holds information about the students attending a college. Each student is given a unique identifier.

Staff - Holds information about the staff, contains a unique identifier for each staff member

Assignments - Holds information about assignments, has unique identifier for each assignment.

Courses - Holds information about courses, has unique identifier for each course.

Progress - This holds information about each student's progress of each assignment. This is also used by the staff to mark assignments, and used by the college intranet to display to students which assignments are currently unfinished.

Now, many students can study on many courses. Many staff can teach on many courses. Each course contains many assignments.

Course=---=Students
Course=---=Staff
Course----=Assignments

Which is easy enough. However, the database is going to be used to create a more intricate information system, as part of mainly an ASP intranet system. So we need to some how add the progress table to the existing design so we can pull the following information from the database easily...

So from the database we need to:

• display new assignments for students (if any) that staff submit
• find out which course the student is studying, so we can direct the student to appropriate information
• display student names for a staff member for individual courses that that they study on, as well as all courses
• and also some other minor queries, but these are not majorly important.

If someone could suggest how we would go about relating the tables together to provide a solution that we can easily query and get the information above, we would really appreciate it, because we are stumped

Thanks in advance,

Daniel Thompson

[andrewst]

Really your database design should be based on how the data is actually related (I like your text colour change trick, I'll have to remember that!); examples of specific queries to be answered are not relevant at this stage (they may be relevant to adding indexes later).

So: what is the Progress entity about? It records the progress of a Student on an Assignment. This suggests:

Student -= Progress =- Assignment

Is there just ONE Progress record per Student/Assignment combination? If so, the key for Progress is (StudentID,AssignmentID). If not, you will need some additional attribute to distunguish records.

[nooch]

yep you got it, the progress table records information about the the progress of students for each assignment.

And i think there is one record per student/assignment configuration, but it also needs taking into consideration that the staff have input on the progress, as they create the assignments and eventually mark them

Please find attached the origional relationship defined in MS access, so some of the relationships may be incorrect, please feel free to comment, and recommend any suggestions.

--Note Attachment is about 97k

[barryw]

I have created a Data Model that you might find interesting :-
http://www.databaseanswers.com/data_...ents/index.htm

There is a set of Business Rules that helps to clarify the conditions that the Database must satisfy if it becomes an operational system to record Student Assignments.

Barry Williams
Principal Consultant
Database Answers

[nooch]

thanks.

OK i created a new layout i will explain as i go along, first i need some help with SQL, why won't the following code work:

CREATE TABLE Tutors(
--Primary Key
TutorID		smallint	NOT NULL
	PRIMARY KEY CLUSTERED	IDENTITY(1,1),

StaffTag	char(11)	NOT NULL,
-- POSSIBILITY OF VALIDATION HERE

Title		varchar(10)	NULL,
Surname		varchar(20)	NOT NULL,
Forename	varchar(20)	NOT NULL,
IntTel		varchar(8)	NULL
)

CREATE TABLE Courses(
--Primary Key
CourseID	smallint	NOT NULL
	PRIMARY KEY CLUSTERED	IDENTITY(1,1),

--Normal Fields
FacultyID	smallint	NULL,
-- Uncomment following two lines and customise to integrate with system
--	FOREIGN KEY REFERENCES Faculty(FacultyID)
--	ON DELETE NO ACTION

CourseCode	varchar(6)	NOT NULL,
CourseName	varchar(50)	NOT NULL,
FTPT		bit		NULL
)

CREATE TABLE TeachesOn(
-- Primary Key
RecordID	smallint	NOT NULL
	PRIMARY KEY CLUSTERED	IDENTITY(1,1),

--Foreign Keys
TutorID		smallint	NOT NULL
	FOREIGN KEY REFERENCES Tutors(TutorID)
		ON DELETE CASCADE,

CourseID	smallint	NOT NULL,
	FOREIGN KEY REFERENCES Courses(CourseID)
		ON DELETE CASCADE
)

The error is:

Server: Msg 8139, Level 16, State 1, Line 31
Number of referencing columns in foreign key differs from number of referenced columns, table 'TeachesOn'.

i've looked for typos, but can't find anything, and as the tables it is referencing are created before i can't see there being a problem with this code? can anyone help me (again...)

Thanks in advance,

Danny

[nooch]

...to answer my own question...

CourseID	smallint	NOT NULL,
	FOREIGN KEY REFERENCES Courses(CourseID)
		ON DELETE CASCADE

you'll notice the comma is in the wrong place