I attached the file with the assignment details
Assessment 1 Relational model and ER diagrams CSE2DCX Database Fundamentals on the Cloud CSE2DCX – Assessment 1 2 Before you begin Objectives This is an individual assignment. Students are not permitted to work in a group when writing this assignment. Copying and Plagiarism This is an individual assignment. Students are not permitted to work in a group when writing this assignment. Plagiarism is the submission of another person’s work in a manner that gives the impression that the work is their own. La Trobe University treats plagiarism seriously. When detected, penalties are strictly imposed. Further information can be found on http://www.latrobe.edu.au/students/academic- integrity/explanation/plagiarism Submission Guidelines Your assignment submission should be typed, not written/drawn by hand. Submit the electronic copy of your assignment through the subject LMS. Submission after the deadline will incur a penalty of 5% of the available assignment mark per day capped at 5 days. No assignment will be accepted after 5 days. If you have encountered difficulties that lead to late submission or no submission, you should apply for special consideration. CSE2DBX – Assessment 1 2020 Edition 3 Background Adam’s auction house has been in the auction business for over a century. They were quite successful until the last decade but since then have been struggling due to dwindling numbers of buyers and sellers. They believe this is due to the rise of eCommerce and have therefore decided to move their business online. The auction house has decided to handover the design of the database for this project to an Information Technology expert, you! Below is a description of the data needed and how it interrelates. You are expected to model this information via an ER / EER diagram and map it to relations. The database will need to model information regarding three individuals that the application will be concerned with: buyer, seller and auctioneer. Each person will have some common information recorded against them, including their ID, first name, last name, date of birth, phone, address 1, address 2, suburb, postcode, email, and type (which can be “buyer”, “seller”, or “auctioneer”). In addition to the general information above, these people will have some additional information that pertains to them individually. For example, payment info for buyers and sellers and working hours for auctioneer. Working hours will include information about the shift such as Shift_ID, Shift_Date, start and finish times. You will also need to store information regarding items for sale, this will include Item_ID, Description, Sale_Date, Sale_Time, Reserve_Price and Item_Size. While information regarding bids will also be stored, this includes Initial_Bid, Current_Bid and Remaining_Time. Buyers can bid for items which are available for sale, they can bid on multiple items at the same time. Also note that generally multiple buyers will be bidding for the same item. The seller can put items up for sale. Whereas each item will have an auctioneer associated with it who will manage the auction process. Tasks The assessment consists of two tasks details for which are as follows, Task 1 Draw an ER/EER diagram for the above-mentioned database in Microsoft Visio 2016 using Crow’s Foot Database Notation. For each entity: 1. Give the entity an appropriate name. 2. Include all attributes. Be aware that not all primary keys and/or foreign keys may have been explicitly mentioned in the problem statement above. It is your job to add these as necessary for a correct solution. 3. Annotate the primary key attribute(s) as . 4. Annotate the foreign key attribute(s) as . For each relationship: CSE2DCX – Assessment 1 4 5. Annotate relationships between entities. Use relationships such as one to one or one to many where needed. 6. Use an arrow to represent any inheritance relationship(s). 7. Add a label to each relationship. For example, if you wanted to model the relationship between a Supplier and a Product in an inventory database, you could use the annotation “provides” to say that supplier “provides” product. [24 marks] Task 2 In this task, you will create a relational model for the above-mentioned database. 1. Map the above diagram from Task 1 to a relational model for all relations in Microsoft Word. For example, for a product relation, Product(ProductID, ProductName, Price, SupplierID), the primary key attribute(s) are in underline, the foreign key attribute(s) are in italics, and both styles would need to be applied if an attribute is both primary and foreign. 2. You must also check that your solutions for Tasks 1 and 2 are compatible with each other. Update your solution(s) as necessary. [16 marks] When you have completed, submit the solutions to these tasks via the Name of Upload link on the LMS. You should submit the following: Submitting your Assignment When you have completed your answers, submit the assessment on the LMS system. You should submit the following: • Submit your Task 1 answers in a drawing called xxx_cse2dcx_assessment1.vsdx (where xxx is your student number). Submit your Task 2 answers in a document called xxx_cse2dcx_assessment1.docx (where xxx is your student number). CSE2DBX – Assessment 1 2020 Edition 5 Assessment Marking Criteria Task Marks Description Total 1.1 4 All entities have been identified and named correctly. 4 3 1-2 entities have been identified or named incorrectly. 2 3-4 entities have been identified or named incorrectly. 1 5-7 entities have been identified or named incorrectly. 0 All entities have been identified or named incorrectly. 1.2 4 All non-key attributes in all entities were correct. 4 3 A number of non-key attributes in 1-2 entities were incorrect. 2 A number of non-key attributes in 3-4 entities were incorrect. 1 A number of non-key attributes in 5-7 entities were incorrect. 0 A number of non-key attributes in all entities were incorrect. 1.3 4 All primary key attributes in all entities were correct. 4 3 A number of primary key attributes in 1-2 entities were incorrect. 2 A number of primary key attributes in 3-4 entities were incorrect. 1 A number of primary key attributes in 5-7 entities were incorrect. 0 A number of primary key attributes in all entities were incorrect. 1.4 3 All foreign key attributes in all entities were correct. 3 2 A number of foreign key attributes in 1-2 entities were incorrect. 1 A number of foreign key attributes in 3-5 entities were incorrect. 0 A number of foreign key attributes in all entities were incorrect. 1.5 3 Cardinality was correct on all association relationships. 3 2 Cardinality was incorrect on 1-2 association relationships. 1 Cardinality was incorrect on 3-5 association relationships. 0 Cardinality was incorrect on all association relationships. 1.6 3 Any/all generalisation relationship(s) were identified and implemented correctly. 3 2 Any/all generalisation relationship(s) were identified and implemented correctly in part only. 1 Any/all generalisation relationship(s) were identified but were not implemented correctly. 0 Any/all generalisation relationship(s) were not identified. 1.7 3 Labels were correct on all association relationships. 3 2 Labels was incorrect on 1-2 association relationships. 1 Labels was incorrect on 3-5 association relationships. 0 Labels was incorrect on all association relationships. 2.1 8 The relational model syntax is fully correct. 8 6 The relational model syntax is mostly correct. 4 The relational model syntax is partly correct. 2 The relational model syntax is seldom correct. 0 The relational model syntax is incorrect. 2.2 8 The Task 1 and Task 2 solutions fully agree with each other. 8 6 The Task 1 and Task 2 solutions mostly agree with each other. 4 The Task 1 and Task 2 solutions partly agree with each other. 2 The Task 1 and Task 2 solutions seldom agree with each other. 0 The Task 1 and Task 2 solutions do not agree with each other. TOTAL 40 CSE2DCX – Assessment 1 6 © Didasko 2020. All rights reserved. 6