Reference no: EM132310176
Assignment - 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.
Assignment Description - 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) - (e).
Mindful Body Fitness Company is planning to design a data model to hold information relating to their programs, coaches, exercise logs and their customers.
As a database designer for Mindful Body Fitness, you need to ensure that each Exercise Log has a unique ID number, Date, Exercise category, Exercise sub category, Time spent, calories burnt.
Each customer has a unique Cust_number and their first name, last name, address, program number, program tile and Payment must be recorded.
Every program has a program number (PNum) that uniquely identifies the program. The program title, StartDate, Finish Date, Price must also be recorded.
Each coach has a unique ID number. Their first name, last name, date of birth, address, contactNo, user name and password must be recorded.
A coach can work on only one program. A program can have one or more coaches associated with it. One customer can enrol for only one program at one time.
Write down any assumptions you make, if they are not explicitly described here.
a. Identify and list entities described in all user requirements given above.
b. Add attributes to these entities and represent them as a collection of tables and attributes (Database schema): e.g. Customer (Customer_Num, Cust_First_Name,. . . . . . )
Note: Select and underline suitable primary key for each table.
c. Outline 3 business rules that describe the relationships between entities.
Note: You can make assumptions to create certain relationships, if it is not specifically mentioned in the given business rules. e.g. A Coach can work on only one program.
d. Determine the functional dependences. e.g. Customer_Num → Cust_First_Name
e. Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency causes it.
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.
Note: You need to use Visio, draw.io [1] or Lucid chart [2] or any other software tool, to create the ER diagram.
3. 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.
I. 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.
II. If you are using SQLite
- Screen shots of CREATE TABLE command and INSERT INTO commands.
4. Report Generation
a. Write an SQL query to generate one example of useful information that can be obtained from this database.
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.
Section to be included in the report -
1. Information Requirements -
a. Identify all entities - Identify entities from the given user requirements.
b. User Views as Tables - Represent the User Views as a collection of Tables.
c. Relationships - Determine the relationships between entities.
d. Functional dependences - Determine Functional dependences.
e. Normalise tables - Analyse above tables and normalize.
2. ER Diagram - Represent the structure of your database visually by using the ER diagram. Evaluate your proposed solutions.
3. Model building - Build the model of the database using MS Access / SQLite.
4. Report Generation - Show the operation of a useful SQL Query and its outputs.
Attachment:- Assignment File.rar