my portfolio
DESCRIPTION
My SetFocus PortfolioTRANSCRIPT
Table of Contents
• Piggybank Project……………………………………………3
• SSRS Sample Reports……………………………………..27
• SSIS / SSRS Project………………………………………….31
• Final Group Project…………………………………………41
• What is SetFocus?.............................................76
2
PiggyBank Specifications The PIGGY Bank Project
Following from your initial design of the PiggyBank, the senior developer would now like you to implement and test the database. He has handed you the following revised ER
diagram/specifications and advises you to use the checklist (gradesheet rubric) so that you are clear on what is absolutely necessary for you to implement in the project. There may be several features suggested in your original design that are not necessary at this time- (implement above and beyond as you see fit- but no
additional credit will be awarded). The datatypes utilized in the diagram are a rough indication of what's expected from you, but feel free to modify them and add constraints, user defined datatypes, as you see fit (the datatypes utilized in the diagram is all you need for full credit however)
After you build the database and set each of your PK identity properties to true, you will need to run the PiggyBank OPENXML script.sql to add the preexisting customer and
account information to your database. This script will NOT add the transactions information from the xml file- this section of the script has been left for you to complete as 1% point Extra Credit.
It is important that all further directives regarding the following specifications (including the gradesheet) be properly documented. Please email any questions to your
Instructor and any important clarifications will be emailed back to you.
Customers: We need to track the customer's name, address, e-mail, home phone, work phone, mobile phone. The name, address and the Home phone number is required. You will need
to be able to both create a customer, and update the customer's personal information.
Accounts: You are only required to support 2 types of accounts: Checking and Saving. Both accounts are tracked by an account number that is assigned by us. The account number
should be 6 digits. We would like the account number to be automatically generated and incremented by 1. One customer can open multiple accounts (and no limits as to how many accounts as long as an initial deposit of $20 for each checking/savings account is made- assume that for higher interest money market and savings
accounts that a higher deposit will be expected-but that this is outside the scope of the current specs). You will need to create accounts for a given customer, and also have the ability to update these accounts (specifics for Update Account are included later). Note that you can break these procedures up as you see fit, i.e.
CreateChecking / CreateSavings in lieu of CreateAccount would be accepted for full credit, but throughout the spec the processes are referred to as CreateAccount and UpdateAccount. To allow for the variations we might receive- the rubric might list grade points for “can be created” or “can be updated”- meaning that there are
different ways that you could approach the requirements and that ultimately we just want to see successful processes.
3
PiggyBank spec’s cont.Account StatusNo account can be deleted- but they can be closed (made “inactive” according to the supplied AccountStatusID) via a customer's request. Upon the closing of an account all remaining money
should be withdrawn from an account. Such an account can also be reopened (made “active”) as long as an initial deposit is made. If an account has a negative balance, an additional deposit should be required before the account can be closed. Feel free to be creative with additional AccountStatusID‟s as you see fit.
Overdraft- two types-“Specific account” and “General” If a customer has more than one account, he/she can also sign up to have one of these accounts be set up as a specific overdraft account for the other. The advantage in doing this is that
additional transactions will process for a relatively small $10 service charge/fee as long as the customer has the money in another account (versus the $30 service charge/fee for the general overdraft explained in the next paragraph). Any other account that is linked through the same customerID in the customerAccounts table can be used as an overdraft account (only one is specified at a time after they fill out the appropriate paperwork). Note that many of the existing customers (in the XML file/script provided) do have specific overdraft accounts. Always charge the $10 service charge/fee to the account that originated the transaction- not the overdraft account- even if money is left in the overdraft to cover such a charge.
Each checking or savings account owner has the option to sign up for a general overdraft by the bank in the amount of $400. The criteria for establishing a customer's eligibility for such an
overdraft will be related to their overall credit worthiness and also whether they have signed up for direct deposit of their payroll checks. It is not necessary for you to implement anything with relation to this eligibility criteria. Also the bank will certainly have a method of limiting their risk from someone from being overdrawn $400 in multiple accounts at the same time- do not worry about this either.
The service charge/fee for using general overdraft is $30 for each time the overdraft is used. This service charge should always be a separate forced transaction and should be charged to the
originating account regardless of balance (i.e. if a transaction would overdraw an account -$400.00(assume there is no specific overdraft account and it is covered by a general overdraft to -$400.00), the transaction would be allowed and then a $30 service charge/fee assessed, for a new balance of -$430.00. If a transaction would take the balance to -$400.01, the transaction would not be allowed. Existing (i.e. those in the XML file/script) and future customers should not be signed up for this general overdraft by default.
Transaction Types- Deposits, Withdrawals-Purchases, Transfers Deposits, should be very straight forward to implement- We need a procedure that will insert the transaction into the transactions table, and update the account balance in the accounts table.
Anyone can deposit money into any account- and besides your standard input validations, the only additional requirement with respect to the general overdraft is Extra Credit (and is explained in that section of the rubric). Keep in mind that initial deposits of $20 are required when opening or re-activating an account – although this will likely be a feature of your CreateAccount and UpdateAccount procedures rather than your deposit procedure.
For the purposes of this project, assume that policy concerning withdrawals, purchases via check card, and checks will be treated exactly the same (although if you choose that a bounced check
should have a additional service charge/fee- this would be a great addition- but no additional credit will be awarded for doing so). It is recommended that you use a @transactionTypeID input
4
Piggybank spec’s cont.parameter for a withdrawal/purchases procedure- rather than utilize three separate procedures. When designing your withdrawal/purchases procedure- it is not necessary to
combine the use of both overdraft accounts. I.E. first check that they have a specific overdraft and that there is enough of a balance in the specific overdraft account- if there is not enough money there, do NOT attempt to utilize the remaining balance from that specific overdraft account. Check next that they have the general
overdraft, and if so that the balance of the originating account would not slip below -$400.00 to see if the withdrawal/purchase should succeed.
To further explain this: you should NOT process a $150 withdrawal/purchase by applying $50 from the specific overdraft and then $100 from the general. If there is not enough in the specific overdraft account- it should be taken from the general overdraft in full (or refused if it exceeds the limits of the general overdraft).
Transfers are a means for a customer to transfer funds between accounts freely for any reason. To transfer funds between accounts, a customer must be connected to an
account through the CustomerAccounts table. Do not charge for transfers or allow balances to slip below 0 when a customer requests a transfer (There is no overdraft for a transfer).
UpdateAccount
You will also need to be able to update each account at various times. You may implement such functionality as you see fit (feel free to break down the processes into multiple procedures/functions should you wish).
Update Account should:
1) Add a customer to a given account (Insert into customerAccounts table) 2) Add a specific overdraft account (An Insert of a six digit Account number into the appropriate column in the Accounts table)
3) Add the general overdraft to an account (change the Bit field in the Accounts table) 4) Make an account active/inactive (As far as account status's I only put these two in via that XML script, but you may add as many as you wish, i.e. „Frozen account – FBI
seizure‟, but only these two are necessary according to our rubric).
Otherwise, as stated before- if you choose to separate these features into separate stored procedures- this is also acceptable for full credit.
For #2 – adding the overdraft account, you cannot add just any account as an overdraft. It must in fact belong to at least one of the owners of the account that you are updating. This is a necessary validation included on the rubric as a grade point.
Interest Rates
The Savings account has a fixed interest rate, which is currently 3% per year. Be sure to insert this interest rate (include this in your final submission scripts). We may need to change this interest rate depending on external economic factors, and you will need to provide a procedure that can be used for this purpose. You will not be required
to calculate any interest during the duration of this project, but there are some considerations you may approach that are listed in the „Extra Credit‟ section in the rubric.
5
Piggybank spec’s cont.DML Trigger
Deletions (not updates) from the Accounts table should be prevented via use of a DML Trigger.
Transactions: Every action performed on an account ex. Deposit, Withdrawals, Purchases, Transfer, Fees etc, must be recorded. These transactions cannot be modified or deleted and
therefore any corrections must be reflected by a new entry- and not a deletion. We would like you to prevent updates and deletions to the Transactions table through the use of a DML trigger. As there are (potentially) multiple customers associated with an account- be sure you record the Customer ID of the customer who performs
a given transaction.
Additional Application Requirements: The application will support all actions like Deposit, Withdraw/Purchase, Transfer funds, obtaining account history (of current month), account history (of any month), etc.
ATM Procedures
The Bank ATM application will utilize two procedures- GetBalance, shows current balance with last 5 transactions, and GetTransactions, which will show the transactions on an account for the last 30 days. Assume that these procedures can only be used after a customer has successfully logged in to an ATM, so input validations regarding
customerID and accountID are unnecessary.
Statement Procedures The Bank tellers will use a windows application to connect to the database and perform their duties. The teller will need to search for customers by phone, name, or account
number. The teller will also need to obtain statement information by customer and account number. One procedure should obtain this information for a given customerID and accountID for the current month. A second should make this information available historically with additional @month and @year input parameters.
A third procedure should list all Checking and Savings accounts for a given customerID.
Views While an OLAP database is certainly also in the works for reporting purposes, we are requiring that for the time being, you utilize appropriately designed views (indexed views
if possible), for each of the 5 required stored procedures above. Please enable Schemabinding for additional security.
Search Engine Queries Please provide a means to search for customers and account information by Name, Phone Number, and AccountID. Procedures or Functions are encouraged here, but simple
queries similar to your “Junglebooks” exercises during the TSQL week will suffice if necessary.
Triggers: Please implement a DDL trigger to guard tables and views within your database from unwanted changes as well as the DML Triggers requested above.
XML Script/Data:
The data from the old system has been output to an XML file. You have been provided with a basic script for importing this data into the new application. 6
Piggybank spec’s cont.Test Scripts:
Please provide us with test scripts, which our testing department will use to evaluate your solution. These test scripts are worth 8 points, so please treat them with the appropriate importance. They should be easy to navigate, and use only test data that you can guarantee will be in the database (hint: because it‟s in the xml file, or
that has been added by you within the script – i.e. CustomerID 31, or Account 100066 would be the first ones you could enter additionally via your procedures).
Deliverables: Provide our IT department with all the scripts for creating the database, i.e. CREATE DATABASE… CREATE TABLE…. CREATE VIEW…. CREATE PROCEDURE… the OPENXML script,
in one all-inclusive script. Please separate your CREATE TRIGGER scripts from the rest (as we will test these separately). Also provide at least one separate test script to test each object. You are not specifically required to separate your test scripts into two- between successful tests and failed validation tests as a requirement but it is certainly a good idea to do so if your script is difficult to navigate. Please .zip up each of the scripts (if you have more than 7, you should certainly combine them) and
name the file according to the following convention: Piggybank_YourName_05-14-10.zip
Good Luck: It is suggested that first you build the database according to the diagram provided and then utilize the PiggyBank OPENXML script.sql to insert the data.
7
Database Schema
Transactions
PK,I1 TransactionID
FK1 AccountIDFK3 TransactionTypeIDFK2 CustomerID TransactionDate TransactionAmount NewBalance
Account
PK,I1 AccountID
FK2 AccountTypeIDFK1 AccountStatusID CurrentBalance OverDraftAccountID GeneralOverdraft
AccountStatus
PK,I1 AccountStatusID
AccountStatus
TransactionType
PK,I1 TransactionTypeID
TransactionTypeName
AccountType
PK,I1 AccountTypeID
AccountTypeName InterestRate
Customer
PK,I1 CustomerID
CustomerFirstName CustomerLastName CustomerMiddleInitial Street City State ZipCode Email HomePhone WorkPhone CellPhone
CustomerAccount
CustomerAccountIDFK1 AccountIDFK2 CustomerID
8
Sample Stored Procedure--==============================================================/****** Object: StoredProcedure [dbo].[uspUpdateInterestRate] ******/-- Installation: SetFocus-- Author: William P. Ringel-- Script Date: 11/23/2010 10:54:05-- Change History: None----==============================================================-- error numbers-- 1 = Interest Rate is required-- 2 = AccountTypeID is required-- 3 = AccountTypeID is invalid--==============================================================CREATE PROCEDURE dbo.uspUpdateInterestRate( @InterestRate decimal(2,2) = NULL, @AccountTypeID tinyint = NULL)
AS
BEGIN SET NOCOUNT ONEND
BEGIN TRY --Checks if the Interest Rate is null IF @InterestRate is null BEGIN RAISERROR('Interest rate must be entered',14,1) END --Checks if the AccountTypeID is null IF @AccountTypeID is null BEGIN RAISERROR('AccountTypeID must be entered',14,2) END
--Checks if the AccountTypeID is valid IF NOT EXISTS (SELECT * FROM AccountType WHERE AccountTypeID = @AccountTypeID) BEGIN RAISERROR('Account Type is invalid',14,3) END BEGIN TRAN --Inserts a new item into the Customer table UPDATE dbo.AccountType SET InterestRate = @InterestRate WHERE AccountTypeID = @AccountTypeID COMMITEND TRYBEGIN CATCH --If the proc was in a transaction when the error occurred if @@trancount> 0 BEGIN ROLLBACK END --Raises a new error to the caller DECLARE @ERRMSG NVARCHAR(1000) ,@ERRSEVERITY int ,@ERRSTATE int SELECT @ERRMSG = ERROR_MESSAGE(), @ERRSEVERITY = ERROR_SEVERITY(),
@ERRSTATE= ERROR_STATE() RAISERROR(@ERRMSG,@ERRSEVERITY,@ERRSTATE)
END CATCH RETURN
9
Database Trigger-- ================================================-- Trigger to Prevent Drop/Alter on Views/Tables -- ================================================
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'ddlDatabaseStopDDLEvents') DISABLE TRIGGER [ddlDatabaseStopDDLEvents] ON DATABASE
GO
USE [PiggyBank]GO
/****** Object: DdlTrigger [ddlDatabaseTriggerLog] Script Date: 11/24/2010 16:35:15 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class_desc = 'DATABASE' AND name = N'ddlDatabaseStopDDLEvents') DROP TRIGGER [ddlDatabaseStopDDLEvents] ON DATABASEGO
USE [PiggyBank]GO
/****** Object: DdlTrigger [ddlDatabaseTriggerLog] Script Date: 11/24/2010 16:35:15 ******/
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
] ON DATABASE--GO
CREATE TRIGGER [ddlDatabaseStopDDLEvents] ON DATABASE FOR DROP_TABLE, ALTER_TABLE, DROP_VIEW, ALTER_VIEW
AS
BEGINSET NOCOUNT ONEND
BEGIN RAISERROR ('You cannot modify this database - period!!!',16,-1) ROLLBACK END GO
--DISABLE TRIGGER [ddlDatabaseStopDDLEvents
10
Account Delete Trigger-- ================================================-- Trigger to Prevent Deletes to Account table -- ================================================
USE PiggyBankGO
/****** Object: Trigger [trAccountDelete] Script Date: 11/24/2010 16:09:16 ******/IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].
[trAccountDelete]'))DROP TRIGGER [dbo].[trAccountDelete]GO
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
-- =============================================-- Author: William Ringel-- Create date: 11/23/2010-- Description: Instead of on Account - Delete-- =============================================CREATE TRIGGER dbo.trAccountDelete ON dbo.Account INSTEAD OF DELETE
AS
BEGIN TRY BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements. SET NOCOUNT ON;
RAISERROR('Deleting rows from the Account table is forbidden',14,1) END END TRY
BEGIN CATCH --If triggered from within a transaction if @@trancount> 0 BEGIN ROLLBACK END --Raises a new error to the caller DECLARE @ERRMSG NVARCHAR(1000) ,@ERRSEVERITY int ,@ERRSTATE int SELECT @ERRMSG = ERROR_MESSAGE(), @ERRSEVERITY = ERROR_SEVERITY(),
@ERRSTATE= ERROR_STATE() RAISERROR(@ERRMSG,@ERRSEVERITY,@ERRSTATE)
END CATCH RETURNGO
11
Transaction Update Trigger-- ================================================-- Trigger to Prevent Updates to Transactions table-- ================================================
USE PiggyBankGO
/****** Object: Trigger [trTransactionsUpdate] Script Date: 11/24/2010 16:09:16 ******/IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].
[trTransactionsUpdate]'))DROP TRIGGER [dbo].[trTransactionsUpdate]GO
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
-- =============================================-- Author: William Ringel-- Create date: 11/23/2010-- Description: Instead of on Transactions - Update-- =============================================CREATE TRIGGER dbo.trTransactionsUpdate ON dbo.Transactions INSTEAD OF UPDATE
AS
BEGIN TRY BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements. SET NOCOUNT ON;
RAISERROR('Updating Transactions table is forbidden',14,1) END END TRY
BEGIN CATCH --If triggered from within a transaction if @@trancount> 0 BEGIN ROLLBACK END --Raises a new error to the caller DECLARE @ERRMSG NVARCHAR(1000) ,@ERRSEVERITY int ,@ERRSTATE int SELECT @ERRMSG = ERROR_MESSAGE(), @ERRSEVERITY = ERROR_SEVERITY(),
@ERRSTATE= ERROR_STATE() RAISERROR(@ERRMSG,@ERRSEVERITY,@ERRSTATE)
END CATCH RETURNGO
12
Transaction Delete Trigger-- ================================================-- Trigger to Prevent Deletes to Transactions table-- ================================================
USE PiggyBankGO
/****** Object: Trigger [trTransactionsDelete] Script Date: 11/24/2010 16:09:16 ******/IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].
[trTransactionsDelete]'))DROP TRIGGER [dbo].[trTransactionsDelete]GO
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ONGO
-- =============================================-- Author: William Ringel-- Create date: 11/23/2010-- Description: Instead of on Transactions - Delete-- =============================================CREATE TRIGGER dbo.trTransactionsDelete ON dbo.Transactions INSTEAD OF DELETE
AS
BEGIN TRY BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements. SET NOCOUNT ON;
RAISERROR('Deleting rows from the Transactions table is forbidden',14,1) END END TRY
BEGIN CATCH --If triggered from within a transaction if @@trancount> 0 BEGIN ROLLBACK END --Raises a new error to the caller DECLARE @ERRMSG NVARCHAR(1000) ,@ERRSEVERITY int ,@ERRSTATE int SELECT @ERRMSG = ERROR_MESSAGE(), @ERRSEVERITY = ERROR_SEVERITY(),
@ERRSTATE= ERROR_STATE() RAISERROR(@ERRMSG,@ERRSEVERITY,@ERRSTATE)
END CATCH RETURNGO
13
Search Accounts by Phone
14
Search Accounts by Customer ID
15
Search Accounts by Account ID
16
Standard Withdrawal
17
Overdrawn Withdrawal
18
Deposit
19
Close Account
20
Reopen Account
21
Transfer between Accounts
22
Account Listing
23
Get ATM Balance
24
30 Day Transaction History
25
Monthly Transaction History
26
Sample Reports from SSRS Labs
27
Sample Pie Chart
28
Sample Stacked Bar Chart
29
Matrix with Embedded Gauge
30
SSIS / SSRS Project• The company Mini-AD is interested in taking historical spreadsheet(CSV) data for
their list of products, vendors, and purchase order history, and loading the data into a SQL Server database.
• Mini-AD wants the load process to work on a go-forward basis, so that new/modified products/vendors/orders can be loaded in SQL Server as well.
• Mini-AD’s load process for orders should validate that any incoming orders with product numbers or vendor numbers that do not match an existing product/vendor number should NOT be written to the SQL Server database. Instead, this data should be written to an exception file and emailed.
• Mini-AD also wishes to build two reports: one for top vendor and product sales, and the second for sales by vendor and ship method across years.
31
ERD DiagramVendor
PK,I1 VendorID
AccountNumber Name CreditRating DateModified DateInserted
Product
PK,I1 ProductID
ProductName ProductNumber ListPrice DateModified DateInserted
PurchaseOrderDetail
FK2 PurchaseOrderID PurchaseOrderDetailID OrderQtyFK1 ProductID UnitPrice LineTotal DateModified DateInserted
PurchaseOrderHeader
PK,FK1,I1 PurchaseOrderID
POHeaderNumberFK3 VendorIDFK2 ShipMethodID OrderDate Freight TotalDue DateModified DateInserted
ShipMethod
PK,I1 ShipMethodID
Name DateModified DateInserted
32
Database Creation
33
Vendor Load
34
Product Load
35
Shipper Load
36
Orders Load
37
Top n Products within Top n Vendors Report
38
Vendor Sales by Year Report
39
Report Deployment• Take the report rptSalesMatrixbyYear and deploy it to localhost.
• Create a shared schedule to run every night at midnight.
• Create 2 linked reports for the 2 ship methods of Cargo Transport 5 (ShipMethodID of 5) and ZY – Express (ShipMethodID of 2).
– Call the 2 linked reports linkedrptSalesMatrixByYear_CargoTransport and linkedrptSalesMatrixByYear_ZYExpress.
• Create a report snapshot associated with each of the 2 linked reports and with the shared schedule. Make sure to save executed snapshots to report history.
• Create 2 email subscriptions associated with each of the 2 linked reports. Email a PDF output to [email protected]
• (Make sure the margins of the report are good and that no page truncation/extra pages occur). Associate the 2 email subscriptions to the shared schedule
40
WLR Data Managers, LLC
The Blockflix Database
Final Team Project
41
The WLR Data Design
The Blockflix DatabaseBill Ringel
Clive Lendore & Paulette Warrick, Team Leader
Offers controlSecurity
Centralization
42
Your Normalized Database
43
Data Control Design
Managing The Blockflix Database
Manage YourInventoryCustomersMembers& Transactions
44
Data Control Examples
Embedded ProceduresEmbedded Triggers
Hidden Data RestrictionsTransactions
BillingManage Inventory
45
46
47
Managing The Memberships
48
49
XML Formatted Data<?xml version="1.0" ?> <Rowset> <Row> <MovieID>1</MovieID> <Title>"The Great Locomotive Chase"</Title> <Rating>1</Rating> <StarRating>4</StarRating> <Description>"Edison's Famous"</Description> <Genre>1</Genre> <ReleaseYear>1979</ReleaseYear> <MoviePrice>25.99</MoviePrice> <StoreID>1</StoreID> <Quantity>10</Quantity> <A1_CastTypeID>1</A1_CastTypeID> <A1_CastMemberID>1</A1_CastMemberID> <A2_CastTypeID>1</A2_CastTypeID> <A2_CastMemberID>2</A2_CastMemberID> <A3_CastTypeID>1</A3_CastTypeID> <A3_CastMemberID>4</A3_CastMemberID> <P1_CastTypeID>3</P1_CastTypeID> <P1_CastMemberID>5</P1_CastMemberID> <P2_CastTypeID>3</P2_CastTypeID> <P2_CastMemberID>8</P2_CastMemberID> <D1_CastTypeID>2</D1_CastTypeID> <D1_CastMemberID>5</D1_CastMemberID> <D2_CastTypeID>2</D2_CastTypeID> <D2_CastMemberID>8</D2_CastMemberID> </Row></Rowset>
50
XML Schema<?xml version="1.0"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Rowset">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="unbounded" name="Row">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="MovieID" type="xs:unsignedInt" />
<xs:element minOccurs="0" name="Title" type="xs:string" />
<xs:element minOccurs="0" name="Rating" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="StarRating" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="Description" type="xs:string" />
<xs:element minOccurs="0" name="Genre" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="ReleaseYear" type="xs:unsignedShort" />
<xs:element minOccurs="0" name="MoviePrice" type="xs:decimal" />
<xs:element minOccurs="0" name="StoreID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="Quantity" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="A1_CastTypeID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="A1_CastMemberID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="A2_CastTypeID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="A2_CastMemberID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="A3_CastTypeID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="A3_CastMemberID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="P1_CastTypeID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="P1_CastMemberID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="P2_CastTypeID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="P2_CastMemberID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="D1_CastTypeID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="D1_CastMemberID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="D2_CastTypeID" type="xs:unsignedByte" />
<xs:element minOccurs="0" name="D2_CastMemberID" type="xs:unsignedByte" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
51
XML Data Load
52
Online Customers
Movie Queue53
54
Governing Movie Checkouts
55
56
57
58
Managing Lost Or Damaged Inventory
59
60
Data Maintenance Design
Weekly Data Backups&
Database Replication
61
62
63
Data Maintenance Design
Reports
64
65
1. Tony Rodriguez $287.68
2. Billy Joe McNeal $319.68
3. John Henry $319.68
4. Rashidah Jones $399.68
5. Sean Connery $479.68
6. Milly Vanilly $559.68
7. Peter Wolf $639.68
8. Denzel Harrington $687.68
1. Million Dollar Baby
2. Jaws
3. Jaws 2
4. Moby Dick
66
67
1 9 3 4 11/11/2007 $4.99 $0.00
6 23 3 15 6/7/2009 $4.99 $4.99
6 3 3 21 7/7/2009 $4.99 $0.00
Security
Seated behind the Firewall
System Configuration Checker
AuthenticationSchema Management
Granular Role Authorizations
68
Security Levels
69
70
Kiosk Management Design
Adding MoviesFee Triggers Transactions Management
71
Video Streaming Proposal
Our VCMS72
Video Content Management SystemOverview
• What is VCMS?– A platform being deployed to
accelerate and increase the capacity for content processing
– The VCMS has a targeted Delivery of 4Q 2011
• Video Content Management System (VCMS) Functions:– Ingests video content using multiple
input sources and formats– Transforms ingested video into
appropriate formats for distribution– Publishes transformed videos to
multiple distribution channels
73
Digital Distribution Clearinghouse (DDC)Video CMS + CDN
• VeriCast has proposed a new WLR product offering called DDC that addresses members
• DDC is essentially a product bundling of BlockFlix and WMR’s VCMS services
Customer Segments: Key Challenges
CONTENT CREATOR
Capitalizing on new distribution channels while maintaining profitability
Transitioning from current to future distribution without placing ‘bets’ on all emerging technologies
DIGITAL MEDIA RETAILER
retailer
DIGITAL DISTRIBUTION
CLEARINGHOUSEdistributor
CONTENT CREATORmanufacturer
CO
NS
UM
ER
DE
VIC
ES
EN
AB
LE
RS
AD
VE
RT
ISE
R
DIGITAL MEDIA RETAILER
Aggregating and accessing digital content from multiple sources
Providing consistent and reliable customer experience across multiple formats and devices
ADVERTISER
Reaching consumers via multiple channels in a fragmented marketplace
Tracking new consumption patterns (e.g., online, time-shifting) without trusted metrics
74
Conclusion
• WLR Data Managers Delivers:– Security– Control– Centralization– Sustainable Data Management– A world of Opportunity
Thank You
75
What is SetFocus?
• The SetFocus SQL Master’s Program is an intensive, hands–on, project oriented program allowing knowledge and valuable experience putting the SQL skill set to use in a simulated work environment.
• I received over 300 hours of in-depths hands on experience
focused on SQL Development.
• SetFocus projects are real world projects that are distributed just as I would receive in a position. I received project specifications and was expected to identify best courses of action with deadlines set for completion.
76