Results 1 to 2 of 2
  1. #1
    Join Date
    May 2016
    Posts
    1

    Identifying redundancies with triple set PK

    I have this relational table ENROLMENT(student#, subject-code, title, enrolemnt-date, status) containing information about the enrolments performed by the students. A student is identified by a student#. A student enrolls a subject identified by a subject-code and described by a title. Enrollment happens on enrolment date (enrolment-date) and it has a status, e.g. valid, dropped, or provisional. A triple of attributes (student#, subject-code, enrolment-date) is a primary key in the table.

    I wish to show sample contents of ENROLMENT with redundant information and explain what redundancies are included in this table. I am stuck.

    Code:
    Student#	Sub-code#	Date	         Title 	Status
    1	         1	             27/04/2016	Database	valid
    1	         2	            27/04/2016	SQL	        valid
    2	         2	            27/05/2016	SQL	         Dropped
    Redundant data are sub-code and date which is . Am I correct? Please clarify.

    OR

    Code:
    Student#	Sub-code#	Date	        Title 	      Status
    1	        1	             27/04/2016	Database	valid
    1	        1 	             27/04/2016	Database	Dropped
    2	        2	             27/05/2016	SQL	        Dropped
    2	        2	              27/05/2016	SQL	       Valid
    3	        2	              27/05/2016 SQL	       Dropped
    Redundant data are student#, sub-code and date which is 1 1 27/04/2016 and 2 2 27/05/2016
    Last edited by jabit; 05-27-16 at 05:05.

  2. #2
    Join Date
    May 2016
    Posts
    89
    Hi Jabit,

    I uploaded

    conceptual data model
    Click image for larger version. 

Name:	cdmd.jpg 
Views:	2 
Size:	66.6 KB 
ID:	16959

    And physical data model
    Click image for larger version. 

Name:	pdm.jpg 
Views:	2 
Size:	81.7 KB 
ID:	16960

    In your PDM, considering your needs, you create an unique index on :

    1. tuple(idStudent/idsubject) if you want to store only the current /active status
    2. tuple(idStudent/idsubject/idStatus) if you want to store all different status (historic. In this case, add a endDate field for being more meaningful)

Tags for this Thread

Posting Permissions

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