BIAM410
Week 1 Lab: Creating a Simple Database
Lab Preparation
Chapters 1 and 2 of your reading this week outline the importance of models and how they work. This Lab looks at the components of a model and how those components are used to create a database. Review the reading to help clarify the following terms:
•Entity
•Attributes,
•Data Types
•Primary Key
•Foreign Key
•Business Rules
•Cardinality
Required Software
This Lab will use the following Lab Resources:
•Microsoft Excel
Use a personal copy of the software or access the Lab Resources, go to the Course Resources page – Lab Resources section.
Scenario
You are a Database Administrator for a small community college. (Approximately 5,000 students). You have been tasked by your Manager to develop a student database that will track the following information:
•Student First Name
•Student Last Name
•Student Address (including street address, city, state, and zip code)
•Birth date
•Tuition Cost
•Status (Freshman, sophomore, junior, or senior)
•Degree plan ID
•Scholarships applied
•Tuition per credit
•Number of credits for student
•Student Email
•Student Phone
Deliverables
For this lab assignment, you will be submitting a single Excel file to Canvas when completed.
•Given the information about each model component, what would you recommend to use for Entities? (There should be at least three entities)
•What would be the list of attributes to have in each entity?
•What would the primary and foreign keys be for each entity?
Use the following format using MS Excel and label each entity, list attributes under the entity name and designate FK and PK.
Example:
Entity Name
Label PK, FK Here Attribute List
You will be graded based on the rubric below:
Criteria Expectation Points
Entities A minimum of 3 entities are identified 10
Attributes At least 3 attributes for each entity are identified/listed 15
Cardinality PK and FK’s are identified for each table 15
Total A quality assignment will fulfill all criteria 40
BIAM410
Week 2: Lab Overview
TABLE OF CONTENTS
Week 2 Lab: Entity Relationship Diagram
Scenario
In this Lab, you will create an entity relationship diagram for a restaurant review website.
The major entities involved in this database design are the following.
•Restaurant – the eating establishment being reviewed
•Administrator – a person authorized to enter and update information about a restaurant such as name, address, hours of operation, etc.
•Reviewer – a person who write reviews of restaurants
•Review – the review of a restaurant
These are the business rules that apply to these entities.
•Each Restaurant can be administrated by one or many Administrators; each Administrator can administer one or many Restaurants.
•Each Review is for one and only one Restaurant; each Restaurant can have zero or many Reviews.
•Each Review is written by one and only one Reviewer; each Reviewer can write zero or many Reviews.
A table provided in the lab instructions gives detailed information about the attributes that must be stored for each entity.
Deliverables
After completing the steps below, submit a single Microsoft Word document file named LabWeek2xxx.docx (where xxx = your initials). This file will contain two Entity Relationship Diagrams (ERDs) copied and pasted from Microsoft Visio:
1.Conceptual: A conceptual data model diagram of this database showing:
a.the four major entities
b.the relationships among the entities
2.Logical: A logical data model diagram of this database showing:
a.all entities (including any needed associative entities)
b.the relationships among the entities
c.all primary and foreign keys
d.attributes for each entity
e.a data type for each attribute
Grading Rubric
Section Deliverable Points
2Conceptual data model diagram includes all entities with correct entity names and all required relationships with correct cardinalities indicated via crow’s foot notation. 20
4Logical data model diagram includes the same entities and relationships as shown on the conceptual data model and correctly resolves the many-to-many relationship using an appropriate associative entity. 30
5Logical data model includes all required attributes for each entity with appropriate data types, correctly designates all primary and foreign keys, and uses a visually organized and readable layout. 30
Total A quality assignment will fulfill all criteria 80
Required Software
Microsoft Visio
Access the software through the Azure Virtual Lab Environment at http://labs.azure.comLinks to an external site. or download and install a personal copy from the Student Software Store. The link to the Student Software Store is on the Course Resources Page in the Introduction and Resources Module.
The lab instructions and videos were created using Microsoft Visio 2019. Any recent version of Visio will work similarly, but some adjustments to instructions may be needed.
NOTE: Microsoft does not provide a MacOS version of Visio. Mac users should access Visio through the Citrix Virtual Lab environment.
Steps: All
Microsoft Office: Word
Access the software through the Azure Virtual Lab Environment at http://labs.azure.com or download and install a personal copy from your student Office365 account, accessed through the student portal.
Steps: All
BIAM410
Week 3 Lab: SQL SELECT Queries
Scenario
Adventure Works Cycles is a fictional company that manufactures metal and composite bicycles for sale to commercial distributors in North America, Europe, and Asia. Adventure Works is a multinational company headquartered in Bothell, WA. The firm recently acquired a manufacturing plant in Mexico that makes touring bicycles and subcomponents of other bicycles. The company is currently seeking to expand its market share by targeting high-volume customers, expanding availability of products on the Web, and lowering production costs.
Executives have requested some information to help them make strategic decisions to carry out this business plan. As a business analyst for Adventure Works Cycles, you will write SQL queries to retrieve the needed information from the corporate database, analyze this information, and make recommendations to management.
Deliverables
After completing the steps below, submit a single Microsoft Word file named LabWeek3xxx.docx (where xxx = your initials). This file will contain screenshots for six queries, including the SQL code, results, and analysis or recommendations for each query.
1.Products with high list prices
2.Shipping methods with low rates
3.Total sales by country
4.Average vacation hours by job title
5.Total sales by product
6.Year-to-date sales by salesperson
Section Deliverable Points
1Query shows ProductID, Name, Color, and ListPrice fields for products with list prices greater than $3,000, in descending order by ListPrice. SQL code and query results are provided. Analysis and recommendations are reasonable and show good business judgment based on query results; and are written professionally with no grammar, spelling, or typographical errors. 12
2Query shows ShipMethodID, Name, ShipBase, and ShipRate fields for shipping methods with shipping rates less than $1.50 in ascending order by ShipRate. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written professionally with no grammar, spelling, or typographical errors. 12
3Query shows CountryRegionCode and sum of SalesYTD for each country in descending order by sum of SalesYTD. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written professionally with no grammar, spelling, or typographical errors. 12
4Query shows JobTitle and average of VacationHours for each job title in descending order by average of VacationHours. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written professionally with no grammar, spelling, or typographical errors. 12
5Query shows ProductID, product Name and sum of LineTotal for each product in descending order by sum of LineTotal. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written professionally with no grammar, spelling, or typographical errors. 16
6Query shows BusinessEntityID, FirstName, LastName, and SalesYTD for each salesperson, in descending order by sum of SalesYTD. Screenshot includes SQL code and results. Analysis and recommendations are reasonable and show good business judgments based on query results; and are written professionally with no grammar, spelling, or typographical errors. 16
Total A quality assignment will fulfill all criteria 80
BIAM410
Week 4 Lab: Database Structure and Implementation
Scenario
The CEO of your organization is looking for some analytics from the AdventureWorks data warehouse. Your responsibility is to select several variable/Entities from the AdventureWorks data warehouse and analyze the results. You have access to MS PowerBI and will need to develop multiple charts from the data warehouse results and report back to the manager.
•Server: ML-RefVm-820127
•Database: AdventureWorksDW2017
Deliverables
After completing the steps below, submit a single Microsoft Word file named LabWeek4xxx.docx (where xxx = your initials).
Step Deliverable Points
2Successfully import and open AdventureworksDW2017. 10
3Create Donut Chart using the following fields: Freight, Dealer Price, Unit Price. Create Bar Chart using the following fields: Freight, Dealer Price, Unit Price. 10
4On your own, develop two additional charts using the chart options you select and using the fields available (20 points per chart). 40
5 – Reflection Include a discussion of your results and recommendation for the CEO. 20
Total A quality assignment will fulfill all criteria 80
Required Software
BIAM410
Week 5: Lab Overview
TABLE OF CONTENTS
Week 5 Lab: Plotting Entities in Network Navigator
Scenario
The CEO of your organization is looking for some specific analytics from the AdventureWorks data warehouse, particularly any network relationships. Your responsibility is to select several Entities from the AdventureWorks data warehouse and plot them in NetworkNavigator found in PowerBI. You have access to MS PowerBI and will need to develop multiple charts from the data warehouse results and report back to the manager.
•Server: ML-RefVm-820127
•Database: AdventureWorksDW2017
Deliverables
After completing the steps below, submit a single Microsoft Word file named LabWeek5xxx.docx (where xxx = your initials). This file will contain the following:
1.A network chart screenshot containing the following fields: FrenchDescription, ProductLine, and Freight.
2.A screenshot viewing the tables with relationships in the PowerBI window.
3.A screenshot of a network chart you have created using 3 fields of your choice.
4.A discussion of your results and recommendations for the CEO.
You will be graded based on the rubric below:
Step Deliverable Points
3Create network chart using the following fields: FrenchDescription, ProductLine, and Freight. Take a screenshot. View the tables with relationships in the PowerBI window and take a screenshot. 30
4On your own create a network chart using three fields of your choice. Hint: select first two fields from Dim table with third field from Fact table. Take a screenshot. 30
5 – Reflection Include a discussion of your results and recommendation for the CEO. 20
Total A quality assignment will fulfill all criteria 80
BIAM410
WEEK 6 Course Project Research Paper
Applications of Big Data
Objectives
Write a 5-7 page research paper describing a current or future use of big data and the importance of data security. This assignment is due Week 6.
The term big data refers to the vast amounts of data being generated from such sources as social media, real-world sensors (for weather, water quality, vehicle traffic, etc.), cellphone GPS signals, and customer transactions. The sheer volume of these data and new ways of managing and analyzing them are enabling applications in many fields that were not possible just a few years ago. Your paper will describe in detail one of these emerging applications of big data.
Possible topics for your paper include but are not limited to the following.
•Big data in e-commerce or retailing
•Big data in government
•Big data in healthcare
•Big data security
•Big data in manufacturing
•Big data in fraud detection
•Big data in location-based services
Your paper should address the following questions regarding your chosen topic.
•What new uses of big data are likely in this field in the next 5 years?
•What are the main sources of big data used in this field?
•What type of data security should be included?
•What problems or issues are encountered in using big data in this field?
Guidelines
•Please include a title for your paper.
•Final Papers must be 5 to 7 pages in length (not including the cover, but including the reference list); be in 12-point, Times New Roman font; be double spaced; and include a cover page, introduction, body of the paper, summary or conclusion, and references.
•Papers must follow APA format. Please use the following APA resources:
oAPA module in SuccessU in the Student Resource Center
oDeVry University Library’s APA / Writing CenterLinks to an external site.
•At least four current, authoritative references are required. Anonymous authors are not acceptable. Web sources, if used, must be authored by recognized experts in the field. At least three references must be peer-reviewed, scholarly papers. At least four references must have publication dates within the last five years. All should be listed on the last page, titled References.
•Appropriate in-text citations are required in the body of the paper, wherever specific information from a source is used.
•All DeVry University policies are in effect, including the academic integrity policy.
•At least 80% of the paper should be in your own original wording. A maximum of 20% of the paper may consist of word-for-word quotations from a source. All word-for-word quotations must be placed in quotation marks or APA block-quote format, and the source must be cited. NOTE: Saying that 20% of the paper may consist of word-of-word quotations is NOT the same as saying the paper may have a score of 20% from TurnItIn or a similar originality checker. Word-for-word quotations that are properly enclosed in quotation marks and cited are not counted by TurnItIn. Any passage that is quoted word-for-word must being properly enclosed in quotation marks and cited. Contact the professor if you have questions about these requirements for originality and proper citation of sources.
•Any questions about this paper may be discussed in the weekly Course Q & A Forum discussion topics.
•See the Grading Rubrics section of this page for point values and grading criteria for this deliverable.
Best Practices
The following are the best practices in preparing this paper.
•Cover page—Include who you prepared the paper for, who prepared it, and the date.
•Abstract—Summarize the main ideas of your paper in 150-250 words.
•Introduction—The purpose of an introduction or opening is to introduce the subject, indicate why the subject is important, preview the main ideas, and establish a tone for the document. Include in the introduction a reason for the audience to read the paper. Also, include an overview of what you are going to cover in your paper and the importance of the material. (This should include or introduce the questions you are asked to answer on each assignment.)
•Body of the paper—State the main ideas, state major points in each idea, and provide evidence. There should be a separate, clearly labeled section with a section heading that addresses each question listed in the Objectives section.
•Summary and Conclusion—Summarizing is similar to paraphrasing but presents the gist of the material in fewer words than the original. An effective summary identifies the main ideas and major support points from the body of your report. Minor details are left out. Summarize the benefits of the ideas and how they affect the field or industry.
•References—Use the APA reference format as specified in the DeVry University Library’s APA resources. Include in-text citations in the body of the paper in the form (author, year) and references in the reference list at the end. Use hanging indent format, and put the reference list in alphabetical order.
Additional hints on preparing the best possible project.
•Apply a three-step process of writing: plan, write, and complete.
•Prepare an outline of your research paper before you go forward.
•Complete a first draft and then go back to edit, evaluate, and make any changes required.
•Use visual communication to further clarify and support the written part of your report. You could use example graphs, diagrams, photographs, flowcharts, maps, drawings, animation, video clips, tables, and infographics. If you use an image or other media from a source, be sure to cite the source where used in the paper and include the source in the reference list at the end.
BIAM410
Week 7: Hadoop Research Assignment Overview
TABLE OF CONTENTS
Scenario
You have heard a lot about Hadoop and are curious as to how it works and if it will be beneficial for a data analysis to understand and be able to use Hadoop for analyzing large data sets.
Deliverables
After completing the steps below, submit a single Microsoft Word document file named Week7Hadoopxxx.docx (where xxx = your initials). This file will contain your answers to the questions below after watching both videos. You will be graded based on the following rubric:
Step Deliverable Points
1Watch both videos. One is 30 minutes and the other is 1 hour. (Practical example begins after 35 minutes). Answer the 8 questions. Include screenshots as needed. 60
2 – Reflection Include a discussion of your results and recommendation for the CEO. 20
Total A quality assignment will fulfill all criteria 80
Required Software
BIAM410
Week 8: Hadoop-Spark Research Assignment Overview
TABLE OF CONTENTS
Scenario
You helped the CEO last week by researching Hadoop and how it works. You did such a great job for the CEO, you are now being asked to explain how the data tool Spark works with Hadoop.
Deliverables
You are expected to report back to the CEO with answers to the questions that they have posed. You will also have to include a recommendations on what software the company should use.
After completing the steps below, submit a single Microsoft Word document file named Week8Sparkxxx.docx (where xxx = your initials). This file will contain your answers to the questions as well as your recommendations. You will be graded based on the following rubric:
Step Description Points
Step 1 Research of two items is completed. 20
Step 2 Six tasks and questions are answered. 60
Step 3 Written report to CEO. 20
Total A quality assignment will fulfill all criteria 100
