I upload assignment task 1
another file is how to write assignment1- logical ERD2- physical ERD3- the relation between them
would you please use this website to do the report go.gliffy.com
CSG1207/CSI4341, S2 2010, Assignment 2 CSG1207D Systems and Database Design Assignment Task 1 - (Database Analysis & Design) Trimester 3, 2018 Assignment: Task 1 (Database Analysis and Design), Task 2(Implementation and Testing) Assignment Marks: Marked out of 120, (30% of unit) Task 1 is marked out of 40, (10% of unit) Task 2 is marked out of 80, (20% of unit) Due Dates: Inform lecturer of group members: Week 3 Submit Task 1 Work Allocation Plan Thu 15/11/2018 (Week 4) Task 1 due 12 noon, Thu 29/11/2018 (Week 6) Task 2 due 12 noon, Thu 17/01/2019 (Week 11) Learning Outcomes (Why are you doing this assignment?) This assignment addresses the following learning outcomes from the Unit Outline: 1. Use Data Analysis to design a Database (Task 1); 3. Implement a Database design using a Database Management system (DBMS), and to construct complex queries upon it (Task 2); General Assignment Information Your first task is to design a database by analysing the requirements for the scenario detailed on the following pages. State any assumptions/notes you have made regarding your database design at the beginning of the database design document. Do not make any assumptions/notes that change the structure of the scenario, as this may make Task 2 of the assignment difficult. Only make assumptions/notes that influence your database design. If you are unsure about an assumption/note you wish to make, ask your lecturer/tutor. Once you have identified the entities, attributes and relationships of the scenario in sufficient depth, you are required to create a logical ER diagram and a physical ER diagram to depict your database. Adhere to the distinctions between logical and physical ER diagrams covered in lectures. Show the Table Creation Order in your physical ERD. Lastly, create a Table Instance Chart (TIC) for each entity in your data model. List your TICs in the appropriate table creation order that will need to be used to create the database. Include any additional information, if any, that may be needed to implement the database. Remember, the TICs should contain all the information needed to implement your database. Download the ‘Sample Table Instance Charts for Assignment Task 1’ from Week 0 in Moodle. Your complete database design should consist of a list of assumptions/notes, logical and physical ER diagrams and TICs. This should be in the form of a single word-processed document. Include an ECC Assignment Coversheet on the first page of this document. Be sure to include details of both team members if appropriate. You must also include the Marking Guide and the Work Allocation Plan (shown at the end of this document) as part of your submission. You must use www.gliffy.com (or any other modelling tool you are familiar with) to draw your ERDs. Show Primary and Foreign Keys as such: CustomerID (PK) and CustID (FK). Do not underline as it is difficult to see and it is even harder when you have an attribute that is a PK as well as an FK. Instead of underlining use this format: OrderID (PK, FK), ProductID (PK, FK). Scenario Details You are required to design and create a database for Books-R-Us, a small general book store that sells books to the public. It would like to create a database to store information about books it sells and orders placed by customers. The following is a description of the entities of interest to the company and a list of attributes. Please note that the attributes listed below are the minimum requested by the users. As an analyst you may add any detail you think is necessary for the design to work well and to support the business requirements. These will be your Notes. Customers The firm stores the following details about their customers: · First name and last name · Gender · Contact numbers · Email Address Orders The firm stores the following details about orders: · An order ID · The date of the order · The date the order was shipped · Details about the customer who placed the order · Details about the books sold, such as quantity, price sold, etc. · Any discounts applied to the order The shipping date must be validated against the order date; it may be the same as the order date or after, but not before the order date. The company may apply one, or none, of the following discounts at the time of purchase: · Staff discount (5%) · Seniors discount (10%) · Loyalty discount (15%) Orders may be received in the following ways, and with the following data requirements: Order type Data to be stored Mail Date received, Clerk who processed the order Web Email address, Reply method Phone Clerk who took the call, Call start time, Customer’s phone number Books The firm stores the following details about books: · The ISBN · The title · The edition of the book · The publisher name · The date published · The cost price, the wholesale price, and the recommended price · The category · The sequel or prequel (if any) of a book · The author order (in the case of books with multiple authors) · The percentage royalty that must be paid to each author The book categories used by the company are as follows: · Fiction · Non-fiction · Romance · History · Business The cost price must not exceed the wholesale price, which in turn must not exceed the recommended retail price. Authors The company stores the following details about the authors of the books they sell: · First name and last name · Contact number · Email address Publishers The company stores the following details about the publishers of the books they sell: · Company name · Contact person and contact details · Email address of the publisher General Information and Guidelines The information above describes all of the entities and relationships required in the database design. Some details, such as the cardinality of some relationships, have been omitted. It is up to you to make and state any assumptions/notes you need in order to complete the database design. If you are uncertain about any part of the scenario described above, seek clarification from your lecturer. It is strongly recommended that, where appropriate, you give each entity an auto-incrementing integer attribute as a primary key. Be sure to specify appropriate data types (and lengths, where applicable) for all columns in your Table Instance Charts. For example, while hourly pay rates are shown with a dollar sign in the table above, they should be stored in some kind of numeric form to allow mathematical operations to be performed on them. Read the scenario details several times to ensure that your database design incorporates all the elements described. If you are uncertain about any part of the scenario described above, seek clarification from your lecturer. Submission Requirements Submit your database design as a single word-processed document by the Task 1 due date above. Include an ECC Assignment and Report Cover Sheet as the first page. Complete a copy of the Work Allocation Plan and the Mark Allocation (last page of this document) and include it in your document, after the ECC Assignment and Report Cover Sheet. YOUR ASSIGNMENT WILL NOT BE MARKED UNLESS IT COMPLIES WITH THESE REQUIREMENTS. You are free to use whichever font you prefer for your assignment but you must ensure the following: 1. Pages must be numbered using the Page x of y format 2. Heading Styles must be used 3. All diagrams must be titled 4. The page orientation for the Table Instance Charts must be changed to landscape to accommodate the width of the tables 5. The document must be spell checked before submission Late submissions will be penalised according to the Unit Outline. PLEASE NOTE: If you are late in submitting your assignment, you should email me a copy and then give me a hard copy in class the following week. I will accept the date/time of your email to me as the date/time of your submission and a deduction for late submission will be applied accordingly. Assignment – Task 1 Work Allocation Plan (Show lecturer in Week 4) Student Names/IDs: ______________________________________________________ Criteria Allocated to Start Date Finish Date Status Task 1 - Design Assumptions/Notes – Any/all assumptions/notes that influence the database design clearly stated. (3 marks) Logical ER Diagram - Diagram accurately depicts the scenario and includes all elements specified in the requirements. (9 marks) Physical ER Diagram - Diagram accurately depicts the scenario and includes all elements specified in the requirements. Diagram is correct translation of the logical ER diagram. Table creation order is shown. (8 marks) Table Instance Charts - Include details of all tables, excluding delete and update rules. The TICs are shown in table creation order. (20 marks) Assignment – Task 1 Marks Allocation Student Names/IDs: ______________________________________________________ Criteria Done By Reviewed By Max Mark Your Mark Task 1 – Analysis and Design Assumptions/Notes – Any/All assumptions/notes that influence the database design clearly stated. 3 Logical ER Diagram - Diagram accurately depicts the scenario and includes all elements specified in the requirements. 9 Physical ER Diagram - Diagram accurately depicts the scenario and includes all elements specified in the requirements. Diagram is correct translation of the logical ER diagram. Table creation order is shown (minus 1 mark if not). 8 Table Instance Charts - Include details of all tables, excluding delete and business rules. The TICs are shown in table creation order (Minus 1 mark if not). 20 Total for Task 1 /40 Feedback and markers comments: __________________________________________________________________________________ ______________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ ~ End of Assignment ~ Page 1 of 5 Assessment and Report Cover Sheet 1. Complete all of the details below - No assessment will be accepted unless this form is completed in full, signed and dated. 2. Submit assessments to your lecturer either in person, via email or via Turnitin STUDENT ID: NAME OF STUDENT: (PRINT CLEARLY) FAMILY NAMEOTHER NAME(S) UNIT CODE: NAME OF LECTURER: RECEIVED BY: TITLE/TOPIC OF ASSESSMENT: “I certify that the attached assessment is my own work and that any material drawn from other sources has been fully acknowledged” Signed:Date: PROCEDURES AND PENALTIES FOR LATE ASSESSMENTS 1. Students who wish to defer an assessment must make an application, in writing, to the Unit Coordinator for an extension of the date for submission. 2. Students applying for an extension of not more than one week after the due date of submission, must obtain approval from the Course Coordinator. 3. Late submission of assessments (without approval) shall incur a penalty as detailed in the Student Handbook. 4. Lecturers will undertake to return your work within 2 weeks of due date. ACADEMIC MISCONDUCT Academic integrity is highly valued at ECC – this means students are required to behave in an ethical manner during their studies.