Data Security and Management: University Database Report

This report provides a comprehensive analysis of data and knowledge management principles applied to a university database system. The report begins by exploring the importance of data security and management within a relational database management system (RDBMS), emphasizing data consistency, integrity, and quality achieved through constraints and effective database design processes. It then delves into the creation of an Entity Relationship Diagram (ERD) to model the university's entities and their relationships. Normalization techniques, including First, Second, and Third Normal Forms, are employed to simplify relations, reduce redundancy, and maintain data integrity. The report presents the database schema and data dictionary, detailing table structures, attributes, data types, and constraints. An evaluation and implementation of the relational database are discussed, including the creation of tables using SQL Data Definition Language (DDL) statements, the application of key constraints to establish relationships, and the population of tables with sample data using Data Manipulation Language (DML) statements (INSERT). Various SQL queries, employing GROUP BY, INNER JOIN, RIGHT JOIN, and LEFT JOIN, are developed to retrieve and analyze data. Finally, the rationale for using the relational model, particularly its consistency and integrity, is presented, highlighting the star schema structure of the developed database.

Running head: DATA & KNOWLEDGE MANAGEMENT
DATA & KNOWLEDGE MANAGEMENT
Name of the Student
Name of the University
Author Note

1DATA & KNOWLEDGE MANAGEMENT
Table of Contents
An analysis of the data security and data management...................................................................2
ERD.................................................................................................................................................2
Normalization..................................................................................................................................4
First Order Normalization............................................................................................................4
Second Order Normalization.......................................................................................................4
Third Order Normalization..........................................................................................................5
Database Schema.............................................................................................................................5
Data Dictionary................................................................................................................................6
An evaluation & Implementation of the relational database...........................................................8
Creation........................................................................................................................................9
Constraints.................................................................................................................................11
Population..................................................................................................................................11
Queries.......................................................................................................................................13
Rationale for the use of the relational model.................................................................................15
References......................................................................................................................................16

2DATA & KNOWLEDGE MANAGEMENT
An analysis of the data security and data management
Data security and management, both are the main aspects of using relational database
management system (RDBMS). The data security can be achieved by maintain the data
consistency, integrity and quality (Liu et al. 2018). The data quality can be achieved by using
constraints in a database along with the integrity and consistency. On other hand, the
management of the data is also achieved during the design process of the database design. For
data security and management several designing processes are utilized, such as Entity
relationship diagram modelling, normalization, and data dictionary which stores the Meta data of
the database (Jukic, Vrbsky and Nestorov 2014).
ERD
In a Relational Database Management System, the real world objects are identified as
Entities. These Entity sets stored in a database keeps the information. Attributes are used for
keeping the information for an Entities (Rossi 2014). Before implementing the physical database
system, system analysis and design is done by developing Entity relationship Diagram (ERD).
The identification of the Entities and their attributes are done on the basis of the business process
of a company (Weske 2019). The ERD collects these entities and represent the attributes and
relations between the entities. Here the ERD of university is provided in figure 1.

3DATA & KNOWLEDGE MANAGEMENT
Figure 1: Entity Relationship Diagram of University
Source: created by author

4DATA & KNOWLEDGE MANAGEMENT
Normalization
To design the database schema from the conceptual diagram, Normalization is a method
used for the simplification of the relations and entities. In this technique tables are organized in
way that it should reduce redundancy, manage integrity and consistency of the data (Al-Absi and
Kang 2014). The dependencies between the data is also reduced during this process. The process
actually divides larger tables into smaller tables and links them using relations. The process of
normalizing the university database involves three steps named First Order Normalization,
Second Order Normalization and Third Order Normalization.
First Order Normalization
According to the first order normalization, the tables should only have atomic values. It
means the values stored by the attribute should only contain single values. The developed ERD
has the attributes that only stores single values (Cellary, Morzy and Gelenbe 2014). The proposal
details were first holding three values intro, background and risk. These three have been
reorganized as single attribute in the researcher table named, proposalIntro, proposalBackground
and proposalrisk. Rest of the relations are in first normal form.
Second Order Normalization
According to second form of normalization, the relations should be in first order
normalized and all the non key attributes of a relation should be depend on the key attribute of
the table. The key attributes of the table are defined as the primary keys of the table. In the
developed ERD, all the non-key attributes depends on the primary key of the table (di Vimercati
et al. 2014). For example the student name, city, country, contact etc. can be identified by
student id. Hence it can be said that the relations are in Second Normal form.

5DATA & KNOWLEDGE MANAGEMENT
Third Order Normalization
According to the third normal form, the relations should be in second normal form first
and there should not be any transitive functional dependencies between the attributes (Varga
2019). To eliminate the transitive functional dependencies between the course and student as
well as researchers and grants, a new table is construct named application. It stores the all other
tables primary keys as foreign keys. Hence, it can be said that the relation is in Third Normal
form.
Database Schema
Database schema represents the physical structure of the database. It consisting of tables,
attributes, their data type and data sizes (Cleve et al. 2015). The database schema for the
University database is given below in figure 2.

6DATA & KNOWLEDGE MANAGEMENT
Figure 2: Database Schema
Source: created by author
Data Dictionary
Data dictionary of a Relational database management system contains the metadata of the
database. It contains the information about the objects used in the database such as table names,
constraints in tables, columns, owner of the table, data types and data sizes of the columns
(Kramer and Thalheim 2014). The data dictionary developed for the university database is given
below:
Grants
Attribute Data Type Size key Referred to

7DATA & KNOWLEDGE MANAGEMENT
GrantId varchar 6 Primary
GrantTitle varchar 30
AvailableFund Decimal 8,2
Duration varchar 20
Course
Attribute Data Type Size key Referred to
Coursecode varchar 6 Primary
CourseTitle varchar 20
HostUniversity varchar 50
Duration varchar 20
type varchar 20
Student
Attribute Data Type Size key Referred to
StudentID varchar 5 Primary
Name varchar 30
DOB date
HomeSchool varchar 50
City varchar 30
Country varchar 30

8DATA & KNOWLEDGE MANAGEMENT
Contact int
CourseStudied varchar 50
Grades varchar 15
RecommendationLetter varchar 3
InvitationFromtheHost varchar 3
Researcher
Attribute Data Type Size key Referred to
ResearcherID varchar 5
Name varchar 30
DOB date
HomeUniversity varchar 50
City varchar 30
Country varchar 30
Contact int
RequiredFund decemal 8,2
ProposalIntro varchar 100
ProposalBackground varchar 150
ProposalRisk varchar 150
Application
Attribute Data Type Size key Referred to

9DATA & KNOWLEDGE MANAGEMENT
ApplicationID varchar 5 Primary
ApplicationDate date
StudentID varchar 5 Foreign Student
CourseCode varchar 6 Foreign Course
ResearcherID varchar 5 Foreign Researcher
GrantID varchar 6 Foreign Grants
An evaluation & Implementation of the relational database
The evaluation of the database starts with the creation of the database with the help of the
developed Entity relationship diagram. The tables are structured and created using the Data
definition Language (Elmasri 2018). All the key constraints are implemented wherever it
required. The key constraints helps in establishing relationship between the table and makes the
data identifiable.
Creation
Below the creation of the database tables are explained using SQL (DDL statement) codes.
Application Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Application](
[ApplicationID] [varchar](5) NOT NULL,
[ApplicationDate] [date] NOT NULL,
[StudentID] [varchar](5) NULL,
[CourseCode] [varchar](6) NULL,
[ResearcherID] [varchar](5) NULL,
[GrantID] [varchar](6) NULL,
PRIMARY KEY CLUSTERED

10DATA & KNOWLEDGE MANAGEMENT
(
[ApplicationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Grants Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Grants](
[GrantID] [varchar](6) NOT NULL,
[GrantTitle] [varchar](30) NULL,
[AvailableFund] [decimal](8, 2) NOT NULL,
[Duration] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[GrantID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Course Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[course](
[CourseCode] [varchar](6) NOT NULL,
[CourseTitle] [varchar](20) NOT NULL,
[HostUniversity] [varchar](50) NOT NULL,
[Duration] [varchar](20) NOT NULL,
[Type] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[CourseCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

11DATA & KNOWLEDGE MANAGEMENT
Student Tale
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[student](
[StudentID] [varchar](5) NOT NULL,
[Name] [varchar](30) NOT NULL,
[DOB] [date] NOT NULL,
[HomeSchool] [varchar](50) NOT NULL,
[City] [varchar](30) NOT NULL,
[Country] [varchar](30) NOT NULL,
[Contact] [int] NOT NULL,
[CourseStudied] [varchar](50) NOT NULL,
[Grades] [varchar](15) NOT NULL,
[RecommendationLetter] [varchar](3) NOT NULL,
[InvitationFromTheHost] [varchar](3) NOT NULL,
PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Researcher Table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Researcher](
[ResearcherID] [varchar](5) NOT NULL,
[Name] [varchar](30) NOT NULL,
[DOB] [date] NOT NULL,
[HomeUniversity] [varchar](50) NOT NULL,
[City] [varchar](30) NOT NULL,
[Country] [varchar](30) NOT NULL,
[Contact] [int] NOT NULL,
[RequiredFund] [decimal](8, 2) NOT NULL,
[ProposalIntro] [varchar](100) NOT NULL,
[ProposalBackground] [varchar](150) NOT NULL,
[ProposalRisk] [varchar](150) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ResearcherID] ASC

12DATA & KNOWLEDGE MANAGEMENT
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Constraints
ALTER TABLE Application
ADD constraint fk_rs FOREIGN KEY (ResearcherID) REFERENCES
Researcher(ResearcherID);
ALTER TABLE Application
ADD constraint fk_st FOREIGN KEY (StudentID) REFERENCES
Student(StudentID);
ALTER TABLE Application
ADD constraint fk_co FOREIGN KEY (CourseCode) REFERENCES
course(CourseCode);
ALTER TABLE Application
ADD constraint fk_gr FOREIGN KEY (GrantID) REFERENCES Grants(GrantID);
Population
On other hand, after creating the tables, the sample data has been inserted inside the
tables with the help of INSERT statement which is a data manipulation language (DDL). It
inserts the data as per the structure of the table.
Application Table
insert into application values ('A101','2020-01-
12','S101','CO1022',null, null);
insert into application values ('A102','2020-01-
11','S102','D02221',null, null);
insert into application values ('A103','2020-01-
7','S103','D02221',null, null);
insert into application values ('A104','2020-01-
05','S104','CO1022',null, null);
insert into application values ('A105','2020-01-
11','S105','RS0011',null, null);
insert into application values ('A106','2020-01-11',null,
null,'R101','G103');
insert into application values ('A107','2020-01-15',null,
null,'R102','G101');

13DATA & KNOWLEDGE MANAGEMENT
insert into application values ('A108','2020-02-11',null,
null,'R103','G102');
insert into application values ('A109','2020-02-29',null,
null,'R104','G101');
Grants Table
insert into grants values('G101','Short term',20000,'6 months');
insert into grants values('G102','ESRC',250000,'24 months');
insert into grants values('G103','Welsh Government',150000,'12
months');
Course Table
insert into course values ('CO1022','Software Engineering','Cardiff
Met University','36 months','BSc');
insert into course values ('D02221','Data Science','MIT','36
months','MSc');
insert into course values ('RS0011','Bioinformatics','MIT','10
months','PhD');
Student Tale
insert into student values ('S101','Paul Mark','1995-09-16','St.
Pauls','London','UK',568457445,'Secondary Education','A+','No','Yes');
insert into student values ('S102','Joana Sekh','1995-06-21','St.
Pauls','Birminghum','UK',54854795,'Higher Education','A+','No','Yes');
insert into student values ('S103','James Beth','1995-03-18','St.
Pauls','London','UK',25365478,'Higher Education','B','No','No');
insert into student values ('S104','Susane Crl','1996-03-12','St.
Pauls','Tentbridge','UK',546589658,'Secondary
Education','B','Yes','No');
insert into student values ('S105','Penny Sebastian','1992-05-25','St.
Pauls','Tentbridge','UK',5142532,'Higher Education','C','No','No');
Researcher Table
insert into Researcher values ('R101','Karl Max','1992-09-16','London
University','London','UK',568457445,150000,

14DATA & KNOWLEDGE MANAGEMENT
'Digital Marketing Proposal','This proposal is effective because it
provides a clear, specific solution to the client’s problems',
'NA');
insert into Researcher values ('R102','Ketty Ann','1991-09-
16','University of Cambridge','Cambridge','UK',568457445,20000,
'Financial Services Proposal','','NA');
insert into Researcher values ('R103','Joseph Tribbiani','1993-09-
16','University of Cambridge','Cambridge','UK',568457445,250000,
'Web Design Proposal','Web Services','NA');
insert into Researcher values ('R104','Li Cahn','1990-09-16','London
University','London','UK',568457445,20000,
'Engineering Services Proposal','Engineering','NA');
Queries
Queries are also done with the help of the Data manipulation language. The developed
queries for the university database are based on group by, inner join, right join and left join of the
tables to represent relationship between them.
i. Group By: The query displays the number of student available in each of the courses
provided by the university.
select c.coursetitle, count(a.studentid) as TotalStudent from
application a, course c
where c.CourseCode=a.coursecode group by c.coursetitle;
ii. Inner Join: The query results the researchers’ names, their city, country, contact and
applied grants and their duration using inner join between the researchers, grants and
application table.
select r.name, r.city, r.country, r.contact, g.granttitle,

15DATA & KNOWLEDGE MANAGEMENT
g.duration from Researcher r
inner join application a on a.researcherid=r.ResearcherID inner
join grants g on
g.grantid=a.grantid;
iii. Right Join: The query results the students name, their contact and course code by left
joing the student table on application table.
select s.name, s.contact, a.coursecode from student s left join
application a on a.studentid=s.StudentID;
iv. Left Join: The query results the name of the researchers, their contact, grand id, grant
title and fund of the grant using right join between the grants, application and researcher
table.
select r.name, r.contact, a.grantid, g.granttitle,
g.availablefund from
application a right join Researcher r on
a.researcherid=r.ResearcherID
right join grants g on g.grantid=a.grantid;

16DATA & KNOWLEDGE MANAGEMENT
Rationale for the use of the relational model
The select relational model is based on the relational database management system. As
the RDBMS stores the data using the tables and attributes, it has better consistency and integrity
in the system. On other hand, the developed schema is a star schema (Girsang et al. 2018). There
is no relation between the researchers-grants and students-course tables except they both applies
for their services provided by the university. The application tables connects all the tables and
acts as a fact table in the system. All the other tables can be treated as the dimension tables in the
system. Hence the developed system becomes a star schema. In this schema, for any type of
query, it will not be going to cross more than two relations.

17DATA & KNOWLEDGE MANAGEMENT
References
Al-Absi, A.A. and Kang, D.K., 2014. Relational database normalization algorithm: A tam
database analyst technique. International Information Institute (Tokyo). Information, 17(7),
p.3223.
Cellary, W., Morzy, T. and Gelenbe, E., 2014. Concurrency control in distributed database
systems. Elsevier.
Cleve, A., Gobert, M., Meurice, L., Maes, J. and Weber, J., 2015. Understanding database
schema evolution: A case study. Science of Computer Programming, 97, pp.113-121.
di Vimercati, S.D.C., Foresti, S., Jajodia, S., Livraga, G., Paraboschi, S. and Samarati, P., 2014.
Fragmentation in presence of data dependencies. IEEE Transactions on Dependable and Secure
Computing, 11(6), pp.510-523.
Elmasri, R., 2018. Data Definition Language (DDL).
Girsang, A.S., Isa, S.M., Saputra, H., Nuriawan, M.A., Ghozali, R.P. and Kaburuan, E.R., 2018,
September. Business Intelligence for Construction Company Acknowledgement Reporting
System. In 2018 Indonesian Association for Pattern Recognition International Conference
(INAPR) (pp. 113-122). IEEE.
Jukic, N., Vrbsky, S. and Nestorov, S., 2014. Database systems: Introduction to databases and
data warehouses (p. 400). Pearson.
Kramer, F. and Thalheim, B., 2014, May. Component-Based Development of a Metadata Data-
Dictionary. In International Conference on Business Information Systems (pp. 110-121).
Springer, Cham.

18DATA & KNOWLEDGE MANAGEMENT
Liu, Z.H., Lu, J., Gawlick, D., Helskyaho, H., Pogossiants, G. and Wu, Z., 2018. Multi-model
database management systems-a look forward. In Heterogeneous Data Management, Polystores,
and Analytics for Healthcare (pp. 16-29). Springer, Cham.
Rossi, B., 2014. Entity relationship diagram.
Varga, J., 2019. Finding the Transitive Closure of Functional Dependencies using Strategic Port
Graph Rewriting. arXiv preprint arXiv:1902.02013.
Weske, M., 2019. Business Process Modelling Foundation. In Business Process
Management (pp. 71-122). Springer, Berlin, Heidelberg.