Download EducationModel.edmx.zip
Introduction
For creating of this articles was used some materials from books and separate articles that published in Internet, also some changes for this materials was added and some parts was added (please see list in the end of articles).
In this article i want to describe how I try to create databese with first 3 normal forms (NF) of reelationship. I prepare some step-by-step explanation what was done.
Background
Currently I want better understand main principles of creating and managing databses. As first step I want to create good database, that allow me to prepare adequate UI and code for managing it.
What was used:
- Microsoft SQL;
- .Net technologies;
- Microsoft SQL management Studion (2012) or Visual studio 2012
Step-by-Step: explanation how to
First of all I read some information about SQL language (Itzik Ben-Gan - Microsoft SQL Server 2008: T-SQL Fundamentals) - it's help to better understand what goin on when you use some command and how it's work. After some trying without additional learning about NF i got database, but after some usage of it with UI I got some problems, as result i start to find some rules for creating databases. And I found even more - NF relationships rules.
Ther are 6 NF for databese (or even more). But even usage of first 3 of them allow you to create really good databse. Lets look a little bit detailed - how I create databse with first 3 NF.
1NF - this is normal relationship, mean all relation already in 1NF. Main properties of such relationship (according to this article) are:
- Table must be two-dimensional, with rows and columns.
- Each row contains data that pertains to one thing or one portion of a thing.
- Each column contains data for a single attribute of the thing being described.
- Each cell (intersection of row and column) of the table must be single-valued.
- All entries in a column must be of the same kind.
- Each column must have a unique name.
- No two rows may be identical.
- The order of the columns and of the rows does not matter.
As example let assume that we need to create database for some school. As result we can get next domain model can be like described below:
- All teachers and student have some school
- All schools have some grade
- All school have some specialized classes with some name and number
- All students have some class
- All studenst, teachers and schools have some address
- Students have some dedicated teacher
- Teacher can have few classes
- Student and teacher can have some project
- Each project have name, speciallity and dueDate
During first step of creating database you can modeling your database as 1 table in 1 relationship, and as result you get 1 table where all information was combined: info about school, teacher, student. Also as result you can got situation, when same information can be duplicated - so additional problems and resources will be needed during updating info about teacher, that have 10 students - you need 10 times so same work - result of such activities - additional code, time required. So data that stored in such way have very big redundantly.
Main problems:
- Anomalies of inserting (INSERT)
- Anomalies of updating (UPDATE)
- Anomalies of deleting (DELETE)
So this example already in 1NF, but it have some anomalies, so 1NF not enough for correct working of database. We need to use 2NF.
2NF - relation ship is relationship of 2NF only if there is no non-key attributes that depend from keys that are already parts of the complex key. Non-key attribute - attribute that excluded from any potential keys.
Requirements for 2NF are:
- Table must be in first normal form (1NF).
- All non-key attributes (columns) must be dependent on the entire key.
If we look at our example it's mean that (few samples):
--> - dependence
- {StudentName, StudentSurname, StudentSecondName} --> StudentID
- {SchoolAddress, SchoolName} --> SchoolId
- ProjectName -->ProjectId
- ClassName -->ClassId
- {TeacherName, SchoolName, ClassName} --> StudentId
- etc.
As you can see part of anomalies that was described in 1NF part, are solved - all data about students, teachers, schools now stored in separate tables - its mean that now now this information stored without any redundancy, but another parts of data still have same problems: as example: {TeacherName, SchoolName, ClassName} --> StudentId.
For solving all remain anomalies we must to use rules from 3NF - all non-key attributes must be independent. Also requirements for this 3NF are next:
- Table must be in second normal form (2NF).
- Table has no transitive dependencies.
After applying all rules to sample database i got next structure:
Points of Interest
You can create database in few way:
- using VisualStudion (as separate *.mdf file, during adding EntityFramework model etc)
- using Microsoft SQL Management Studio
- using another software
I use Visual Studio and EntityFramework Model First. After finishing i got generated script of database: you can download *.sql file as sample
Usefull links:
Whats to do next:
Next step - creating programm that will be use this database.
Comments:
Please look at my database, hope all was done correctly, if no - please comment and let me know where i was wrong.
