Enhanced Entity Relationship Development And Requirement Specification For A Resource Management System

Summary on Assignment 1

In assignment 1, EER was developed on the data centric method and requirement specifications of the new system. EER was based on Unified modelling language notations. However, some improvements were made based on the previous EER. For example, the newly developed EER does not display the foreign keys in the tables. Also, Category, privileges and course offered details are added to the EER. Apart from that some attributes are added and replaced to the tables to reflect on more details.  The complete requirement specification, EER, data dictionary, normalization process and DBDL representation of relations has been discussed in this report.

In the system, the catalogue is created combinedly using these movable and immovable resources. The main generalized entity of resources is specialized as the immovable and movable items. The details of the different type of items should be shown on the catalogue page. User should be able to view, filter and sort according to the resource type.

Borrower data is stored in the system such as name, id, and contact details. The generalized entity is the borrower it is specialized into staff and student entities. On other hand, a borrower can be either staff or a student. Staff can be subtyped using their qualifications and students can be subtyped based on their degree and course details. These details differentiate a student from a staff.

The reservation and loan data mainly includes dates and charges for resources. Reservations are made for immovable items where the details such as reservation date, start date and end date of the reservation are stored along with mapping of borrower with the immovable resource id. On other hand, the movable items are stored for loan taken by the borrower hence there is a separate entity for the loan details which stores, loan issue date, expected return date and actual return date.

The main system for the SIT resource management manages different type of resources that can be lent by the borrowers. In this case, the Immovable resources are one of the types where user needs to make reservation for the resource. This type of resource cannot be moved or taken by the borrower. For example, a room or a large speaker can be categorized into the immovable objects resource.

On other hand, other types of resources can be categorized into the movable resources such as cameras, phones, or software’s. These types of resources can be taken by the borrower on a specific date and returned on a specified date as loan. This type of resources lend is considered as loan details. The cameras and phones can be lent physically by the borrower. On other hand, software’s can be taken in removable devices or CDs.

Requirement Specification

Transaction is the process of finding, manipulating, or changing data in a database. It can be divided into requests and manipulations

1. Display all the borrower details.
2. Display borrower details who is a student. Also, show the course and degree details.
3. Display borrower details who is a staff. Also, show qualification details of the staff.
4. Display borrower who has failed to return the loan at the expected date.
5. Display borrowers who have not failed any return on the expected date.
6. Display the number of loans for each immovable item.
7. Display the revenue generated from the reservation, loan, and combined.
8. Display the revenue generated from the loan of each item where the charge amount is greater than the overall average loan charge.
9. Display the total amount spent by each of the borrowers.
10. Display the Reservation details for each room order by date descending.

Business rules are organizational policies that help to execute it successfully. These business rules should be carefully considered when designing a database for a business.

1. A borrower can be either a staff or a student. Each borrower can place zero or many reservations or loans.
2. Each loan or reservation can be placed by one and only one borrower.
3. Each Resource can be either movable or immovable. Each movable item is taken as a loan and each immovable item is reserved.
4. Each Immovable or movable item is associated with one and only one reservation or loan at a time.
5. Each resource belongs to one and only one category. Each category can have 0 or many resources.
6. Each Course offerings can have many privileges and each privilege can be assigned to many offerings
7. Each Privileges can belong to one and only one Category and each category have 0 or more privileges.

EER Model: The entity-relationship model (or ER model) describes related things of interest in a particular area of knowledge. The basic ER model consists of entity types (which categorize items of interest) and defines the relationships that can exist between entities (instances of those entity types).

Data Dictionary: A data dictionary is a textual description of data objects and their interrelationships. It is most used to authenticate data requirements and create and manage database systems for database developers. The data dictionary describes the physical properties of the data item.

Figure 1: Enhanced Entity Relationship Diagram

Entity Name	Description	Aliases	Occurrence
Resources	A resource can be used by the staff or student for their project or other purposes	R	All types of resources
Movable	A subtype of resource which can be movable such as camera, speaker, etc.	M	All movable resources
Immovable	A subtype of resource that is not movable such as rooms.	I	All immovable resources
Reservations	A reservation can only be made for immovable resources.	RS	Reservation for immovable resources
Loans	For movable resources, loans are given and recorded.	L	Loans for movable resources
Borrowers	Borrowers can be anyone from a student or a staff.	B	Borrowers combined of student and staff
Staff	A subtype of a borrower who is the only staff.	S	All staffs
Student	A subtype of the borrower is a student.	ST	All students
Category	A Category of the resources for all types of resources.	C	All Categories
Privilege	A privilege for the categories of resources that can be used by a borrower based on their course.	P	All Privileges
CourseOffering	A course offering by the university that student is pursuing.	CO	All courses offered

Entity Name	Multiplicity	Relationship	Multiplicity	Entity Name
Borrowers	1..1	Makes	0..*	Reservations
Borrowers	1..1	Takes	0..*	Loans
Reservations	0..*	Has	1..1	Immovable
Loans	0..*	Has	1..1	Movable
Resources	0..*	Belongs to	1..1	Category
Privilege	0..*	For	1..1	Category
CourseOffering	0..*	Has	0..*	Privilege

Entity Name	Attributes	Description	Data Type Length	Null	Multivalued	Derived	Default
Borrowers	BorrowerID	Unique id for borrower	Int(5)	N	N	N
Bname	Name of borrower	Varchar(100)	N	N	N
Address	Address of the Borrower	Varchar(100)	N	N	N
Contact	Contact number for borrower	Int(11)	Y	N	N
EmailAddress	Email Address for Borrower	Varchar(100)	N	N	N
Status	Status of the borrower	Varchar(15)	N	N	N
Student	StudentID	Unique id for student	Int(5)	N	N	N
CourseName	Coursename student assigned to	Varchar(100)	N	N	N
Degree	Degree student pursuing	Varchar(100)	N	N	N
Staff	StaffID	Unique id to staff	Int(5)	N	N	N
JoiningDate	Joining date of staff	Date	N	N	N
Qualification	Qualification of staff	Varchar(100)	N	N	N
Reservations	ReservationID	Unique Id for reservations	Int(10)	N	N	N
ReservationDate	Date of reservation	Date	N	N	N
StartDate	Start date of reservation	Date	N	N	N
EndDate	End date of reservation	Date	N	N	N
BorrowerID	Unique id for borrower	Int(5)	N	N	N
ResourceNumber	Unique number for resources	Int(10)	N	N	N
Loans	LoanNumber	Unique number for loans	Int(10)	N	N	N
IssueDate	Issue date of loan	Date	N	N	N
ExpectedReturnDate	Expected return date for loan	Date	N	N	N
ActualReturnDate	Actual return date for loan	Date	N	N	N
BorrowerID	Unique id for borrower	Int(5)	N	N	N
ResourceNumber	Unique id for resource	Int(10)	N	N	N
Resources	ResourceNumber	Unique number for resource	Int(10)	N	N	N
Status	Status of the resource whether available or not	Varchar(30)	N	N	N
CategoryCode	Category code for resource type	Varchar(5)	N	N	N
Immovable	ResourceNumber	Unique number for resource	Int(10)	N	N	N
Description	Description of the resource	Char(200)	N	N	N
ReservationCharge	Charge of reservation	Decimal(10,2)	N	N	N
Movable	ResourceNumber	Unique id for resource	Int(10)	N	N	N
Itemname	Item name of resource	Varchar(100)	N	N	N
ItemWeight	Weight of the item	Int(5)	N	N	N
LoanCharge	Charge of the loan	Decimal(10,2)	N	N	N
Manufacturer	Manufacturer of the item	Varchar(50)	N	N	N
Model	Model of the Item	Varchar(40)	N	N	N
AssetValue	Value of the asset in terms of price.	Decimal(10,2)	N	N	N
Year	Manufacturing year of the item	Int(4)	N	N	N

Borrowers (BorrowerID, BName, Address, Contact, EmailAddress, Status)

Primary Key BorrowerID

Alternate Key EmailAddress

Student (StudentID, CourseName, Degree)

Primary Key StudentID

Foreign Key StudentID references Borrower (BorrowerID)

On Update Cascade, On Delete Cascade

Staff (StaffID, JoiningDate, Qualification)

Primary Key StaffID

Foreign Key StaffID references Borrower (BorrowerID)

On Update Cascade, On Delete Cascade 

Category (CategoryCode, Name, Description)

Primary Key CategoryCode

Alternate Key Name 

Resources (ResourceNumber, Status, CategroyCode)

Primary Key ResourceNumber

Foreign Key CategoryCode references Category (CategoryCode)

On Update Cascade, On Delete Cascade 

Immovable (ResourceNumber, Description, ReservationCharge, Capacity)

Primary Key ResourceNumber

Foreign Key ResourceNumber references Resources (ResourceNumber)

On Update Cascade, On Delete Cascade 

Movable (ResourceNumber, ItemName, ItemWeight, LoanCharge, Manufacturer, Model, AssetValue, year)

Primary Key ResourceNumber

Alternate Key ItemName

Foreign Key ResourceNumber references Resources (ResourceNumber)

On Update Cascade, On Delete Cascade 

Reservations (ReservationID, ReservationDate, StartDate, EndDate, BorrowerID, ResourceNumber)

Primary Key ReservationID

Foreign Key BorrowerID references Borrowers (BorrowerID)

On Update Cascade, On Delete Cascade

Foreign Key ResoruceNumber references Immovable (ResourceNumber)

On Update Cascade, On Delete Cascade

Loans (LoanNumber, IssueDate, ExpectedReturnDate, ActualReturnDate, BorrowerID, ResourceNumber)

Primary Key LoanNumber

Foreign Key BorrowerID references Borrowers (BorrowerID)

On Update Cascade, On Delete Cascade

Foreign Key ResourceNumber references Movable (ResourceNumber)

On Update Cascade, On Delete Cascade 

Privilege (PriviID, Description, CategoryCode)

Primary Key PriviID

Foreign Key CategoryCode references Category (CategoryCode)

On Update Cascade, On Delete Cascade 

CourseOffering (OfferID, Year)

Primary Key OfferID 

Functional Dependency: BorrowerID à BName, Address, Contact, EmailAddress, Status

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: StudentID à CourseName, Degree

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: StaffID à JoiningDate, Qualification

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: CategoryCodeàName, Description

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: ResourceNumberà Status

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: ResourceNumber àDescription, ReservationCharge, Capacity

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: ResourceNumber à ItemName, ItemWeight, LoanCharge, Manufacturer, Model, AssetValue, Year

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. However, there is partial functional dependency ItemName à model, Itemweight, manufacturer, assetvalue. Hence it is not normalized in second normal form.

Now a separate table is need to store the model and manufacturer details as there can be multiple items for each item name and model. Revised Movable and item table will be:

Movable (ResourceNumber, ItemName, LoanCharge, Year)

Data Requirements

Item (ItemName, ItemWeight, Model, Manufacturer, AssetValue)

1. Now there is not any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: ReservationID à ReservationDate, StartDate, EndDate, BorrowerID, ResourcceNumber

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: LoanNumber, IssueDate, ExpectedReturnDate, ActualReturnDate, BorrowerID, ResourceNumber

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: OfferID à Year

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF. 

Functional Dependency: PriviID à Description

1. All the values are atomic and there are no repetitive groups hence it is in first normal form.
2. Also, there is no partial functional dependency, hence it is in second normal form.
3. However, a transitive functional dependency is found as PriviID à Description as there is a many to many relationships between privilege and course offering. Hence an intermediate table is required to show the assignment of offeringid and priviID. Newly created relation can be represented as:

CourseHasPrivileges (PriviID, OfferID)

Here PriviID refers to the privilege table and OfferID refers to the CourseOffering table.

1. Now both the foreign keys of CourseHasPrivieges can be created as the Super key of the table.

Borrowers (BorrowerID, BName, Address, Contact, EmailAddress, Status)

Primary Key BorrowerID

Alternate Key EmailAddress

Student (StudentID, CourseName, Degree)

Primary Key StudentID

Foreign Key StudentID references Borrower (BorrowerID)

On Update Cascade, On Delete Cascade 

Staff (StaffID, JoiningDate, Qualification)

Primary Key StaffID

Foreign Key StaffID references Borrower (BorrowerID)

On Update Cascade, On Delete Cascade 

Category (CategoryCode, Name, Description)

Primary Key CategoryCode

Alternate Key Name 

Resources (ResourceNumber, Status, CategroyCode)

Primary Key ResourceNumber

Foreign Key CategoryCode references Category (CategoryCode)

On Update Cascade, On Delete Cascade 

Immovable (ResourceNumber, Description, ReservationCharge, Capacity)

Primary Key ResourceNumber

Foreign Key ResourceNumber references Resources (ResourceNumber)

On Update Cascade, On Delete Cascade

Item (ItemName, ItemWeight, Manufacturer, Model, AssetValue)

Primary Key ItemName

Alternate Key Model

Movable (ResourceNumber, ItemName, LoanCharge, year)

Primary Key ResourceNumber

Alternate Key ItemName

Foreign Key ResourceNumber references Resources (ResourceNumber)

On Update Cascade, On Delete Cascade

Foreign Key ItemName references Item (ItemName)

On Update Cascade, On Delete Cascade

Reservations (ReservationID, ReservationDate, StartDate, EndDate, BorrowerID, ResourceNumber)

Primary Key ReservationID

Foreign Key BorrowerID references Borrowers (BorrowerID)

On Update Cascade, On Delete Cascade

Foreign Key ResoruceNumber references Immovable (ResourceNumber)

On Update Cascade, On Delete Cascade 

Loans (LoanNumber, IssueDate, ExpectedReturnDate, ActualReturnDate, BorrowerID, ResourceNumber)

Primary Key LoanNumber

Foreign Key BorrowerID references Borrowers (BorrowerID)

On Update Cascade, On Delete Cascade

Foreign Key ResourceNumber references Movable (ResourceNumber)

On Update Cascade, On Delete Cascade 

Privilege (PriviID, Description, CategoryCode)

Primary Key PriviID

Foreign Key CategoryCode references Category (CategoryCode)

On Update Cascade, On Delete Cascade 

CourseOffering (OfferID, Year)

Primary Key OfferID

CourseHasPrivileges (PriviID, OfferID)

Primary Key PriviID, OfferID

Foreign Key PriviID references Privilege (PriviID)

On Update Cascade, On Delete Cascade

Foreign Key OfferID references CourseOffering (OfferID)

On Update Cascade, On Delete Cascade 

Al-Atar, M. H. (2021). Key and functional dependency constraints for incomplete databases with limited domains.

Chau, V. T. N., & Chittayasothorn, S. (2021, April). A Bitemporal SQL Database Design Method from the Enhanced Entity-Relationship Model. In 2021 7th International Conference on Engineering, Applied Sciences and Technology (ICEAST) (pp. 85-90). IEEE.

Connolly, T. M., & Begg, C. E. (2005). Database systems: a practical approach to design, implementation, and management. Pearson Education.

Fong, D., & Schurr, A. (2020). Relational database choices and design. In Information Technology for Energy Managers (pp. 255-263). River Publishers.

Fong, J. S., & Wong Ting Yan, K. (2021). Data Normalization. In Information Systems Reengineering, Integration and Normalization (pp. 287-316). Springer, Cham.