Design a ERD Diagram

Information to be retained: 

Student Information

 

For student information, we need to keep:

 

• first name
• middle name initials
  • there can be more than one initial, but not normally more than 5
• last name
   
• phone number
• Residential address
  • ( this is one large field, at this stage we will not break it down into any smaller fields)
  • Residential address example: 27 Brown St, Bowden, South Australia, 5127
     
• email
• Nationality
• Date of birth
• date of enrolment at the school
• Gender
• in our current systems we have a student identifier with 8 characters starting with "1"
  • Student ID example:  11002233

Subject Information 

 

 

• Subject name -   Maximum of nine characters
  • Examples  WEB451SQL,   ICT302IWC, IT_PracTL, 5SDA
• subject description -   Up to 150 characters.
• cost of subject ( With decimal points in the price)
• duration in hours
• number of assessment items
• subject coordinator

 

 

 

School Information 

 

 

• school name
• address
• phone number
• email
• We do not have identifiers currently allocated to the schools, we just use the school name - we dont want new identifiers

 

Teacher Information 

 

For teacher information, we keep:

 

• Teacher ID which has eight characters starting with "4"
  • example:    44556677
• first name
• middle name initials
• last name
• email
• phone number

 

Subject Enrolment Information

 

The subject enrolment information that needs to be retained is not explicitly provided,  However we will need to keep: 

 

• The Date a student enrolled in a subject

Grade Information 

The Grade information that needs to be retained is not explicitly provided, However we need to keep: 

 

• The Grade a student acheves for a subject - in the format of 4 Characters being Pass, Cred, Dist, Fail
• The Result a student achieves for a subject - in the format of a Numeric value between 0 and 100  -  decimal points are not retained
• The Date a student receives their Grade in a subject

 

 

Hint: the grade range is:  

 

• <50 >
• 50 - 65 = Pass
• 65 - 80= Cred
• 80 - 100 = Dist

 

[the grade range does not need to be in its own table - it is simply information for the values used in the database you create ] 

 

 

 

Relationships identified - Needed for the ERD 

 

• A school has many students
• A student has one school
• A school has many teachers
• A teacher has one school
• A teacher coordinates many subjects
• A subject has one coordinator (who is a teacher)
• A subject has many students enrolled in the subject
• ( A subject  has many students - but each student is enrolled in that subject only once )
• A student has many subjects in which they enrol
• (A student enrolment has many subjects - but each student only enrols in each subject once )
   

     A working database for this scenario should end up with 5 tables
when identifying the relationships,  sometimes the potential entities may have a variety of different names or descriptors  ( ie student enrolment / subject enrolment / enrolment )