Database Modeling Assignment KC7013 - MSc Information Science

This document presents a comprehensive solution to a database modeling assignment (KC7013) for an MSc Information Science program at Northumbria University. The assignment is divided into three parts. Part 1 covers the Entity Relationship (ER) Model, depicting the database schema visually, and the SQL Data Definition Language (DDL) code used to create the database tables. Part 2 focuses on SQL Data Manipulation Language (DML) for populating the database with data, along with SQL queries and their corresponding Relational Algebra expressions to retrieve specific information. The solution includes data similar to the courses and modules studied, as well as other relevant information within Northumbria University. Part 3 addresses professional, legal, ethical, and security issues related to database management, including data privacy, data accuracy, and security threats. It also discusses database design approaches, specifically ER modeling and database normalization. The solution demonstrates a strong understanding of database concepts and their practical implementation.

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