Database and Design Assignment

HS2021 Database Design and Use

Week 2 - 2020 Tutorial

Date:

Instructions:

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.

Assessment Weight:

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. Finally, 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 item. 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. A courier delivers 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 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.

Student Name: _____________________________________________________

Student ID: ____________________________