Please help, thanks.
Instructions for assignment 4 – Create Tables & Indexes EACH DELIVERABLE IS NOTED WITH TWO DOLLAR SIGNS IN FRONT ($$), COLORED RED AND IS WORTH 10 POINTS (partial credit will be given as appropriate) 1) USE THIS WORD DOC AND INSERT THE DELIVERABLES WHERE INDICATED – PLEASE NOTE THAT ALL SCREENSHOTS SHOULD BE FULL-SCREEN SCREENSHOTS (no cropped screenshots) 2) Create all of the following objects within the AdventureWorks2014 database that you restored in assignment #3 For all of the below, you are free to use SSMS or T-SQL , however, T-SQL is recommended due to the details requested and deliverables needed The intent of this exercise is to create a new set of tables and indexes under a new schema added to the AdventureWorks2014 database – PLEASE READ INSTRUCTIONS CAREFULLY TO CATCH ALL OF THE NUANCES BEING REQUESTED 3) Create a new schema named CustomerService with owner of dbo. 4) Create a new filegroup in the AdventureWorks2014 DB containing one file – name the filegroup AD_CustomerService and file whatever you wish, but use standard naming convention for the suffix. 5) ($$)DELIVERABLE - Take a screenshot of the database properties in SSMS showing the new filegroup and file. Paste the screen shot in the word doc below: 6) Create a new CLUSTERED table named CustomerService.Reps on the newly created Filegroup with the following columns and properties: a. Column called RepID that is the primary key and an ever-increasing unique integer (use Identity property and make this the primary clustered key) b. Column called BusinessEntityID that is integer and must exist in the Person.Person table, if populated (enforce with referential integrity with the BusinessEntityID column in the Person table – DO NOT CASCADE DELETES or UPDATES and nulls are not allowed) c. Column called SupervisorID that is integer and must exist in the Person.Person table, if populated (enforce with referential integrity with the BusinessEntityID column in the Person table – DO NOT CASCADE DELETES or UPDATES and nulls are not allowed) d. Table should be compressed at the page level e. ($$)DELIVERABLE – COPY THE T_SQL TO CREATE THE ABOVE TABLE INTO THE WORD DOC BELOW: 7) Create a unique index on the BusinessEntityID column (you can use the ALTER TABLE command) that sorts in temp_db and is created online – ($$)DELIVERABLE-COPY THE T_SQL TO CREATE THIS INDEX INTO THE WORD DOC BELOW: 8) Create a new CLUSTERED table named CustomerService.Contacts on the newly created Filegroup with the following columns and properties: a. Column called ContactID that is the primary key and an ever-increasing unique integer (use Identity property and make this the primary clustered key) b. Column called CustomerID that is integer and must exist in the Sales.Customer table (enforce with referential integrity with the CustomerID column in the Customer table and nulls are not allowed) c. Column called RepID that is integer and must exist in the CustomerService.Reps table (enforce with referential integrity with the RepID column in the Reps table and nulls are not allowed) d. Column called ContactDateTime, which is of type datetime and not null e. Column called ContactMethod, that is a variable length character with a maximum length of 5 that only accepts the following values: “Email”, “Phone”, “Chat”, “Other”, (use a check constraint and defaults to “Other”) and nulls are not allowed f. Column called ContactPhone, that is a variable length character with a maximum length of 14 with nulls allowed. g. Column called ContactEmail, that is a variable length character with a maximum length of 50 with nulls allowed. h. Column called ContactDetail, which is a variable length character with a maximum length of MAX and nulls not allowed. i. Table should be compressed at the page level j. ($$)DELIVERABLE – COPY THE T_SQL TO CREATE THE ABOVE TABLE INTO THE WORD DOC BELOW: 9) Create a non-unique index on the CustomerID column that sorts in temp_db and is created online – ($$)DELIVERABLE-COPY THE T_SQL TO CREATE THIS INDEX INTO THE WORD DOC BELOW: 10) Create a non-unique index on the RepID column that sorts in temp_db and is created online – ($$)DELIVERABLE-COPY THE T_SQL TO CREATE THIS INDEX INTO THE WORD DOC BELOW: 11) Create a non-unique index on the ContactDateTime column that sorts in temp_db, is created online and covers (includes) the ContactMethod column – ($$)DELIVERABLE-COPY THE T_SQL TO CREATE THIS INDEX INTO THE WORD DOC BELOW: 12) Create a new CLUSTERED table named CustomerService.Attachments on the newly created Filegroup with the following columns and properties: a. Column called AttachmentID that is the primary key and an ever-increasing unique integer (use Identity property and make this the primary clustered key) b. Column called ContactID that is integer and must exist in the CustomerService.Contacts table, if populated (enforce with referential integrity with the ContactID column in the Contacts table and nulls are not allowed) c. Column called AttachmentDateTime of type datetime and not null d. Column AttachmentName, that is a variable length character with a maximum length of 50 with nulls NOT allowed. e. Column called Attachment that is a variable length binary column with a maximum length of MAX and nulls are not allowed. f. Table should be compressed at the page level g. ($$)DELIVERABLE – COPY THE T_SQL TO CREATE THE ABOVE TABLE INTO THE WORD DOC BELOW: 13) Create a non-unique index on the ContactID column that sorts in temp_db and is created online – ($$)DELIVERABLE-COPY THE T_SQL TO CREATE THIS INDEX INTO THE WORD DOC BELOW: 14) Test your work! The following provides a test of your tables. Not all of the above detail is tested with the below, but at least some aspects are. If these tests do not behave as expected, then you should review your work. The following commands should complete successfully: use AdventureWorks2014 insert into CustomerService.Reps values (275, 285); insert into CustomerService.Contacts values (112,1,CURRENT_TIMESTAMP,'Phone','305-555-5843',NULL,'Customer called to complain about order #4394, where the product was defective; picture attached.'); /******FOR THE FOLLOWING, PLEASE CHANGE “c:\pick any file on your PC” TO REFER TO ANY FILE ON YOUR PC AND RUN ALL OF THESE COMMANDS AT THE SAME TIME******/ DECLARE @jpg VARBINARY(MAX) SELECT @jpg = BulkColumn FROM OPENROWSET(BULK N'c:\pick any file on your PC', SINGLE_BLOB) AS Document INSERT INTO CustomerService.Attachments (ContactID, AttachmentDateTime, AttachmentName, Attachment) values(1,CURRENT_TIMESTAMP,'Picture of defective product',@jpg) AFTER the above are successful, the following commands should ALL FAIL: use AdventureWorks2014 insert into CustomerService.Reps values (276, 522); insert into CustomerService.Reps values (522, 285); insert into CustomerService.Contacts values (750,1,CURRENT_TIMESTAMP,'Phone','305-555-5843',NULL,'Customer called to complain about order #4394, where the product was defective; picture attached.'); insert into CustomerService.Contacts values (112,200,CURRENT_TIMESTAMP,'Phone','305-555-5843',NULL,'Customer called to complain about order #4394, where the product was defective; picture attached.'); insert into CustomerService.Contacts values (112,5,CURRENT_TIMESTAMP,'Phon','305-555-5843',NULL,'Customer called to complain about order #4394, where the product was defective; picture attached.'); /******FOR THE FOLLOWING, PLEASE CHANGE “c:\pick any file on your PC” TO REFER TO ANY FILE ON YOUR PC AND RUN ALL OF THESE COMMANDS AT THE SAME TIME******/ DECLARE @jpg VARBINARY(MAX) SELECT @jpg = BulkColumn FROM OPENROWSET(BULK N'c:\pick any file on your PC', SINGLE_BLOB) AS Document INSERT INTO CustomerService.Attachments (ContactID, AttachmentDateTime, AttachmentName, Attachment) values(200,CURRENT_TIMESTAMP,'Picture of defective product',@jpg) 15) ($$)DELIVERABLE - Provide the reason for the EACH failed command: 16) If working on a lab machine, remember to backup your database onto the U: drive to restore for your next assignment. Extra Credit (4 points for each deliverable) – Use FileStream to do the following: 1) Create an ‘Attachments2’ table with the same columns as the ‘Attachments’ table above, but use Filestream on any local drive to store the varbinary(max) column. Follow the instructions in the “Creating Tables” powerpoint. 2) ($$)DELIVERABLE – Using SQL Server Configuration Manager, enable FileStream for your instance (note that you need to cycle your instance for the change to take place) and provide a screenshot below: 3) ($$)DELIVERABLE – Using SSMS, enable FileStream at the instance level and provide a screenshot of the properties being change below: 4) ($$)DELIVERABLE – Using SSMS, produce a “Script Table as” -> “CREATE To” T-SQL script of the new “Attachments2” table and copy the resulting T-SQL to the Word doc below: 5) You can test your new table with following T-SQL statements: /******FOR THE FOLLOWING, PLEASE CHANGE “c:\pick any file on your PC” TO REFER TO ANY FILE ON YOUR PC AND RUN ALL OF THESE COMMANDS AT THE SAME TIME******/ DECLARE @jpg VARBINARY(MAX) SELECT @jpg = BulkColumn FROM OPENROWSET(BULK N'c:\pick any file on your PC', SINGLE_BLOB) AS Document INSERT INTO CustomerService.Attachments2 (ContactID, AttachmentDateTime, AttachmentName, Attachment) values(1,CURRENT_TIMESTAMP,'Picture of defective product',@jpg)