KC7013 DATABASE MODELLING | SQL

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

DATABASE MODELLING1
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

DATABASE MODELLING2
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,

DATABASE MODELLING3
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,

DATABASE MODELLING4
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)

DATABASE MODELLING5
);
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,