HS2021 Database Design And Use Week 3 Tutorial

Hs2021 Database Design And Useweek 3 2020 Tutorialdateinstructions

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 DDL statement to create tables described in the following Entity-Relationship Diagram.

Question B: Create the DDL statement to create tables described in the following Entity-Relationship Diagram.

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

Paper For Above instruction

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 core entities of the system. Each customer has a unique customer number, first name, last name, address, and date of birth. A customer can hold multiple accounts within the bank’s system. Each account is characterized by a unique account number, account type (e.g., everyday, savings, or business), and current amount. Each account is associated with exactly one customer. Furthermore, customers can review their transaction histories, which include multiple transactions per account. Each transaction has a unique transaction id, a type (withdrawal or deposit), and an amount. The system must model these relationships clearly, ensuring referential integrity between customers, accounts, and transactions.

Similarly, BestDelivery focuses on tracking the status of shipped items. The core entity, Item, is characterized by a unique item code, delivery status, and destination address. Items are assigned to couriers, each represented by a unique employee number, with attributes such as first name, last name, and driving license number. Each courier can handle multiple items within a single day, indicating a one-to-many relationship. The ER diagram should reflect these entities and their relationships, ensuring proper cardinality and dependency.

Lastly, BestFreelancer aims to provide a platform connecting freelancers with projects. Freelancers register and have personal profiles with a unique profile code, first and last names, and email addresses. Each freelancer can register multiple projects, with each project characterized by a unique project code, start and end dates, title, and description. The ER diagram should model these relationships, highlighting the one-to-many relationship from freelancers to projects, ensuring data integrity and clarity in the schema design.

References

  • Fundamentals of Database Systems (7th ed.). Pearson.
  • Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Database Management Systems (3rd ed.). McGraw-Hill.
  • Database System Concepts (7th ed.). McGraw-Hill.
  • A First Course in Database Systems. Pearson.
  • An Introduction to Database Systems (8th ed.). Pearson.
  • Modern Database Management (12th ed.). Pearson.
  • Using MIS (6th ed.). Pearson.
  • Information Systems, 66, 74-86.
  • Journal of Data Management, 11(3), 45-52.

Related Assignments