SOLUTION: Designing a Database for BigM | My Assignment Tutor

1804ICT & 7003ICT – Assignment Part 1 Page 1 2814ICT – DATA MANAGEMENT7003ICT – DATABASE DESIGNSchool of Information & Communication TechnologyTrimester 1, 2021Assignment Part 1:Designing a Database for BigM AimsTo gain experience in designing a database using Entity Relationship Diagram (ERD),Normalisation, and Relational Database modelling techniques.Learning ObjectivesIn the process of this assessment task you will: plan, schedule and execute project tasks with a view to improving your personal andgroup productivity; gain awareness of the typical challenges related to the design of practical databases; learn that database design is an iterative process; and use the ERD, Normalisation techniques, and Relational models to develop elegantlogical models for a database. Due date:Week 6, 23 Apr Fri 5:00 pmOne submission per group, file named as ‘a1-.pdf’.Any student’s ID in the group is fine.Each group member will have a short presentation or viva.Latesubmission:Any submission after the due date will receive a deduction of10% per day. Standard university policy will apply for all latesubmissions. See the course website/profile for detail.Marks:(a) Group submission: a total 100 marks and it is worth 7% outof the total assessment, plus (b) individual viva/presentation: 3%out of the total assessment.Extensions:An extension will only be considered with supportingdocumentation from a health professional and if theproblem/illness occurred within the week prior to the duedate. If an extension is granted the extension will then equal thenumber of days specified on the doctor’s certificate, with amaximum limit of five (5) working days.Authorship:This assignment is a Group assignment and it shall becompleted by the students in each group only. The finalsubmission must be identifiably the work of the individualgroup members. Breaches of this requirement will result in anassignment not being accepted for assessment and may result inthe offending student or students being required to presentbefore the Disciplinary Committee. 2814ICT & 7003ICT – Assignment Part 1 Page 2 of 3Designing a Database for BigMIntroductionThe proprietors of BigM have approached you and asked if you could design a database tohelp them manage their business. The management has commissioned you (in your capacityas a Database Management System consultant) to analyse, design and develop an appropriateconceptual data model and relational database schema, based on the following informationgathered about the current business activities.Business Rules (for both 2814ict and 7003ICT students): BigM operates stores in many cities in Australia. Stores are referenced bystore number. BigM also keeps store name, phone, fax, email for each store.Each store has several departments, for example, finance, accounts, sales,customer service etc. A department can appear in many stores. For each department, there is a department ID, department name, phone, and e-mailaddress. Each department has several employees. For each employee, BigM keeps arecord of their employee ID, first name, last name, phone, date of birth, startdate of his/her work, hourly rate, annual salary, and tax file number.Each store is managed by an employee as a store manager and each department is supervised by an employee. The department supervisor is alsothe supervisor for all the staffs within that department. Each store may be assigned a supervising store where all training, payroll,server application and help desk are located.BigM sells different products like CDs, cloths, computers, etc. For eachproduct it stores product number, product description, product size and price.Customer may place orders in the store. Customer details are always takenat each order. A customer is referenced by a customer number, customerfirst & last names, and phone number, if available.A customer may order more than one product at a time, and they may order multiple copies of the same product. BigM also records the date a productarrives and the date when it is picked up by the customer. Note that thesedates may be different for each product.Additional business rules for 7003ICT students only: Each supervising store generates pay slips for all staffs (in this store andother stores being supervised) on a fortnightly basis. For each pay slip, thestore records a pay ID, supervising store ID, employee ID, pay date,number of hours and the gross payment. An inventory of the number of each product in each store is kept. BigMkeeps track of the quantity of each product that is on order, as well as thenumber currently available in each store.BigM understands that they may not have provided you with sufficient information.If you need to make assumptions about their organisation, please ensure that yourecord them.2814ICT & 7003ICT – Assignment Part 1 Page 3 of 3Assessable Tasksa) Group SubmissionFrom the BigM business requirements specified above, prepare a document according tothe followings:1. Use the supplied template for your Assignment submission.2. An appropriate title page that includes the signatures of all studentsin the group and an acknowledgement of all students and staff you havespoken to about the assignment.3. A table of contents and page numbers.4. A logical ERD using the Crow’s Foot notation only. The diagram should include:a) all entities, attributes, and relationships (including names);b) primary keys (underlined) and foreign keys (italic) identified;c) cardinality and modality symbols; andd) assumptions you have made, e.g., how you arrived at the cardinalityand/or participation for those not mentioned or clear in the businessdescription, etc.5. Normalisation of the relations which identifies:a) covert each table to a relation schema;b) dependency diagram or functional dependencies for each relation;c) the level of Normalisation achieved for each relation; andd) the reasons for any relation that is NOT maintained in 3NF.6. A bibliography, containing all resources used to complete the assignment. Ifno resources have been used please indicate this appropriately.b) Individual Presentation / VivaIn the week of the submission due date, students in each group will be required to attendthe workshop/lab in-person/online. The tutor will assess each student via a briefpresentation / viva.Assessment Criteria and Marking Overview Task a: The Group SubmissionMarksPresentationHow clear and well-presented your submission is.10ERDAdherence to the standard of the course, assumptions made,inclusion of correct primary and foreign keys, appropriateentities, relationships, and attributes.60NormalisationAppropriate interpretation of each normal form, arguments forleaving the schema in the normal form you consider optimal.30Total100Out of 7% of the total assessment7 Task b: Individual Presentation / VivaMarksCorrect answers to questions related to the assignment /submission.3Sub-total3Out of 3% of the total assessment3