Part 1 You need to create a batch file that implements a simple menu system so users can request the generation of reports. The name of the batch file must be Assign1.bat. The batch file must be commented adequately. The batch system generates the following menu: NOTE: The menu title must be your FirstName LastName Movie Rentals

The menu above always appears at the top of the window Selection of value other than a 1 or a 2 results in the following error (redisplay the main menu at the top of the window after displaying the error) :

Pressing enter without selecting an option results in the following error (redisplay the main menu at the top of the window after displaying the error) :

Selecting option 1 will generate the report explained in Part 2 of this assignment (redisplay the main menu at the top of the window after displaying the report). Selecting option 2 terminates the batch file.

Call your SQL script Assign1.sql

Use the CONN command as the first line of your SQL script after the comments to connect to the database. Ensure you provide your password.

The report must be saved to a file located in directory C:\DBMSDBII\A1\Reports o This directory may or may not exist your batch program needs to create it if necessary. It if is already there then just use it.

The name of the report file is LastName_FirstNameInitial.txt

The report must prompt the user to enter a client identifier using the prompt Customer ID: . The entered value must be a number 3 digits maximum.

The report must be generated with a single SELECT statement

Stored Procedures/Functions are not permitted All column formatting must be done using the COLUMN statement discussed in class

Due to the large size of some columns, they are to be shortened as follows: FName First 15 Characters LName First 15 Characters Name First 55 Characters

The AgreementDate must occupy 12 spaces on the report

The line size must be exactly 120 characters

The page size must be exactly 30 lines

Run by: in the page footer must show the UserID of any person running the report Submission Instructions: Submit the following:

The DOS batch file to create the menu.

The SQL Script to create the report.

The following standards are to be used when coding:

- Keywords are in Uppercase

- Names are coded in TitleCase (First letter of each word capitalized)

- Each clause is placed on a line by itself

JR Movie Rental 2016/05/09 Contributor ContributorID NUMERIC (5) NOT NULL FName VARCHAR (50) MName VARCHMR (50) LName VARCHAR(50) Musical Group VARCHAR (100) Customer CustID NUMERIC (5) NOT NULL FName CHWR (25) NOT NULL LName VARCHAR (5) NOT NULL StreetNo NUMERIC (5) Street VARCHAR (100) City VARCHAR (25) Province CHWR (2) PCode CHURO) PrimaryCustID NUMERIC (5) (FK) SongAuthor Song NUMERIC (5) NOT NULL(K) ContributorID NUMERIC (5) NOT NULL (FO) SongPerformer Song NUMERIC (5) NOT NULL (FO) ContributorID NUMERIC (5) NOT NULL (FO) Featured CHUR(1) M RentalAgreement Agromenti NUMERIC (5) NOT NULL CustID NUMERIC (5) NOT NULL (FK) Agement Date DATE NOT NULL MovieCount NUMERIC (3) urationID NUMERIC (7) NOT NULL (FK) Z Movie Actor Movie NUMERIC (7) NOT NULL (FK) ContributorID NUMERIC (5) NOT NULL (FK) Billing CHAR (10) Song Movie Song Movie NUMERIC (7) NOT NULL (FK) SongID NUMERIC (5) NOT NULL (FK) Song NUMERIC (5) NOT NULL Name VARCHAR(150) NOT NULL Movie Rented Movie NUMERIC (1) NOT NULL FK Agreement NUMERIC (5) NOT NULL (FR) RontalAmount NUMERIC (5.2) NOT NULL PercentReduction Applied NUMERIC (4.2) Movie Movie NUMERIC) NOT NULL (7 Name VARCHAR (100) NOT NULL ) Released DATE Distribution Cost NUMERIC (52) NOT NULL Rentamount NUMERIC (5.2) NOT NULL Stars NUMERIC (2) Ratingid CHAR (3) (FK) ( ValidDuration DurationID NUMERIC (2) NOT NULL Name CHAR (15) NOT NULL Duration NUMERIC (2) NOT NULL MovieDistributor Moviel NUMERIC (1) NOT NULL) DistributorID NUMERIC (10) NOT NULL (IK) MovieGenre Movie NUMERIC (7) NOT NULL (FK) GenrelD CHAR (2) NOT NULL (FK) Distributor Volume Promotion Distributor NUMERIC (10) NOT NULL DistributorName VARCHAR (100) NOT NULL MinMovies NUMERIC (2) NOT NULL Max. Movies NUMERIC 2.0) Duration NUMERIC (2) NOT NULL PercentReduction NUMERIC (42) NOT NULL Genre GenrelD CHAR (2) NOT NULL Genre Type VARCHAR(50) NOT NULL Rating Rating CHR (3) NOT NULL RatingType CHR (30) Description VARCHAR (100) 1/1 Part 2 Generate the following report (generated when the batch file option 1 on the previous screen is selected): Movie Rental Details for client 933 Page: 1 Agreement First Name Last Name Date Movie Name Paid 7 Willie Leake 25 Willie 32 Willie 39 Willie BS Willie Leake Leake Leake Leake 2014-01-04 2014-01-04 2014-01-04 2014-01-15 2014-01-19 2014-02-22 2014-02-05 Phantom of the Opera BraveHeart Tombatone Edward Scissorhands Demolition Man The Patriot Speed $2.28 $4.05 53.73 $4.21 $3.28 $3.70 $3.69 Total $24.94 Run by: PRODREPORT