Database Schema For Lodge Management System

1 Task 1

1.1 ERD

1.1 Business rules

Many faculties make up the university
One or more school make up the faculty
One or more courses are offered in a school
A student can only enroll in one course. The student enrolls either as part time student or a full time student
The social lodge at the university provides one or more services or facilities to both the part-time and full-time students
Services and facilities at the university result
Student can volunteer for various jobs at the lodge. The volunteer ship which is optional is done during enrollment.
A student may have a special diet that is considered in the lodge.

1.2 3NF relations

Students (studentNO,firstName,familyName,phoneNO,streetNO,city,state,postcode,type)
Roster ( rsID,taskID,studentNO,startDate,endDate,status)
Volunteership (volunteershipID,rosterID,studentNO)
Tasks (taskID,taskName,details,time,jobID)
Special_diet (dietID,studentNO,details)
Jobs (jobID,jobNname,details,fsID)
Course (courseID,courseName,duration,schoolID)
Unit (unitID,unitName,courseID)
Enrollment (enrollmentID, courseID, studentNO, date)
Facilities_and_services (fsID, name, type, description)
Faculty (facultyID, facultyName, dean)
School (schoolID, schoolName, facultyID)

1.3 Data Dictionary

Relation	Attributes	Meaning	Data type	Other
roster	rosterID	Unique primary key identifying a roster	INT	primary key
	taskID	Foreign key referencing a specific task	INT	Foreign key references task.taskID
	studentNO	Foreign key identifying a specific student	INT(8)	foreign key references students.studentID
	startDate	Starting date of the roster	VARCHAR(25)	NOT NULL
	endDate	Ending date of the roster	VARCHAR(25)	NOT NULL
	status	Status of completion of the roster	VARCHAR(10)	NOT NULL Eg “complete”
Students	studentNO	Unique primary key identifying the student	INT	Primary Key
	firstName	Firstname of a student	VARCHAR(50)	Not Null
	familyName	Family name given to a student	VARCHAR(50)	Not Null
	phoneNO	Mobile phone number of the student	VARCHAR(25)	NOT NULL
	streetNO	Street of origin	VARCHAR(50)	NOT NULL
	City	City of origin	VARCHAR(50)	NOT NULL eg “Melbourne”
	State	State of origin	VARCHAR(50)	NOT NULL
	postcode	Postcode of a student	INT(8)	Not Null
	type	Enrollment type; part-time or full time	VARCHAR(25)	NOT NULL eg “full-time”
faculty	facultyID	Unique primary key identifying a student	INT	Primary key
	facultyName	Specific name given to a faculty	VARCHAR(50)	NOT NULL eg “faculty of business”
	dean	Details of the dean in charge of the faculty	VARCHAR(50)	NOT NULL
Enrollment	enrollmentID	Unique primary key identifying the enrollment	INT	Primary key
	courseID	Foreign key referencing a specific course	INT	Foreign key references course.courseID
	StudentNO	Foreign key referencing a specific student	INT	Foreign key references students.studentNO
	date	Date and time the enrollment happened	Timestamp	NOT NULL Current_timestamp
units	unitID	Unique primary key identifying a unit	INT	Primary key
	unitName	Specific name of a unit	VARCHAR(50)	MANDATORY
	courseID	Foreign key referencing a specific course	INT	Foreign key references course.courseID
course	courseID	Unique primary key of a course	INT	Primary key
	courseName	Specific name given to a course	VARCHAR(50)	Not Null
	duration	Years the course takes start to completion	INT	NOT NULL For example “4”
	schoolID	Foreign key referencing a specific school	INT	Foreign key references school .schoolID
School	schoolID	Unique primary key identifying a school	INT	primary key
	schoolName	Specific name of a school	VARCHAR(50)	Not Null For example “School of engineering”
	facultyID	Foreign key referencing a specific faculty	INT	NOT NULL foreign key References faculty.facultyID
Special_diet	dietID	Specific primary key identifying a specific special_diet	INT	primary key
	studnetNO	Foreign key referencing a specific student	INT	foreign key references students.studentID
	description	Details of the special diet	TEXT	NOT NULL
task	taskID	Unique primary key identifying a specific task	INT	primary key
	taskName	Specific name of a task	VARCHAR(50)	NOT NULL
	description	Details of the task	TEXT	NULL
	Time_interval	Time it takes between two tasks	VARCHAR(25)	NOT NULL eg “daily”
Lodge_services	facOrSerID	Unique primary key identifying the lodge service of facility	INT	primary key
	name	Specific name given to the service or facility	VARCHAR(50)	NOT NULL
	type	Service or facility	VARCHAR(25)	NOT NULL Eg “service”
	description	Description of the service or facility	TEXT	NULL
Jobs	JobID	Unique primary key identifying a job	INT	Primary key
	name	Specific name of a job	VARCHAR(50)	NOT NULL
	description	Details of the job	TEXT	NULL
	facOrSerID	Foreign key referencing the specific facility or service in the lodge	INT	NOT NULL foreign key references facility_and_services. facOrSerID
volunteership	volunteershipID	Unique primary key of a volunteership	INT	Primary key
	rosterID	Foreign key referencing a specific roster	INT	foreign key references roster.rosterID
	studentNO	Foreign key referencing a specific student	INT	foreign key references roster.rosterID