Qualification - BTEC Level 5 HND in Computing
Unit Name - Database Design & Development
Unit Number - Unit 4
Unit Code - H/615/1622
Unit Level - Level 5
Assignment Title - Database Design & Development as a Business Solution
Learning Outcome 1: Use an appropriate design tool to design a relational database system for a substantial problem.
Learning Outcome 2: Develop a fully functional relational database system, based on an existing system design.
Learning Outcome 3: Test the system against user and system requirements.
Learning Outcome 4: Produce technical and user documentation.
Hire Our Unit 4 Database Design & Development - BTEC Level 5 HND In Computing Assignment Help Today To Get Top Scores In Exams!!
Assignment Brief
You are employed as an Associate Database Engineer for consultancy company who provides IT solutions for clients. In the beginning of this year, the company has been contacted by Institute of Higher Studies (Pvt) Ltd who is a pioneer in Higher Education. Institute of Higher Studies is a prestigious education and vocational training provider in private sector in Sri Lanka. The campuses of the institute offer various educational courses ranging from certificate level educational courses to higher national diplomas and degrees in IT, English, Teacher Training covering the entire Sri Lanka in every district. The courses are conducted as group classes and individual basis to students at different age levels semester wise.
Executive Summary
The database which is developed will be used by the different users which are student, Program manager and the staff. The database fulfill the functional requirements of all the users. The staff should be able to add the details of the students in the database, the staff members should be update the details of the students in the database and the staff should be able to delete the students who has let the course or who has completed the course. The report provides the functional requirements, database design and implementation of the database.
Part 1
Before you start the development of the database system, Over Team Lead, your project manager has asked you to produce a report for the CEO of IHS, containing the following:
1. The design of the relational database system using appropriate design tools and techniques. It should contain at least four interrelated tables.
Solution:
Introduction
The institute of the higher education is famous institute of the higher studies and the institute is providing the large number of the courses to the different students. The students can enroll in different courses and different departments are there in the different campuses. The report provides the relational design of the database system. The functional and the system requirements of the system are described in the report. The report provides the entity relationship diagram (Al-Masree, 2015) of the database system and the relational structure of the database.
Functional and the system requirements
The functional requirements of the system are discussed in this section.
The database which is developed will be used by the different users which are student, Program manager and the staff. The database fulfill the functional requirements of all the users. The staff should be able to add the details of the students in the database, the staff members should be update the details of the students in the database and the staff should be able to delete the students who has let the course or who has completed the course. The staff should be able to get the analytics data such as how many students are enrolled in each course, what is the age of the different students, which is the semester of the different students, how many students are enrolled in particular course, what is the payment status of the different students, what is the duration of the each course and how batches are running for each course. The staff should be able to get the different reports on the enrollment of the students and which course has been allotted to them for delivery.
The program manager should be ill be able to add the different courses which are offered by the university, the program manager should be able to add the details of the course such as the course name, duration, fees, start date. The program manager should be able to track how many students have enrolled in each course and what is the semester of the students. The program manager should also be able to track how many students have paid fees and how many students have put the registration application. The Program manager should be able to delete the course which are no longer offered by the university. The program manager should be able to update the details of the course.
The student should be able to view the information of the different course which are offered by the university. The student should be able to know the fees and the duration of the different courses which are offered by the university. They should be able to know which batches are available for particular course and the what is the timings of each batch. They should get information about the person who will be delivering the course.
2. Clear statements of user and system requirements.
You would prefer to produce a more detailed document, so you will produce a comprehensive design for a fully functional system which will include interface and output designs, data validations and cover data normalization.
Project manager would like a separate report on your assessment of the effectiveness of the design in relation to user and system requirements.
Solution:
Relational database system
The relational database system of the Institute. The different entities (Weber, 1996) of the database are campus, staff, department, course, student, batch , registration of application. Each of the entity has different attributes associated with it.
• Campus(CampID, CampName, Address, District)
Campus entity represents the different campuses of the institute. The campus entity has the primary key which is CampID which identifies each of the campus uniquely. if the two campuses have the same name then, they will be determined by their campus ID. This will be unique and not null for each campus. The other attributes of the campus entity are campus name, address of the campus and the district of the campus.
• Staff( StaffID, Name, Address, Phone, Salary, CID*);
Staff entity represents the staff members of the different campuses of the institute. The staff entity has the primary key which is StaffID which identifies each of the staff member uniquely. if the two staff members have the same name then, they will be determined by their StaffID. This will be unique and not null for each staff member. The other attributes of the campus entity are name of staff member, address of the staff member, phone number of staff member, salary of staff member and the course delivered by staff member. The CID acts as the foreign key for the relation and protect the referential integrity of the relation and protects the invalid entries in the table. This attribute belongs to course relation.
• Department( DepID, Name, Staff_count, Student_count, Amount_spent, CampID*);
Department entity represents the different departments of the different campuses of the institute. The department entity has the primary key which is DepID which identifies each of the department uniquely. if the two department have the same name then, they will be determined by their DepID. This will be unique and not null for each department. The other attributes of the department entity are name of department, count of the staff member in the department, number of student in department, amount spent by the department and the campus to which the specific department belongs. The CampID acts as the foreign key for the relation and protect the referential integrity of the relation and protects the invalid entries in the table. This attribute belongs to campus relation
• Course( CID, Name, duration, Fees, Start_date, No_of_students, DepID*);
Course entity represents the courses of the different departments of the different campuses of the institute. The course entity has the primary key which is CID which identifies each of the course uniquely. if the two courses have the same name then, they will be determined by their CID. This will be unique and not null for each course. The other attributes of the course entity are name of the course, duration of each course, start date of the each course, number of students in each course and the department to which the specific course belongs. The DepID acts as the foreign key for the relation and protect the referential integrity of the relation and protects the invalid entries in the table. This attribute belongs to department relation.
• Student( SID, Name, Phone, Address, Age, Semester, CID*, Payment_status, CampId*);
Student entity represents the courses of the different students of the different campuses of the institute. The student entity has the primary key which is SID which identifies each of the student uniquely. if the two students have the same name then, they will be determined by their SID. This will be unique and not null for each student. The other attributes of the course entity are name of the student , address of student, age of student. The CID and Campid acts as the foreign key for the relation and protect the referential integrity of the relation and protects the invalid entries in the table. This attribute belongs to course and campus relation.
• Batch( BatchID, CID*, Timings, Course_Progress, No_of_students);
• Registration_Application ( AppID, Initial_name, Full_name, DOB, Age, NIC_no, Contact, landline, Mobile, CID*, Semester, SID*);
Conclusion
The report provides the database design of the institute of the higher education. The implementation of the database will automate the processes of the institute and the institute will not be required to keep the details in the paper base mode. The relational database structure is discussed in the report.
Expert Writers At Your Service to Avail HND Assignment Help And Get Guaranteed Top Grades!
Part 2
Once the designs have been accepted by project manager, you have been asked to develop the database system using evidence of user interface, output and data validations and querying across multiple tables.
You want to include more than just the basics so you will implement a fully functional database system which will include system security and database maintenance features.
The developed system will be demonstrated to project manager and has asked you to produce a report:
1. Assessing whether meaningful data has been extracted through the use of query tools to produce appropriate management information.
Solution:
Implementation of System
Create table Campus (CampID integer(5) primary key, CampName varchar(30), Address varchar(40), District varchar(40));
Create table Department( DepID integer(5) primary key, Name varchar(40), Staff_count integer(5) , Student_count integer(5), Amount_spent integer(5), CampID integer(5), foreign key (CampID) references Campus(CampID));
Create table Course( CID integer(5) primary key, Name varchar(40),Duration integer(5),Fees integer(10), start_date date, No_of_students integer(5), DepID integer(5), foreign key (DepID) references Department(DepID));
Create table Staff( StaffID integer(5) primary key, Name varchar(40), Address varchar(40), phone integer(10), Salary integer(5),CID integer(5), foreign key (CID) references Course(CID));
Create table Batch( BatchID integer(5) primary key, CID integer(5), Timings varchar(40), Course_Progress varchar(40), No_of_students integer(5), foreign key (CID) references Course(CID));
Create table Student( SID, Name integer(5) Primary key, Phone integer(5), Address varchar(40), Age varchar(40), Semester varchar(40), CID integer(5), Payment_status varchar(30), CampId integer(5), foreign key (CID) references Course(CID), foreign key (CampID) references Campus(CampID));
Create table Registration_Application( AppID integer(5) primary key, Initial_name varchar(40), Full_name varchar(40), DOB date, Age integer(5), NIC_no integer(5), Contact integer(5), landline integer(5), Mobile integer(5), CID integer(5), Semester integer(5), foreign key (CID) references Course(CID), foreign key (SID) references Student(SID));
Insertion of data
INSERT into Campus (CampID, CampName , Address , District) values (101, 'Melbourne', 'Australlia', 'East Melbourne');
INSERT into Campus (CampID, CampName , Address , District) values (102, 'Melbourne', 'Australlia', 'Queenland');
INSERT into Campus (CampID, CampName , Address , District) values (103, 'Melbourne', 'Australlia', 'Queenland');
INSERT into Campus (CampID, CampName , Address , District) values (104, 'Melbourne', 'Australlia', 'New South Wales');
INSERT into Department( DepID, Name, Staff_count , Student_count, Amount_spent, CampID) values (1001, 'HR', 7, 100, 1000, 102);
INSERT into Department( DepID, Name, Staff_count , Student_count, Amount_spent, CampID) values (1002, 'HR', 20, 100, 2000, 103);
INSERT into Department( DepID, Name, Staff_count , Student_count, Amount_spent, CampID) values (1003, 'HR', 11, 100, 3000, 104);
INSERT into Department( DepID, Name, Staff_count , Student_count, Amount_spent, CampID) values (1004, 'Marekting', 15, 2000, 1000, 101);
INSERT into Department( DepID, Name, Staff_count , Student_count, Amount_spent, CampID) values (1005, 'Marketing', 7, 3000, 1000, 102);
INSERT into Department( DepID, Name, Staff_count , Student_count, Amount_spent, CampID) values (1006, 'Marketing', 7, 200, 4000, 101);
Querying the data
• select Name, CID from Student;
This query will provide the name of the student and the course taken by the student;
• select count(CID), DepID from course group by DepID;
This query will provide the count of the course offered by each department.
2. Evaluating the effectiveness of the database solution in relation to user and system requirements, and suggest improvements.
Solution:
Evaluation of effectiveness
The database which is developed will be used by the different users which are student, Program manager and the staff. The database fulfill the functional requirements of both the users. The staff will be able to add the details of the students in the database, the staff members will be able to update the details of the students in the database and the staff will be able to delete the students who has let the course or who has completed the course. The staff will be able to get the analytics data such as how many students are enrolled in each course, what is the age of the different students, which is the semester of the different students, how many students are enrolled in particular course, what is the payment status of the different students, what is the duration of the each course and how batches are running for each course. The staff will be able to get the different reports on the enrollment of the students and which course has been allotted to them for delivery.
The program manager will be able to add the different courses which are offered by the university, the program manager will be able to add the details of the course such as the course name, duration, fees, start date. The program manager will be able to track how many students have enrolled in each course and what is the semester of the students. The program manager will also be able to track how many students have paid fees and how many students have put the registration application. The Program manager will be able to delete the course which are no longer offered by the university. The program manager will be able to update the details of the course.
The student will be able to view the information of the different course which are offered by the university. The student will be able to know the fees and the duration of the different courses which are offered by the university. They will be able to know which batches are available for particular course and the what is the timings of each batch. They will get information about the person who will be delivering the course.