Best Data model development and implementation in 2020

Data model development and implementation

Assessment Details and Submission Guidelines
Unit CodeMN405
Unit TitleData and Information Management
Assessment TypeAssignment 2
Term, YearT3, 2018
Assessment TitleData model development and implementation
Purpose of the assessment (with ULO Mapping)The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain understanding of data model development. Then implement the data model using a commercially available database management system development tool.

On completion of this assignment students will be able to:

a.      Understand the fundamental principles of the networking and data requirements of a network.

b.      Identify organisational information requirements.

c.       Model organisational information requirements using conceptual data modelling techniques.

d.      Convert the conceptual data models into relational data models and verify their structural characteristics with normalisation techniques.

e.      Implement and utilise a relational database using a database system.

Weight20% of total assessment for the unit
Total Marks60  Marks
Word limitNo specific word limit
Due DateLab class week 11
Description of this assignment:This assignment is to develop a data models, map and implement a Database System in a standard development environment to gain understanding of data model development and implementation.
Submission Guidelines·     All work must be submitted on Moodle by the due date. Write your answers in this document underneath the question and save,file name should be:

“MN405_T3_2018_Assigment2_your_name.docx”.

·      The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and 2.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 at the end in a reference list using IEEE referencing style.

Extension·      If an extension of time to submit work is required, a Special Consideration Application must be submitted directly to the School’s Administration Officer, in Melbourne on Level 6 or in Sydney on Level 7.  You must submit this application three working days prior to the due date of the assignment. Further information is available at:

http://www.mit.edu.au/aboutmit/institutepublications/policiesproceduresandguidelines/specialconsiderationdeferment

Academic Misconduct 

 

·      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 course or rescinding the degree. Students should make themselves familiar with the full policy and procedure available at:http://www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure.For further information, please refer to the Academic Integrity Section in your Unit Description.

 Assignment Description

60 Marks

Questions:

  1. Complete the information-level design for a database that satisfies the following constraints and user view requirements. In order to complete this information–level design you are required to answer the questions given below 1(a) – (f).

The local city youth league needs a database system to help track children who sign up to play soccer. Data needs to be kept on each team, the children who will play in each team, and their parents. Also, data needs to be kept on the coaches for each team and matches.

You need to store Team ID number and Team name of each team and Player ID number, Player first name, last name, and Player age of all players. Team may or may not have a Player. A Player must have a Team and should belong to only a one team.

You also need to store Coach ID number, Coach first name, Coach last name, and Coach home phone number. A Team may have many Coaches or may not have a Coach.  A Coach must have only one team assigned to him / her.

For each parent, the Parent’s ID number, last name, first name, Home phone number, and Home address are kept in the system. A Player may have many Parents. A Parent may have only one Player.

Teams play matches. For each match we need to keep track of the following:

Match ID

  • The date on which the game is played
  • The final result of the match
  • Host team ID (HID) and Guest team ID (GID)

Write down any assumptions you make, if they are not explicitly described here.

  1.  Identify and list entities described in all user requirements given above. (5 Marks)
  2.  Add attributes to these entities and represent them as a collection of tables and attributes (Database schema): e.g. Team(Team ID_Num, Team name,..)

Note: Select and underline suitable primary key for each table.                                            (5 Marks)

  1. outline 3business rules that describe the relationships between entities. (5 Marks)

Note: You can make assumptions to create certain relationships, if it is not specifically mentioned in the given business rules.

e.g.        A Player may have many Parents.

 

  1. Determine the functional dependences. (5 Marks)

e.g.  Team ID number  àTeam names

 

  1. Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency causes it.

(5 Marks)

  1. Represent the structure of your database visually by using anentity-relationship (E-R) diagram. If you make any assumptions about data that are not explicitly given in the problem, these must be described.

Note: You need to use Visio, draw.io[1] or Lucid chart [2]or any other software tool, to create the ER diagram.                                                                                                                      (15 Marks)

  1. Build this model using MS Access/SQLite by creating these tables and Relationships.   Populate these tables with appropriate data;include at least 2 records in each table.  Include the following in your MS Word document.                                                                                                                                 (10 Marks)
  2. If you are using MS Access include following screen shots
  • Relationship diagram created in Access. (Select database tools àRelationships in the Access menu)
  • Data sheet view and design view of your tables.
Figure 2 Design View

 

Figure 1 Datasheet view

  1. If you are using SQLite
  • Screen shots of CREATE TABLE command and INSERT INTO commands.

Figure 3 CREATE TABLE command in SQLite

  1. Report Generation
  2. Write anSQL query to generate one example ofuseful information that can be obtained from this database.                                                                                                                         (5 Marks)
  3. For additional 5 marks you can execute more queries(at least 2) on the database you created in Question 3 andincludethe screen shots of the outputs and all SQL statements.                                                                                                                                                (5 Marks)

Bibliography

x

[1]Draw.io. (2018, December). [Online]. https://www.draw.io/
[2]Lucidchart. (2018, December) ER Diagram Tool. [Online]. https://www.lucidchart.com/pages/tour/ER_diagram_tool

x

Marking 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 sectionMarks
1.      Information Requirements
a.      Identify all entitiesIdentify entitiesfrom the given user requirements5
b.      User Views as TablesRepresent the User Views as a collection of Tables5
c.      RelationshipsDetermine the relationships between entities5
d.      Functional dependences.DetermineFunctional dependences.5
e.      Normalise tablesAnalyse above tables and normalise5
2.      ER DiagramRepresent the structure of your database visually by using the ER diagram.Evaluate your proposed solutions15
3.      Model buildingBuild the model of the databaseusingMS Access / SQLite10
4.      Report GenerationShow the operation of a useful SQL Query and its outputs10
TOTAL marks or the Report60

 

Example Marking Rubric for Assignment 2Report

Grade

Mark

HD

100%

DISTINCTION

80%

CREDIT

60%

PASS

40%

Fail

0-40%

1.(a),(b),(c)

Identify organisational information requirements

(15 marks)

Ability to think critically,and analysed clearly.

 

Relevant, and soundly analysed.

 

Generally relevant, and analysed.

 

Some relevance, and briefly presented.Not relevant to the assignment question.
1(d), (e)

Functional dependencies and normalisation

Convert the conceptual data models into relational data models and verify their structural characteristics with normalisation techniques.

(10 marks)

Demonstrated   excellent ability to think critically and identifieddependencies correctly.Demonstrated  good ability to think critically and identified dependencies correctly.Demonstrated   ability to think critically and identify some dependencies.Demonstrated   some ability to think critically, andidentified some dependencies.

 

Did not demonstrate ability to think critically.

 

2.

ER Diagram –

Model organisational information requirements using conceptual data modelling techniques.

(15 marks)

Extremely good effort. All elements are present and very well integrated.

Outstanding.

Good effort. Most components are present and well integrated.Good effort made but not outstanding.Made some effort  with inaccuracies.Very little attempt. Lazy effort with inaccuracies
3.      Model building on MS Access or SQLite

 

(10 marks)

 

Extremely good effort. All elements are present.Good effort. Most components are present.Good effort made but some components are missing.Made some effort  with inaccuraciesVery little attempt. Lazy effort with inaccuracies.
4.      Report Generation – Implement and utilise a relational database using a database system- SQL queries

(10 marks)

All elements are present and very well integrated.

Logic is very clear and easy to follow.

Components present and well integrated. Logic is clear.Components present and mostly well integrate. Logic is mostly consistent.Most components present; but logic is not clear and consistent;Representation lacks structure.

 

Arguments are confused and disjointed.

 

Leave a Comment