SQL CT8

Congratulations! Recently, you responded to a Request for Proposal (RFP), and you were awarded the contract to deploy, configure, and manage the SQL Server for the CSU Global Student Management System.

You are required to perform the following steps:

1.Install SQL Server 2012/2014, using unattended method to standardize installs.

2.Configure the TCP to run on port TCP 4459.

3.Create a database named CSUGlobalEdu with following settings:

• Data file initial size: 100MB
• Log file initial size: 50MB
• Set to Autogrow
• No limit on maximum file size.

4.Create following tables using scripts:

• Class

Name, Data Type, Constraint
Course Number, Int, Primary Key
Course Name, Nvarchar (30), Not null
Time of the Day, Time, Not null
Room, Char (10), Not null
Room Capacity, Int Not null
Course Description, Nvarchar (3000), Not null
Credit Hours, Int,Not Null

• Instructor

Name, Data Type, Constraint
Address, Nvarchar (100), Not Null
InstructorID, Int, Primary Key
Phone Number, Nvarchar(10), Not null
Office, Int, Not null
Office Hours, Nvarchar (30), Not null
Department, Nvarchar (15), Not null
Department ID, Int, Not null
First Name, Nvarchar (30), Not null
Last Name, Nvarchar (30), Not null

• Student

Name, Data Type, Constraint
Address, Nvarchar (100), Not Null
StudentID, Int, Primary Key
Phone Number, Nvarchar(10),Not null
Major, Char (10), Not null
GPA, Decimal (3,2), Not null
First Name, Nvarchar (30), Not null
Last Name, Nvarchar (30), Not null

5.Develop scripts to INSERT data into Class, Instructor, and Student tables. Load the tables with sample data with at least 10 rows.

6.Construct and execute queries that match the following criteria:

• List all classes sorted by course number followed by course name.
• List all instructors by last name followed by first name.
• List all instructors by last name, first name, InstructorID.
• List all instructors by last name followed by first name.
• List all students by last name, first name, GPA, Major.

7.Generate a certificate for the database.

8.Develop a SQL query to generate a new symmetric key for encrypting data.

9.Develop a SQL query to alter the Student table and a new column names StudentDataEncrypt with data type varbinary(128).

10.Create a backup plan with following criteria:

• The backups should occur on Sundays starting at 9:00 AM.
• CSU Global Campus cannot afford to risk data loss of more than one hour.