Write SQL scripts in Microsoft SQL Server for OLTP
Write SQL scripts in Microsoft SQL Server for OLTP that include:
A Data Definition Language (DDL) script creating the four tables with appropriate data types, primary and foreign keys
Data Manipulation Language (DML) scripts that insert a minimum of five records into each table
Select scripts showing the full contents of each table
Write and run a test script for Step Two.
Save a screenshot of the results.
Create a 6-page Technical Design Document that includes the SQL scripts in Microsoft SQL Server, test script, and screenshots.
Compress the Technical Design Document that includes the SQL scripts in Microsoft SQL Server, test script, and screenshots into a ZIP file.
Submit the ZIP file.
DBM384 Special Purpose Databases
Week 2 Assignment
Specialist Database Presentation
Your team is tasked with creating an inventory price-tracking database for a company of your choosing.
Complete the following steps in Microsoft® SQL Server® 2016:
Create a table. This table will tell you what price the inventory item started at, what it currently is, and what it ends at. The primary key must be a combination of the inventory number and the current price date. Elements to include in your table are as follows:
a. Inventory Number Integer
b. Inventory Description Text
c. Date Price Previous Date
d. Date Price Current Date
e. Date Price End Date
f. Inventory Price Decimal
Create an insert statement that will be used to initially add an inventory item. Remember that the previous date will be null, the start date will be today’s date, and the end date will be null.
Create a stored procedure that will be used to update the price of an item. The procedure will take the inventory number and a new price.
Update the current record by updating the Date Price End Date to be the current date. Then, create a new inventory record where the Date Price Previous Date is today’s date, Date Price Current Date is tomorrow’s date (price is effective tomorrow), and Date Price End Date is null. Remember to bring over the inventory description and inventory number, as well.
Create a query that will display a list of all the current inventory prices.
Write and run a test script for Steps Two through Four.
Save a screenshot of the results.
Compress your DDL and DML scripts, query, test script, and results into a ZIP file.
DBM384 Special Purpose Databases
Week 2 Assignment
Data Warehouse Project, Part II
Includes 5 Long Answers to Discussion Question
Use the credit card database from the Week One Individual assignment, “Data Warehouse Project, Part I,” as a basis for the creation of a data warehouse.
Complete the following steps:
Define the business requirements, objectives, and deliverables.
Declare the grain.
Choose dimensions.
Identify fact duration.
Identify facts.
Create a 6-page Technical Design Document that includes a description of the five steps above and the results of completing the steps for the credit card data warehouse.
Submit your Technical Design Document.
DBM384 Special Purpose Databases
Week 3 Assignment
Specialist Database Presentation
Determine how spatial components can be used in the database your team created in Week Two.
Create an Entity Relationship Diagram (ERD) with spatial components. Use Microsoft Visio to create this diagram.
Include a minimum of two spatial components from the list below:
Longitude/Latitude
Polygon
Points
Lines
Add comments to your ERD explaining the components and their use.
DBM384 Special Purpose Databases
Week 3 Assignment
Data Warehouse Project, Part III
Use Microsoft Visio and an Excel spreadsheet or Word document to complete this assignment.
Identify and define all dimension attributes for the dimensions identified in the Week Two Individual Assignment, “Data Warehouse Project, Part II”.
For each dimension, document the following information for each of its attributes:
Name
Description
Type
Characteristics
Design a star schema diagram for the credit card data warehouse.
Create a 5-page Technical Design Document that includes both the dimension definitions for each dimension attribute and the star schema diagram.
Submit your Technical Design Document.
DBM384 Special Purpose Databases
Week 4 Assignment
Specialist Database PresentationComplete the following activities:
In the SQL Server 2016 query window, write an XML script that provides an update to one table in the database your team created in Week Two.
Write a XML script that provides a data feed to an external dashboard used by the executives of the company. The Dashboard is not required to be turned in only the XML code providing the data.
Write and run a test script for Steps One and Two.
Save a screenshot of the results.
Add comments to your XML scripts explaining the benefits to your Learning Team company that a mobile database would provide the existing data system.
Compress your XML scripts, comments, test script, and results into a ZIP file.
Submit the ZIP file.
DBM384 Special Purpose Databases
Week 4 Assignment
Data Warehouse Project, Part IV
Complete the following using Microsoft SQL Server 2016:
Write SQL scripts that include the following:
A Data Definition Language (DDL) script creating the dimension and fact tables for the credit card database
Data Manipulation Language (DML) scripts that insert records into each table from the data created in the OLTP database created in Week One
Select scripts showing the full contents of each table
Create a star schema using the Database Diagram tool in SQL Server®
Write and run a test script for Step One.
Save a screenshot of the results.
Create a 5-page Technical Design Document that includes the following topics:
The star schema diagram
The SQL DDL scripts
The SQL DML scripts
The test script
The screenshot of the results from running the scripts
Submit your Technical Design Document.
DBM384 Special Purpose Databases
Week 5 Assignment
Specialist Database PresentationCreate a 1-page Technical Design Document, which describes the business use of the Learning Team company’s database.
Include scripts and Entity Relationship Diagrams (ERDs).
Submit your document.
DBM384 Special Purpose Databases
Week 5 Assignment
Data Warehouse Project Final Project
Finalize the credit card data warehouse by completing the following steps using MicrosoftSQL Server 2016:
Write a Select query against the fact table and one dimension table.
Write a Select query against the fact table and at least two dimension tables, one of which should be the time dimension.
Create a view that queries the fact table and three dimension tables.
Write and run a test script for steps one through three.
Save a screenshot of the results.
Complete the 6-page Technical Design Document that includes the SQL queries created for Week Five, the test script, and screenshots of the results.
Construct a final 15-slide tutorial in Microsoft PowerPoint with speaker notes that addresses the following criteria:
Differentiates between general purpose and specialized databases
Identifies uses of special purpose databases in business
Introduces goals for effective database design
Explains how temporal databases are used in business
Explains how Structured Query Language (SQL) is used with temporal databases
Demonstrates how a temporal database is used in business
Explains how spatial databases are used in business
Explains how SQL is used with spatial databases
Explains the importance of indexing with spatial databases
Demonstrates how a spatial database is used in business
Demonstrates how an eXtensible Markup Language (XML) database is used
Explains how multimedia databases are used in business
Explains how NoSQL databases are being used to store unstructured business data
Explains how mobile databases are used in business
Contrasts Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) databases
Describes data warehouses and data mining
Explains knowledge management
Explains the process of information retrieval
Submit the Technical Design Document and the tutorial presentation.
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
