Database Design & Development Project: XYZ College IT Section - 2019

This document details a database design and development project undertaken for the IT section of XYZ College. The project encompasses the creation of a database system designed to track student information and provide course details. The assignment includes user stories outlining system requirements from various user perspectives, such as students and IT staff. It explores database security measures, including firewall configurations, access controls, and the implementation of user roles and permissions. Furthermore, the project outlines a comprehensive database recovery plan, covering backup strategies, including frequency and recovery models, along with the steps and responsibilities involved in database restoration. The project utilizes an ER diagram and SQL scripts to illustrate the database structure and relationships, providing a practical guide to database design and implementation.

qwertyuiopasdfghjklzxcvbnmqw
ertyuiopasdfghjklzxcvbnmqwert
yuiopasdfghjklzxcvbnmqwertyui
opasdfghjklzxcvbnmqwertyuiop
asdfghjklzxcvbnmqwertyuiopasd
fghjklzxcvbnmqwertyuiopasdfgh
jklzxcvbnmqwertyuiopasdfghjkl
zxcvbnmqwertyuiopasdfghjklzxc
vbnmqwertyuiopasdfghjklzxcvb
nmqwertyuiopasdfghjklzxcvbnm
qwertyuiopasdfghjklzxcvbnmqw
ertyuiopasdfghjklzxcvbnmqwert
yuiopasdfghjklzxcvbnmqwertyui
opasdfghjklzxcvbnmqwertyuiop
asdfghjklzxcvbnmqwertyuiopasd
fghjklzxcvbnmqwertyuiopasdfgh
jklzxcvbnmrtyuiopasdfghjklzxcv
Database Design & Development
XYZ College
6/3/2019

XYZ College
Table of Contents
Introduction...........................................................................................................................................2
User Stories...........................................................................................................................................2
Conversation with the User...................................................................................................................2
User Story ID -2................................................................................................................................2
User Story ID – 3...............................................................................................................................3
Database Security..................................................................................................................................3
Database Recovery Plan........................................................................................................................4
Backup Portion..................................................................................................................................4
Frequency of Backups...................................................................................................................4
SQL Server Recovery Model.........................................................................................................4
Use of Full Backups......................................................................................................................5
Use of Point-in-time Backups........................................................................................................5
Recovery Portion...............................................................................................................................5
Steps to Recover Database.............................................................................................................5
Roles and Responsibilities for Database Recovery........................................................................5
Conclusion.............................................................................................................................................6
References.............................................................................................................................................7
1

XYZ College
Introduction
The project includes the development of a database system for the IT section of the XYZ
College. The system will be utilized to track the students and it will also provide the students
with the information on various courses offered by the college. The system is to be developed
as a working database and it will be accessible to the end-users through an interface. In order
to develop the system, a database will be designed and the required information will be put in
the database. The front-end of the database will be used to carry out different operations on
the database.
User Stories
User Story ID User Story
1 As a system user, I would like to log in to the application to access the
application data.
2 As a student, I would like to access the course details to select the desired
course (Hudda, Mahajan and Chopra, 2016).
3 As an IT section associate, I would like to track the student details to
determine the student involvement.
4 As an IT section associate, I would like to track the course details to
determine the availability in the course.
5 As an IT section associate, I would like to view and modify the
information so that the student database is always up to date.
6 As the database admin, I would like to set permissions to keep the
control on the system.
7 As the database admin, I would like to manage the user profiles and
information to monitor the user access.
Confirmation Points
User Story
ID
Confirmation Points
1 Verify that the user is connected with a network before logging in to the
application.
2 Verify that the user has login credentials to access the system.
2

XYZ College
3 Verify that the user has login credentials to access the system.
Verify that the student information is updated in the system.
4 Verify that the user has login credentials to access the system.
Verify that the course information is updated in the system.
5 Verify that the user has login credentials to access the system.
Verify that the real-time student data is available in the system.
6 Verify that the user has login credentials to access the system.
Verify that the admin is able to manage user access.
7 Verify that the user has login credentials to access the system.
Verify that the admin is able to make user profile settings.
Conversation with the User
User Story ID -2
The user story is recorded as – As a student, I would like to access the course details to select
the desired course.
The conversation was carried out with the student in the form of interviews and group
discussions with the student groups. These conversations were carried out to determine the
student expectations from the system and to understand their perspective. The students
provided the access to the course details as their primary expectation from the system because
the current process is not up to the mark. There are multiple channels that are involved in
accessing the course information at present. The students focussed that the database system
will provide them with ease of access and non-stop access to the course details and
information (Memmel and Obendorf, 2010).
Course name selection screen:
3

XYZ College
Course details screen
User Story ID – 3
The user story is recorded as - As an IT section associate, I would like to track the student
details to determine the student involvement.
The conversation was carried out with the executives and resources in the IT section of the
XYZ College through the interview sessions and group discussions. These conversations
4

XYZ College
were carried out to determine the expectations of these users from the system and to
understand their perspective. The IT section resources and executives provided the details
that the primary expectation from the system is to have integrated student information at one
place so that they may be able to keep a track of the same. The resources stated that they will
be able to track the student details through the database system and the interface (Hudson,
2013).
5

XYZ College
6

XYZ College
ER diagram
Database Security
The security of the database will be a primary concern as there are numerous risks associated
with the logins, users, and schemas. Some of the security risks that appear include SQL
injection attacks, broken configuration management, denial of service attacks, encrypted data,
malware attacks, un-patched database systems.
In order to maintain the database security, it will be essential that the following security
controls are included.
 The database server shall be located behind the firewall to ensure that the unwanted
traffic is eliminated.
 The firewall rules shall not allow the direct client access to the database application.
 Only the Database Administrator shall be able to make changes in the firewall rules.
 There must be network security controls and scanners installed such that the network-
based security risks and attacks are avoided (Blake, 2014).
 The unwanted default accounts in the database shall be avoided and the default
passwords shall be changed by the end-users.
 Database Administrator shall sign the non-disclosure agreement with all the
associated vendor groups.
 The Database Admin account shall be an individual account and it must never be a
group account.
Key users of the database are database administrator and XYZ college management:
7

XYZ College
1. DBA - It will have sysadmin login and will have privileges ALL i.e. DBA can create more
user and even define the new user roles. For instance: it can define two roles - teacher access
and principle access. A teacher can just add a new course but cannot define the existing
courses. A principle can even add a new teacher, modify existing teachers and in addition to
teacher role, it can edit or delete a course. It will have SQL authenticated login.
2. XYZ management - This includes dean of the college and it will have regular user with
defined permissions by the DBA as mentioned above. It will have regular login i.e. windows
authentication mechanism.
User Database role
Teacher db_denydatawriter
db_denydatareader
db_datareader
XYZ management db_denydatawriter
db_denydatareader
db_datareader
db_datawriter
DBA db_owner
db_securityadmin
db_owner
The Database roles on the system shall be Database Administrator, Security Admin, Data
Reader, and Data Writer. These will be the fixed database roles. The creation of the new roles
for the database will be a privilege to the Database Administrator only (Paci, 2012). The
Security Admin and the Database Admin will have the view, read, write, and modify
permissions. The Data Writer will be able to successfully write only after the permissions
from the Database Administrator. The Reader will have the read-only permissions. There will
be an individual account set up for the Database Administrator. The other account will be set
up for the application. The permissions and access rights on the database will be controlled
by the Database Administrator.
The DBA will be the database owner as the role db_owner indicates. It is a fixed database
role and all the maintenance and configuration activities will be performed by the DBA. The
creation of the users and management of the permissions will also be performed by the DBA
as the role db_securityadmin is assigned to the DBA. The roles that are mapped with XYZ
management will provide the ability to add, delete, or modify the data in all the user tables
with the role as db_datawriter. The ability to read the data and information sets will be
8

XYZ College
provided with the role as db_datareader. There will be some of the entities under XYZ
management that will also be assigned with the roles as db_denydatareader and
db_denydatawriter to make sure that the permission to read or modify the data sets is not
provided. It will maintain the overall security of the data sets and access control will also be
applied. The teachers will also have the same roles as XYZ management except
db_datawriter.
Generated database from Visual Paradigm
CREATE TABLE STUDENT (
studentID int(10) NOT NULL AUTO_INCREMENT,
studentFirstName char(30) NOT NULL,
studentLastName char(30) NOT NULL,
address varchar(255) NOT NULL,
email varchar(255) NOT NULL,
PRIMARY KEY (studentID));
CREATE TABLE COURSE (
courseID char(30) NOT NULL,
courserName varchar(50) NOT NULL UNIQUE,
STUDENTstudentID int(10) NOT NULL,
PRIMARY KEY (courseID));
CREATE TABLE StudentCourse (
STUDENTstudentID int(10) NOT NULL,
COURSEcourseID char(30) NOT NULL,
PRIMARY KEY (STUDENTstudentID,
9

XYZ College
COURSEcourseID));
ALTER TABLE Entity ADD CONSTRAINT FKEntity25549 FOREIGN KEY
(STUDENTstudentID) REFERENCES STUDENT (studentID);
ALTER TABLE Entity ADD CONSTRAINT FKEntity174703 FOREIGN KEY
(COURSEcourseID) REFERENCES COURSE (courseID);
CREATE TABLE StudentCourseDetails (
STUDENTCourseStudentstudentID int(10) NOT NULL,
STUDENTCourseCOURSEcourseID char(30) NOT NULL,
duration int(2) NOT NULL,
startDate date NOT NULL,
endDate date NOT NULL,
PRIMARY KEY (STUDENTCourseStudentstudentID,
startDate));
ALTER TABLE Entity ADD CONSTRAINT FK_SC FOREIGN KEY
(STUDENTCourseStudentstudentID) REFERENCES STUDENT (studentID);
ALTER TABLE Entity ADD CONSTRAINT FK_SCC FOREIGN KEY
(STUDENTCourseCOURSEcourseID) REFERENCES COURSE (courseID);
Database Recovery Plan
Backup Portion
Frequency of Backups
The transaction logs will be backed up on a daily basis. The rest of the information stored in
the database will be backed up on a weekly basis.
10

XYZ College
SQL Server Recovery Model
The SQL Server Recovery Model that will be used for this case will be full model. These are
the models that will need log backups. There will be no work lost because of a lost file or
damaged data file.
This database model will allow the backup t arbitrary and specific points in time.
Use of Full Backups
Full data backup is the full copy of the complete data set in the database. The full recovery
model stated above will capture the full data backups. These databases provide the best
protection as the organizations have a copy of the complete data sets in place. As a result, the
organizations succeed in avoiding the security risks and attacks associated with the databases.
Use of Point-in-time Backups
Point-in-time backups are the ones wherein the backups are stored to a specific point. These
are beneficial as these do not capture enhanced disk space. Also, there is required protection
provided to the users and the recovery can be made as per the need.
Recovery Portion
Steps to Recover Database
The following steps shall be followed to recover the database.
 The access to the Microsoft SQL Server shall be obtained and the databases shall be
accessed.
 Right click on the recover databases option and a pop-up window will appear.
 The backup location shall be selected in the next step by providing the details of the
device or link to the backup files (Boicov, 2015).
 Upon clicking ok, specify backup window will appear. Select the designation for
restoring the information.
 There will be an Options link visible. The option to overwrite the existing database or
to restore with non-recovery shall be made.
The database will be recovered and restored using these steps.
Roles and Responsibilities for Database Recovery
The primary responsibility of ensuring the database backups and recovery shall be on the
Security Admin.
11