# Entity Names and Attributes

#### $1.$ Resident

$-**\_$ResidentID$\_**($Surrogate PK$)$

$-$ FirstName

$-$ LastName

$-$ DateOfBirth $($Date$)$

$-$ Gender

$-$ ContactNumber $($Numeric$)$

$-$ EmergencyContact

$-$ EmergencyContactNumber

#### $2.$ Room

$-**\_$RoomID$\_**($Surrogate PK$)$

$-$ RoomNumber

$-$ Floor

$-$ Type $($varchar$)$

$-$ Capacity $($Numeric$)$

#### $3.$ MedicalRecord

$-**\_$MedicalRecordID$\_**($Surrogate PK$)$

$-\_$ResidentID$\_($FK$)$

$-$ ConditionDescription $($Composite PK$)$

$-$ DateRecorded $($Date$)$

#### $4.$ Medicine

$-**\_$MedicineID$\_**($Surrogate PK$)$

$-$ MedicineName

$-$ Description

#### $5.$ Prescription

$-**\_$PrescriptionID$\_**($Surrogate PK$)$

$-\_$MedicalRecordID$\_**($FK$)$

$-\_$MedicineID$\_**($FK$)$

$-$ Dosage $($Varchar$)$

$6.$ Staff

$-**\_$StaffID$\_**($Surrogate PK$)$

$-$ FirstName

$-$ LastName

$-$ Role

$-$ DateOfHire $($Date$)$

$-$ ContactNumber $($Numeric$)$

$-$ Email $($Alphanumeric$)$

#### $7.$ VisitSchedule

$-**\_$StaffID$\_$ResidentID$\_$Date$\_**($Composite PK$)$

$-\_$StaffID$\_($FK$)$

$-\_$ResidentID$\_($FK$)$

$-$ Date $($Date$)$

$-$ Purpose

#### $8.$ Department

$-**\_$DepartmentID$\_**($Surrogate PK$)$

$-$ Name $($Varchar$)$

$-$ Description

#### $9.$ Staff$\_$Department

$-**\_$StaffID$\_$DepartmentID$\_**($Composite PK$)$

$-\_$StaffID$\_($FK$)$

$-\_$DepartmentID$\_($FK$)$

#### $10.$ FamilyMember

$-**\_$FamilyMemberID$\_**($Surrogate PK$)$

$-\_$ResidentID$\_($FK$)$

$-$ FirstName

$-$ LastName

$-$ Relationship

$-$ ContactNumber $($Numeric$)$

#### $11.$ RoomAssignment

$-**\_$RoomAssignmentID$\_**($Surrogate PK$)$

$-\_$RoomID$\_($FK$)$

$-\_$ResidentID$\_($FK$)$

$-$ AssignmentDate $($Date$)$

#### $12.$ Inventory

$-**\_$InventoryID$\_**($Surrogate PK$)$

$-$ ItemName $($Varchar$)$

$-$ Quantity $($Numeric$)$

$-$ Description

### Relationships, Cardinality, and Participation

$-**$Resident to MedicalRecord$**$: $1$:N

$-$ A resident can have multiple medical records.

$-$ Medical Record includes: ConditionDescription, DateRecorded

$-**$MedicalRecord to Prescription$**$: $1$:N

$-$ A medical record can have multiple prescriptions.

$-$ Prescription includes: Dosage

$-**$Resident to FamilyMember$**$: $1$:N

$-$ A resident can have multiple family members associated.

$-**$Room to RoomAssignment$**$: $1$:N

$-$ A room can have multiple residents assigned over different dates.

$-$ Room includes: RoomNumber, Floor, Type, Capacity

$-**$Resident to RoomAssignment$**$: $1$:N

$-$ A resident can reside in multiple rooms over time.

$-$ Assignment includes: AssignmentDate

$-**$Department to Staff$\_$Department$**$: $1$:N $($Department$-$to$-$Many Staff$)$

$-$ A department can have many staff members.

$-$ Staff$\_$Department composite includes: StaffID$\_$DepartmentID

$-**$Staff to Staff$\_$Department$**$: $1$:N $($Staff$-$to$-$Many Departments$)$

$-$ A staff member can work in many departments.

### Specialization Hierarchy

$-**$Staff$**($Supertype$)$

$-$ Nurse $($Disjoint Subtype$)$

$-$ Doctor $($Disjoint Subtype$)$

### Normalization

$1.**1$NF $($First Normal Form$)**$ ensures each column contains an atomic value, and each record is unique.

$2.**2$NF $($Second Normal Form$)**$ removes partial dependency, ensuring all non$-$key attributes are fully functional dependent on the primary key.

$3.**3$NF $($Third Normal Form$)**$ removes transitive dependencies, ensuring non$-$key attributes are dependent only on the key. come up with ER digram