Many-to-Many Relationship – Student and Course Relationship with Grade and Attendance

I like the gist of what you're doing. I think the entity names are a bit confusing though, and I would put some fields in different places. From my understanding of the system you're trying to build, here's a diagram I recommend:

In this model, Assignments have one AssignmentOccurrence for every time it occurs. If an Assignment occurs once, it would have one AssignmentOccurrence.

Take the values of the AssignmentOccurrence with a grain of salt, there's not enough information for me to know what data needs to be stored in there.

I was just going to make this a comment but it is too long to just be a comment, and I think it is one viable solution though I would say that there are many solutions and it will depend on how large the tables are going to be, how frequently they are used, how they are used, etc...as to what is going to be the best for your situation.

In this case, I think having your Type, Status and SubStatus tables all be separate and having a table that links them together, seems to make sense.

Here is the code that I put together to make that happen for your data.

create table tblType
(
    TypeID int identity(1,1) primary key clustered
    ,TypeName varchar(20)
);
GO
Create table tblStatus
(
    StatusID int identity(1,1) primary key clustered
    ,StatusName varchar(20)
);
GO
Create table tblSubStatus
(
    SubStatusID int identity(1,1) primary key clustered
    ,SubStatusName varchar(20)
);
GO
create table StatusSubStatus
(
    StatusSubStatusID int identity(1,1) primary key clustered
    ,TypeID int
    ,StatusID int
    ,SubStatusID int
);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Type 
FOREIGN KEY (TypeID) REFERENCES tblType(TypeID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_Status 
FOREIGN KEY (StatusID) REFERENCES tblStatus(StatusID);
GO
ALTER TABLE StatusSubStatus ADD CONSTRAINT fk_SubStatus 
FOREIGN KEY (SubStatusID) REFERENCES StatusSubStatus(StatusSubStatusID);
GO
insert into tblType 
Values ('Type 1'), ('Type2'); --id 1, 2
GO
insert into tblStatus 
Values ('A'); --id 1
GO
insert into tblSubStatus 
Values ('X'), ('Y'), ('Z');  --id's 1, 2, 3 respectively
GO
insert into StatusSubStatus
Values (1, 1, 1), (1, 1, 2), (2, 1, 3);
GO

The advantage to this is that you get to keep your Status/Type tables from being large. The disadvantage is that you have a linking table that can get quite large. But it is a clean way to get all of this stuff together cleanly.

If you wanted to see all of the data for TypeID of 1 you would just do the query

Select TypeName, StatusName, SubStatusName
from StatusSubStatus sss
join tblType tt on sss.TypeID = tt.TypeID
join tblStatus ts on sss.StatusID = ts.StatusID
join tblSubStatus tss on tss.SubStatusID = sss.SubStatusID
Where tt.TypeID = 1;

There are many solutions to making this work, this is one that I like but it will certainly depend on your situation as to whether this is the best one for you or not.