ER Diagram, Relational Data Logical Model, Data Dictionary

This document provides an ER Diagram, Relational Data Logical Model, and Data Dictionary for the Advanced Databases course. It includes tasks such as creating an ER Diagram, defining the relational data model, and documenting the data dictionary.

BIT358 Advance Databases
May 4
2019
Student ID:
Student Name:
Assessment 2 –
Semester 1

Task1: ER Diagram
(CS Odessa Corp. 2019)
(Cinergix Pty Ltd. 2011)
Assumptions
1. The course is the major of the student.
2. A student will enroll for only one course.
3. There will be number of subjects in a course.

Task2: Relational Data Logical Model
Course (courseCode, course, description)
Primary Key (courseCode)
Student (studentId, studentName, address, phone, email, fatherName, motherName, DOB,
courseCode)
Primary Key (studentId)
Foreign Key (courseCode) References Course (courseCode)
Subject (subjectCode, title, description, courseCode, lecturer, campus, room)
Primary Key (subjectCode)
Foreign Key (courseCode) References Course (courseCode)
Grade (grade, description, range)
Primary Key (grade)
Result (studentId, courseCode , semester, subjectCode, grade)
Primary Key (studentId , courseCode, semester, subjectCode)
Foreign Key (studentId) References Student (studentId)
Foreign Key (courseCode) References Course (courseCode)
Foreign Key (subjectCode) References Subject (subjectCode)
Foreign Key (grade) References Grade (grade)
(1keydata.com 2015)

Task3: Data Dictionary
Table
Name
Attribute
Name
Content
s
Type Forma
t
Range Require
d
PK/
FK
FK
reference
s table
Course
courseCode Char(5) 99999 10000
-
99999
Y PK
course Varchar(30) Aaaaa Y
description Varchar(100
)
Aaaaa Y
Studen
t
studentId Y
studentNam
e
Char(5) 99999 10000
-
99999
Y PK
address Varchar(50) Aaaaa Y
phone Varchar(20) Aaaaa Y
email Varchar(50) Aaaaa Y
fatherName Varchar(30) Aaaaa Y
motherNam
e
Varchar(30) Aaaaa Y
DOB Date dd-
mm-
yyyy
Y
courseCode Char(5) 99999 10000
-
99999
Y FK Course
Grade
grade Char(5) 99999 10000
-
99999
Y PK
description Varchar(50) Aaaaa Y
range Varchar(20) Aaaaa Y
Subjec
t
subjectCode Char(5) 99999 10000
-
99999
Y PK
title Varchar(20) Aaaaa Y
description Varchar(50) Aaaaa Y
courseCode Char(5) 99999 10000 Y

-
99999
lecturer Varchar(20) Aaaaa Y
campus Varchar(20) Aaaaa Y
room Varchar(20) Aaaaa Y
Result
studentId Char(5) 99999 10000
-
99999
Y PK/
FK
Student
courseCode Char(5) 99999 10000
-
99999
Y PK/
FK
Course
semester Varchar(20) Aaaaa Y PK
subjectCode Char(5) 99999 10000
-
99999
Y PK/
FK
Subject
grade Char(5) 99999 10000
-
99999
Y FK Grade

References
Cinergix Pty Ltd. (2011). Ultimate Guide to ER Diagrams. [Online].
Available:http://creately.com/blog/diagrams/er-diagrams-tutorial/. [Accessed: 4-May-2019]
1keydata.com (2015), Database Normalization, [Online]. Available:
http://www.1keydata.com/database-normalization/. [Accessed: 4-May-2019]
CS Odessa Corp. (2019), Entity Relationship Diagram Symbols, [Online]. Available:
http://www.conceptdraw.com/How-To-Guide/erd-entity-relationship-diagram-symbols.
[Accessed: 4-May-2019]