Student, Age, Alen

The content discusses database normalization, a process used to remove redundancy and improve data integrity by organizing data into smaller tables that can be joined together as needed. The First Normal Form (1NF) removes repeating groups, the Second Normal Form (2NF) removes partial dependencies, the Third Normal Form (3NF) removes transitive dependencies, Boyce-Codd Normal Form (BCNF or 3.5NF) adds requirements for each determinant to be a candidate key, and Fourth Normal Form is less frequently used. The normalization process can be represented through flowcharts and diagrams. Additionally, issues with the degree of normalization chosen for design are discussed, including joins being expensive, normalized designs being difficult to create, increased memory requirements, and expertise required to remove redundancies.

database report
ASSIGNMENT
MAY 21, 2015
STUDENT

CONTENTS
MILESTONE 1.........................................................................................................................2
TASK 1..................................................................................................................................2
NEED FOR DATABASE IN COMMERCIAL ENVIRONMENT.............................2
NEED FOR DATABASE IN NON-COMMERCIAL ENVIRONMENT...................3
TASK 2..................................................................................................................................3
TASK 3..................................................................................................................................4
TASK 4..................................................................................................................................5
ER DIAGRAM.................................................................................................................5
ENTITY TYPES AND KEYS.........................................................................................6
ASSUMPTIONS...............................................................................................................8
TASK 5..................................................................................................................................9
MILESTONE 2.......................................................................................................................14
TASK 6................................................................................................................................14
TASK 7................................................................................................................................16
TASK 8................................................................................................................................17
REFERENCES.......................................................................................................................18

MILESTONE 1
TASK 1
Critically evaluate the features and importance of DBMS in commercial and non-
commercial environment.
Commercial Environment Non- commercial Environment
Banks Government Bodies
NHS-Hospitals Libraries
Educational Sectors Churches and Religious
Organizations
Telecom Sector Home Office
Web Industries Security Industry
DVLA/TV Licenses Charity Organizations
Aviation Industry
NEED FOR DATABASE IN COMMERCIAL ENVIRONMENT
When it comes to commercial environments such as banks, web industries, aviation etc. the
database is used to save historical records, maintain current records, setup up the security of
data by controlling the data access. Above all of them, keeping the data organized in the form
of tables.
Apart from this the data present in the database can be used to build data warehouse
for the commercial industry. Thus will help in mining the data and extracting useful
information for future purposes such as expanding the business. For example aviation can
have its own data warehouse built from its various databases to find growth patterns. In case
of educational sectors, the database can be used to store records of student of past 20-30 years
such as details, roll number etc. for future references, rather than keeping them on paper.
In this way we can say that databases are of high importance nowadays. The database used
are licensed ones and have large no. of features of data handling. Advantages they provide
are:
1. Data security
2. Data integrity: by keeping only valid changes
3. Data organization

4. Controlled access of the data via permissions and much more.
NEED FOR DATABASE IN NON-COMMERCIAL ENVIRONMENT
When it comes to non-commercial databases, the use is simple rather than being complex. It
is used for simple storage and retrieval of data. In non-commercial organizations such as
Libraries, Churches and Religious Organizations, Home Office etc. just need to store data,
manipulate it, calculate totals, retrieve data or generate reports if needed. For example library
will have data about the books, their publisher, who issues it and when etc.
All these data are not of high importance and thus the database used with them is a non-
commercial which is available free of cost. The data records may be just a few hundreds as
compared to commercial databases which have records in millions and need high security
which is required less in the non-commercial one. The data is not very critical and of not so
high importance. Thus database act as a basic component for storing data, daily small
transactions etc., rather than keeping records on paper.
TASK 2
The key issues are:
1. INTEGRITY: it refers to the accuracy of the data. In case of databases, to achieve
this accuracy, certain constraints are used called the database integrity constraints. For
example, setting the field as “not null” will ensure that the table’s field doesn’t accept
null values. These constraints helps us to design the database as per the logical design
decided and in the sequential manner. In other words we can say that “designing
complete database integrity is designing a complete database integrity constraint”.
Some of the integrity constraints that help in storing accurate values are: [4]
a. Unique
b. Not null
c. Referential : primary key, foreign key
The database integrity helps in the following ways:
a. Preventing illegal users from adding non-semantic data
b. Handling the transactions by achieving the transaction rules.
c. Reducing the complexity by use of primary key and foreign key.
d. Helps in finding errors and improves the testing software/database testing results.

2. SECURITY: In databases, security is one of the most important factors. Use of
database is done to organize the data and then applying restricted access to it so that it
can be protected from varied illegal sources. This is done by setting up the user roles
and read, write, and execute access permissions on the database for enabling only
authorized data modifications.
3. CONSISTENCY: it is one of the acid properties that ensures that the changes made
by the user to any instance of the database are right and consistent with reference to
the other values present in the database related to it. This property is used to setup the
pre, post and current conditions in a consistent manner. For example if the user
deposits money in its account, this update remain consistent and changes are made to
user account and bank’s amount as well. In simpler words we can say that either all
changes occur or none. The database is not left in an inconsistent state. [5]
4. RECOVERY: this property enables to create recovery options for the situations
when the data can be lost or changes may not be permanent. For this purpose, redo
and write ahead logs are created so that the changes made after transaction or the
values before transaction can be recovered, i.e., rolling back of the entire transaction.
5. CONCURRENCY: Concurrency is a mechanism in which we control the transaction
mechanisms. When to transactions occur at the same time and access the same
resource, then concurrency control is applied over them so as to control the deadlock
or avoid inconsistent changes. Using the database mechanism. We assure that on a
table or records, only one user commits the changes at once. This helps in maintaining
the integrity and consistency of the data present. This is achieved by locking the data
being used by a transaction, so that no other transaction interferes with it.
6. APPLICATION OF DATABASE WITHIN ORGANIZATION: the database is
used to store and manage information within the organization. It is an appropriate way
to manage large amount of data in an organized way rather than keeping them
traditionally on papers. Nowadays, database acts a backbone for the application
software.
TASK 3
When designing a software, a database is usually considered as a fundamental component of
the information system, especially for the commercial systems. Thus we can say that

designing the database is also a part of the overall software development lifecycle. [6]
The diagram below shows that how the database design is a part of the software development
lifecycle.
The phases of Database Design and Database Implementation present in the in the middle of
the picture are the phases that are used for development of the database. The other phases are
briefly described. Firstly all the needs are analysed for the databases and all the requirements
are documented before the actual design begins. Next on the basis of the requirements, the
logical diagrams are created so that the tables can be designed accordingly, reaching the final
implementation then. The phases are described below:

1. Database Planning
In this phase, we realize the information in a very effective and efficient way. The needs to be
produced in the database are gathered and documented here. This phase is integrated with the
other Information System strategy phases.
In the very first step of database planning, the objective and the problem are defined.
The definition contains:
1. Requirements of the system
2. Purpose of the system
3. Old resources for support
4. Tasks needed to be carried out.
2. Systems Definition
This phase is used to describe the scope of the system. This includes:
1. What the new system will do
2. Who will be the users
3. Who are the present users
4. What are the present functions of the system
5. How the database will be used with other parts of the system.
6. What areas are to be included in the database.
3. Requirements Collection and Analysis
Here the information about what the user wants, is collected. Then the entire present and
future needs are analysed to create the perfectly documented requirements, in order to create
appropriate tables, attributes, relationships, key constraints etc. for example details and the
description of the data to be constructed or the data that is currently present.
4. Database Design
This phase consists of the actual designing of the database. The steps followed in this phase
are:
1. Conceptual design in which the data model is developed on the basis of the system
requirements.
2. Logical design which contains the logical table structure say relational structure is
constructed, independent of the physical layer.

3. Physical design in which the actual database is constructed or implemented on the
physical memory for use. The constraints, relations, keys, fields, security, indexes,
records etc. are created by the use of SQL language.
5. Database Management System Selection
This phase helps in deciding the database software to be used to implement the system,
depending upon the budget of the user. Although this is an optional phase. When a new
system is to be implemented, this phase is carried out. DBMS that can be used are MS -
Access, SQL Server, MySQL, Oracle, etc. Many recommendations are made to the user and a
final decision is reached where the right product is selected for implementation.
6. Application Design
This phase includes the designing of the application user interface and the other modules
which will use the database.
7. Prototyping
In this phase a model of the actual database is created to present a picture of the actual
models and add new functionalities or remove redundancies or flaws. The diagram below
shows the vertical and horizontal prototypes and their features.

8. Implementation
In this phase, the database and application designs are physical realized. We can call it the
programming phase.
9. Data Conversion and Loading
This phase is needed when a new database is replacing an old system. During this phase the
existing data will be transferred into the new database.
10. Testing
This phase consists of the testing of the database. Here the entire database is tested for
integrity, security, consistency, concurrency, load etc. the main goal is to find and remove the
errors or flaws.
11. Operational Maintenance
The operational maintenance is a phase where the database is monitoring regularly and the
errors or bugs are fixed and maintaining post installation of the database system.
Monitoring is a process where performance of the system is seen and improved on regular
basis for example say, tuning the database, or improving the response time. And Maintaining
and upgrading of the database system requires that the system is regularly modified and
updated as the needs arise in the future.

TASK 4
ER DIAGRAM

ENTITY TYPES AND KEYS
ENTITY ATTRIBUTES TYPES KEYS
COLLEGE INFO VARCHAR2(50)
ESTB_YEAR DATE(DD/MM/YYYY)
ADDRESS VARCHAR2(30)
DEPARTMENT DEPT_ID INTEGER PRIMARY KEY
DEPNAME VARCHAR2(50)
NOOFPEOPLE INTEGER
DEGREES DEGREE_ID INTEGER PRIMARY KEY
DEGREE_TYPE VARCHAR2(20)
DURATION NUMBER(5) (IN YEARS)
DEGNAME VARCHAR2(20)
DEPT_DEGREE DEPTID INTEGER FOREIGN KEY
DEPARTMENT(DEPT_ID)
DEGREEID INTEGER FOREIGN KEY DEGREES
(DEGREE_ID)
DEGREEFEE DEGID INTEGER PRIMARY KEY
STUDENTTYPE1 VARCHAR2(30)
FEE1 NUMBER(10,2)
STUDENTTYPE2 VARCHAR2(30)
FEE2 NUMBER(10,2)
FEEPLAN PLANID INTEGER PRIMARY KEY
DURATION NUMBER(5) (IN YEARS)
INSTALMENT_AMT NUMBER(10,2)
DISCOUNT NUMBER (10, 2) (IN %)
STUDENT STUDENTID INTEGER PRIMARY KEY

STUDENTTYPE VARCHAR2(20)
NAME VARCHAR2(30)
ADDRESS VARCHAR2(50)
PHONE NUMBER(10)
COUNTRY VARCHAR2(20)
STUDENTDEGRE
E
STUDENTID INTEGER PRIMARY KEY
DEGREEID INTEGER FOREIGN KEY
DEPT_DEGREE(DEGREEID)
FEEPLANID INTEGER FOREIGN KEY FEEPLAN
(PLANID)
COURSEID VARCHAR2(10) FOREIGN KEY
COURSE(COURSEID)
COURSE COURSEID VARCHAR2(10) PRIMARY KEY
NAME VARCHAR2(30)
SUB1 VARCHAR2(20)
SUB2 VARCHAR2(20)
SUB3 VARCHAR2(20)
PAYMENT PAYMENTID INTEGER PRIMARY KEY
STUDENTID INTEGER FOREIGN KEY
STUDENT(STUDENTID)
AMT NUMBER (10,2)
DATE DATE(DD/MM/YYYY)
LATE_PAYMENT NUMBER (10,2)
MARKS STUDENTID INTEGER FOREIGN KEY
STUDENT(STUDENTID)
ASSESSMENTID INTEGER FOREIGN KEY
ASSESSMENT(ASSESSMENTI

D)
MARKS NUMBER (10,2)
ASSESSMENT ASSESSMENTID INTEGER PRIMARY KEY
DATE DATE(DD/MM/YYYY)
TYPE VARCHAR2(30)
OTHERS VARCHAR2(30)
REGISTER DEPTID INTEGER PRIMARY KEY
STUDENTID INTEGER FOREIGN KEY
STUDENT(STUDENTID)
ATTENDANCE CHAR(10)
DATE DATE(DD/MM/YYYY)
TIME DATETIME
CLASS CHAR(50)
ASSUMPTIONS
The assumptions made in the data model are:
1. College has many department, that is, one or more.
2. Department provides one or more degrees.
3. Each degree has two types of fee, that is, one for local students and the other for
outside ones.
4. Student can opt for a fee plan, one is full payment where some discount is provided
and the other is instalment based.
5. Student has to select a degree, then a course in it and a fee plan for the payment.
6. Course has multiple subjects and is taken up by one or more students.
7. Each student has to give one or more assessment for which is provided with marks.
8. Assessment details are also recorded. An assessment has one or more marks related to
it.
9. A register is created for each department.
10. The attendance is maintained for students for all dates and respective times.
11. Each student has to make a payment or more.

TASK 5
Data Normalization is one of the popular processes that come across with the database. It is a
process in which two tasks are accomplished one is process in which all the redundancy in
the database is removed (for instance if same set of data is stored in more than one table then
this will be removed) and the another one is the one in which it ensures that all the data
dependencies make sense. Both of these tasks of the normalization are done in order to ensure
that amount of space that a database is using is reduced to the appropriate level and it also
ensures that the entire range of the data is logically stored in the database.
The Normal Forms:
The database administrators, experts and the entire community have developed a set of
procedures and the guidelines for assisting the administrators in the normalization process.
And these set of the guidelines are known as the normal forms and these normal forms are
known as 1NF, 2NF and the 3NF and along with these three one occasional form is also there
and this one is known 4NF. Further, an additional form 5NF is not used much frequently.
It is very important to understand that these normalized forms are only the guidelines.
First Normal Form (1NF):
This form provides the very basic guidelines for organizing the database. Moreover, the
guidelines for this form are:
 First guideline is that all the duplicate columns in the database are needed to be
removed. [3]
 Second guideline is that separate tables are needed to be created for each separate
group for the related set of the data that is present in the database. Moreover, the
identification of the rows is done with the help of the unique set of the columns, and
this unique column should be known as the primary key. [3]
Example of First Normal Form (1NF):
Consider the below table that is needed to be normalized:
Studen Age Subject

t
Alen 14
History ,
Geography
John 13 Math
Maria 14 Math
After the normalization for each subject a separate row will be inserted:
Studen
t Age Subject
Alen 14 History
Alen 14 Geography
John 13 Math
Maria 14 Math
Second Normal Form (2NF):
In this form, the guidelines for the removal of the duplicate data are given. Moreover, the
guidelines for this normalized form are:
 First guideline is to ensure that all the requirements of the first normalized form are
met.
 Second guideline is to remove all the subsets of the data, and this is needed to be done
for the subsets that are applicable to the multiple rows. Moreover, finally, the data is
needed to be placed in a separate form. [3]
 The last guideline is that with the help of the foreign keys a relationship between the
predecessors and the new tables are created.
Example of Second Normal Form (2NF):
Now For the second normalized form the table will be split into independent table as given
below:
Student Age

Alen 14
John 13
Maria 14
Thereafter the student column will be selected as the candidate key and the other columns
will depend on this one as given below:
Student Subject
Alen History
Alen Geography
John Math
Alen Math
Third Normal Form (3NF):
This is the next step that comes after the first two set of guidelines. In this form, below a set
of procedure is used:
 First guideline is to meet all the requirements of the second normal form.
 Then the second guideline is to remove all the set of the unnecessary columns that are
directly based on the use of the primary key.
Example of Third Normal Form (3NF):
Consider the below table to understand the third normal form:
Stude
nt ID
Na
me
Do
b
Cit
y
Stat
e
Zi
p
Now in this table the name and DOB are dependent on the student ID and the city and the
state are dependent on the zip:
So, this will be divided in the two tables in the following way:
Stud
ent
Na
me
D
ob

ID
Zip
Cit
y
Stat
e
Boyce-Codd Normal Form (BCNF or 3.5NF):
This form is also known as the three, and half form of the normalization and this adds the
below requirements:
 The first guideline is that all the requirements for the third form are needed to be met.
 The second guideline is that each determinant in the database can work as a candidate
key.
Fourth Normal Form:
This form is not used much and has present the below guidelines that are needed to be
fulfilled:
 First guideline is that all the requirements of the third normal form are needed to be
met in order to proceed with the guideline of the fourth normal form. [3]
 The second guideline is that if the columns of the database do not have the multi-level
dependencies then in that case it is important to make it in relation with the 4NF. [3]
Normalization process and flow chart:
The normalization flowchart representation with the help of the diagram is given below:

The fifth phase is shown in the flow chart, but this not frequently used in the process.
The normalization process representation with the help of the diagram is given below:
Issues around the degree of normalisation chosen for the design:
1. Joins are expensive: Normalization is not an easy process and requires the creation of
the large number of the tables. In fact, the redundancy removal in some cases
becomes very easy with the help of the queries rather than using with the table. In
some cases doing so with the help of tables, is a painstaking process. So, in all those

cases where the simple queries can solve the purpose then one should not use the
normalization as it will complicate the entire process. [2]
2. Normalized design is difficult: While dealing with the complex designs of the
database the use of the normalization is considered as one of the most difficult tasks
since the use of the normalization in the complex designs will further add to the
complexity. For, instance if while doing the fourth normalization you do not
understand what is needed to be done and how is this needed to be accomplished then
in that case normalization is definitely not a good idea. [2]
3. The tables produced by the normalization are more in number than those in
denormalized form. Thus, more memory requirements increase the overall cost.
4. And at the last, it requires expertise to remove redundancies and set the normalized
tables by setting up primary key-foreign key relationships.

MILESTONE 2
TASK 6
THE SAMPLE SCREENS ARE:

TASK 7
FORMS

SAMPLE REPORTS

TASK 8
TEST
CASE
ID
TEST CASE
DESCRIPTION
INPUT EXPECTED
OUTPUT
RESULT
OUTPUT
TEST RESULT
(PASSED/
FALIED)
1 enter alphabet in
studentid /
degreeid/paymentid /
planid
abc Enter integer
value
2 enter alphabet in
student contact
abcdeelkm Enter integer
value
3 Enter alphanumeric in
date in any form
09-bn-19bn Enter date in correct
format
4 Enter student id which
doesn’t exist in
payment module
78 Enter the value
present in student
table
(integrity violation)
5 Enter alphabet for
amount in payment
table
aaaa Enter numeric value
6 Enter null value for
studentid /
degreeid/payment id /
planid/ dept_id/ course
id
Cannot be left blank
(integrity violation)
7 Enter repeated value
for studentid /
degreeid/payment id /
planid/ dept_id/ course
id
Cannot enter
repeated values
(integrity violation)

REFERENCES
[1] P. Bosc, D. Dubois and H. Prade. Fuzzy functional dependencies and redundancy
elimination. Journal of the American Society for Information Science (1986-1998) 49(3), pp.
217. 1998.
[Accessed 20 May 2015]
[2] T. (TJ) Wang J. and D. K. Dennis. Normalizing database normalization definitions in
AIS textbooks. The Review of Business Information Systems 15(1), pp. 41-51. 2011.
[Accessed 21 May 2015]
[3] T. A. Turk. Using data normalization techniques for effective database design. The
Journal of Information Systems Management 2(1), pp. 36. 1985.
[Accessed 21 May 2015]
[4]
Anonymous, 2010. DATABASE PROPERTIES. [Online]
Available at: http://lightwolftech.com/index.php?page=backgrounders
[Accessed 21 May 2015].
[5] Anonymous, 2015. DATABASE INTEGRITY. [Online]
Available at: http://www.databasecompare.com/What-is-Database-Integrity.html
[Accessed 20 May 2015].
[6] Liisa Auer, T. T., 2006. Database development lifecycle. [Online]
Available at:
http://www2.amk.fi/digma.fi/www.amk.fi/opintojaksot/0303011/1142845462205/114284777
4995/1142849037295/1143037341377.html
[Accessed 20 May 2015].