INFO6001 2024 T1 Assignment 2

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

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)

Relationships

Attributes:

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)