1.one office of Ambo University Woliso Campus such as:- Dormitory . design a database for the office that you selected .
2.State clearly the current problem of the office and the purpose of your database to the office.
3.Identify the necessary entities and their attributes for your database. Set appropriate. primary key for each entity accordingly.
4.Identify the relationships and the cardinalities among those entities.
5.Draw the E-R diagram for your database.
1. Dormitory Database
2. Current Problem: The current problem of the dormitory office is inefficiency and manual handling of student dormitory information and administrative tasks. There is a lack of a centralized system to manage room allocation, student information, occupancy status, and other related processes. The purpose of this database is to streamline and automate the dormitory management system, providing a reliable and efficient platform for handling all dormitory-related tasks.
3. Entities and Attributes:
- Student: Student ID (primary key), Name, Gender, Age, Contact Info, Department, Year, Room Number (foreign key), Check-in Date, Check-out Date
- Room: Room Number (primary key), Capacity, Availability (occupied or vacant), Building (foreign key)
- Building: Building ID (primary key), Building Name
4. Relationships and Cardinalities:
- Student and Room: One student can be allocated to only one room, but one room can accommodate multiple students. (1 to many relationship)
- Room and Building: One room belongs to only one building, but one building can have multiple rooms. (1 to many relationship)
5. E-R Diagram:
+---------------+ +-------------------+
| Student | | Room |
+---------------+ +-------------------+
| - Student ID | | - Room Number |
| - Name | | - Capacity |
| - Gender +----| - Availability |
| - Age | | - Building (FK) |
| - Contact Info| +-------------------+
| - Department |
| - Year |
| - Check-in Dt |
| - Check-out Dt|
+---------------+
|
|
|
+---------------+
| Building |
+---------------+
| - Building ID |
| - Building Name|
+---------------+
1. Database design for a Dormitory at Ambo University Woliso Campus:
Entities:
- Dormitory: This entity will store information about the dormitory, such as dormitory ID, name, capacity, and type (male, female, mixed).
- Room: This entity will store information about individual rooms within the dormitory, such as room number, capacity, and status (occupied or available).
- Student: This entity will store information about the students residing in the dormitory, such as student ID, name, gender, phone number, and room number.
Attributes and primary key:
- Dormitory: DormitoryID (primary key), Name, Capacity, Type
- Room: RoomNumber (primary key), Capacity, Status, DormitoryID (foreign key referencing Dormitory table)
- Student: StudentID (primary key), Name, Gender, PhoneNumber, RoomNumber (foreign key referencing Room table)
2. Current problem: The office lacks a systematic way to manage dormitory occupancy, student information, and room availability. The purpose of the database is to provide a solution for managing dormitories and facilitating efficient allocation of rooms to students.
3. Entities and attributes with primary keys:
- Dormitory (DormitoryID, Name, Capacity, Type)
- Room (RoomNumber, Capacity, Status, DormitoryID)
- Student (StudentID, Name, Gender, PhoneNumber, RoomNumber)
4. Relationships and cardinalities:
- One dormitory can have multiple rooms (1-to-many relationship between Dormitory and Room).
- One room can have multiple students (1-to-many relationship between Room and Student).
5. E-R Diagram:
```
+------------+ +-----------+
| Dormitory | | Room |
+------------+ +-----------+
| DormitoryID|<>------------|<<PK>>RoomNumber |
| Name | | Capacity |
| Capacity | | Status |
| Type | | DormitoryID|
+------------+ +-----------+
| |
| 1 N |
| |
+---------+ +---------+
| Student | | Student |
+---------+ +---------+
| StudentID| | Name |
| Name | | Gender |
| Gender | | PhoneNumber|
| PhoneNumber| | RoomNumber|
| RoomNumber|<<FK>>-----------------| |
+---------+ +---------+
```
Note: <<PK>> denotes primary key, <<FK>> denotes foreign key. The cardinalities are shown using "1" and "N" notation.