In this assignment, you are to design and implement the Best Deal business database that you have modelled in the assignment-1 and a series of SQL queries to reflect the business logic of the Best Deal.
Note: If your assignment-1 was incomplete to cover the application scenario or incorrect in some parts, you will need to remodel it before completing this assignment. Read the marking comments of assignment-1 carefully and talk to your marker if necessary.
The specification of this assignment
1. Database implementation
• You are to design and implement the ER model of assignment-1, including entities/associative entities, relationships and cardinalities, into a Microsoft Access database.
You need to ensure that your database is compatible with MS Access 2007 for marking purpose.
• You need to ensure that relations of your database meet 3NF.
• Your database needs to include sample data to support the required SQL queries.
2. SQL query
As a way to demonstrate success, you are to implement a series of queries on the database to reflect the business logic of the Best Deal. Note: You are to create the queries manually by using the SQL view of MS Access query builder to demonstrate your competency in SQL. Failing to do so may result in marks to be deducted.
Query-1: List all the sellers to show:
• The seller names
• The seller addresses
Query-2: List all the buyers to show:
• The buyer names
• The buyer addresses
Query-3: List all the contact details of a seller or a buyer to show:
• The buyer/seller name
• The contact type e.g. email
• The contact details e.g. [email protected]
Note: the seller name is a parameter to enter when executing this query in Microsoft Access. A seller or buyer could have more than one contact e.g. email, mobile and home phone.
Query-4: List all the sellers and the products that they have already sold to show:
• The seller name
• The product brand, model , sold price and sold amount
Query-5: List all the listings that have no bids to be put for them so far to show:
• The product ID, brand and model
• The listing numbers
Query-6: List all the applications for all used phones to show
• The product ID, brand and model
• The data, call rates and network
• The weekly rent
• The application numbers
Query-7: List sellers and the benefit they have made to show:
• The customer name
• The product ID, brand and model
• The data, call rates and network
• The weekly rent and contract weeks
• The benefit that can be made in dollars
Query-8: List all sellers who had obtained quick delivery comments to show:
• The seller name
• The complete comment
3. Query Report
Generate a Microsoft Access report for the biggest bid of each listing to show:
• The listing ID
• The brand and model
• The starting price of the listing and the biggest bid for it
• The bidder name and all of his/her contact details
4. Implementation document
You are to write a short report to demonstrate that you have thought carefully about the issues that arise when implementing the database. You may address:
• The multiple implementation choices, such as the data type and size selection for a particular attribute or the cardinality of a relationship etc and justify your selection.
• The major issues that you have encountered for the database implementation.
• The major issues that you have encountered for creating queries/reports.