BIT358 Advance Databases: ER Diagram, Relational Model & Data

This assignment solution for BIT358 Advance Databases covers the creation of an Entity-Relationship (ER) diagram, a relational data logical model, and a data dictionary. The ER diagram outlines the relationships between entities such as courses, students, and subjects, with assumptions made regarding course enrollment and student majors. The relational model translates the ER diagram into a set of tables including Course, Student, Subject, Grade, and Result, defining primary and foreign keys to ensure data integrity. The data dictionary provides detailed information about each attribute within these tables, including data types, formats, ranges, and primary/foreign key designations. References to external resources are provided for further information on ER diagrams and database normalization. Desklib offers similar solved assignments and resources for students.

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]