Question

A new technology company would like to build a database to implement mobile money in Jamaica. The company would like to store information on clients who can be categorized as senders or receivers. Clients have a TRN, name, Date of Existence, email, all mobile numbers, address and customer type. A client can be a sender and receiver at the same time. The company would like to know which Client referred another client if a referral occurred. A sender may have a verification pin and pin expiry date where as receivers will have External Id Number (not unique), ID expiry Date, ID Issuing Country and Id Type.

Each Sender can transfer money to a receiver by creating a transaction identified by a system-generated transaction Id. The company would also like to store the transaction amount and date. A set of related transaction can be flagged under an investigation where the investigation will have a reference number, date it started and date ended and the purpose of the investigation. One investigation can contain several transactions as well as one transaction can be linked to several investigations. When each investigation takes place the company would like to assign an auditor to the transaction for that particular investigation. Auditors have an ID, name, Dob, Gender and salary.
Required
a) You are required to model the above scenario in a conceptual diagram. Your diagram should show:
i) cardinalities
ii) entity participation
iii) state any assumptions that do not conflict with the scenario. Further assumptions should be used to clarify only ambiguous statements.
b) Develop the relational notation for your ERD diagram.

Answer 1

The conceptual diagram for a mobile money database involves entities such as Client, Sender, Receiver, Transaction, Investigation, and Auditor, with specific attributes and relationships which include cardinalities and entity participation. Assumptions are made to resolve ambiguities and a relational notation is provided for the ERD.

Step-by-step explanation:

Modeling a Mobile Money Database in Jamaica

To create the conceptual diagram for a mobile money service, we must first establish the entities and their relationships based on the provided requirements. Assumptions are made only to resolve ambiguities not addressed directly in the scenario.

Entities include:

• Client (attributes: TRN, Name, Date of Existence, Email, Mobile Numbers, Address, Customer Type)
• Sender (attributes: Verification Pin, Pin Expiry Date, inherits Client)
• Receiver (attributes: External Id Number, ID Expiry Date, ID Issuing Country, Id Type, inherits Client)
• Transaction (attributes: Transaction Id, Amount, Date)
• Investigation (attributes: Reference Number, Start Date, End Date, Purpose)
• Auditor (attributes: ID, Name, DOB, Gender, Salary)

Relationships and Cardinalities:

• Client to Client (Referral): Many to Many, optional on both sides.
• Sender to Receiver (Transaction): One to Many, participation of Sender is mandatory, Receiver is optional.
• Transaction to Investigation: Many to Many, both sides optional.
• Investigation to Auditor: Many to One, participation of Investigation is mandatory, Auditor is optional.

Assumptions:

• A client can have multiple mobile numbers, and an assumption is made that there is a link table to accommodate this.
• For the purpose of this diagram, External Id Number in Receiver is non-unique and will not be considered a primary key.
• An auditor may be assigned to multiple investigations.

Relational Notation for ERD:

• Client(TRN, Name, Date, Email, Address, CustomerType)
• MobileNumber(MobileNumber, Client_TRN)
• Sender(TRN, VerificationPin, PinExpiryDate)
• Receiver(TRN, ExternalIdNumber, IDExpiryDate, IDIssuingCountry, IDType)
• Transactions(TransactionId, Amount, Date, Sender_TRN, Receiver_TRN)
• Investigation(ReferenceNumber, StartDate, EndDate, Purpose)
• Auditor(AuditorID, Name, DOB, Gender, Salary)
• ClientReferral(Referrer_TRN, Referee_TRN)
• TransactionInvestigation(TransactionId, ReferenceNumber)
• InvestigationAuditor(ReferenceNumber, AuditorID)