Data model development and implementation | My Assignment Tutor

Prepared by: Dr. Deepani Guruge Moderated Prof. Paul Kwan April, 2021 Assessment Details and Submission GuidelinesUnit CodeBN204Unit TitleDatabase TechnologiesAssessmentTypeAssignment 2 (Individual)Term, YearT1, 2021Assessment TitleData model development and implementationPurpose of theassessment(with ULOMapping)The purpose of this assignment is to develop data models and map Database System intoa standard development environment to gain understanding of data modeldevelopment. Then implement the data model using a commercially available databasemanagement system development tool.On completion of this assignment students will be able to:a. Describe issues related to the integrity and security of database (DB) systems.b. Make an informed and critical assessment of database management systems(DBMS).c. Develop data models and implement DB systems.d. Analyse business decisions related to DB information systems.e. Demonstrate skills in building a database application using a commerciallyavailable database management system development tool.Weight20% of total assessment for the unitTotal Marks100 = 30 (for online quiz- Part A) + 70 (for Part B)Word limitNo specific word limitDue Dateweek 11/T1 2021 ; 6th June 21 11:55PMDescription ofthis assignment:This assignment consists of two parts:Part A: An online quiz Assignment 2, which includes 30 questions. Students mayhave 3 attempts at this quiz. The system will select the highest grade. This Quizwill be opened only during the week 9 Laboratory class.Part B: The creation of an ER diagram, checking data integrity and implementationSubmissionGuidelines All work must be submitted on Moodle by the due date. Write your answers in thisdocument underneath the question and save as“BN204_T1_2021_Assigment2_your_name.doc”. The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and2.5 cm margins on all four sides of your page with appropriate section headings. Reference sources must be cited in the text of the report, and listed appropriately atthe end in a reference list using IEEE referencing style. Submit both the database that you created and the word document. BN204 Database Technologies Page | 2 of 2Prepared by: Dr. Deepani Guruge Moderated by: Prof. Paul Kwan April, 2021 Extension If an extension of time to submit work is required, a Special Consideration Applicationmust be submitted directly to the School’s Administration Officer, in Melbourne onLevel 6 or in Sydney on Level 7. You must submit this application three working daysprior to the due date of the assignment. Further information is available at: Academic Misconduct is a serious offence. Depending on the seriousness of the case,penalties can vary from a written warning or zero marks to exclusion from the courseor rescinding the degree. Students should make themselves familiar with the full policyand procedure available at: For further information, please refer to the Academic Integrity Section in your UnitDescription. BN204 Database Technologies Page | 3 of 3Prepared by: Dr. Deepani Guruge Moderated by: Prof. Paul Kwan April, 2021Assignment DescriptionPart A: (20 marks)Paste below the summary of your Assignment 2 online quiz on Moodle. The quiz will be open in week 9laboratory class.Part B: (70 marks)1. Database DesignThe School of Information Technology & Engineering (SITE) would like to help its Master of Networkingstudents with placement in various companies and firms. To facilitate this, they need to design a databasewith the primary purpose of scheduling interviews and facilitating searches by students and companiesthat are looking for candidates. MIT’s IT Manager provided following business rules to the softwaredeveloper:Complete the information-level design for the new database “MIT training” that satisfies the constraintsand user view requirements given below. In order to complete the information-level design you arerequired to answer questions given below.User view 1 requirement: The database should have information about students, job openings,companies, interviewers and interviews. Student’s first name, last name, student id number, driver’slicense number and expected graduation date must be recorded.User view 2 requirements: Companies post job openings that students can apply for. For each job posting,the date, application deadline of the posting, title of the position, base salary, minimum requirements,and description are recorded.User view 3 requirements: One student can apply for many open positions (Job postings) in differentcompanies, and the date and time of each application is recorded. Students should also be able to see thestatus of their application (whether there will be a follow-up interview and whether they were turneddown for the position).User view 4 requirements: Interviews can be facilitated by the University and interviews are conductedby the respective companies. It is required to keep track of date and time of the interview, conferenceroom location, which employees interviewed which students, as well as the result of the interview. Acompany from the Business School reserves MIT conference rooms and the company needs to specify theresources needed (e.g. computer, projector, etc.).User view 5 requirements: An interviewer is an employee of a company and he/she has an employeeid, telephone number and position title.User view 6 requirements: A company has an identifier, name, main contact number and main faxnumber.Write down any assumptions you have made, if they are not explicitly described here.While completing the information-level design you are required to answer questions givenbelow 1(a) – (c)BN204 Database Technologies Page | 4 of 4Prepared by: Dr. Deepani Guruge Moderated by: Prof. Paul Kwan April, 2021a. Identify and list entities described in the given case scenario.(5 Marks)Outline at least 2 business rules that describes relationships between entities and write the typeof the relationship. Eg (1:1, 1:M or M:N)(5 Marks) b. c. ER diagramAdd attributes to those entities and select suitable primary keys for each entity.Represent the structure of your database visually by using the entity-relationship (ER) diagram.I. If you make any assumptions about data that are not explicitly given in the problem,these must be described.II. You are required to use a software tool to create the ER diagram. You can[2], Lucidchart [1], Visio or any other software tool to create the ER diagram.(15 Marks)2. Data Integritya. Represent entities and attributes as collection of tables and attributes as given below. Eg.Student (Stu_ID, Sname,…….NB: Select suitable primary key for each table and underline them.(5 Marks) b. Determine the functional dependences. (5 Marks)Eg. Stu_ID — > Sname, , ……, ……c. Normalise these tables. Make the normalization to 3NF and explain every step in the process.(10 Marks)3. Model Building Build this model using MS Access/SQLite by creating these tables and Relationships.Populate these tables with appropriate data, at least 2 records in each table.Include following into your MS Word document. (10 Marks)I. If you are using MS Access include following screen shots Relationship diagram created in Access. (Select database tools Relationships in theAccess menu)Data sheet view and design view of your tables as given below in Figure 1 and 2. For Student_Name and Student_ID you need to use your MIT ID and your name as onedata record.Figure 1 Data Sheet View Figure 2 Design ViewII. If you are using SQLiteBN204 Database Technologies Page | 5 of 5Prepared by: Dr. Deepani Guruge Moderated by: Prof. Paul Kwan April, 2021 Include screen shots of CREATE TABLE command and INSERT INTO commands.Eg.4. Report Generationa. Write a query to extract useful information from this “MIT training” database. (3 Marks)b. Execute this query on the database and attach the screenshot of your output. (3 Marks)5. Research QuestionRead the research paper titled “Indoor navigation systems based on data mining techniques in internetof things: a survey” and answer questions 5(a) to 5(c). Answer these questions with IEEE reference style.“Sattarian, M., Rezazadeh, J., Farahbakhsh, R., & Bagheres, A. (2019). Indoor navigationsystems based on data mining techniques in internet of things: a survey. Wireless Networks,25(3), 1385-1402.”You can download the paper saved in the assignment folder on Moodle or access it through download the paper.a. What are “indoor positioning systems” and why have researchers indicated that, they areimportant? (5 Marks)b. This paper presented a comprehensive literature review on data mining techniques for IndoorNavigation Systems in IoT based scenarios. Discuss 4 data mining techniques applied in Indoornavigation systems which is described in Section 4 of this paper. (4 Marks)Bibliography [1]Lucidchart, “ER Diagram Tool,” 2018.[2], 2018. Figure 3 CREATE TABLE command in SQLite 1BN204 Database Technologies Page | 6 of 6Prepared by: Dr. Deepani Guruge Moderated by: Prof. Paul Kwan April, 2021Marking criteria:Example of marking criteria is shown in the following table. Marks are allocated as follows: Section to be included in the reportDescription of the sectionMarksPART AAssignment 2 Quiz marksMarks for Assignment 2 Quiz20PART B1. Database Designa. Identify all entitiesIdentify entities in all userrequirements5b. Outline at least 2 business rulesOutline at least 2 business rulesthat describes relationshipsbetween entities5c. ER diagramRepresent the structure of yourdatabase visually.152. Data Integrity – Evaluate yourproposed solutionsa. collection of tablesRepresent entities andattributes as collection oftables5b. functional dependences (FD)Determine the functionaldependences5c. Normalise tablesAnalyse above tables andnormalise103. Model buildingModel building on MS Access orMS SQL Server104. Report GenerationExample for useful reporta. Write a query3b. Execute the query35 Research Question9Total marks or the Report70TOTAL90 Example Marking Rubric for Assignment 2 Report: Total Marks 70 for the report GradeMarkHD100%DISTINCTION80%CREDIT60%PASS40%Fail0-40%Identify allEntities /5Ability to thinkcritically.Relevant andsoundlyanalysed.Generallyrelevant andanalysed.Some relevanceand brieflypresented.Not relevant.Relationships/5Demonstratedexcellent abilityto think criticallyand identifiedrelationships.Demonstrated good abilityto thinkcritically andidentifiedrelationships.Demonstratedability to thinkcritically andidentify somerelationships.Demonstratedsome ability tothink critically.Did notdemonstrateability to thinkcritically. BN204 Database Technologies Page | 7 of 7Prepared by: Dr. Deepani Guruge Moderated by: Prof. Paul Kwan April, 2021 ER Diagram/15Extremely goodeffort. Allelements arepresent and verywell integrated.OutstandingGood effort.Mostcomponentsare presentand wellintegrated.Good effortmade but notoutstanding.Made someeffort withinaccuracies.Very littleattempt. Lazyeffort withinaccuracies.Represent userViews as Tables/5Analysed clearly.Soundlyanalysed.Generallyrelevant andanalysed.Some relevanceand brieflypresented.Not relevant tothe assignmentquestion.FunctionalDependency /5All elements arepresent and verywell integrated.Componentspresent andwellintegratedComponentspresent andmostly wellintegratedMostcomponentspresentRepresentationlacks structure.Normalise table/10Logic is very clearand easy tofollow.Logic is clearandconvincing.Mostlyconsistent andlogical.Adequatecohesion andconviction.Arguments areconfused anddisjointed.Model building/10Extremely goodeffort.Good effort.Good effort.Made someeffort withinaccuracies.Very littleattempt.4. ReportGenerationa. Write a query/3All elements arepresent.Mostcomponentsare present.Made but somecomponentsare missing.Made someeffort withinaccuracies.Lazy effort withinaccuracies.4. ReportGenerationb. Execute thequery/3All elementsare present.Mostcomponentsare present.Made butsomecomponentsare missing.Made someeffort withinaccuracies.Lazy effortwithinaccuracies.5 ResearchQuestion/9Analysed veryclearly.Soundlyanalysed.Generallyrelevant andanalysed.Some relevanceand brieflypresented.Not relevant tothe assignmentquestion.


Leave a Reply

Your email address will not be published. Required fields are marked *