Database schema for a school

Here is the database query needed to create the database:

create table Area
(
AreaId int primary key identity(1,1),
Name nvarchar(64) not null
)

create table Level
(
LevelId int primary key identity(1,1),
Name nvarchar(32) not null,
Principle nvarchar(512) not null
)

create table Subject
(
SubjectId int primary key identity(1,1),
AreaId int foreign key references Area(AreaId),
LevelId int foreign key references Level(LevelId),
Name nvarchar(32) not null,
Abbreviation nvarchar(16)
)

create table StaffType
(
StaffTypeId int primary key identity(1,1),
Name nvarchar(64) not null
)

create table Staff
(
StaffId int primary key identity(1,1),
StaffTypeId int foreign key references StaffType(StaffTypeId),
Name nvarchar(128) not null,
LastNameFather nvarchar(256) not null,
LastNameMother nvarchar(256) not null,
DateOfBirth datetime,
PlaceOfBirth nvarchar(256),
Sex nvarchar(8) not null,
Carnet nvarchar(64),
Telephone nvarchar(64),
MobilePhone nvarchar(64),
Address nvarchar(256),
FatherName nvarchar(256),
MotherName nvarchar(256),
FatherContactNumber nvarchar(64),
MotherContactNumber nvarchar(64),
FatherPlaceOfWork nvarchar(64),
MotherPlaceOfWork nvarchar(64),
DateOfHiring datetime,
YearsOfService int,
Formation nvarchar(128)
)

create table Grade
(
GradeId int primary key identity(1,1),
Name nvarchar(32) not nul开发者_如何学编程l,
LevelId int foreign key references Level(LevelId),
Observation nvarchar(256)
)

create table GradeInstance
(
GradeInstanceId int primary key identity(1,1),
StaffId int foreign key references Staff(StaffId),
GradeId int foreign key references Grade(GradeId),
Name nvarchar(32) not null,
Year datetime
)

create table Student
(
StudentId int primary key identity(1,1),
RUDE int,
Name nvarchar(64) not null,
LastNameFather nvarchar(256) not null,
LastNameMother nvarchar(256) not null,
DateOfBirth datetime not null,
PlaceOfBirth nvarchar(128),
Sex nvarchar(8),
Carnet nvarchar(32),
Telephone nvarchar(64),
MobilePhone nvarchar(64),
Address nvarchar(256),
FatherName nvarchar(512),
MotherName nvarchar(512),
FatherMobilePhone nvarchar(64),
MotherMobilePhone nvarchar(64),
FatherProfession nvarchar(64),
MotherProfession nvarchar(64),
FatherPlaceOfWork nvarchar(256),
MotherPlaceOfWork nvarchar(256),
Observations nvarchar(3000)
)

create table StudentInstance
(
StudentInstanceId int primary key identity(1,1),
GradeInstanceId int foreign key references GradeInstance(GradeInstanceId),
StudentId int foreign key references Student(StudentId)
)

create table StudentGradeReport
(
StudentGradeReportId int primary key identity(1,1),
StudentInstanceId int foreign key references StudentInstance(StudentInstanceId),
SubjectId int foreign key references Subject(SubjectId),
FirstTrimester int,
SecondTrimester int,
ThirdTrimester int,
FinalGrade int
)

If you find an attribute that should be null checked, please disregard it. I've gone over this with the client and they want certain things to left blank if so chosen by the end user.

My main concern when designing this database was how to associate a student with a grade here and now, yet keep a record for previous years and manage to see what grade he got back in 2009. See?

I think I've done a good job but you never know - the hivemind here will probably find a flaw and I'd love some feedback.

• No longer using SQLite, but MSSQL.
• I've tried to normalize the database as much as I could, but not religiously, just as much as it took to make it 'click'.
• Before a Student, was directly related to a Classroom (Grade) instance. Now I create 'instances' of Students and assign them to a Classroom (Grade) instance. Each instance has a date saying which year it belongs to. This helps me keep a record of previous years without dirty hacks.

create table Area
(
AreaId int primary key identity(1,1),
Name nvarchar(64) not null
)

"Name" should be UNIQUE.

create table Level
(
LevelId int primary key identity(1,1),
Name nvarchar(32) not null,
Principle nvarchar(512) not null
)

"Name" should be UNIQUE.

create table Subject
(
SubjectId int primary key identity(1,1),
AreaId int foreign key references Area(AreaId),
LevelId int foreign key references Level(LevelId),
Name nvarchar(32) not null,
Abbreviation nvarchar(16)
)

Odds are good that one or more of these apply. Impossible to tell without representative sample data.

• The set {AreaId, LevelId} should be UNIQUE.
• "Name" should be UNIQUE.
• "Abbreviation" should be UNIQUE.

I'm running out of time.

create table StaffType
(
StaffTypeId int primary key identity(1,1),
Name nvarchar(64) not null
)

"Name" should be UNIQUE.

More later if I have time.