Reference no: EM133768471

Database Systems - Bachelor of Information Technology

Learning Outcome 1: Analyze the applications of databases to business decision-making.
Learning Outcome 2: Apply database normalization techniques to avoid data anomalies.
Learning Outcome 3: Use SQL to query data from existing databases.

Assessment Objective: To analyze the problem, demonstrate their ability to design, build and query the relational database for the real-world problem.

For group work and report (PART 1), the students should form groups of 2-4 students.
For group activity students must work with their respective group members to fulfil the assessment. Group formations should be completed by WEEK 5.
No individual submission for the group part will be accepted.
You must nominate one person in the group to coordinate group part of the assessment.
The group report must be submitted online in Moodle by ONLY ONE member of each group.
You will not receive any marks for the assignment if your group members collectively report against you for non-participation or non-cooperation.

Assessment Description
Vintage Collectibles E-Commerce Platform

You are provided with an Excel spreadsheet containing data related to a vintage collectibles e-commerce platform. The data shows that there a buyer can purchase different products in a single transaction. It also shows that a seller can sell different vintage products. Your task is to normalize this data, design and implement a relational database schema to efficiently manage and query the platform's operations.

PART 1: Database Design Considerations (GROUP WORK):
Draw the dependency diagram for both tables identifying any partial and transitive dependencies. Clear label the dependencies. Show step by step the process of removing dependencies and brining your tables in 3NF.
Design an ER diagram based on the normalized tables after completion of STEP 1. Make sure that your ERD:
Identify entity relationships, optional/mandatory participation, attributes, and cardinality (one-to- many, many-to-many).
Identify Weak/Strong Relationships
Identify Bridge entities if any.
Vintage Collectibles later informed you that each product listed on their platform undergoes verification by an antique expert who issue their certificate for the product. In your ERD, incorporate an entity called verifier that will store the details of different experts who does verification of the antique products and issues a certificate for products. Each VERIFIER has their own Certificate No. Establish the relationship between Verifier and Products entity and represent it in your final ERD.
Imagine you are the database administrator of your application. You need to create two groups of users for your database: sellers and buyers. Write the appropriate commands to create these user groups and assign them the necessary rights using the GRANT statement to ensure the security of the database and application is maintained.
SUBMISSION GUIDELINES FOR GROUP WORK:

One member of the group will submit the report in WORD FILE format in the provided link on Moodle.
Coversheet should be submitted with each group member's contribution clearly identified and endorsed by all
members of the group.
Use of AI tools is not allowed for this assessment.

PART 2: Implementation Steps: (Individual Work)

Note: Once PART 1 of the assessment is complete, each member of the group should IMPLEMENT DATABASE SEPARATELY. Any similarity with other group member's work will be considered a case of academic misconduct and will be subjected to a penalty. Each member will create their own database and execute their own set of queries.
Schema Definition:
Create a database with YOUR FIRST NAME.
Convert the ER diagram into SQL DDL statements to create all tables.
Specify primary keys, foreign keys, and necessary constraints to maintain data integrity.
Data:
Populate the database tables with some of the data from the Excel spreadsheet. As mentioned in tables given above, one record for buyer should be your name, one with your lecturer's name and three other records with any other random name. The e-mail addresses should be accordingly as well.
Querying
Write SQL queries to get answers of the following questions:

For the seller ID 202, find the products that have price 200 or higher. Query should display price and seller_id column.
Display the names of the products that was purchased in transaction no 200.
List the total number of products purchased by distinct buyers.
Modify the seller table to add a new column called seller_rating.
Update the product table so that each product price is increased by $10.