MN405 Data And Information Management, Melbourne Institute Of Technology, Australia

Title: Data Model Development And Implementation

SAVE TOP GRADE USING MN405 DATA AND INFORMATION MANAGEMENT ASSIGNMENT HELP SERVICE OF EXPERTSMINDS.COM!

Question 1: Complete the information-level design for a database and answer the following questions:

A. Identify and list entities described in all user requirements.

Answer: Database Design: Mindful body fitness company provides health care services to various customers and help people to stay with healthy mind and fit body .The database Mindful body fitness company has four entities named as Coach, Program, Customer and Exercise log. . The attributes of various entities of Mindful body fitness company are :

A) Entities and Attributes

a) Coach

• ID (Primary key):-it gives unique identity to every coach of Mindful body Fitness Company.

• FNAME

• LNAME

• Addr

• DOB

• Contact

• Uname

• Pwd

b) Program

• PRONUM (Primary key):-it uniquely identifies every program of Mindful body fitness company.

• title:-It identifies title of the program

• Sdate

• Fdate

• Price

c) Customer

• Cnum(Primary key):-it uniquely identifies every customer of Mindful body fitness company.

• Fname

• Lname

• Addr

• PRONUM:- it gives the program number of the program choosen by the particular customer.

• Ptitle:-it givers the title of the program taken by the customer.

• Payment

d) Exercise log

• id(Primary key):- it uniquely identifies every exercise log of the customer.

• EDate

• Ecat

• Esubcat

• Time

• Calories

B. Add attributes to these entities and represent them as a collection of tables and attributes.

Answer:

C. Outline 3 business rules that describe the relationships between entities.

Answer: Business Rules of Mindful body fitness company

• A program can have one or more coaches associated with it.

• Each program has one or more customers in it.

• Each customer has one or more Exercise log.

• A coach can work on only one program.

• One customer can enroll for only one program at one time.

• A coach guides one or more customers

Relationships between entities

• Coach and customer has one to many relationship.

• Customer and Exercise log has one to many relationship.

• Coach and Program has one to many relationship.

• Customer and Program has one to many relationship.

D. Determine the functional dependencies.

Answer: Functional Dependencies

Cnum→Fname

Cnum→Lname

Cnum→Addr

Cnum→PRONUM

Cnum→title

Cnum→Payment

PRONUM→title

Id→Fname

Id→Lname

Idr→Addr

Id→DOB

Id→Contact

Id→Uname

Id→Pwd

PRONUM→Sdate

PRONUM→Fdate

PRONUM→Price

id→EDate

id→Ecat

id→Esubcat

id→Time

id→Calories

EXPERTSMINDS.COM ACCEPTS INSTANT AND SHORT DEADLINES ORDER FOR MN405 DATA AND INFORMATION MANAGEMENT ASSIGNMENT - ORDER TODAY FOR EXCELLENCE!

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

Answer: Normalization of tables: Normalization is the process of removing redundancy from the tables.

The tables are in first normal form as all the tables contain one value for each field and no field is null.

The tables are in second normal formal as they satisfy the first normal form and all the non key attributes are fully dependent on the key attributes.

The table customer is not in third normal form as there are transitive dependencies in these tables.

Cnum→PRONUM

PRONUM→title

Cnum→title

So, we will split customer table on basis of these functional dependencies

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

Answer: E-R diagram of Mindful body fitness:

E_R diagram of database of Mindful Body Fitness Company

Question 3: Build this model using MS Access/SQLiteby creating these tables and Relationships. Populate these tables with appropriate data;include at least 2 records in each table.

a) Relationship diagram created in Access. (Select database tools → Relationships in the Access menu)

Answer: Screenshots of MS ACCESS

a) Relationship between the tables the tables

Screenshot showing relationships between the tables

ORDER NEW MN405 DATA AND INFORMATION MANAGEMENT ASSIGNMENT AND GET 100% ORIGINAL SOLUTION AND QUALITY WRITTEN CONTENTS IN WELL FORMATS AND PROPER REFERENCING!

b) Data sheet view and design view of your tables.

c) If you are using SQLite: Screen shots of CREATE TABLE command and INSERT INTO commands.

Answer: b) Design view of the tables

Customer

Customer Programs

Program

Coach

Exercise Log

c) Datasheet View

Customer

Program

Customer Programs

Exercise Log

Coach

NEVER BE CAUGHT IN PLAGIARISM, AVAIL MN405 DATA AND INFORMATION MANAGEMENT ASSIGNMENT HELP SERVICE OF EXPERTSMINDS.COM AND SAVE HIGHER MARKS!

Question 4: Report Generation

a. Write an SQL query to generate one example of useful information that can be obtained from this database.

Answer: Example and screenshot of query

Query 1

SELECT Customer.First_name, Customer.Last_name, CustomersPrograms.Ptitle, Program.Start_date, Program.Finish_date, Customer.Payment

FROM Program INNER JOIN (Customer INNER JOIN CustomersPrograms ON Customer.Cust_num = CustomersPrograms.Cust_num) ON Program.P_NUM = CustomersPrograms.P_NUM;

Screenshot of Query 1

b. For additional 5 marks you will execute more queries(at least 2) on the table join from the database you created in Question 3 and include the screen shots of the outputs and all SQL statements.

Answer: Additional Queries

Query 2

SELECT Coach.First_name, Coach.Last_name, Program.Ptitle, Program.Start_date, Program.Finish_date

FROM [Exercise log], Program INNER JOIN Coach ON Program.P_NUM=Coach.PNUM;

Screenshot of Query2

Report showing the coach of different programs

Query 3

SELECT Coach.First_name, Coach.Last_name, Program.Ptitle, Program.Start_date, Program.Finish_date

FROM [Exercise log], Program INNER JOIN Coach ON Program.P_NUM = Coach.PNUM;

Screenshot of the query3

Acquire our Melbourne Institute Of Technology, Australia assignment help services for major related courses and academic units such as -

• MN404 - Fundamentals of Operating Systems and Programming Assignment Help
• MN501 - Network Management in Organisations Assignment Help
• MN603 - Wireless Networks and Security Assignment Help
• MN621 - Advanced network Design Assignment Help
• MN692 - Capstone Project Assignment Help
• MN502 - Overview of Network Security Assignment Help
• MN503 - Overview of Internetworking Assignment Help
• MN601 - Network Project Management Assignment Help
• MN504 - Networked Application Management Assignment Help
• MN506 - System Management Assignment Help