Results 1 to 6 of 6

Thread: Database Design

  1. #1
    Join Date
    Jun 2003
    Location
    Wakefield, UK
    Posts
    8

    Database Design

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Database Design

    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Wakefield, UK
    Posts
    8
    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
    Attached Thumbnails Attached Thumbnails relationship.jpg  

  4. #4
    Join Date
    Apr 2003
    Location
    London, England
    Posts
    42
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Wakefield, UK
    Posts
    8
    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:

    Code:
    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:
    Code:
    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

  6. #6
    Join Date
    Jun 2003
    Location
    Wakefield, UK
    Posts
    8

    Red face

    ...to answer my own question...

    Code:
    CourseID	smallint	NOT NULL,
    	FOREIGN KEY REFERENCES Courses(CourseID)
    		ON DELETE CASCADE
    you'll notice the comma is in the wrong place


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •