Database Design And Use Week 2 Tutorial

Hs2021 Database Design And Useweek 2 2020 Tutorial

This exam has three (3) questions. You are expected to select one question out of three (3) questions and to submit your answer via the blackboard assessment system. This test accounts for five per cent (5%) of total marks. Total marks for the paper 5 marks.

Question A: Create the Entity-Relationship Diagram and the Relational Schema for the following scenario:

BestBank prides itself on having up-to-date information on the current account balance of its customers. To do this, BestBank relies on a company-wide information system.

Customers are the heart of the BestBank information system. Customers are characterized by their customer number (unique), first name, last name, address, and date of birth. A customer can have multiple accounts into the BestBank information system. Accounts are characterized by their account number (unique), account type (i.e., everyday, savings, business), and amount, and they must be assigned to a specific customer. To keep track of their spending habits, BestBank customers can review all the transactions executed using their accounts.

A transaction must be associated with a specific account, and each account can have multiple transactions. Each transaction is characterized by a transaction id (unique), a transaction type (i.e., withdraw or deposit), and the transaction amount.

Question B: Create the Entity-Relationship Diagram and the Relational Schema for the following scenario:

BestDelivery prides itself on having up-to-date information on the status of shipped items. To do this, BestDelivery relies on a company-wide information system.

Items are the heart of the BestDelivery information system. Items are characterized by their item code (unique), delivery status, and destination address. Items are assigned to couriers who are in charge of their delivery. Couriers deliver several items in a day. Couriers are characterized by their employee number (unique), first name, last name, and driving license.

Question C: Create the Entity-Relationship Diagram and the Relational Schema for the following scenario:

BestFreelancer prides itself on having the most efficient platform through which it is possible to find freelancers for any type of work. Freelancers can freely register on the platform and provide information about all the projects they have completed. To do this, BestFreelancer relies on a company-wide information system.

Freelancers are the heart of the BestFreelancer information system. Freelancers are characterized by their profile code (unique), first name, last name, and email. Freelancers can list, within their profile, as many projects as they want.

Projects are characterized by their project code (unique), start date, end date, project title, and project description.

Paper For Above instruction

Introduction

Database design is a critical process in developing effective information systems. It involves creating a detailed data model that accurately represents the real-world entities and relationships within the organization’s domain. Entity-Relationship (ER) diagrams serve as visual tools to conceptualize these data structures, while relational schemas translate these models into tables suitable for database implementation. This paper discusses the design of ER diagrams and relational schemas based on three distinct scenarios: banking, delivery, and freelancing services, each illustrating different aspects of data modeling and normalization processes.

Scenario A: Bank Customer Management System

Entity-Relationship Diagram

The primary entities identified in the scenario are Customer, Account, and Transaction. The Customer entity has attributes such as Customer Number (primary key), First Name, Last Name, Address, and Date of Birth. Customers can own multiple Accounts, suggesting a one-to-many relationship between Customer and Account, where each account is uniquely identified by Account Number and contains attributes like Account Type and Amount.

The Account entity has a foreign key linking it to the Customer entity to enforce ownership. Additionally, the Transaction entity contains Transaction ID, Transaction Type, and Transaction Amount. Each Transaction must be associated with a specific Account, indicating a one-to-many relationship between Account and Transaction.

The ER diagram visually demonstrates these relationships, with Customer connected to Account through a 1:N relationship, and Account linked to Transaction with another 1:N relationship.

Relational Schema

  • Customer: CustomerNumber (PK), FirstName, LastName, Address, DateOfBirth
  • Account: AccountNumber (PK), CustomerNumber (FK), AccountType, Amount
  • Transaction: TransactionID (PK), AccountNumber (FK), TransactionType, TransactionAmount

Scenario B: Delivery Item Tracking System

Entity-Relationship Diagram

The central entities are Item and Courier. The Item entity includes ItemCode (PK), DeliveryStatus, and DestinationAddress. Each item is assigned to a Courier, with courier-specific attributes like EmployeeNumber (PK), FirstName, LastName, and DrivingLicense. Since a courier may deliver multiple items in a day, there exists a one-to-many relationship from Courier to Item, indicating that each courier can handle multiple deliveries.

The relationship reflects assignment of items to couriers, which can be represented as a foreign key in the Item entity pointing to the Courier entity.

Relational Schema

  • Courier: EmployeeNumber (PK), FirstName, LastName, DrivingLicense
  • Item: ItemCode (PK), DeliveryStatus, DestinationAddress, EmployeeNumber (FK)

Scenario C: Freelance Platform

Entity-Relationship Diagram

The primary entities are Freelancer and Project. The Freelancer entity comprises ProfileCode (PK), FirstName, LastName, and Email. The Project entity includes ProjectCode (PK), StartDate, EndDate, ProjectTitle, and ProjectDescription. It is a many-to-many relationship because each freelancer can work on multiple projects, and each project can involve multiple freelancers. To model this, a junction table, such as Freelancer_Project, is introduced with foreign keys referencing both Freelancer and Project entities.

Relational Schema

  • Freelancer: ProfileCode (PK), FirstName, LastName, Email
  • Project: ProjectCode (PK), StartDate, EndDate, ProjectTitle, ProjectDescription
  • Freelancer_Project: ProfileCode (FK), ProjectCode (FK)

Conclusion

Designing ER diagrams and relational schemas for diverse scenarios requires understanding entity relationships, attributes, and constraints. The scenarios presented—banking, delivery, and freelancer management—highlight critical data modeling practices, including handling one-to-many and many-to-many relationships. Accurate ER models facilitate efficient database implementation, ensuring data integrity and supporting organizational decision-making processes.

References

  1. Fundamentals of Database Systems (7th ed.). Pearson.
  2. Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). Pearson.
  3. An Introduction to Database Systems. Pearson.
  4. Database System Concepts (6th ed.). McGraw-Hill.
  5. Database Management Systems (3rd ed.). McGraw-Hill.
  6. Database Systems: The Complete Book. Prentice Hall.
  7. Database Concepts (8th ed.). Pearson.
  8. Fundamentals of Database Systems (6th ed.). Pearson.
  9. Modern Database Management (12th ed.). Pearson.
  10. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design. Addison-Wesley.

Related Assignments