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
- Display all the borrower details.
- Display borrower details who is a student. Also, show the course and degree details.
- Display borrower details who is a staff. Also, show qualification details of the staff.
- Display borrower who has failed to return the loan at the expected date.
- Display borrowers who have not failed any return on the expected date.
- Display the number of loans for each immovable item.
- Display the revenue generated from the reservation, loan, and combined.
- Display the revenue generated from the loan of each item where the charge amount is greater than the overall average loan charge.
- Display the total amount spent by each of the borrowers.
- 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.
- A borrower can be either a staff or a student. Each borrower can place zero or many reservations or loans.
- Each loan or reservation can be placed by one and only one borrower.
- Each Resource can be either movable or immovable. Each movable item is taken as a loan and each immovable item is reserved.
- Each Immovable or movable item is associated with one and only one reservation or loan at a time.
- Each resource belongs to one and only one category. Each category can have 0 or many resources.
- Each Course offerings can have many privileges and each privilege can be assigned to many offerings
- 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)
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
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF.
Functional Dependency: StudentID à CourseName, Degree
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF.
Functional Dependency: StaffID à JoiningDate, Qualification
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF.
Functional Dependency: CategoryCodeàName, Description
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF.
Functional Dependency: ResourceNumberà Status
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF.
Functional Dependency: ResourceNumber àDescription, ReservationCharge, Capacity
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- 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
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- 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)
- 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
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- 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
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF.
Functional Dependency: OfferID à Year
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- Also, it does not have any transitive functional dependency hence it is in third normal form and BCNF.
Functional Dependency: PriviID à Description
- All the values are atomic and there are no repetitive groups hence it is in first normal form.
- Also, there is no partial functional dependency, hence it is in second normal form.
- 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.
- 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.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
