INFO6002: Database Management | My Assignment Tutor

Page 1 of 5INFO6002: Database Management 2Trimester 2, 2021 – Callaghan & OnlineAssignment 2 – Database Design & ImplementationBlackboard Submission Due: 10pm, Friday 2nd July 2021Demonstration Sessions:Callaghan students (Lab Session): 4 – 6pm, Wednesday 7th July 2021Online students (via Zoom): 5 – 6pm, Tuesday 6th July 2021WORTH 25% of final course mark.This is an INDIVIDUAL Assignment.Assignment RequirementsThis assignment contains 5 parts. You will submit your work to Blackboard as well asdemonstrate your working scripts. For Callaghan enrolled students, you will demonstrateduring the Week 9 lab session. For Online enrolled students, you will demonstrate on Zoom(5 – 6pm, Tuesday 6th July 2021).Part 1: Revised Data Requirements, EER Model & Data Dictionary (2 marks)In this section, you will revise your work from Assignment 1 based on any feedback givenby your lecturer. You will re-submit your:A. Data RequirementsB. EER ModelC. Data DictionaryPart 2: Relational Mapping & Normalisation (3 marks)Next, the EER Model needs to be mapped to a relational schema and normalised.The relational model needs to be documented in DBDL format. Sample DBDL format is givenin the below:ISBN (id, number, itemNo)Primary Key idAlternate Key numberForeign Key itemNo references Book(itemNo)ON UPDATE CASCADE, ON DELETE CASCADEDBDL format is provided in your text. (Databases Systems – A Practical Approach toDesign, Implementation, and Management – 6th Edition by Connolly and Begg 2015).Secondly, identify any relations that are not normalised and show the steps to transformthem into a normalised relation.Page 2 of 5Part 3: Implementation – Database Script (5 marks)Create a T-SQL script for the database design in Part 2. You will create a database with allthe necessary tables and constraints: primary key, foreign key, not null, unique and checkconstraints. The database must be populated with sufficient and meaningful records forevaluation.Part 4: Stored Procedure (10 marks)Implement the following stored procedures. Ensure that each stored procedure is tested withappropriate sample data. Test cases should be saved in a separate test script. (1) Create an orderProcedure nameusp_createCustomerOrderDescriptionThis stored procedure creates a new customer order. The sales tax is 10% of order amount.Input ParametersCustomer id – Id of customerItems – A Table-valued Parameter (TVP) of items (item number, quantity,discountPromotionCode). Note that the discountPromotionCode is null for items where adiscount does not apply or items are not part of promotionFulfilmentType – Type of order fulfilment (delivery or pickup)OrderType – Type of order (phone, walk-in, app, website)Employee id – Employee id of employee taking the order. This will be null for an onlineorderOrderDateTime – Date and time of order is placedDeliveryAddress – Delivery address if it is a delivery orderExpectedOrderFulfilmentDateTime – Date and time when the order needs to be fulfilled.Output ParameterOrder number of the newly created orderFunctionalityCreates a new order with the provided input parameters. After each order, the ingredientsused for the order are deducted from the current stock levels of the ingredients. Returnsthe newly created order number. If there is any error an appropriate error message israised.SQL scriptcreate_usp_createCustomerOrder.sqlTest scripttest_usp_createCustomerOrder.sql Section 5: Business Rule (5 marks)Business Rule: Order SatisfiabilityBefore an order can be taken, it is important to verify that the order can be satisfied with theavailable ingredients in the store. If the ingredients available are insufficient to fulfill the orderan appropriate error message needs to be generated and the order cancelled.Ensure that the above business rule is enforced in the database. You need to generateappropriate error messages if an attempt to violate the constraint is attempted.Page 3 of 5Blackboard Submission RequirementsThe following items need to be submitted to Blackboard at:Assessment / ASSIGNMENT 2 / Assignment 2 Submission.Submit a single .zip folder named as:A2, your first name, your surname and your student numbere.g. A2SimonLee1234567.zipThe zip folder will contain the following files: DescriptionFormatPart 1A: RequirementsDocumentRevised Requirements Document including DataRequirements, Transaction Requirements & BusinessRulesMS Wordor PDF formatPart 1B: EER ModelRevised EER ModelVisioor PDF formatPart 1C: Data DictionaryRevised Data DictionaryMS Wordor PDF formatPart 2: RelationalMapping & NormalisationDocument containing:• Relational Database Schema in DBDL Format• Normalisation discussion including identifying thenormal form of each relation and clear documentationof normalisation steps for any relations not alreadynormalisedMS Wordor PDF format The following T-SQL Script files must also be included in the .zip file: SQL Script nameDescriptionPart 3:Implementation –Database ScriptcreateDB.sqlContains the script that creates thedatabase along with all constraints. Also,inserts sample data into the tables.Part 4: StoredProcedurecreate_usp_createCustomerOrder.sqlContains T-SQL Script to create the storedprocedure.test_usp_createCustomerOrder.sqlContains the test scripts to test the storedprocedure.Part 5: BusinessRulecreate_enforceBusinessRule.sqlContains T-SQL Script to create thebusiness rule.test_enforceBusinessRule.sqlContains the test scripts to test thebusiness rule. Page 4 of 5Demonstration RequirementsEach student must demonstrate their working SQL Scripts during the week 9 lab session onWednesday 7th July 2021 from 4-6pm (Callaghan students) or Zoom session on Tuesday6th July from 5-6pm (Online students). Failure to attend the demonstration can result in azero grade for the assignment.Marking RubricThe assessment RUBRIC is given below: ExcellentSatisfactoryFailPart 1: Revised DataRequirements, EER Model& Data Dictionary.(2)(2)(1)(0)All requirements documented inclear and complete manner. Thedocument includes datarequirements, transactionrequirements and business rules.All requirements are accuratelycaptured and modelled in EER.Data Dictionary without errors.Many requirements outlined. Somerequirements missing/incorrect.Most requirements are accuratelycaptured and modelled in EER.Data Dictionary mostly withouterrors.No requirements document or EERmodel or Data Dictionary submitted.(3)(1-2)0Part 2: Relational Mapping& Normalisation(3)Conceptual model is correctlymapped to relational model withoutany omissions..Normalisation discussed in detail.The relational model is mostlymapped accurately.Normalisation has omissions/errors.Relational schema is missing and/orpoorly constructed.No normalisation performedExcellentGoodSatisfactoryPoorFailPart 3: Implementation –Database Script(5)(5)(4)(3)(1-2)(0-1)The T-SQL scriptmaps to thedatabase designaccurately. Thescript executeswithout any errors.The code is welldocumented. Allnecessary tablesand constraintsare clearly shown.The database ispopulated withsufficient andmeaningful recordsfor evaluation.The T-SQL scriptmaps to thedatabase designaccurately. Thescript executeswithout any errors.The code isdocumented.Necessary tablesand constraints areshown.The database ispartially populated.T-SQL script maps todatabase design formost cases.The script executescorrectly with someconstraints.Partial data insertedto database.The T-SQL scripthas missing content/partially maps todesign.Many critical objectsmissing/errors inscript.Partial or nodocumentationof scripts.Missing or little datainserted.Missing or poorlywritten script witherrors, missingcontent – objects,constraints anddata. Page 5 of 5 Part 4: Stored Procedure(10)(9-10)(7-8)(4-6)(2-3)(0-1)The functionality isimplementedwithout errors.The code is welldocumented andpresented.All appropriatewarning and errormessages areraised.All appropriate testcases areimplemented toverify thecorrectness.The functionality isimplementedcorrectly.The code is partiallydocumented and/ortested.Major test cases areimplemented.The code has minorerrors.The code is partiallydocumented andtested.The code executesand has at least 1test case.The code has majorerrors.It is poorlydocumented andtested.No code and/orbasic outline offunctionalitypresented.No functionalityexecuted or tested.Part 5: Business Rule(5)(5)(4)(3)(1-2)(0-1)The business rule iscorrectlyimplemented.The code is welldocumented.Error messages andwarnings areappropriately raised.The code is testedwith allappropriate testcases.The business rule iscorrectlyimplemented.The code is partiallydocumented.Appropriate errormessages andwarnings are raised.Major cases aretested.The business rule isimplemented withminorerrors/omissions.The code is partiallycommented.Error messages areraised.Partially tested.The code has majorerrors and/orpartially executes.Poor documentationand testing.The code does notcompile or run.Has major errorsand/or partial logicis shown.No testing and/ordocumentation


Leave a Reply

Your email address will not be published.