Advanced Database

Normalisation

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.

UNF

Un-Normalised Form
Booking	(booking_ref_no, booking_date, student_no, student_name, student_address, student_telephone, course_code, course_name, course_level, start_date, finish_date, course_cost, lecturer_code, lecturer_name, lecturer_telephone, centre_name, centre_phone, class_size)

 

1NF

First Normal Form
Student 1	(student_no, student_name, student_address, student_telephone, course_code, course_name, course_level, start_date, finish_date, course_cost, lecturer_code, lecturer_name, lecturer_telephone, centre_name, centre_phone, class_size)
Booking 1	(booking_ref_no, booking_date, student_no)

2NF

Second Normal Form
Booking 2	(booking_ref_no, booking_date, student_no)
Student 2	(student_no, student_name, student_address, student_telephone, course_code)
Course 2	(course_code, course_name, course_level, start_date, finish_date, course_cost, centre_name, centre_phone, class_size, lecturer_code)
Lecturer 2	(lecturer_code, lecturer_name, lecturer_telephone)

3NF

Third Normal Form
Booking 3	(booking_ref_no, booking_date, student_no)
Student 3	(student_no, student_name, student_address, student_telephone, course_code)
Course 3	(course_code, course_name, course_level, start_date, finish_date, course_cost, centre_code)
Lecturer 3	(lecturer_code, lecturer_name, lecturer_telephone)
Centre 3	(centre_code, centre_name, centre_phone)
Class 3	(centre_code, class_size)

E-R Model

Data Dictionary

Generation of Database  
 DDL generated by the server model:  
 -- E:\anitsql\anit2.tab  
 ---- Generated for Oracle 9i on Mon May 13 11:24:30 2013 by Server Generator 10.1.2.6.18  
  PROMPT Creating Table 'COURSES'  
 CREATE TABLE COURSES  
  (COURSE_LEVEL VARCHAR2(15)  
  ,FINISH_DATE VARCHAR2(240)  
  ,START_DATE VARCHAR2(240)  
  ,COURSE_NAME VARCHAR2(25)  
  ,COURSE_CODE INTEGER NOT NULL  
  ,COURSE_COST INTEGER  
  )  
 /  
 PROMPT Creating Table 'BOOKINGS_COURSES'  
 CREATE TABLE BOOKINGS_COURSES  
  (COU_COURSE_CODE INTEGER NOT NULL  
  ,BOO_BOOKING_REF_NO INTEGER NOT NULL  
  )  
 /  
 PROMPT Creating Table 'LECTURERS'  
 CREATE TABLE LECTURERS  
  (LECTURE_TELEPHONE VARCHAR2(10)  
  ,LECTURE_NAME VARCHAR2(25)  
  ,LECTURE_CODE INTEGER NOT NULL  
  )  
 /  
 PROMPT Creating Table 'COURSES_LECTURERS'  
 CREATE TABLE COURSES_LECTURERS  
  (LECT_LECTURE_CODE INTEGER NOT NULL  
  ,COU_COURSE_CODE INTEGER NOT NULL  
  )  
 /  
 PROMPT Creating Table 'COURSES_STUDENTS'  
 CREATE TABLE COURSES_STUDENTS  
  (STUD_STUDENT_NO INTEGER NOT NULL  
  ,COU_COURSE_CODE INTEGER NOT NULL  
  )  
 /  
 PROMPT Creating Table 'CENTERS'  
 CREATE TABLE CENTERS  
  (CENTER_NAME VARCHAR2(25)  
  ,CENTER_CODE INTEGER NOT NULL  
  ,CENTER_TELEPHONE VARCHAR2(10)  
  )  
 /  
 PROMPT Creating Table 'BOOKINGS'  
 CREATE TABLE BOOKINGS  
  (BOOKING_REF_NO INTEGER NOT NULL  
  ,BOOKING_DATE VARCHAR2(240)  
  ,STUD_STUDENT_NO INTEGER  
  )  
 /  
 PROMPT Creating Table 'STUDENTS'  
 CREATE TABLE STUDENTS  
  (STUDENT_NAME VARCHAR2(25)  
  ,STUDENT_TELEPHONE VARCHAR2(10)  
  ,STUDENT_NO INTEGER NOT NULL  
  ,STUDENT_ADDRESS VARCHAR2(30)  
  )  
 /  
 PROMPT Creating Table 'CENTERS_COURSES'  
 CREATE TABLE CENTERS_COURSES  
  (COU_COURSE_CODE INTEGER NOT NULL  
  ,CEN_CENTER_CODE INTEGER NOT NULL  
  )  
 /  
 PROMPT Creating Table 'LECTURERS_STUDENTS'  
 CREATE TABLE LECTURERS_STUDENTS  
  (STUD_STUDENT_NO INTEGER NOT NULL  
  ,LECT_LECTURE_CODE INTEGER NOT NULL  
  )  
 /  
 PROMPT Creating Table 'CLASSES'  
 CREATE TABLE CLASSES  
  (CLASS_SIZE INTEGER  
  ,CENTER_CODE INTEGER NOT NULL  
  ,CEN_CENTER_CODE INTEGER NOT NULL  
  )  

Data insert using SQLPlus:

Data Flow Diagram

Functional Hierarchy Diagram

Process Modeller Diagram

Further Discussion

From the last 30 years. There are a number of the newer techniques available in our experience though even the technology has changed dramatically. While developing requirements for an interactive system, we initially need to discover and report: the overall process-flow, the activities being performed, the inputs & outputs to the activities, the event that trigger the activities, who performed the activities, where the activities are performed, where information is stored, who supplies the information & who needs to receive the information. Likewise we need to assess all these things and specify them in a set of new business process requirements.

Data Flow Approach

Using the data flow approach has a lot of advantage, especially for data driven applications where processing requirements are beyond the computational power. Data flow diagrams are the one of the main methods available for analysing data-oriented systems. Through the uses of data flow emphasizes the logic underlying the systems; the systems analysts can put together a graphical representation of data movement through the organization. For the LONDON METROPOLITAN COLLEGE “Student Management System” data flow diagram helps to maintain the analysis proposed system to determine if all the data and processes have been defined. By the incorporation of different levels the DFD can provide the view of the whole London metropolitan university overall activities relating the students management. Also this data flow approach help to find the boundary of the college administrative tasks. Finally it also helps to generate the information from the view point of data movements, which includes the inputs and outputs to which students can readily relate. The main advantages are

·         It is the part of the system documentation file

·         Straightforward graphical technique which is easy to recognise

·         It supports the logic behind the data flow within the system.

The disadvantages are

·         Physical considerations are left out

·         Make programmer little confusing concerning the system.

·         It takes long time to create, so long that the analyst may not receive support from management to complete it.

Process Diagram Approach

The process diagram provides a visual representation of the steps in a process. Flow charts are referred to as process mapping or flow diagrams. There are various benefits of using process diagram such as

·         Make process flowcharts

·         Give everyone a clear understanding

·         help to identify non value added operations

Read More