Data model development and implementation

Click here to order this assignment @Tutoriage.us. 100% Original.Written from scratch by professional writers.

 

Assessment Details and Submission Guidelines
Unit Code MN405
Unit Title Data and Information Management
Assessment Type Assignment 2
Term, Year T2, 2018
Assessment Title Data 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.

Weight 20% of total assessment for the unit
Total Marks 60  Marks
Word limit No specific word limit
Due Date        Lab 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_T2_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

 

 

SmartProjects Company is planning to design a Data model to hold information relating to their projects, Employees working on company Projects and their customers.  Initial requirements analysis brings out the following User view requirements / business rules about what needs to be recorded. Assume that you are given a task to model the database by using these business rules.

 

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

 

  

User view 1 requirement   :  Each employee has a unique ID number, First name, last name, and date of Birth, Address, user name and password.

An employee can work on only one project.

 

User view 2 requirements   :  Each customer has a unique Cust_number, first name, last name, address and Payment.

One customer can submit only one project at one time.

 

User view 3 requirements:  Each project has a project number that uniquely identifies the project (PNum), title, StartDate, managerEmpNum and managerEmpName.

A Project can have one or more Employees associated with it.

One project may belongs to several departments.

 

User view 4 requirements:  Each department has a unique ID number and name.

A Department can have Many Employees. An Employee can only work in One Department.  A department controls number of projects.

 

Questions:

 

1.

  1. Analyse the all user requirements given above, identify and list all entities described in each user requirement.                                                                                                                                            (5 Marks)

 

  1. Add attributes to these entities and represent these entities (or Tables) and attributes as a collection of Tables and attributes / database schema. You are required to arrange them as given in the example below. (5 Marks)

Note: Select suitable primary key for each table and underline it.

Eg.      Employee (EmpNum,…….)

  1. Outline all business rules that describe 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.

Eg.          One customer can submit only one project at one time.

 

  1. Determine the functional dependences.                                                                                       (5 Marks)

Eg.  EmpNum  à Fname, Lname, TaxFileNum, address ….

 

 

  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 the entity-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 are required to use Visio or any other software tool to create the ER diagram.

 

(15 Marks)

 

  1. Build this model using MS Access by creating these tables and Relationships. Populate these tables with appropriate data, at least 3 records in each table.

Include :

  1. Relationship diagram created in Access. (Select database tools -àRelationships in the Access menu)

 

  1. Data sheet view and design view of your tables.

 

(5 marks x 2 =10 Marks)

 

 

  1. Write SQL queries for the following questions and execute them on the database you created in Question 2.

Include screen shots of the outputs and all SQL statements you used to answer following questions.

 

  1. List details of all Projects started in year 2017

 (3 Marks)

 

  1. The management is interested in knowing names and email addresses of Customers who paid more than $5000.

 (3 Marks)

 

  1. Explain how to formulate a query to print details of projects ( Proj_Num, Project title, managerEmpNum) and employees (Employee ID, name)  assigned to them.

Hint : You need to  join Employee table and Project tables using a suitable query.

(4 Marks)

 

Marking criteria:

Example of marking criteria is shown in the following table. Marks are allocated as follows:

 

Section to be included in the report Description of the section Marks
1.    
a.       identify all entities Identify entities in all user requirements 5
b.       User Views as Tables Represent the User Views as a collection of Tables 5
c.       Relationships Determine the relationships between entities 5
d.       Functional dependences. Determine Functional dependences. 5
e.       Normalise tables Analyse above tables and normalise 5
f.        ER Diagram Represent the structure of your database visually by using the ER diagram. Evaluate your proposed solutions 15
2.       Model building Model building on MS Access  Server 10
3.       Implement and utilise a relational database using a database system   10
  TOTAL marks or the Report 60

 

Example Marking Rubric for Assignment 2 Report: Total Marks 60 for the report

 

Grade

Mark

HD

100%

DISTINCTION

80%

CREDIT

60%

PASS

40%

Fail

0-40%

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

Identify organisational information requirements-

 

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.

Demonstrated   excellent ability to think critically and identified dependencies 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 identify some dependencies.

 

Did not demonstrate ability to think critically

 

f.

ER Diagram –

Model organisational information requirements using conceptual data modelling techniques

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
 2. Model building

 

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 inaccuracies Very little attempt. Lazy effort with inaccuracies
3.Implement and utilise a relational database using a database system- SQL queries 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 integrated, Logic is mostly consistent Most components present, logic is not clear and consistent Representation lacks structure.

 

Arguments are confused and disjointed