INFO6001 2024 T1
Assignment 2
Project: Database design of SCS Resource Management
Logical Database Design
(Please note: this is a template only. You are suggested to follow this templet in completing your A2, with all needed content.)
Preface
This is the requirements analysis, conceptual design and logical database design for the implementation of the database of SCS Resource Management.
The requirements analysis includes the data requirements, transaction requirements and business rules. This will ensure that all the data required to be stored is identified, can be manipulated and is managed according to the store’s business policies.
The conceptual design includes an extended entity relationship diagram in UML which describes the required entities, their attributes and their relationships. It is further clarified with a data dictionary for the entities, attributes and also the relationships. This conceptual model was developed from the requirements analysis.
From the conceptual model, a relational model is established and presented in the form. of DBML, which is then normalised to BCNF, with a discussion of the steps to produce to logical design of the database.
In this report, I first present …., then …., and finally …
Part 1: Reflection on Assignment 1
This section presents necessary discussion to point out the differences between my submitted EER for assignment 1 and the solution EER.
In my submitted EER for assignment 1, ….
In the following discussions, the provided solution EER will be used in all the discussions.
Part 2: Requirements
Data Requirements
· Loan Service
Loan
Loan describes loans that members have made. It is created when a loan is made by a member.
Each member can loan movable resources, the number of resources the member can loan is dependent on the privileges. As staff does not have privilege, the amount of resource they can loan are not restricted. Information stored include (the resource loaned, the member lending it), date and time loaned, due date and time and date and time returned. All loans have a unique loan id. The due date will depend on the duration allowed by the category.
More on other data…
Transaction Requirements
Data Manipulation
Insert, Update and Delete existing Loan
More …
Queries
× Search a loan based on loanID.
More…
Business Rules
· Student member set to ‘Disabled’ if the current date is later than end date of his course offering
More …
Part 2: EER Model
EER Model
Data Dictionary
Entity
Note: xxx entities (including sup & sub)
Entity Name
|
Description
|
Aliases
|
Occurrence
|
Loan
|
describing loans that members have made
|
Resource borrowed
|
When a loan is made by a member
|
…
|
Relationships
Entity Name
|
Multiplicity
|
Relationship
|
Multiplicity
|
Entity Name
|
Loan
|
0..*
|
is of
|
1..1
|
Member
|
0..*
|
to
|
1..1
|
Movable
|
…
|
|
|
|
|
Attributes:
Entity
|
Attributes
|
Description
|
Data Type & Length
|
Nulls
|
Multi-valued
|
Derived
|
Default
|
Loan
|
LoanID
|
|
|
|
|
|
|
|
dateOfLoan
|
The date the resource is loaned out
|
date
|
FALSE
|
FALSE
|
FALSE
|
current date
|
|
dateOfDue
|
The date the resource is due for return
|
date
|
FALSE
|
FALSE
|
FALSE
|
|
|
dateOfReturn
|
The date the resource is returned
|
date
|
TRUE
|
FALSE
|
TRUE
|
|
…
|
|
|
|
|
|
|
|
Part 4: Mapping the EER to Relational Model
Using the mapping rules, got the following relations for all entities in EER.
For MovableResource
MovableResource (resourceID, name, manufacturer, model, year, assetValue, BuildingId)
Primary Key resourceID
Foreign Key resourceID References Resource (resourceID)
And more …
Part 5: Normalising the Scheme up to BCNF
According to the definitions of 1NF, 2NF, 3NF and BCNF, it is identified that relations x1, x2, ..xn are all in BCNF, since all the attributes are atomic, and there exists only one function dependency in each table, and the left side of the FD is a PK.
But the following relations are not in BCNF. They are normalized as below.
MovableResource (resourceID, name, manufacturer, model, year, assetValue, BuildingId))
Primary Key resourceID
FD1: resourceID -> name, model, year, assetValue, BuildingId
FD2:, model -> manufacturer
So MovableResource is not in 3rd norm form.
Normalising to 3rd norm form.
MovableResourceModel(model, manufacturer)
PK model
MovableResourceMain(resourceID, name, model, year, assetValue, BuildingId)
PK resourceID
FK model References MovableResourceModel (model)