You have to do only Lab 1.. bcoz my sir failed me in lab 1 so you have to do correct it.
ICANWK503A – Install and Maintain Valid Authentication Processes a RTO No: 20829 CRICOS Provider No: 02044E ABN: 41 085 128 525 STUDENT COPY- CR Assessment Workbook Unit Details ICTDBS502 – Design a database Assessment Requirements This unit requires each student to undergo adequate practice and preparation prior to undertaking the assessments in a classroom/simulated environment or an actual job workplace (formative). Students are required to complete ALL assessment tasks listed below in order to be deemed “Satisfactory” in this unit of competency. Assessment Methods (Three Assessments for this unit) 1. Practical Observation Assessment type: Laboratory Exercises 2. Written Test Assessment type: Multiple choice/short answer 3. Project / Report Assessment type: Short answer/Written Report Document Version 1.1. Issued July 2017 Unit Summary The unit objectives, prerequisites, corequisites and other pertinent information about this unit is described at https://training.gov.au/Training/Details/ICTDBS502 General Instructions for Students READ THESE INSTRUCTIONS CAREFULLY BEFOR YOU BEGIN! · You should download the softcopy of this document and use it as a template for your own assignment submission. · You will be directed to save screen prints and/or write down answers to questions when you complete this workbook. You need to include those screen prints/answers inline (i.e. at the correct place in the document) so your assignment can be assessed. If there are sample screen prints included in the document, you need to delete those and include your own screen prints. Do not submit your assignment without all the required screen prints/documents, as you will be marked NYC and need to resubmit the assignment · To complete the practical tasks, you will need to consult the Learner Resources and/or Lab Guide. These are available in Moodle · Keep your softcopy of your assessment/workbook (in case you need to resubmit it) · Answer all parts of the assessment · Type all your answers (do not handwrite) · Do not cheat. Anyone caught cheating will automatically be marked Not Yet Satisfactory for this unit. There are NO EXCEPTIONS to this rule. In order to achieve competency, all assessment tasks MUST be completed to a satisfactory level Note: All references made to workplace and or employer, are directly related to the trainers and facilities at VIT. PRACTICAL OBSERVATION – INFORMATION FOR STUDENTS Laboratory Exercises This information is to be handed to each student to outline the assessment requirements This assessment is to be undertaken in an actual job workplace or a classroom/simulated environment. Read the scenario below and then work through the lab exercises. At different points in the lab you will need to write down answers to questions, and/or include screen prints showing you completed practical tasks on the computer. Make sure you answer all questions and include all required screen prints. Where example screen prints and ansers have been included for you, make sure you delete these and include your own answers/screen prints This assessment is done during the allocated laboratory/tutorial sessions or can be completed offsite. Once you have finished the workbook, you need to print it out and submit the hardcopy for assessment Reference Material/Textbooks: See your Learner resources for each topic in Moodle Microsoft, Database Administration Fundamentals (in Learner Resources) See ICTDBS502 Lab Guide (in Learner resources) for instructions/resources on how to complete the lab Lab 1 Logical database design Complete lab1 as described in the Lab Guide. Save your evidence for completing lab 1 in the space below (see lab guide for details) Lab 2. Database implementation Complete lab 2 as described in the Lab Guide. Save your evidence for completing lab 2 in the space below (see lab guide for details) Lab 3 Implementing constraints and indexes Complete lab 3 as described in the Lab Guide. Save your evidence for completing lab 3 in the space below (see lab guide for details) Lab 4 Querying and reporting data Complete lab 4 as described in the Lab Guide. Save your evidence for completing lab 4 in the space below (see lab guide for details). Make sure you save evidence for all five questions for lab 4. Label your evidence clearly e.g. query1, query 2 etc Lab 5 Backing up your database Complete lab 5 as described in the Lab Guide. Save your evidence for completing lab 4 in the space below (see lab guide for details). PRACTICAL OBSERVATION – ASSESSOR INFORMATION Assessor Use Only Assessor Comments ( Satisfactory (S) ( Not Yet Satisfactory (NYS) Assessor Signature: _______________________________ Date: _____________ Questions – INFORMATION FOR STUDENTS Multiple choice questions This information is to be handed to each student to outline the assessment requirements This is an open book written assessment that will be delivered through Moodle, the VIT e-learning platform. You need to logon to Moodle to complete the test. You can make as many attempts as you like at the test, but you must get all questions correct to be judged competent in this assessment task (100%). Note: Once you have got the required score, you need to take a screen print in Moodle and include that screen print in your assessment as proof you completed the test. If you do not include this screen print in your submission, you cannot be marked Competent for the task. Make sure you include your screen print of your Moodle result! Include your Moodle Test result here (delete the sample image first) Assessor Use Only Assessor Comments ( Satisfactory (S) ( Not Yet Satisfactory (NYS) Assessor Signature: _______________________________ Date: _____________ Project – Information for Students Answer all the questions below. Keep your answers short and to the point. No answers should exceed 70 words. Type (do not handwrite) your answers inline in the document. Do not write your answers in separate document; write them inline in this document. Note: to answer these topics, you will need to refer to Lesson 5 “Administering a Database” in Database Administration Fundamentals and other references in lecture topics in Moodle 1. What is the main advantage of using Windows Authentication mode as the way I authenticate users trying to access SQL Server? 2. Give two examples of predefined server roles in SQL Server. 3. Write down the SQL command I would use to assign a user called Fred to the Marketing role in SQL Server? 4. What are the three recovery modes available for a database in SQL Server? 5. What is one advantage and one disadvantage of full recovery mode on a SQL database? 6. You have proposed the following design for a CUSTOMER table to a client (see SQL statement defining the CUSTOMER table below). The client points out that, for his business, customer identifiers can have letters as well as numbers e.g. MR12345 is a valid customer id, and also points out that we need to store the customer’s date of birth. Write a new CREATE TABLE statement for the Customer table that addresses the concerns of this customer by defining a CUSTOMER table that will suit the client’s expressed needs. CREATE TABLE [dbo].[Customer]( [CustomerId] [int] NOT NULL, [Fname] [varchar](10) NULL, [Sname] [varchar](10) NULL, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 7. You have proposed a database design to a client, where the database has four tables: CUSTOMER, ORDER, PRODUCT and SALESPERSON. The client has informed you that the database will have two users, Andy and Betty. Andy only needs read (or SELECT) access to the CUSTOMER table on the database, whereas Betty needs full access (SELECT, INSERT, UPDATE, DELETE) to the CUSTOMER table table. Write the SQL statements to (i) grant Andy read access to the CUSTOMER table; and (ii) grant Betty full access to the CUSTOMER table 8. List one benefit of using Transparent Data Encryption (TDE) on a SQL Server Database 9. You have been asked to investigate the scalability of SQL Server 2014 Standard Edition. Specify (i) the maximum size of database permitted (ii) the maximum amount of memory addressable; and (iii) the maximum number of cores allowed on the database server 10. A programmer has proposed the following Student table definition to you below. You notice that the field Language is a repeating group, and that the proposed table design violates the rules for First Normal Form. Write down two SQL CREATE TABLE statements that will create tables that are mormalised. CREATE TABLE [dbo].[Student]( [StudentId] [int] NOT NULL, [Sname] [varchar](10) NULL, [Language1] [nchar](10) NULL, [Language2] [nchar](10) NULL, [Language3] [nchar](10) NULL, CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED END OF WRITTEN ASSIGNMENT Assessor Use Only Assessor Comments ( Satisfactory (S) ( Not Yet Satisfactory (NYS) Assessor Signature: _______________________________ Date: _____________ Page 5 of 16