KC7013 Database Modelling Assignment: MSc Computer Science

This assignment solution addresses a database modelling problem for the University of Gharnata, encompassing several key aspects of database design and implementation. Part 1 involves creating a logical database design using either an Entity-Relationship (ER) diagram with mapping to relations or through normalization. The solution includes an ER diagram, mapping to relations, and an SQL DDL script for Oracle 11g/12c. Part 2 focuses on populating the database with sample data, writing SQL queries using relational algebra, and providing the output. Part 3 delivers a detailed report for the Rector, discussing professional, legal, ethical, and security issues related to the database design, along with recommendations. The report also compares and contrasts different database design approaches, justifying the chosen method.

Assessment # 1 Submission Template
Database Modelling (KC7013)
Programmes: MSc Computer Science / MSc Information Science (Data Analytics)
Module Code: KC7013
Module Title: Database Modelling
Distributed on: Friday 14th February 2020
Submission
Time and Date: Wednesday 18th March 2020 by 23.59 GMT
Weighting This coursework accounts for 50% of the total marks for this module
Student ID/
Username
W19026287
Student Name Sharissa Lightbourne
ASSIGNMENT #1
Page 1 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
Assignment Questions
Part 1 (50 marks)
This part is based on the ‘AiS’ scenario as described in the Appendix.
(A) Using a database design approach of your choice, produce a logical design for
the database to support the information system, which is needed at the
University of Gharnata.
(30 marks)
Your answer must consist of ONE of the following: An entity-relationship (ER) diagram (20 marks) and its mapping into a set of
relations (10 marks). The ER diagram should show all relevant entity types,
relationship types, attributes, primary keys, and structural constraints. Note
that not all keys are identified/mentioned in the scenario, so you are required
to identify/devise appropriate primary keys for all the entity types. Your ER
diagram must not show/include any foreign keys or any such attributes that
represent foreign keys as these are logical and not conceptual concepts. As
part of the mapping process, for each relation, you should identify appropriate
primary keys as well as foreign keys (if applicable). Furthermore, you need to
make sure your relations obtained from mapping your ER diagram are in the
3rd normal form.
 A set of normalised relations (10 marks) obtained through normalisation
process (20 marks) instead of ER modelling. You should make clear how the
normalisation process has been carried out, and the reasoning employed, in
particular quoting/providing evidence (series of steps) to support the
decisions made and how your relations have been derived. Each relation in
your answer should be in the 3rd normal form.
Page 2 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
Answer Part 1 A:
ER Diagram (20 marks) and ER Diagram to Relations Mapping to
obtained normalised relations (10 marks)
OR
Steps of your Normalisation process (20 marks) and set of Normalised
Relations (10 marks)
Page 3 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
Page 4 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
(B) Based on your logical design from Part 1 (A) and the information available in the
scenario, produce an SQL script file using Oracle 11g/12c.
(20 marks)
Answer Part 1 B: SQL DDL Script file contents: 16 marks
CREATE TABLE MODULE
(
ModuleTitle VARCHAR(11) NOT NULL,
ModuleCreditHrs INT(5) NOT NULL,
ModuleLevel VARCHAR(11) NOT NULL,
Module__ID INT(5) NOT NULL,
Faculty_ID INT(5) NOT NULL,
PRIMARY KEY (Module__ID)
);
CREATE TABLE STUDENT
(
StudentName VARCHAR(11) NOT NULL,
TermAddress VARCHAR(11) NOT NULL,
HomeAddress VARCHAR(11) NOT NULL,
StudentTele VARCHAR(11) NOT NULL,
StudentEmail VARCHAR(11) NOT NULL,
StudentDOB DATE NOT NULL,
StudentGender VARCHAR(11) NOT NULL,
StudentNOK VARCHAR(11) NOT NULL,
Student_ID INT(5) NOT NULL,
PRIMARY KEY (Student_ID)
);
CREATE TABLE FACULTY
(
FacultyName VARCHAR(11) NOT NULL,
Page 5 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
HomeAddress VARCHAR(11) NOT NULL,
FacultyNOK VARCHAR(11) NOT NULL,
OfficeLocation VARCHAR(11) NOT NULL,
FacultySalary_ INT(11) NOT NULL,
FacultyDOB DATE NOT NULL,
FacultyGender VARCHAR(11) NOT NULL,
FacultyQualif VARCHAR(11) NOT NULL,
DateJoined DATE NOT NULL,
FacultyTelephone INT(11) NOT NULL,
FacultyEmail VARCHAR(11) NOT NULL,
Faculty_ID INT(5) NOT NULL,
Student_ID INT(5) NOT NULL,
Module__ID INT(5) NOT NULL,
PRIMARY KEY (Faculty_ID),
FOREIGN KEY (Student_ID) REFERENCES STUDENT(Student_ID),
FOREIGN KEY (Module__ID) REFERENCES MODULE(Module__ID)
);
CREATE TABLE COURSE
(
CourseLeader VARCHAR(11) NOT NULL,
CourseName VARCHAR(11) NOT NULL,
CourseDuration INT(5) NOT NULL,
CourseLevel VARCHAR(11) NOT NULL,
CreditHrsNeeded INT NOT NULL,
Course_ID INT(11) NOT NULL,
Student_ID INT(11) NOT NULL,
Department_ID INT(11) NOT NULL,
Module__ID INT(11) NOT NULL,
Faculty_ID INT(11) NOT NULL,
PRIMARY KEY (Course_ID),
FOREIGN KEY (Module__ID) REFERENCES MODULE(Module__ID),
FOREIGN KEY (Faculty_ID) REFERENCES FACULTY(Faculty_ID)
);
Page 6 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
CREATE TABLE DEPARTMENT_
(
DeptName VARCHAR(11) NOT NULL,
PrimaryLocation VARCHAR(11) NOT NULL,
SecLocation VARCHAR(11) NOT NULL,
TeleNum INT NOT NULL,
FaxNum INT NOT NULL,
Department_ID INT NOT NULL,
Course_ID INT NOT NULL,
Module_ID INT NOT NULL,
Faculty_ID INT NOT NULL,
PRIMARY KEY (Department_ID),
FOREIGN KEY (Faculty_ID) REFERENCES FACULTY(Faculty_ID),
FOREIGN KEY (Course_ID) REFERENCES COURSE(Course_ID)
);
Page 7 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
Answer Part 1 B: SQL DDL Output (e.g., SPOOL file contents): 4 marks
Page 8 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
Part 2 (30 marks)
This part is based on your answer / solution to Part 1, i.e., design and
implementation of the database for the ‘AiS’ scenario.
(A) Populate the database with some data (e.g., data similar to the courses and
modules you study, and other relevant information within Northumbria
University).
(10 marks)
Answer Part 2 A: SQL code for populating the AiS Database: 8 marks
SQL DML
INSERT INTO MODULE(ModuleTitle, ModuleCreditHrs, ModuleLevel,
Module__ID, Faculty_ID)
VALUES (‘Database Modelling’, 4, Masters, KC7013, F101);
INSERT INTO STUDENT(StudentName, TermAddress, HomeAddress,
StudentTele, StudentEmail, StudentDOB, StudentGender,
StudentNOK, Student_ID)
VALUES (‘Sharissa Lightbourne’, ’32 Syd’, ’23 Givange Rd Nrb’,
[phone], [e-mail], 1995-12-03, Male,
MSC123, W19026287),
(‘Tonny Chep’, ’33 Eldy’, ’43 Chd Rd Newcastle’,
[phone], [e-mail], 1994-08-23, Female,
MSC123, W19026286);
INSERT INTO FACULTY(FacultyName, HomeAddress, FacultyNOK,
OfficeLocation, FacultySalary, FacultyDOB, FacultyGender,
FacultyQualif, DateJoined, FacultyTelephone, FacultyEmail,
Faculty_ID, Student_ID, Module__ID)
VALUES (‘Roberts Light’, ’223 Givnge Rd Newcastle’, F223, ‘Rm
123 Academic Building’, 25000.00, 1978-07-22, Male, Phd, 2006-
08-27, [phone], [e-mail], F234F,
W19026287, KC7013);
INSERT INTO COURSE(CourseLeader, CourseName, CourseDuration,
CourseLevel, CreditHrsNeeded, Course_ID, Student_ID,
Faculty_ID, Department_ID, Module__ID)
VALUES (‘Roberts Light’, ‘MSc Computer Science’, ‘2 Years’,
Masters, ‘840 Hrs’, CS1204, W19026287, F234F, D1001, KC7013);
Page 9 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
INSERT INTO FACULTY(DeptName, PrimaryLocation, SecLocation,
TeleNum, FaxNum, Department_ID, Course_ID, Module_ID,
Faculty_ID)
VALUES (‘Computer Science’, ‘Academic Building 2nd Flr’,
‘Technology Centre Rm 130’, ‘Rm 123 Academic Building’,
+1328864, 345344, D1001, CS1204, KC7013, F234F);
Answer Part 2 A: SQL code output (e.g., SPOOL file contents): 2 marks
Student table
Studen
t_ID
Student
Name
HomeAdd
ress
StudentEmail CourseL
evel
ModuleT
itle
W19026
287
Shariss
a
Lightbo
urne
23
Givange
Rd Nrb
sharissalight@gm
ail.com
Masters Databas
e
Modelli
ng
W19026
286
Tonny
Chep
43 Chd
Rd
Newcast
le
[e-mail] Masters Databas
e
Modelli
ng
Page 10 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
(B) Answer the following queries (retrievals) using SQL and relational algebra.
(20 marks)
1) Display names of students, details of the course they study, details of the
module they have studied and their marks for all postgraduate students.
Answer Part 2 B (1): Relational Algebra Expression: 2 marks
Let Student table be S, Course be C and Module to be M.
S union C union M is
S U C U M
∏ Student_ID, StudentName (STUDENT) ∪ ∏ CourseName, CourseLevel
(COURSE) ∏ ModuleTitle (MODULE)
Answer Part 2 B (1): SQL code: 4 marks
RETRIEVAL CODE
SELECT Student_ID, StudentName FROM STUDENT
JOIN
SELECT CourseName, CourseLevel FROM COURSE
JOIN
SELECT ModuleTitle FROM MODULE
ON
Student_ID=Student_ID
Answer Part 2 B (1): SQL code output (e.g., SPOOL file contents): 2 marks
Student_ID StudentName CourseName CourseLevel ModuleTitle
W19026287 Sharissa
Lightbourne
MSc
Computer
Science
Masters Database
Modelling
W19026286 Tonny Chep MSc
Computer
Science
Masters Database
Modelling
Page 11 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
2) Display details of all people of ‘AiS’ (students and academic staff), e.g., their
names, their home addresses and name of the department where they work
or study.
Answer Part 2 B (2): Relational Algebra Expression: 4 marks
Student U Faculty
∏ Student_ID as ID, StudentName as Name, StudentGender as
Gender, HomeAddress (Student) ∪ ∏ Faculty_ID, FacultyName,
FacultyGender, HomeAddress (Faculty)
Answer Part 2 B (2): SQL code: 6 marks
SELECT Student_ID as ID, StudentName as Name, StudentGender as Gender,
HomeAddress FROM Student
UNION
SELECT Faculty_ID, FacultyName, FacultyGender, HomeAddress FROM Faculty
ORDER BY ID DESC;
Answer Part 2 B (2): SQL code output (e.g., SPOOL file contents): 2 marks
ID Name Gender HomeAddress
W19026287 Sharissa
Lightbourne
Male 23 Givange Rd
Nrb
W19026286 Tonny Chep Female 43 Chd Rd
Newcastle
F234F Roberts Light Male ’223 Givnge Rd
Newcastle
Page 12 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
Part 3 (20 marks)
Consider the ‘AiS’ scenario in the Appendix. Produce a report for the Rector of
the University of Gharnata elaborating on professional, legal, ethical and
security issues that need to be considered and make recommendations that
you think are appropriate for ‘AiS’.
Ethical issues
Ethical issues are sets of principles of correct conduct or theory or system of moral values
(Besio and Pronzini, 2014). It can also be taught as ‘doing what is right’ in the
organisation. Another big challenge is to tell what is ethical and what is legal. It can be
said that what is ethical is legal and what is legal is ethical.
Some of the unethical issues that could be witnessed in database technology is use of data
mining and data warehousing to aggregate and find associations and patterns among
disparate data. Also, for example, a database administrator is asked to implement a
schema for a loan application process that includes the applicant’s race or national origin.
These examples illustrate legal behaviours that many would consider unethical and
therefore all actions done should be critically analysed to ascertain whether they are legal
and ethical before being implemented.
Another aspect to consider in database technology is security. Databases today are facing
different kind of attacks. It is important to describe the various categories of attacks
before describing
Professional issues to be considered in database design
Strive to providing the quality services and product within your professional role
Improve professional competency or effectiveness
Respect ethical rules and laws that govern your work
Provide reviews according to your profession
Page 13 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
Educate public on the database, computing and systems.
Honor agreements, and responsibilities
Legal issues
Close monitoring of how information are secured and how they can be accessed by the
users,
Policies that govern use of data within an organization
Using unlicensed software to access or store data
Exposing trade secrets
Patterns –software or product patterns.
General computer laws
Cybercrimes laws and legislation
Security issues
Malware issues
Injection
Privileges beyond the recommended level
Uncontrolled sensitive data
Unauthorized access
Poor encryptions
Weak authorization and authentication
Answer Part 3 (A): 12 Marks [9 for the quality of report covering all the issues, 2 for the
quality of referencing and citation and adhering to the Harvard style, 1 for presentation]
(A) Compare and contrast different approaches to database design (e.g., entity
relationship modelling, normalisation, etc.) and briefly justify the approach you
Page 14 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
have used for answering Part 1 (A).
(8 marks)
Answer Part 3 (B): 8 Marks [5 for the quality of comparison of the two approaches, for
the quality of referencing and citation and adhering to the Harvard style, 1 for presentation, 1
for choosing / justifying the approach used for part 1(A) ]
Entity relationship diagram is the widely use database design technique. It is a conceptual
model that is used to define the relationship between various set of entities in a database
(Thalheim, 2013). The sets entity and relationship sets are then model to create a
database. Normalisation on the hand is a process of checking on the database with a view
to remove redundancy or repetition of data.
After the design of entity relationship model, a database is created, this database is
improved through the process of normalisation. Redundancy is removed to improve the
accuracy of stored table and consistency in querying the database.
A comparison of ERD and normalization is summarised through two diagrams. The first
diagram (diagram 1) represents the ERD model while the second diagram (diagram 2)
show a normalize ERD model of diagram 1.
ERD
Page 15 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
Diagram 1
Diagram 2
Page 16 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
The difference from the two diagram is that in ERD model the “customer has a one to
many with “Order entity”, in diagram 2 there is no such relationship.
In the second diagram (normalisation), the table “Order” has one to many relation
whereas in the first diagram, the entity has one to many entities with a “quote product
entity”.
Entity relationship diagram is the most appropriate design to use here. From the diagrams,
there will be always “One product” for every “Quote product”. Thus one will find that
there is a “Quote product” for every “Ordered products” and not vice versa. It is therefore
sensible to have entities as it is in ERD diagram, that makes an entity have one to many
relationships.
Page 17 of 18

Assessment # 1 Submission Template
Database Modelling (KC7013)
References
Besio, C. and Pronzini, A., 2014. Morality, ethics, and values outside and inside
organizations: An example of the discourse on climate change. Journal of Business
Ethics, 119(3), pp.287-300.
Thalheim, B., 2013. Entity-relationship modeling: foundations of database technology.
Springer Science & Business Media.
Page 18 of 18