KC7013 DATABASE MODELLING | SQL

Running head: DATABASE MODELLING
KC7013 DATABASE MODELLING
Name of the Student
Name of the University
Author’s note:

1DATABASE MODELLING
Table of Contents
Part 1................................................................................................................................................2
A) Entity Relationship Model:.....................................................................................................2
B) SQL DDL:..............................................................................................................................2
Part 2:.............................................................................................................................................15
A) SQL DML:............................................................................................................................15
B) SQL Queries and Relational Algebra:..................................................................................16
Part 3..............................................................................................................................................18
A) Professional, Legal, Ethical and Security Issues:.................................................................18
B) Database Design Approaches:..............................................................................................21
Bibliography:.................................................................................................................................23

2DATABASE MODELLING
Part 1
A) Entity Relationship Model:
Figure 1: Entity Relationship Model of AIS
(Source: Created by Author)
B) SQL DDL:
create table Students (
ID VARCHAR(50),
course_id INTEGER,
next_of_kin_id INTEGER,
tutor_id INTEGER,

3DATABASE MODELLING
first_name VARCHAR(50),
middle_name VARCHAR(50),
last_name VARCHAR(50),
term_address VARCHAR(150),
home_address VARCHAR(150),
email_address VARCHAR(100),
dob DATE,
gender CHAR(10),
student_from CHAR(15),
PRIMARY KEY(ID)
);
create table Departments (
ID INTEGER,

4DATABASE MODELLING
name VARCHAR(50),
primary_location VARCHAR(150),
secondary_address VARCHAR(150),
telephone CHAR(15),
fax_number CHAR(15),
PRIMARY KEY(ID)
);
create table Courses (
ID INTEGER,
course_leader INTEGER,
dept_id INTEGER,
name VARCHAR(50),
type VARCHAR(50),
standard_duration INTEGER,
credit_hours INTEGER,
PRIMARY KEY(ID)

5DATABASE MODELLING
);
create table Modules (
ID INTEGER,
title VARCHAR(50),
credit_hours INTEGER,
module_level INTEGER,
PRIMARY KEY(ID)
);
create table CourseModules (
ID INTEGER,
course_id INTEGER,

6DATABASE MODELLING
module_id INTEGER,
PRIMARY KEY(ID)
);
create table SelectedModules (
module_id INTEGER,
student_id VARCHAR(50),
academic_year INTEGER,
marks DECIMAL NULL,
PRIMARY KEY(module_id, student_id)
);
create table Tutors (
ID INTEGER,
faculty_id INTEGER,

7DATABASE MODELLING
course_id INTEGER,
PRIMARY KEY(ID)
);
create table FacultyMembers (
ID INTEGER,
line_manager INTEGER NULL,
next_of_kin_id INTEGER,
course_id INTEGER,
name VARCHAR(50),
home_address VARCHAR(50),
office_location VARCHAR(50),
room_number INTEGER,
telephone_extension CHAR(10),
email_address VARCHAR(50),
date_of_birth DATE,
gender CHAR(15),
PRIMARY KEY(ID)

8DATABASE MODELLING
);
create table NextOfKin (
ID INTEGER,
name VARCHAR(50),
address VARCHAR(50),
relationship char(10),
phone char(15),
PRIMARY KEY(ID)
);

9DATABASE MODELLING
create table StudentTelephones (
student_id VARCHAR(50),
telephone_number char(15),
PRIMARY KEY(student_id, telephone_number)
);
create table ModuleTutors (
faculty_id INTEGER,
module_id INTEGER,
PRIMARY KEY(faculty_id, module_id)
);
create table DepartmentChairpersons (
dept_id INTEGER,
faculty_id INTEGER,
role_assign_date DATE,

10DATABASE MODELLING
PRIMARY KEY(dept_id, faculty_id)
);
create table LineManager (
ID INTEGER,
dept_id INTEGER,
faculty_id INTEGER,
PRIMARY KEY(ID)
);
ALTER TABLE StudentTelephones
ADD CONSTRAINT fk_student_phone
FOREIGN KEY (student_id)
REFERENCES Students(ID);
ALTER TABLE Students

11DATABASE MODELLING
ADD CONSTRAINT fk_student_next_of_kin
FOREIGN KEY (next_of_kin_id)
REFERENCES NextOfKin(ID);
ALTER TABLE Tutors
ADD CONSTRAINT fk_tutor_faculty
FOREIGN KEY (faculty_id)
REFERENCES FacultyMembers(ID);
ALTER TABLE Tutors
ADD CONSTRAINT fk_tutor_course
FOREIGN KEY (course_id)
REFERENCES Courses(ID);
ALTER TABLE Students
ADD CONSTRAINT fk_student_tutor
FOREIGN KEY (tutor_id)
REFERENCES Tutors(ID);
ALTER TABLE Students
ADD CONSTRAINT fk_student_course

12DATABASE MODELLING
FOREIGN KEY (course_id)
REFERENCES Courses(ID);
ALTER TABLE FacultyMembers
ADD CONSTRAINT fk_faculty_next_of_kin
FOREIGN KEY (next_of_kin_id)
REFERENCES NextOfKin(ID);
ALTER TABLE FacultyMembers
ADD CONSTRAINT fk_faculty_course
FOREIGN KEY (course_id)
REFERENCES Courses(ID);
ALTER TABLE FacultyMembers
ADD CONSTRAINT fk_faculty_line_manager
FOREIGN KEY (line_manager)
REFERENCES LineManager(ID);
ALTER TABLE Courses
ADD CONSTRAINT fk_course_dept
FOREIGN KEY (dept_id)

13DATABASE MODELLING
REFERENCES Departments(ID);
ALTER TABLE Courses
ADD CONSTRAINT fk_course_dept
FOREIGN KEY (course_leader)
REFERENCES FacultyMembers(ID);
ALTER TABLE LineManager
ADD CONSTRAINT fk_line_manager_dept
FOREIGN KEY (dept_id)
REFERENCES Departments(ID);
ALTER TABLE LineManager
ADD CONSTRAINT fk_line_manager_faculty
FOREIGN KEY (faculty_id)
REFERENCES FacultyMembers(ID);
ALTER TABLE ModuleTutors
ADD CONSTRAINT fk_module_faculty
FOREIGN KEY (faculty_id)
REFERENCES FacultyMembers(ID);

14DATABASE MODELLING
ALTER TABLE ModuleTutors
ADD CONSTRAINT fk_module
FOREIGN KEY (module_id)
REFERENCES CourseModules(ID);
ALTER TABLE DepartmentChairpersons
ADD CONSTRAINT fk_dc_dept
FOREIGN KEY (dept_id)
REFERENCES Departments(ID);
ALTER TABLE DepartmentChairpersons
ADD CONSTRAINT fk_dc_faculty
FOREIGN KEY (faculty_id)
REFERENCES FacultyMembers(ID);
ALTER TABLE CourseModules
ADD CONSTRAINT fk_cm_course
FOREIGN KEY (course_id)
REFERENCES Courses(ID);

15DATABASE MODELLING
ALTER TABLE CourseModules
ADD CONSTRAINT fk_cm_course
FOREIGN KEY (module_id)
REFERENCES Modules(ID);
ALTER TABLE SelectedModules
ADD CONSTRAINT fk_sm_module
FOREIGN KEY (module_id)
REFERENCES CourseModules(ID);
ALTER TABLE SelectedModules
ADD CONSTRAINT fk_sm_student
FOREIGN KEY (student_id)
REFERENCES Students(ID);
Part 2:
A) SQL DML:
insert into Departments values(1, 'Computer Science', 'CIS BUILDING,
NORTHUMBERLAND BUILDING, SANDYFORD BUILING', 'STUDENT CENTRAL, UNIVERSITY
LIBRARY', '1234567890', '0987654321');
insert into Courses(ID, dept_id, name, type, standard_duration,
credit_hours) values(1, 1, 'Msc information Science', 'postgraduate', '24',
'300');

16DATABASE MODELLING
insert into NextOfKin values(1, 'Alexandra Alexander', '94 Abingdon
Road, BRANSCOMBE, EX12 6LT', '079 5359 4872');
insert into FacultyMembers(ID, next_of_kin_id, course_id, name,
home_address, office_location, room_number, telephone_extension,
email_address, date_of_birth, gender)
values(1, 1, 1, 'Dr. AKTHA ALI AND MOHAMMED ALI', '98, brighton grove,
Newcastle, NE4 5NT', 'Main Building', 12, '070', '[e-mail]',
TO_DATE('1973/05/03', 'yyyy/mm/dd'), 'Male');
insert into Tutors values(1, 1, 1);
insert into Students values('e26737975', 1, 1, 1, 'Amy', '', 'Godfrey',
'27 Oxford Rd, WORTWELL, IP20 3FR', '93 New Dover Rd, WALDERTON, PO18 9QG',
'[e-mail]', TO_DATE('1993/05/03', 'yyyy/mm/dd'), 'Female', 'UK');
insert into Students values('w19028975', 1, 1, 1, 'Adedeji', '',
'Abimbola', '70 Wrexham Rd, EYE, PE6 4WQ', '98 Berkeley Rd, STRATFORD ST
ANDREW, IP17 4PY', '[e-mail]', TO_DATE('1993/05/03',
'yyyy/mm/dd'), 'Female', 'UK');
insert into Modules values(1, 'KC7013 DATABASE MODELLING', 100, 1);
insert into CourseModules values(1, 1, 1);
insert into SelectedModules values(1, 'w19028975', 2020, 75.80);
insert into SelectedModules values(1, 'e26737975', 2020, 67.97);
B) SQL Queries and Relational Algebra:
1) Query:
Select students.name, Courses.*, Modules.*, SelectedModules.marks, from
students

17DATABASE MODELLING
inner join Courses on Students.course_id = Courses.ID
inner join SelectedModules on Students.ID = SelectedModules.student_id
inner join CourseModules on SelectedModules.module_id = CourseModules.ID
inner join Modules on CourseModules.module_id = Modules.ID
where Courses.type = 'postgraduate';
Relational Algebra:  students.name, Courses.*, Modules.*, SelectedModules.marks (Courses.type =
'postgraduate'((((students* Students.course_id = Courses.ID Courses)* Students.ID =
SelectedModules.student_id SelectedModules)* SelectedModules.module_id = CourseModules.ID
CourseModules)* CourseModules.module_id = Modules.ID Modules))
1) Query:
Select FacultyMembers.name as Name, FacultyMembers.home_address as
HomeAddress, Departments.name as DepartmentName from FacultyMembers
inner join Courses on FacultyMembers.course_id = Courses.ID
inner join Departments on Courses.dept_id = Departments.ID
UNION
Select CONCAT(CONCAT(Students.first_name, ' '), Students.last_name) as
Name, Students.home_address as HomeAddress, Departments.name as DepartmentName
from Students
inner join Courses on Students.course_id = Courses.ID
inner join Departments on Courses.dept_id = Departments.ID;

18DATABASE MODELLING
Relational Algebra:  FacultyMembers.name, FacultyMembers.home_address, Departments.name
((((FacultyMembers* FacultyMembers.course_id = Courses.ID Courses)* Courses.dept_id =
Departments.ID Departments)
UNION
 Students.name, Students.home_address, Students.name ((((Students* Students.course_id = Courses.ID
Courses)* Courses.dept_id = Departments.ID Departments)
Part 3
A) Professional, Legal, Ethical and Security Issues:
As a security professional it is very important to understand the legal and ethical
responsibilities within an organization. Professional and ethical issues goes hand in hand within
an organization. In order to avoid professional issues, ethical issues need to be understood.
Ethics, in simple words, talks about justice, duties, values, actions, rights and consequences.
Some of the information security issues are data damage, lost or alter, integrity and
confidentiality (WARREN JUSTICE and SUPREME, 2018, p.12). Various professional and
ethical issues remain in existence that challenges information security in present time. Data
privacy can be compromised if professional and ethical approach is not followed. People who
has information about outcome new offers, data patterns or some other vital information. If

19DATABASE MODELLING
someone passes these data to an unauthorized person then professional and ethical rules will be
violated. In accuracy in information can lead to various professional issues (Logan, 2020, p.3).
Many organizations also violate this rule. Often organizations submit wrong data to government.
If internal situation is assumed, then employee may mistakenly or purposefully insert wrong data
into system. This will lead to data corruption risk. Ethically, a professional should always do
their duties unbiased way.
As per scenario of AIS, tutors will be allowed to enter marks for students. If one tutor
mistakenly or purposefully enter wrong mark information, then entire system will show that
wrong data to everyone. Students can also provide wrong data of their kin so that in case
anything happen their real kin will not get any information (Berg and Bartz, 2018). These are
ethical issues that can arise in the system. Administrator will have all the information about
students, faculty members, courses and modules. If admin shares all these data with an outsider
then professional rule will be violated.
Information system of AIS will be under civil, criminal, tort, private and public laws.
Defending the intellectual property has become considerably tough in today’s world. AIS will be
storing a lot of information of staff and students. With the increase in cyber threat, AIS must
protect its data from outside threats. Loosing personal information of student or staff can lead to
lawsuit. No matter which service AIS use run the system, proper SLA and other documents
should be made before using service (Losavio and Losavio, 2017). University of Gharnata
should first get the authority from government regarding information system installation. It
should follow all the legal procedures and legislations to build the system. Such as storing data
for a specific time period and many more. University of Gharnata also focus on developing the
system without violating any copyright protection. Many tools, technologies and other features

20DATABASE MODELLING
are available on the internet for free but if University of Gharnata wants to implement this for
professional purpose then proper agreement need to be done. University of Gharnata should
always see that their system does not steal features or other technical aspects from other systems
(Loorits, 2016). This can lead to huge copy right issues.
The first and foremost security threat for AIS is viruses and malicious codes.
Digitalization of processes are very much reliable on computers that are extremely vulnerable if
not maintained properly. Viruses are malicious programs or computer codes that exploits loop
holes in the system to damage it (Gao et al., 2017, p.241). Viruses can also control
administrative privileges of the system. It makes viruses very dangerous. Viruses can be injected
to the system through various input methods such as internet, external hard disks, CD/DVDs and
many other ways. Each year new viruses are created by cyber criminals and predicting what
virus will be used to attack the system is very unpredictable. Denial of Service is a very complex
and devastating security issues for AIS. This prevents the users from accessing the system. It can
be applied to AIS through flooding the host with unimaginable count of requests. It results in
server breakdown. It is one of the hardest types of issues to resolve (Arduin and Vieru, 2017).
Once a denial of service attack initiates, it is nearly impossible to stop it. Outdated security
software or antiviruses can be a big security concern in AIS. Security software are the way of
protecting the computers from various ransomware, viruses, Trojan and other attacks. In case
antiviruses are out of date, then new cyber threats can easily compromise the system.
Insider threats are also very crucial for AIS to consider as issues. People who uses the
system from inside are the one who poses biggest insider security threats. Most of the time, loop
holes made in system mistakenly or intentionally generate issues in system (Gao et al., 2017,

21DATABASE MODELLING
p.241). Administrator of the system or any other user can exploit it to enter malicious code.
Exploiting the system from inside is much easier than attacking from outside.
B) Database Design Approaches:
ER modelling or Entity Relationship modelling is considered to be a graphical
representation of database. In ER modelling, entities and relations are used to represent the
model. Entities represents real world objects. These objects can be person, elements or other
things. In the given case study of AIS faculty members, courses, modules and students are
entities. These entities are distinguishable from one another (Yang and Cao, 2016, p.1). Each
entity has a set of properties called attributes that has values. These entities are called tables in a
developed database. Relationships among the entities are very important in Entity Diagram.
Entity relationship diagram shows the entity and attribute types perfectly. Such as different
layouts are used for strong and weak entities. Attributes types such as key, multivalued,
composite and derived are shown perfectly in ER Modelling (Model, 2020, p.31). Relationships
like one to many, many to one, one to one and many to many are very important in ER
Modelling. The behavior of the entities, use of referential integrity are all dependent on
relationship types. Taken as an example, each faculty member can be associated with one course.
Another example is course can be part of one department. This way ER Models provides a solid
idea on how to relate each entity with one another. The features of ER Models are enhanced in
Enhanced ER Models or EERD (Iqbal, 2018). Enhanced ER Models shows generalization and
specialization. Generalization can be considered as the bottom-up approach for combining lower
level entities to form higher level entity. Generalization can have many levels such as one higher
level entity can be lower level of entity of another higher level entity.

22DATABASE MODELLING
Database normalization is the method for creating organized tables in such a manner that
database experience lesser redundancy and data dependency. Database normalization
concentrates on decomposing tables so that complex situations can be break down into relatable
and manageable states (Amin et al., 2019, p.48). Normalization also uses relations to link the
tables. However, the type of relation between the entities does not consider as important. Unlike
ER Modelling, the real purpose of using normalization process is reducing the redundancy.
Normalization makes sure that each data stored within the database has some logic behind it.
Normalization process concentrates more on the relation between attributes rather than entities.
As per attribute type, normalization only check for multivalued attributes in its first normal form.
This is because, first normal form does not accept multivalued attributes because it can crease
data redundancy. After multivalued attributes are converted into normal attributes, the primary
key of the database is set. Once, database primary key is determined, dependency of attributes
are identified. If any non-key attribute is dependent on another non-key attribute is found then
database is decomposed to create new tables. These two tables has relation through referential
integrity (Eessaar, 2016, p.5). Transitive dependency is a very important part of normalization
because it helps in finding many-to-many relations between tables. Many-to-Many are
decomposed to many-to-one and one-to-many through removing transitive dependency.
Therefore, it is clear that ER Model mainly focuses on how entities, attributes and
relations are represented in a database. ER Models do not put much consideration on the
structure of the database or anomalies it can experience. Often the real world, database does not
match with the ER Model because it can have many to many relation or high order generalization
that are not supported in real world database. On the other hand, normalization concentrates on
structure of the database and make more adaptable to real world scenarios. It is best to create an

23DATABASE MODELLING
ER model before normalization and after normalization. The ER Model created before
normalization will be logical model and one created after normalization will be Physical model.

24DATABASE MODELLING
Bibliography:
Amin, M., Romney, G.W., Dey, P. and Sinha, B., (2019). Teaching Relational Database
Normalization in an Innovative Way. The Journal of Computing Sciences in Colleges, p.48.
Arduin, P.E. and Vieru, D., (2017). Workarounds as means to identify insider threats to
information systems security. Association for Information Systems.
Berg, K. and Bartz, B., 2018. A STUDY AND COMPARISON OF ETHICAL CODES FOR
INFORMATION TECHNOLOGY PROFESSIONALS. Issues in Information Systems, 19(2).
Eessaar, E., (2016). The database normalization theory and the theory of normalized systems:
finding a common ground. Baltic Journal of Modern Computing, 4(1), p.5.
Gao, X., Gu, Z., Kayaalp, M., Pendarakis, D. and Wang, H., (2017). ContainerLeaks: Emerging
security threats of information leakages in container clouds. In 2017 47th Annual IEEE/IFIP
International Conference on Dependable Systems and Networks (DSN) (pp. 237-248). IEEE.
Iqbal, A., (2018). Penerapan Entity Relationship Diagram (ERD) Perancangan Sistem Basis Data
Terhadap Penjualan Produk Indihome.
Logan, P.Y., (2020). Crafting an undergraduate information security emphasis within
information technology. Journal of Information Systems Education, 13(3), p.3.
Loorits, L., 2016. INTEGRATING CLINICAL AND GENOMIC DATA IN ESTONIAN
HEALTH INFORMATION SYSTEM: TECHNOLOGICAL, LEGAL, ETHICAL AND
SOCIAL PERSPECTIVES.
Losavio, M.M. and Losavio, A., (2017). Downstream Competence Challenges and Legal/Ethical
Risks in Digital Forensics.

25DATABASE MODELLING
Model, E.R., (2020). Data base management systems. Syllabus, p.31.
WARREN, E., JUSTICE, C. and SUPREME, U., (2018). Legal, Ethical, and Professional Issues
in Information Security. Retrieved 31st January.
Yang, L. and Cao, L., (2016). The Effect of MySQL Workbench in Teaching Entity-Relationship
Diagram (ERD) to Relational Schema Mapping. International Journal of Modern Education and
Computer Science, 8(7), p.1.