IT163-1: Synthesize database concepts needed to effectively design a database. PC-2.1: Achieve goals through planning and prioritization. Module Outcomes: Describe basic database concepts.Examine the

IT163-1: Synthesize database concepts needed to effectively design a database.

PC-2.1: Achieve goals through planning and prioritization.

Module Outcomes: 

• Describe basic database concepts.
• Examine the relational database model.
• Create a conceptual model.
• Define relationships between entities.
• Analyze the concepts of normalization.
• Create a logical data model from a conceptual model.
• Identify the attributes for your logical model.
• Identify the primary and foreign keys.
• Examine tables, columns, and data types for a database design.
• Create a physical data model.

Explanation

This assessment is divided into 3 parts with multiple file submissions. Please read all directions, including submission directions for each part, and submit as directed.

Module 1 Assessment Part 1

Purpose

All databases start with data; without the data, there would be no need for a database. In this scenario, Fit @ Home provides home gym concierge services. Their services include equipment subscription packages and personal training video sessions in the convenience and safety of home.

The data provided to you (IT163_Data1) represents the current data they are storing outside of a database. As you can see, it is a very archaic and disorganized system for maintaining their data and it is becoming unwieldy. They need your help to organize their data so that they can manage it more effectively to grow their business.

Assessment Instructions

For this assessment, you will be using the diagramming application Microsoft Visio, obtained through Microsoft Azure. See the Azure link under Course Resources for details.

1. Your initial task is to review the data provided and determine the entities and relationships that would exist for your relational database design. You will document them in upcoming steps.

Refer to IT163_Example1 to see a demonstration of how to work through this process.

Things to consider as you analyze the data:

• What groupings of information exist?
• What data depends on other data?
• How do these groupings relate to one another?
  • Look for one-to-one, one-to-many, and many-to-many relationships in the data.
• An entity-relationship diagram at a conceptual level will only show the names for each entity and the relationships between them. There will be no actual data depicted in the model you are building.Reference:Harrington, J. L. (2016). Relational database design and implementation (4th ed.). Morgan Kaufmann Publishers.
  • Chapter 4: “Entities and Relationships”

1. Using Microsoft Visio, create a new ERD diagram. Choose the Crow’s Foot Database Notation template.
2. Create your conceptual diagram to show the four entities you identified.

For extra help, see Optional Resources for Assessment under the Academic Tools, Library link:

References:

Campbell, J. (2016). Software development fundamentals: Creating entity-relationship diagrams [Video]. Skillsoft. https://libauth.purdueglobal.edu/sso/skillport?context=106426 

Harrington, J. L. (2016). Relational database design and implementation (4th ed.). Morgan Kaufmann Publishers.

• Chapter 3: “Why Good Design Matters”
  • The Bottom Line

1. Your diagram should show the relationships using the crow’s foot style of notation to represent the cardinality.

For extra help see Optional Resources for Assessment under the Academic Tools, Library link:

References:

Harrington, J. L. (2016). Relational database design and implementation (4th ed.). Morgan Kaufmann Publishers.

• Chapter 4: “Entities and Relationships”
  • Basic Data Relationships
  • Documenting Relationships

Sampson, A. (2015). Generic design and modeling databases: Determining relationships in an ERD [Video]. Skillsoft. https://libauth.purdueglobal.edu/sso/skillport?context=9454

1. Your diagram should also label each entity clearly with a meaningful name.

For extra help see Optional Resources for Assessment under the Academic Tools, Library link:

Sampson, A. (2015). Generic design and modeling databases: Defining entities [Video]. Skillsoft. https://libauth.purdueglobal.edu/sso/skillport?context=94551 

Assessment Requirements—Part 1

For this assessment, you will create your diagram using Microsoft Visio. You will submit a single Visio diagram file (*.vsd) as your submission.

Module 1 Assessment Part 2 A&B

Purpose

Normalization involves making the database design better through the use of a specific sequence of rules to reach each normal form. The goal is to reach third normal form. Using the conceptual model as a guide, take the data provided previously, IT163_Data1, and work to normalize your design to third normal form. You will be showing your normalization process and building your logical diagram.

Assessment Instructions

Refer to the file IT163_Example2 for a demonstration.

1. For this first step take the data provided previously, IT163_Data1, and put it into a tabular format. See the example document for an idea of how you might organize the data into a table.

Note: Organizing all of the data into a single table does not represent a database nor a normalized design. This data store helps to visualize the data in one place. This is just where you will begin the process.

1. Put the data into first normal form. Use a simple table in Word or an Excel spreadsheet to show your attributes in first normal form with a partial data example.

For extra help see Optional Resources for Assessment under the Academic Tools, Library link:

Reference:

Harrington, J. L. (2016). Relational database design and implementation (4th ed.). Morgan Kaufmann Publishers.

• Chapter 7: “Normalization”
  • First Normal Form

Sampson, A. (2015). Generic design and modeling databases: First normal form [Video]. Skillsoft. https://libauth.purdueglobal.edu/sso/skillport?context=94562 

1. Looking at your first normal form and the data examples, identify problems that need to be addressed to put this data into second normal form. Use a simple table in Word or an Excel spreadsheet to show this data in second normal form.

For extra help see Optional Resources for Assessment under the Academic Tools, Library link:

Reference:

Harrington, J. L. (2016). Relational database design and implementation (4th ed.). Morgan Kaufmann Publishers.

• Chapter 7: “Normalization”
  • Second Normal Form

Sampson, A. (2015). Generic design and modeling databases: Second normal form [Video]. Skillsoft. https://libauth.purdueglobal.edu/sso/skillport?context=94563 

1. Taking your second normal form of the data, analyze the issues that prevent it from being in third normal form. Use a simple table in Word or an Excel spreadsheet to show this data in third normal form.

For extra help see Optional Resources for Assessment under the Academic Tools, Library link:

Reference:

Harrington, J. L. (2016). Relational database design and implementation (4th ed.). Morgan Kaufmann Publishers.

• Chapter 7: “Normalization”
  • Third Normal Form

Sampson, A. (2015). Generic design and modeling databases: Third normal form [Video]. Skillsoft. https://libauth.purdueglobal.edu/sso/skillport?context=94564 

1. Now that you have worked through the normalization of the data, use Microsoft Visio to create your logical diagram to show the normalized design. The diagram should represent relationships using crow’s foot notation. The diagram should include the entity names, attributes, and notation of primary and foreign keys for each entity.

Green, B. (2016, June 22). Create an ERD in MS Visio [Video]. YouTube. https://www.youtube.com/watch?v=dwo48lxj1UM

Assessment Requirements

Your work will consist of two parts:

Part 1: The visualization of your normalization process, steps 1, 2, and 3 (use tables in Word or a spreadsheet). See the example file provided (IT163_Example2).

Part 2: The Visio diagram file (*.vsd).

Module 1 Assessment Part 3

Purpose

The purpose of this assessment is to take the logical model and expand it into the physical model. All of the efforts in previous steps to think through your design allows you to move forward with your physical implementation more easily and effectively. You can now refine your logical model into the physical data model which can then directly map between the diagram and the actual database management system.

Assessment Instructions

For this assessment, you will complete the physical model for your database design. This model will be based on the logical model you created previously. Developing your physical model will require that you carefully document the definitions for your database that you will later implement in the database management system, Microsoft Access.

Think carefully about the type of data you will be storing in each field.

1. Will it be numeric?
2. Will it be text?
3. Can it be left blank (null)?

Refer to the file IT163_Example3 for a demonstration.

You will use Microsoft Visio to build your physical ERD diagram and may start with your existing logical diagram and modify it as follows:

1. As an additional challenge for this phase of the design, you will add one more table to the database design. Think of some other aspect of this business model that you could bring into the design at this point. You must plan out how this new table will relate to at least one other existing entity. Show the relationship using crow’s foot style of notation. 
2. Table names. Include those from your conceptual diagram and the new table from the previous step. Remember to use meaningful names and consistent naming conventions.Hint: These started out as entities. 
3. Field names. Identify appropriate field names for each table in your diagram. Remember to use consistent naming conventions. (i.e. clientID, petID, breedID might all be primary key fields).Hint: These started out as your attributes.Reference: http://www.databasedev.co.uk/database_field_names.html 
4. Primary keys/foreign keys. Notate the primary and foreign keys in each table as applicable. Remember to be consistent.Hint: The primary key in the “one” table matches to the foreign key in the related table.

Reference:

Ulrich, L. A., & Cook, K. (2018). Access 2019 for dummies. Wiley.

• Chapter 5: “Table Tune-Ups”
  • The Primary Key to Success

1. Data types and field sizes. Identify appropriate data types for each field and field sizes as applicable.Hint: right-click your entity block and choose show attribute types to add a column to the right of your field names.

Reference:

Ulrich, L. A., & Cook, K. (2018). Access 2019 for dummies. Wiley.

• Chapter 4: “Sounds Like a Plan”
  • Determining Data Types

1. Nullability. Identify those fields that can be left blank.Hint: You may simply identify those fields that may be left blank with a null indicator as shown in the example.

For extra help see Optional Resources for Assessment under the Academic Tools, Library link:

Reference:

Oppel, A. (2010). Data modeling: A beginner’s guide. McGraw-Hill/Osborne.

• Chapter 8: “Physical Database Design”
  • Integrating Business Rules
  • Data Integrity

Sampson, A. (2015). Generic design and modeling databases: Defining constraints and nullability [Video]. Skillsoft. https://libauth.purdueglobal.edu/sso/skillport?context=94554

Assessment Requirements

You will build your diagram using Microsoft Visio. You may use the ERD template with crow’s foot notation as referenced previously. You will submit your Visio diagram file (*.vsd).