Question

A boarding school in your area has asked you to design a simple system so that they could easily identify the students who are staying in the hostel rooms as well as the wardens who are in charge of each student blocks.

Each staff may be in charge of guarding a hostel block. This is not permanent because after certain duration the staff will change blocks. The guarding duty is rotational and not all the staff are required to do this duty. So it is important to keep track of the staff number, staff name and staff contact number as well as the start date and end dates of their guarding duties. The block name and location must also be recorded.

Each hostel block will have many rooms. The room details will be room number and room level. A student can occupy a room but might change rooms in different school terms. A room can be occupied by many students but in different terms. Not all rooms in a hostel block is used for student occupancy. Some rooms are used as store rooms and pantry.

There are many clubs in the school. The clubs are important so that students can enroll in extra co-curricular activities. The school has made it a rule that each student must enroll in at least one club. A club will have many students enrolled as members. The club details will be club name, club established date and the club fee. When a student registers in a club, the date of enrollment must be recorded.

Each student in the school will be assigned under one academic staff. A staff may be in charge of looking after many students. Not all staff are assigned students. The administrative staff will not be assigned any students. Once a student is assigned under the care of a staff, it will be permanent until the day they end their studies in the school. It is very important to know which staff is assigned to which student. Student’s details such as student number, name, name of their guardian as well as the guardian’s contact number must be recorded in case they need to be contacted.

Based on the situation given above, draw a complete Entity Relationship Diagram using the Crow’s Foot notation which includes:

(i)

All entities and attributes

(ii)

Relationships

(iii)

Connectivity and relationship participation

(iv)

Primary and foreign keys

Answer 1

Entity- Entity is anything like person, place which has attributes that describes the entity.

Entities for given scenario with primary keys underlined.

Students(st-name, st_num, st-guardian, g-contact)

Rooms( r-level, r-num)

club(c_name, c_date, c_fee)

staff(s_no,s_name,s_contact,s_start_date,s_end_date)

Block(b_name,b_location)

Assumptions

Block and rooms , students and club have total participation.

Foreign keys

Student- c_name, r_num,s_no

Room- b_name, st_num

Cardinalities

​​​​​​One-to-many

Student and room- one room can have two or more students and many students can be accommodated in one room.

Rooms and block- each block have multiple rooms while one room will be located only in one block.

Mandatory many

between club and student as each student must enroll in a club and club can have many students enrolled in it.

Between block and staff as block will definitely have staff and staff can be incharge of multiple blocks.

Block and staff