DATABASE DESIGN AND PROGRAMMING

(ESCI/ECII/ECCI 1207)

GROUP 3 SCCJ/2023
MEMBERS
1.TERRY MWIKALI-SCCJ/00637/2023
2.KIMURA MUTAHI-SCCJ/00635/2023
3.ELIJAH KARIUKI-SCCJ/01507/2022
4.ANNA MUOKI-SCCJ/00633/2023
5.JANET CHEPKURUI-SCCJ/00648/2023

1
 TECHNICAL UNIVERSITY OF KENYA STUDENT PROCESSES
PROCESSES
1.Admision and Enrollment

2.Course Registration

3.Payment of fees and Scholarship

4.Academic progress monitoring

5.Student support services

6.Extracullicular Activities

7.Internship and work study progress

8.Graduation process

9.Allumni Relations

10.Hostel Management

1.ADMISSION AND ENROLLMENT

●Application Submission: Students submit their applications online, including academic records,
personal statements, and recommendation letters.

●Verification of Documents: The university verifies all submitted documents (certificates, transcripts).

●Admission Decision: The university's admission office sends out acceptance or rejection letters.

●Orientation Programs: New students participate in orientation programs to get familiar with campus
facilities, policies, and culture.

ENTITIES AND ATTRIBUTES:

 Student
National ID
Phone number
Guardian’s Phone number
Email
Address
Date of Birth

 Application

2
 Application ID
Student documents
Program applied
Admission offer status
 Admission Committee
Committee ID
Name
Department
Decision date
Admission decision
 Course
Course ID
Course name
Credit
Department
Capacity
 Faculty
Faculty ID
Name
Department
Contact

RELATIONSHIP:
 A student submits an Application to the Admission Committee.
 Admission Committee Reviews Application and decides the Admission offer.
 Once admitted, a student enrolls in multiple courses.
 Each Course is taught by a Faculty member.

2.COURSE REGISTRATION

● Course Registration: Admitted students choose and register for their courses for the semester.
● Course Selection: Students choose courses based on their degree requirements, interests, and
availability.
● Prerequisite Verification: The system checks if students meet the prerequisites for the selected
courses.
● Time Slot Allocation: Students register for courses based on available time slots to avoid
scheduling conflicts.
● Approval: In some cases, students may need approval from academic advisors or department
heads.
● Final Enrollment: After the courses are selected and approved, the student is officially enrolled
in the course.

3
ENTITIES AND ATTRIBUTES:

 Course
Course ID
Course name
Credit
Department
Capacity

 Student
Student ID
Name
Program
Year of Study
Status (Full-time/Part-time)
Academic Standing (Good/Probation)
 Registration System

Registration Status (Open/Closed

Registration Start Date

Registration End Date

 Academic Advisor
Advisor Name
Department
Approval Requirement (Yes/No)
Advisor Comments/Notes

RELATIONSHIPS
 Student registers for Course through the Registration System.
 Academic Advisor may approve or review the Student’s course selections.
 Payment System processes tuition related to the Student’s registration.

3.PAYMENT OF FEES AND SCHOLARSHIPS

● Fee Payment: Students pay tuition fees and other associated costs (accommodation, books,
etc.).

● Scholarship and Financial Aid Applications: Students apply for scholarships or financial aid,
followed by an assessment by the university.
● Disbursement of Scholarships/Loans: Approved scholarships or loans are disbursed to students
or credited to their accounts example: the Higher Education Funding

4
● Installment Plans: Some students may opt for installment payment plans, with regular
deadlines.

ENTITIES AND ATTRIBUTES:

 Student
Student ID
Name
Enrollment Status (Full-time/Part-time)
Financial Aid Eligibility (Yes/No)
Outstanding Balance
Payment History
 Tuition Bill
Bill ID
Student ID (Foreign Key)
Amount Due
Due Date
Payment Status (Paid/Unpaid)
Fees (e.g., registration, activity)
 Financial Aid
Aid ID
Student ID (Foreign Key)
Type of Aid (Scholarship, Grant, Loan)
Amount Awarded
Disbursement Date
Renewal Status (Renewed/Not Renewed)
 Scholarship
Scholarship ID
Name of Scholarship
Amount
Eligibility Criteria
Application Deadline
Award Status (Awarded/Not Awarded)
 Loan
Loan ID
Student ID
Principal Amount
Interest Rate
Disbursement Date
Repayment Schedule

5
  Payment Transaction
Transaction ID
Student ID
Amount
Date of Payment
Payment Method (Credit Card, Bank Transfer)
Status (Successful/Failed)

RELATIONSHIPS:

 Student receives Tuition Bill based on enrollment and course registration.

 Student applies for Financial Aid, which may include Scholarships and Loans.
 Financial Aid is awarded to Student based on eligibility criteria.
 Tuition Bill is settled through Payment Transaction initiated by the Student.
 Loan is granted to Student and recorded in their financial records.
 Budget is allocated to various departments and tracked for expenses and remaining
funds.

4.ACADEMIC PROGRESS MONITORING

● Course Registration and Timetable Creation: Students register for classes, and the university
allocates time slots, lecturers, and classrooms.
● Lectures and Classes: Regular classes, practical, and seminars are conducted as per the
academic schedule.
● Assignment and Coursework Submission: Continuous assessment via assignments, projects,
and coursework is an integral part of student evaluation.
● Midterm and Final Exams: Students take midterm tests and final exams, which contribute to
their overall grades.
● Grading and Results: Instructors grade assignments, exams, and projects, and results are
communicated to students.

ENTITIES AND ATTRIBUTE

 Student
Student ID
Name
Program
Year of program
Enrollment Status
 Course
Course ID

6
 Name
Credits
Semester
Instructor
Schedule
 Faculty
Faculty ID
Name
Contact
Department
 Exam
Exam ID
Date
Type
Max marks
 Assignment
Assignment ID
Submission date
Topic
Max marks
 Grade
Grade ID
Course ID
Exam marks
Assignment marks
Final grades

RELATIONSHIPS
 A Student registers for multiple Courses.
 Each Course is taught by a Faculty member
 Student completes assignment and takes exams
 Exams and assignments are graded, and the grade is recorded for the student in each course

5.STUDENT SUPPORT SERVICES

● Academic Advising: Students receive advice from faculty or academic counselors about course
choices, academic difficulties, or career paths.
● Career Counseling and Placement: Career centers offer guidance on internships, job
opportunities, and organize job fairs for students.
● Mental Health and Counseling Services: Universities offer mental health support, counseling
sessions, and wellness programs.

7
● Library and Research Support: Students can access libraries, databases, and research support
services.

ENTITIES AND ATTRIBUTES:

 Student
Student ID
Name
Program
Issues
 Advisor
Advisor ID
Name
Department
Contact
 Counselling Session
Session ID
Date
Duration
Topic
Counselor ID
 Library
Library ID
Book ID
Borrow Date
Return Date

RELATIONSHIPS
 A Student is assigned to an Advisor for academic guidance.
 Student may request a Counseling Session for mental health or academic issues.
 Library loans Books to Students.

6.EXTRA CURRICULAR ACTIVITIES

● Clubs and Societies: Students participate in various student clubs, societies, and activities that
contribute to personal and social development.
● Sports and Recreation: Sports facilities and recreation centers allow students to participate in
competitive and recreational sports.
● Events and Festivals: Universities host cultural, academic, and social events such as seminars,
conferences, and festivals.

8
 ENTITIES AND ATTRIBUTES:
 Student
Student ID
Name
Year of study
Program
Enrollment status
Activities participated
 Club
Club ID
Name of the club
Type
Meeting schedule
Number of members
Leader name
Budget
 Sports team
Team ID
Team name
Sport type
Coach name
Training schedule
Number of players
Team roster
 Event
Event ID
Name of event
Club
Date and time
Location
Number of participants
Event type
 Participation Record
Record ID
Student ID
Activity type
Participation status
Date of joining

9
 Leadership role
 Budget
Budget ID
Club ID
Allocated funds
Expenses
Remaining funds

RELATIONSHIPS
 Student participate in club event or sports team
 Club hosts events
 Student has a participation record associated with club, event, sports team and its
recorded
 Sports team is coached by a coach and has a roster of students
 Club manages a budget for events and activities
 Event is organized by club and participated by students
 Student may hold a leadership role in a Club or sports team

7.INTERSHIP AND WORK STUDY PROGRESS

 Preparation: Students get ready for internships through career services, skill
development, and resume workshops.
 Searching for Internships: They use job portals, career fairs, and networking to find
suitable opportunities.
 Application Process: This involves submitting applications, resumes, and sometimes
cover letters, followed by interviews.
 During the Internship: Interns undergo orientation, receive mentorship, and work on
specific projects.
 Post-Internship: Students receive feedback, reflect on their experiences, and maintain
professional connections.

ENTITIES AND ATTRIBUTES

 Student
Student ID
Name
Email
Program
Year of study
 Internship
Internship ID
Company name
Position
Duration

10
 Start date
End date
Stipend
 Company
Company ID
Name
Industry
Location
Contact information
 Mentor
Mentor ID
Name
Position
Email
Phone number
 Application
Application ID
Date submitted
Status
Resume
Cover letter

RELATIONSHIPS
Student applies for Internship
A student can apply for multiple internships.
An internship can receive applications from multiple students.
Internship is offered by Company
A company can offer multiple internships.
An internship is offered by one company.
Student is mentored by Mentor
A student can have one or more mentors during an internship.
A mentor can guide multiple students.

8.GRADUATION PROCESS

● Graduation Application: Students nearing completion of their academic requirements apply for
graduation.
● Degree Audits: The university checks that students have met all the academic requirements for
their degree.
● Final Examination/Thesis Submission: Students submit their final projects, dissertations, or
participate in viva voce exams (if applicable).

11
● Convocation and Awarding of Degrees: Graduating students participate in the convocation
ceremony, where degrees are officially conferred.

ENTITIES AND ATTRIBUTES

 Student
Student ID
Name
Email
Program
Year of study
Credit earned
 Graduation Application
Application ID
Student ID
Application date
Status
 Degree Audit
Audit ID
Student ID
Audit date
Status
Comments
 Thesis
Submission ID
Student ID
Title
Submission date
Project
Status
 Convocation
Convocation ID
Date
Location
Student ID
Degree awarded

RELATIONSHIPS

● Students take final Exams in each Course.

● Once all requirements are met, Students are awarded a Degree.

12
9.ALUMNI RELATIONS

● Alumni Relations: Universities maintain relationships with alumni through events, newsletters,
and donation programs.
● Job Placement and Career Tracking: Universities often track their graduates’ employment
outcomes and offer alumni career support.
● Further Studies and Continuous Learning: Alumni might return for postgraduate programs,
certifications, or continuing education.

ENTITIES AND ATTRIBUTES:

 Alumni
Alumni ID
Name
Graduation Year
Degree
Current Employment
 Job
Job ID
Tittle
Company
Start Date
Salary
 Alumni Event
Event ID
Name
Date
Participants
 Donation
Donation ID
Alumni ID
Amount
Date

RELATIONSHIPS
 Alumni attend Alumni Events organized by the university.
 Alumni may make Donations to the university.
 The university tracks Alumni employment status and organizes events for networking
and engagement.

13
10.HOSTEL MANAGEMENT

Room Allocation: Assigning rooms to students

Maintenance: Keeping the hostel clean and in good repair
Security: Ensuring the safety of students
Food Management: Providing nutritious meals.
Financial Management: Budgeting and managing expenses.
Record Keeping: Tracking student details and attendance
Using Technology: Implementing systems to streamline management tasks.

ATTRIBUTES AND ENTITIES

Student
Student ID
Name
Age
Gender
Emergency contact
Room Number

Room
Room Number
Room capacity
Room Type

Hostel
Hostel ID
Location
Capacity
Type
Name

Staff

14
Staff ID
Name
Role
Contact number
Hostel ID

Maintenance Request
Request ID
Description
Date reported
Status
Room ID
Staff ID

Payment
Payment ID
Student ID
Amount
Date
Payment method

Health Record
Record ID
Student ID
Checkup Date
Health Issue
Treatment

RELATIONSHIPS
1. A student stays in a room in a hostel.

2. A student can make payments for their stay.

3. A student can submit maintenance requests for their room, handled by staff.

4. A student has one health record.

15
 5. A room belongs to a hostel and can be occupied by multiple students.

QUESTION 2:
DEMI PARCEL DISTRIBUTION LTD

A) Capture all the processes/tasks that happen in DEMI PARCEL DISTRIBUTION LTD
related to customers and parcels (20 Marks)

1. Parcel Reception Process:

 Receive parcels from customers or external partners.

 Verify parcel contents and details (weight, dimensions, sender, and recipient).

 Assign a unique tracking ID to each parcel.

 Update parcel information in the database, linking it to the customer or sender.

2. Customer Registration Process:

 Collect customer details (name, contact information, address, payment

preferences).

 Store customer information securely in the database.

 Assign a unique customer ID for identification and tracking.

3. Parcel Sorting and Categorization:

 Categorize parcels based on destination, size, priority, or special handling

instructions.

16
  Update the database to reflect each parcel's status and destination.

4. Storage and Handling of Parcels:

 Ensure proper storage of parcels in the warehouse or designated areas.

 Track parcel location within the facility for easy retrieval.

 Record any special handling instructions or requirements (e.g., fragile items).

5. Parcel Dispatch Process:

 Schedule delivery routes based on customer location and priority.

 Assign delivery personnel or third-party couriers for parcel distribution.

 Update the parcel's status in the system to "Out for Delivery" and track the real-
time location if applicable.

6. Delivery and Receipt of Parcels:

 Confirm delivery with recipients (either in-person or through alternative means

like locker systems or pick-up points).

 Capture customer signatures or acknowledgment upon delivery.

 Update parcel status to "Delivered" in the database.

7. Customer Payment Process:

 Issue invoices or request payments from customers for parcel services.

17
  Integrate multiple payment methods (credit card, bank transfers, mobile
money, etc.).

 Record payment details and update the customer’s balance.

8. Refund and Dispute Management:

 Handle refund requests for damaged or lost parcels.

 Manage disputes related to parcel handling, billing, or delivery issues.

 Update the system with resolution status and any necessary adjustments to
payments.

9. Tracking and Notification System:

 Send tracking updates to customers via SMS, email, or through an online portal.

 Notify customers of the parcel's current status (received, in transit, out for
delivery, delivered).

10. Customer Support and Inquiry Handling:

 Address customer inquiries related to parcel status, payment issues, or delivery

concerns.

 Log customer complaints or feedback for future reference.

 Ensure that support tickets are created and resolved promptly.

11. Report Generation:

18
  Generate reports for management on parcel volumes, payment collections,
delivery performance, and customer satisfaction.

 Automate recurring reports for better operational oversight.

12. Inventory and Resource Management:

 Track warehouse space availability for storing parcels.

 Monitor delivery fleet availability and performance.

 Maintain a record of resources used in parcel handling (packaging, fuel, etc.).

13. Integration with External Systems:

 Synchronize data with external partners such as courier services or payment

gateways.

 Ensure real-time updates on parcel status and payments.

14. Compliance and Regulatory Procedures:

 Ensure parcels comply with legal and regulatory requirements (e.g., customs for
international parcels).

 Maintain records for audits and reporting.

15. Security Measures for Parcels:

 Implement security protocols to prevent theft or loss of parcels.

19
  Track access to parcel storage areas within the facility.

16. Feedback and Survey Collection:

 Collect customer feedback on parcel handling and delivery services.

 Use survey data to improve service quality and customer experience.

17. Customer Relationship Management:

 Maintain a database of customer preferences and past interactions.

 Offer promotions or discounts to loyal customers based on historical data.

18. Archiving and Data Retention:

 Archive old parcel records and customer interactions.

 Retain data for legal or operational purposes as required.

19. Staff Task Assignment and Monitoring:

 Assign tasks to employees based on real-time workload and parcel volumes.

 Monitor task completion and employee performance.

20. System Backup and Recovery:

20
  Ensure regular backups of customer and parcel data.

 Implement disaster recovery plans in case of system failure.

B) For each of the tasks/processes, capture all the Entities, Attributes & Relationships (40 Marks)

1. Parcel Reception Process:

Entities:
 Parcel
 Customer

Attributes:
Parcel:
 Parcel-ID
 Weight
 Dimensions
 Sender
 Recipient
 Status
Customer:
 Customer-ID
 Name
 Address
 Contact-Info

Relationships:

A Customer sends a Parcel.

A Parcel has a Tracking-ID associated with it.

2. Customer Registration Process:

21
Entities:
Customer

Attributes:
Customer:
 Customer-ID
 Name
 Email
 Phone-Number
 Address
 Payment-Method

Relationships:
A Customer registers to receive services.

3. Parcel Sorting and Categorization:

Entities:
Parcel
Category

Attributes:
Parcel:
 Parcel-ID
 Weight
 Dimensions
 Priority
 Special-Instructions
 Category-ID
Category:
 Category-ID
 Name
 Description

Relationships:
A Parcel belongs to a Category (e.g., Priority, Standard, Fragile).

22
4. Storage and Handling of Parcels:

Entities:

Parcel
Storage-Location

Attributes:
Parcel:
 Parcel-ID
 Storage-Location-ID
 Handling-Instructions
 Status

Storage-Location:
 Location-ID
 Location-Name
 Capacity
 Current-Occupancy

Relationships:
A Parcel is stored in a Storage-Location.

5. Parcel Dispatch Process:

Entities:
Parcel

Delivery-Staff

Attributes:

Parcel:
 Parcel-ID
 Dispatch-Date
 Status
 Destination

Delivery-Staff:

23
Staff-ID
Name
Contact-Info
Assigned-Parcels

Relationships:

A Delivery-Staff member is assigned to a Parcel.

A Parcel is dispatched to a Destination.

6. Delivery and Receipt of Parcels:

Entities:

Parcel

Recipient

Attributes:

Parcel:
 Parcel-ID
 Delivery-Date
 Status
 Recipient-Signature

Recipient:
 Recipient-ID
 Name
 Address,
 Contact-Info
 Parcel-ID

Relationships:

A Recipient receives a Parcel.

A Parcel status changes to "Delivered" once received.

24
7. Customer Payment Process:

Entities:
 Payment
 Customer
 Parcel

Attributes:

Payment:
 Payment-ID
 Amount
 Payment-Date
 Payment-Method
 Payment-Status

Customer:
 Customer-ID
 Name
 Account-Balance

Parcel:
 Parcel-ID
 Associated-Charges

Relationships:

A Customer makes a Payment for a Parcel.

A Parcel has an associated Charge.

8. Refund and Dispute Management:

Entities:

Dispute
Customer
Parcel
Refund

25
Attributes:

Dispute:
 Dispute-ID
 Dispute-Reason
 Status
 Date-Filed
 Parcel-ID

Refund:
 Refund-ID
 Amount
 Refund-Date
 Status
 Parcel-ID

Customer:
 Customer-ID
 Name

Parcel:
 Parcel-ID

Relationships:

A Customer files a Dispute about a Parcel.

A Dispute can lead to a Refund.

9. Tracking and Notification System:

Entities:
Parcel
Notification
Customer

Attributes:

Parcel:
 Parcel-ID
 Current-Location
 Status

26
Notification:
 Notification-ID
 Notification-Type
 Date-Sent
 Delivery-Status
 Customer-ID

Customer:
 Customer-ID
 Name
 Contact-Info

Relationships:

A Customer receives Notifications about the status of a Parcel.

10. Customer Support and Inquiry Handling:

Entities:

Inquiry

Customer

Support-Staff

Attributes:

Inquiry:
 Inquiry-ID
 Type
 Status
 Date-Filed
 Resolution-Status

Support-Staff:
 Staff-ID
 Name
 Assigned-Inquiries

27
Customer:
 Customer-ID
 Name

Relationships:

A Customer files an Inquiry.

A Support-Staff member handles an Inquiry.

11. Report Generation:

Entities:

Report

Parcel

Customer

Attributes:

Report:
 Report-ID,
 Report-Type
 Generated-Date,
 Data-Range

Parcel:
 Parcel-ID,
 Delivery-Time,
 Status

Customer:
 Customer-ID,
 Total-Parcels-Sent

Relationships:

A Report is generated for a Parcel or Customer.

28
12. Inventory and Resource Management:

Entities:

Resource

Parcel

Delivery-Staff

Attributes:

Resource:
 Resource-ID,
 Type,
 Quantity,
 Usage

Delivery-Staff:
 Staff-ID,
 Name,
 Availability

Parcel:
 Parcel-ID,
 Resource-Required

Relationships:

A Parcel requires a Resource.

Delivery-Staff use Resources.

13. Integration with External Systems:

Entities:
External-Partner
Parcel

29
Attributes:
External-Partner:
 Partner-ID
 Name
 Integration-Type
 Last-Sync-Date

Parcel:
 Parcel-ID
 Current-Status
 External-Tracking-ID

Relationships:

A Parcel is linked with an External-Partner for tracking or handling.

14. Compliance and Regulatory Procedures:

Entities:

Parcel
Regulatory-Body

Attributes:

Parcel:
Parcel-ID
Customs-Status
Regulatory-Approval

Regulatory-Body:
 Body-ID
 Name
 Regulation-Type

Relationships:

A Parcel must comply with Regulatory-Body requirements.

30
15. Security Measures for Parcels:

Entities:

Security-Event
Parcel

Attributes:

 Security-Event:
 Event-ID
 Type
 Date
 Action-Taken

Parcel:
 Parcel-ID
 Security-Status

Relationships:

A Security-Event is logged for a Parcel.

16. Feedback and Survey Collection:

Entities:

Feedback

Customer

Parcel

Attributes:

Feedback:
 Feedback-ID

31
  Rating
 Comments
 Date

Customer:
 Customer-ID,
 Name

Parcel:
 Parcel-ID

Relationships:

A Customer provides Feedback for a Parcel.

17. Customer Relationship Management:

Entities:

Customer

Interaction

Attributes:

Customer:
 Customer-ID
 Name
 Loyalty-Status

Interaction:
 Interaction-ID
 Type
 Date
 Outcome

Relationships:

A Customer has an Interaction with the company.

32
18. Archiving and Data Retention:

Entities:

Archived-Record
Parcel

Attributes:

Archived-Record:
 Record-ID
 Record-Type
 Archive-Date

Parcel:
 Parcel-ID
 Status

Relationships:

A Parcel has an associated Archived_Record.

19. Staff Task Assignment and Monitoring:

Entities:

Staff
Task

Attributes:

Staff:
 Staff-ID
 Name
 Role

Task:
 Task-ID

33
  Type
 Deadline
 Status

Relationships:

A Staff member is assigned a Task.

20. System Backup and Recovery:

Entities:

Backup-Record
System

Attributes:

Backup-Record:
 Backup-ID
 Date
 Status

System:
 System-ID,
 Name,
 Last-Backup

Relationships:

A System has a Backup-Record.

34
35