Data Management: The Queensland College database

This article discusses the conceptual design, logical design, and schema refinement and documentation of the Queensland College database. It includes tables and attributes, types, constraints, and foreign keys.

1
Running head: DATA MANAGEMENT
Data Management:
The Queensland College database
[Student Name]
[University Name]

2
DATA MANAGEMENT
Table of Contents
Section I...........................................................................................................................................3
Task 1: Conceptual design...........................................................................................................3
Task 2: Logical design.................................................................................................................4
Task 3: Schema refinement and documentation..........................................................................5
Reference.........................................................................................................................................7

3
STAFFS
Staffid
Fullname
DOB
Sex
postaladdress
Level
Position
Expertise
SCHOOL
Schoolid
Schoolname
Officelocation
Telephonenumber
Faxnumber
Headstaffid
Deputystaffid
Adminofficerstaffid
Headstartdate
Adminstartdate
OFFICE
Officeid
Roomid
Phonenumber
Building
Staffid
COURSE
Courseid
Classid
Classtype
Weekday
Time
Length
Roomno
Buidingno
Staffid
ACADEMICPROGRAM
Programid
Programname
Director
Courses
Courseid
Coursetype
Creditpoints
STUDENTS
Studentno
DOB
Sex
Nationality
Homeaddress
Dateenrolled
Email
Programid
NEXTOFKIN
Nextofkinid
Name
Phone
Address
Email
Relationship
Studentno
DATA MANAGEMENT
Section I
Task 1: Conceptual design

4
STAFFS
Staffid int(10)
Fullname varchar(200)
DOB date
Sex varchar(200)
Postaladdress varchar(200)
Level varchar(200)
Position varchar(200)
Expertise varchar(200)
SCHOOL
Schoolid int(10)
Schoolname varchar(200)
Officelocation varchar(200)
Telephonenumber varchar(200)
Faxnumber int(20)
Headstaffid int(10)
Deputystaffid int(10)
Adminofficerstaffid int(10)
Headstartdate date
Adminstartdate date
OFFICE
Officeid int(10)
Roomid int(10)
Phonenumber int(20)
Building varchar(200)
Staffid int(10)
COURSE
Courseid int(10)
Classid int(10)
Classtype varchar(200)
Weekday varchar(200)
Time datetime
Length int(10)
Roomno int(10)
Buidingno int(10)
Staffid int(10)
ACADEMICPROGRAM
Programid int(10)
Programname varchar(200)
Director varchar(200)
Courses varchar(200)
Courseid int(10)
Coursetype varchar(200)
Creditpoints varchar(200)
STUDENTS
Studentno int(10)
DOB date
Sex varchar(200)
Nationality varchar(200)
Homeaddress varchar(200)
Dateenrolled date
Email varchar(200)
Programid int(10)
NEXTOFKIN
Nextofkinid int(10)
Name varchar(200)
Phone int(10)
Address varchar(200)
Email varchar(200)
Relationship varchar(200)
Studentno int(10)
DATA MANAGEMENT
Task 2: Logical design
(Raghu , 2015).

5
DATA MANAGEMENT
Task 3: Schema refinement and documentation
In the above entity relation diagram all the tables are in 3-NF and they are related to each other
without any duplication or data redundancy this is due to the following reasons (Ullman, 2016).
i. There is no data redundancy in the tables.
ii. There are no anomalies like the insertions, update and deletions (Bipin, 2014).
iii. All tables have the data integrity.
Table name Attributes Description Type Constraint Comment
SCHOOL
FKS:
Schoolid
Schoolname
Officelocation
Telephonenumber
Faxnumber
Headstaffid
Deputystaffid
Adminofficerstaffid
Headstartdate
Adminstartdate
HeadstaffidSTAFFS(Headstaffid)
Deputystaffid STAFFS(Headstaffid)
Adminofficerstaffid
STAFFS(Headstaffid)
School id
School name
Office locations
Telephone number
Fax number
Head
Deputy
Admin officer
Head start date
Admin start date
int(10)
varchar(200)
varchar(200)
varchar(200)
int(20)
int(10)
int(10)
int(10)
date
date
Primary key
Foreign key
Foreign key
Foreign key
unique
STAFFS Staffid
Fullname
DOB
Sex
Postaladdress
Level
Position
Staff identity
Full names
Date of birth
Sex
Postal address
Level
Position
int(10)
varchar(200)
date
varchar(200)
varchar(200)
varchar(200)
varchar(200)
Primary key unique

6
DATA MANAGEMENT
Expertise Expertise varchar(200)
OFFICE
FKS:
Officeid
Roomid
Phonenumber
Building
Staffid
StaffedSTAFFS(staffid)
Office identity
Room identity
Phone number
Building name
Staff identity
int(10)
int(10)
int(20)
varchar(200)
int(10)
Primary key
Foreign key
unique
COURSE
FKS:
Courseid
Classid
Classtype
Weekday
Time
Length
Roomno
Buidingno
Staffid
StaffedSTAFFS(staffid)
Course identity
Class identity
Class type
Weekday
Time
Length
Room number
Buiding number
Staff identity
int(10)
int(10)
varchar(200)
varchar(200)
datetime
int(10)
int(10)
int(10)
int(10)
Primary key
Foreign key
unique
ACADEMIC
PROGRAM
FKS:
Programid
Programname
Director
Courses
Courseid
Coursetype
Creditpoints
CourseidCOURSE(Courseid)
Program identity
Program name
Director name
Courses
Course identity
Course type
Credit points
int(10)
varchar(200)
varchar(200)
varchar(200)
int(10)
varchar(200)
varchar(200)
Primary key
Foreign key
unique
STUDENTS Studentno
DOB
Sex
Student number
Date of birth
Sex
int(10)
date
varchar(200)
Primary key unique

7
DATA MANAGEMENT
FKS:
Nationality
Homeaddress
Dateenrolled
Email
Programid
Programid PROGRAMS(Programid )
Nationality
Home address
Date enrolled
Email
Program identity
varchar(200)
varchar(200)
date
varchar(200)
int(10) Foreign key
NEXTOFKIN
FKS:
Nextofkinid
Name
Phone
Address
Email
Relationship
Studentno
StudentnoSTUDENTS(Studentno)
Next of kin id
Names
Phone number
Address number
Email address
Relationship
Student number
int(10)
varchar(200)
int(10)
varchar(200)
varchar(200)
varchar(200)
int(10)
Primary key
Foreign key
unique
(Abraham, 2013).

8
DATA MANAGEMENT
Reference
Abraham, S.(2013). Database System Concepts .Michigan:
McGraw-Hill.
Bipin, D.(2014). An Introduction to Database Systems.Boston:
Addison-Wessley Publication.
Raghu ,R.(2015). Database Management Systems. New York:
McGraw-Hill Higher Education
Ullman,D.(2016). Principles of Database Systems. Berlin:
Springer publishers;