Part I:video link you may need to use:1.https://www.youtube.com/watch?time_continue=3&v=DV4IMjnL_IU&feature=emb_logo 2.https://www.youtube.com/watch?v=Qq4yhhAk9fc&feature=emb_logo Learning Objective...

1 answer below »


Part I:video link you may need to use:1.https://www.youtube.com/watch?time_continue=3&v=DV4IMjnL_IU&feature=emb_logo

2.https://www.youtube.com/watch?v=Qq4yhhAk9fc&feature=emb_logo



Learning Objective



SSIS




  • Adding annotation


  • Label Objects


  • Error Log


  • Transformation Objects


    • Conditional Split


    • Derived Column


    • Union All **


    • Data Conversion **







Task:



Create an SSIS package using the data provided - NJCustomers.txt



1.Extract the data


2. Transform the dat



  • Records with a state = "NJ" are considered "Good"data

  • Records with a state that has more than 2 characters are considered "Bad"data

  • Make the first and last names uppercase


3. Load



  • Write the good data to a file called "NJGoodRecords.txt"

  • Write the bad data to a file called "NJBadRecords.txt"


Add appropriate annotation to the design


Label all objects appropriately







What to submit:


1. The Project Solution


2. A Word document with screenshot of



  • The SSIS Data Flow screen



Naming convention:firstnameLastname_BCS425_Lab3.fileExt





Part II (ASSIGNMENT 1.ZIP):


Valid format: Word document with file name in format “yourlastname_yourfirstinitial_BCS425_Assignment1.sql”



**See the attached file for assignment requirements *


´You will need to do the following:


Using your database incsserver.farmingdale.edu


´Create the tables according to the given schemas


´Create primary and foreign key constraints


´Write queries


´Backup the database




BCS425_Assignment_1.docx BCS 425 - Assignment 1 Valid format: Word document with file name in format “yourlastname_yourfirstinitial_BCS425_Assignment1.docx” IMPORTANT: Late assignments will be accepted for ONLY 1 week after the due date (with valid reason) but will have 50% taken off of the grade. Any assignments turned in more than a week late will not be accepted. · You will need to do the following: Using your database in csserver.farmingdale.edu · Create the tables according to the given schemas · Create primary and foreign key constraints · Write queries · Backup the database 1. Create the following tables with the given schemas: a. Sailors(sid: int, sname: nvarchar(50), rating: int, age: decimal(3, 1)) Primary key: sid b. Boats(bid: int, bname: nvarchar(50), color: nvarchar(50)) Primary key: bid c. Reserves(sid: int, bid: int, day: date) Primary key: sid, bid, day Foreign key:sid Sailors bid Boats Make sure you create the primary and foreign key constraints!!! 2. Load data into the BoatReservations DB. There is an SQL file on Blackboard that you can use to populate your BoatReservations DB. File Name: LoadBoatReservationsData.sql 3. Write the following queries: Q1 – Find the names of all sailors. There should NOT be any duplicates. Q2 – Find the names of sailors who have reserved boat number 103. Q3 – Find the sids of sailors who reserved a red boat (No duplicates). Q4 – Find the names of sailors who reserved a red boat (No duplicates). Q5 – Find the colors of boats reserved by Lubber. *Q6 – Find the names of sailors that have reserved at least one boat. *Q7 – Find the most recent boat reserved by each sailor. Save all these queries in one .sql file, please. LoadBoatReservationsData.sql /*Load Sailors*/ /*Sailors(sid: int, sname: nvarchar(50), rating: int, age: decimal(3, 1)) Primary key: sid*/ Insert into Sailors Values (1,'Lubber', 5, 35.3) Insert into Sailors Values (2, 'Smith', 3, 55.1) Insert into Sailors Values (3, 'Nolan', 1, 20.0) Insert into Sailors Values (4, 'Garner', 4, 62.2) Insert into Sailors Values (5, 'Kovak', 5, 33.0) Insert into Sailors Values (6, 'Kindley', 2, 33.4) Insert into Sailors Values (7, 'Presley', 5, 42.0) Insert into Sailors Values (8, 'Pichardo', 5, 35.2) Insert into Sailors Values (9, 'Wint', 3, 48.2) Insert into Sailors Values (10, 'Aaron', 4, 23.8) Insert into Sailors Values (11, 'Gaul', 5, 32.0) Insert into Sailors Values (12, 'Ray', 4, 34.0) Insert into Sailors Values (13, 'Ewart', 5, 56.7) Insert into Sailors Values (14, 'Max', 3, 45.0) Insert into Sailors Values (15, 'Dan', 3, 45.0) Insert into Sailors Values (16, 'Rinald', 2, 33.0) Insert into Sailors Values (17, 'Gilbert', 5, 44.0) Insert into Sailors Values (18, 'Punam', 5, 23.0) Insert into Sailors Values (19, 'Ricardo', 4, 33.5) Insert into Sailors Values (20, 'Shafiq', 5, 33.0) /*Load Boats*/ /*Boats(bid: int, bname: nvarchar(50), color: nvarchar(50)) Primary key: bid */ Insert into Boats Values (101, 'Lady Liberty', 'red') Insert into Boats Values (102, 'Misty', 'blue') Insert into Boats Values (103, 'Queen', 'black') Insert into Boats Values (104, 'Midnight Run', 'white') Insert into Boats Values (105, 'Blessed One', 'green') Insert into Boats Values (106, 'Jupiter', 'pink') Insert into Boats Values (107, 'Galaxy', 'red') Insert into Boats Values (108, 'Pride of the Sea', 'black') Insert into Boats Values (109, 'Fortune', 'white') Insert into Boats Values (110, 'Mad Max', 'red') Insert into Boats Values (111, 'Czar', 'green') Insert into Boats Values (112, 'Destiny', 'yellow') Insert into Boats Values (113, 'First Run', 'green') Insert into Boats Values (114, 'Silky Smooth', 'black') Insert into Boats Values (115, 'Mistletoe', 'blue') /*Load Reserves*/ /*Reserves(sid: int, bid: int, day: date) Primary key: sid, bid, day Foreign key:sid : Sailors bid : Boats */ Insert into Reserves Values (1, 103, '01/20/18') Insert into Reserves Values (5, 110, '02/02/18') Insert into Reserves Values (3, 105, '01/02/18') Insert into Reserves Values (4, 115, '02/02/18') Insert into Reserves Values (5, 103, '02/02/18') Insert into Reserves Values (6, 101, '01/28/18') Insert into Reserves Values (7, 109, '02/01/18') Insert into Reserves Values (8, 115, '02/06/18') Insert into Reserves Values (9, 106, '12/02/18') Insert into Reserves Values (9, 103, '01/02/18') Insert into Reserves Values (9, 102, '02/02/18') Insert into Reserves Values (10, 115, '12/15/18') Insert into Reserves Values (10, 113, '01/15/18') Insert into Reserves Values (10, 103, '02/05/18') Insert into Reserves Values (2, 105, '01/02/18') Insert into Reserves Values (2, 101, '02/02/18') Insert into Reserves Values (12, 104, '12/09/18') Insert into Reserves Values (12, 107, '01/19/18') Insert into Reserves Values (12, 114, '02/01/18') Insert into Reserves Values (11, 111, '01/17/18') Insert into Reserves Values (11, 107, '02/08/18') Insert into Reserves Values (14, 109, '02/02/18') Insert into Reserves Values (14, 110, '02/08/18') Insert into Reserves Values (15, 103, '01/04/18') Insert into Reserves Values (15, 109, '02/04/18') Insert into Reserves Values (5, 104, '02/10/18') Insert into Reserves Values (1, 107, '01/20/18') Go
Answered Same DayFeb 08, 2021

Answer To: Part I:video link you may need to...

Neha answered on Feb 09 2021
134 Votes
Dump20200209/bcs425_boats.sql
-- MySQL dump 10.13 Distrib 8.0.19, for Win64 (x86_64)
--
-- Host: localhost Database: bcs425
-- ------------------------------------------------------
-- Server version    8.0.19
/*!4
0101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `boats`
--
DROP TABLE IF EXISTS `boats`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `boats` (
`bid` int NOT NULL,
`bname` varchar(50) DEFAULT NULL,
`color` varchar(50) DEFAULT NULL,
PRIMARY KEY (`bid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `boats`
--
LOCK TABLES `boats` WRITE;
/*!40000 ALTER TABLE `boats` DISABLE KEYS */;
INSERT INTO `boats` VALUES (101,'Lady Liberty','red'),(102,'Misty','blue'),(103,'Queen','black'),(104,'Midnight Run','white'),(105,'Blessed One','green'),(106,'Jupiter','pink'),(107,'Galaxy','red'),(108,'Pride of the Sea','black'),(109,'Fortune','white'),(110,'Mad Max','red'),(111,'Czar','green'),(112,'Destiny','yellow'),(113,'First Run','green'),(114,'Silky Smooth','black'),(115,'Mistletoe','blue');
/*!40000 ALTER TABLE `boats` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here