Information Management system of School

학교에 관한 데이터베이스 디자인을 간단하게 구성해 보았다. 다음과 같은 구성으로 데이터베이스 디자인을 해보았다: 

1. ER diagram 

2. Schema 

3. SQL code

4. Normalization

 

1. ER Diagram

 

Figure 1: ER Diagram of School

Figure 1 은 다음과 같은 가정을 한다: 

- 학생들은 복수 전공이 가능하다

- 휴학을 하고 있는 학생들로 인해 어느 과목에도 수강신청이 되어 있지 않은 학생들이 있다

- Insructors 중에는 대학에서 오로지 연구만 진행하는 경우가 있다

 

Participation constraints도 존재한다: 

- students​ must have ​majors​ and ​majors​ must have ​students
- courses​ must have ​students
- courses​ must have ​instructors
- instructors​ should be included in specific ​department​, and vice versa
- departments​ must provide ​majors​ and vice versa
- college​ must have ​departments​ and vice versa
- course​ must be offered by the ​department
- major​ must have core ​course(​ s)

 

Key constraints는 다음과 같다: 

- Major​ can only be included in one ​department
- department​ can only belong to one ​college
- instructors​ can be working in only one ​department
- course​ can only be offered by one ​department

 

ISA Hierarchy 는 students, instructors, 그리고 person entity 사이에 hierarchy를 형성하기 위해 사용하였다. Person은 logical sub-components인 students과 instructors로 구성되어 있다. Overlap contraints는 허용되지 않는다. 즉, Person은 동시에 instructor이면서 student일 수 없다. Covering constraints 또한 있다; 모든 사람들은 student 와 instructor 둘중에 하나에는 꼭 포함이 되어 있어야 한다. 

 

2. Conceptual Schema: 

Person(ID: integer, FirstName: string, LastName: string)
Student(ID: integer, StudyMode: string, Nationality: string, Age: integer, EnrollmentDate: string) enroll(Grade: string, ID: integer, CID: integer)
Course(CID: integer, Title: string, credit: integer, DID: integer)
instructor(ID: integer, Salary: real, HireDate: string)
Office(officeID: integer, Location: string)
Major(MID: integer, Mname: string, DID: integer)
Department(CoID: integer, DID: integer, Dname: string, Budget: real, StartDate: string) Offer(Semester: string)
College(Coname: string, CoID: integer)
work (ID: integer, DID: integer, Since: string)
HasMajor(ID: integer, MID: integer)
Core(MID: integer, CID: integer)
Offer(CID: string, DID: integer, Semester: string)
Teach(CID: integer, ID: integer)

 

3. SQL

Figure1 을 참조하여 SQL 코드를 작성하면 다음과 같이 나타낼 수 있다: 

 

4. Normalization

데이터 무결성과 데이터 의존성을 확보하고 중복되는 데이터를 없애기 위하여 각 테이블에 Boyce-Codd Normal Form을 적용하였다. Normalization을 통하여 테이블을 더 효율적으로 사용할 수 있다. 

 

Starting with Person entity, we have R(ID, FirstName, LastName), with FD of ID -> (FirstName, LastName). Since {ID}+ ={ID, FirstName, LastName}, it is in BCNF.

 

For Student entity, we have R(ID, EnrollmentDate, Age, nationality, StudyMode), with FD of ID -> (EnrollmentDate, Age, nationality, StudyMode). As {ID}+ = {ID, EnrollmentDate, Age, nationality, StudyMode}, it is in BCNF.

 

For Office entity, we have R(officeID, Location), with FD of OfficeID -> Location. As {OfficeID}+ = {OfficeID, Location}, it is in BCNF.

 

College entity has R(CoID, CoName), with FD of CoID -> CoName. Since {CoID}+ = {CoName, CoID}, it is in BCNF.

 

Department entity has R(DID, CoID, DName, Budget, StartDate). Its FD are the following: DID -> (CoID, DName, Budget, StartDate), DName -> DID. {DID}+ ={DID, CoID, DName, Budget, StartDate} and {DName}+ = {DName, DID, CoID, Budget, StartDate}. Therefore it is in BCNF.

 

Instructor has R(ID, HireDate, Salary, officeID, DID). Its FD are are following: ID-> (HireDate, Salary, officeID, DID) and officeID -> ID. Since {ID}+ = {ID, HireDate, Salary, officeID, DID} and {officeID}+ = {ID, HireDate, Salary, officeID, DID}, it is in BCNF.

Work has R(ID, DID, Since), with FD of ID, DID -> Since and ID -> since. This is not in 2NF. So we decompose R(ID, DID, Since) into R1(ID, DID) and R2(ID, Since). There is no transitive FD so 3NF holds. {ID}+ = {ID, DID}, therefore R1 is in BCNF. {ID}+ = {ID, Since}, therefore R2 is also in BCNF.

 

For Major, it has R(MID, DID, MName), with FD of MID -> (DID, MName) and MName -> MID. Since {MID}+ = {DID, MName, MID} and {MName}+ = {MName, DID, MID}, it is in BCNF.

 

For HasMajor, it has R(ID, MID), it has no non-key attributes. Therefore, it does not have functional dependencies.

 

Course has R(CID, Title, Credit, DID). One course can be divided into different session, therefore can have different CID for the same Title. The FD here will therefore be: CID -> (Title, Credit, DID) and Title -> (credit). By applying Armstrong’s Rules split, we can infer that CID -> Title. We find that the functional dependency is not 3NF give Title -> Credit. Thus, we decompose R(CID, Title, Credit, DID) to R1(Title, Credit) and R2(CID, Title, DID). For R1, as {Title}+ = {Title, Credit}, it is in BCNF. For R2, as {CID}+ ={CID, Title, DID}, it is in BCNF.

 

Enroll has R(ID, CID, Grade). Its FD are ID -> (CID, Grade) and (ID, CID) -> Grade. As it is not in 2NF, we decompose R(ID, CID, Grade) into R1(ID, Grade) and R2(ID, CID). There are no transitive FD. For R1, {ID}+ = {ID, Grade}, and for R2, {ID}+ = {ID, CID}. Therefore, it is in BCNF.

 

Core has R(MID, CID). It has no non-key attributes. Therefore, it does not have functional dependencies.

 

Offer has R(CID, DID, Semester). FD are CID -> DID, (CID, DID) -> Semester and CID -> Sem. As it is not in 2NF, we decompose R(CID, DID, Semester) into R1(CID, Sem) and R2(CID, DID). Closure is {CID}+ = {CID, Sem} for R1, therefore is in BCNF. For R2, {CID}+ = {CID, DID}.

 

Teach has R(CID, ID). It is a fully-keyed relation. Therefore, it does not have functional dependencies.