Construct an entity-relationship model for the database- Software Engineering
This is a complete IT based report so I request that you assign this to someone with ample knowledge in the field of data base architecture.
ER Diagram
There are several notation styles of ER diagrams in common usage (Refer to Appendix). You may use whichever style you feel comfortable. Your ER diagram should contain the following components: Entities, named relationships, attributes (keys) and cardinality for relationships. Logic behind the ER model is what attracts marks not the style of notation used.
A faster way of doing the ER diagram is to sketch it up on a blackboard, take a digital photograph and paste it in your report. Alternatively you may use any software tools at your disposal - Lucid Charts is a good one. If hand-drawn please make sure your ER diagram is legible.
Specification
Parts & Plumbers Online is a small, but rapidly growing business operating out of Geelong. The owner, Jason Lenon who is a plumber by trade, started this business to sell various plumbing products online and book household plumbing services with his customer base. Selling plumbing parts alone was not a profitable occupation, so Jason also branched out into selling other types of physical goods such as tools, plants and animal feed to the local community.
Within a year of beginning his operation Jason was approached by his friends who were also interested in selling various specialised goods that typical shops were not interested in selling (e.g. gourmet food, herbal remedies, organic fertilizers, adhoc farming equipment, homemade spice concoctions etc.) At the same time he is approached by others (handymen, plumbers, electricians, babysitters, mechanics etc.) seeking his help in getting their services booked with customers. All service bookings must specify a venue or a job site where the work is to be carried out. While most clients are from Victoria, the service bookings originate from all Australian states and territories.
To encourage bulk buying, sellers offer a 10% discount for order quantities of 10 or more. Individual
buyers purchasing 10+ items would qualify for the discount. An individual buyer falling short of the 10- item limit could still receive the discount by getting into a “buyer’s group”. Jason would assign such buyers into a group and make a single purchase of 10+ items then distribute the items amongst group members.
Three years later, Jason finds that he has become an agent, the go-between for over 500 buyers and sellers. He is generating a reasonable income from the commissions charged (5% of total transaction value). The household service bookings business has also expanded and he has over 50 service providers depending on him for finding work, and the demand is still increasing.
Parts & Plumbers Online now has the following operations:
– Acting on behalf of sellers to find a buyer
– Acting on behalf of buyers to find a seller
– Booking households with service providers for odd jobs around the house
– Booking service providers with households
So far his business has been run with a ledger book and a calendar (paper-based), but these tools are becoming inefficient, and increasingly inapt for managing the growing demands of the business. A transition to an information system is urgently needed.
You are employed as a business analyst at Deakin Innovative Solutions Pty Ltd, a business-consulting group. You are assigned to investigate and develop an ER model for the system, generate required SQL based business reports for the business owners and make recommendations.
Note: The design of any payments system is outside the scope of this project.
Business Reports
The business owners have requested that the new system needs to be able to generate the following business reports:
i. List of all female buyers who are adults sorted by their last name.
ii. List of all new buyers and sellers who have joined Parts & Plumbers Online after the 1st of January 2016 ordered by the joined date. This list should not have any repeated entries (if a person has sold an item and also bought an item, that person should not be listed twice.)
iii. List of all current service provider bookings sorted by the type of service and booking date (Most recent first).
iv. Count of households from each postcode in the database.
v. (Research Required) A monthly report on households, type of service and the service provider. Hint: You may have to join various tables in SQL to achieve the desired output, lookup join command.
You are required to perform the following tasks in this assignment.
Task
1. Construct an entity-relationship (ER) model for the database. Make sure you include in your model details of entities, relationships, attributes, keys and cardinality for relationships.
a. List any assumptions made and ensure that you give adequate justification.
b. List any important business rules.
2. Show by providing SQL statements, that all of the reports listed in the “Business Reports” section above can be produced from your ER diagram.
a. NOTE: You do not need to actually create the database and run the queries. This is a thought exercise to show that the queries are possible based on your ER diagram.
3. Produce a business report (with ER diagram and SQL queries included within) according to the format outlined in ” Guide to Writing a Business Report.pdf” 10 Marks
Please go through the ERD notes and make the ERD’s in the same format. If you could send me part by part updates on this assignment it would be good. Since the time is less I wouldnt want to keep a lot of corrections pending in the last moment.
