Assignment

1. Create a database named MIDTERM-XX, where XX are your initials. The data file should initially be 5MB in size.

2. Create a second data file for the database named MIDTERM-XX2 (where XX are your intitals) and place it in a filegroup named ARCHIVE. This file should be 10MB in size.

3. Create five tables in MIDTERM-XX with the following fields:

a. STUDENT
StudentID- char 6
Fname- nvarchar 20
Lname- nvarchar 20
State- char 2
Country- char 2
IsCurrent- bit

b. INSTRUCTOR
EmployeeID- char 6
Fname- nvarchar 20
Lname- nvarchar 20
Campus- char 3
Salary- money

c. COURSE
CourseID- char 6
Description- nvarchar 30
Credits- integer

d. REFERENCE
ReferenceID- char 6
CourseID- char 6
EmployeeID- char 6
Day- char 2
Timeslot- char 10

e. SCHEDULE
StudentID- char 6
ReferenceID- char 6
Paid- bit

• The INSTRUCTOR table should be assigned to the ARCHIVE filegroup.

4. Create a database diagram with the newly created tables and name it MIDTERM-ERD.

5. Create primary key/foreign key relationships that reflect the following conditions:

a. STUDENT has a one-to-many relationship with SCHEDULE
b. INSTRUCTOR has a one-to-many relationship with REFERENCE
c. COURSE has a one-to-many relationship with REFERENCE
d. REFERENCE has a one-to-many relationship with SCHEDULE

6. Create the following check constraints:

a. The Day field in the REFERENCE table should only accept the values MW, TR, or F.
b. The Credits field in the COURSE table should not accept a number greater than 5.
c. The Country field in the STUDENT table should default to US.
d. The State field in the STUDENT table should default to FL.

7. Create the following users on the server. They should both be SQL logins with a password of Password#1 and their default database should be MIDTERM-XX:

a. DAckroyd
b. BMurray
c. EMurphy

8. Create the following users and groups on the local Windows Server.

a. WCoyote (user)
b. FLeghorn (user)
c. AcmeUsers (group)- WCoyote and FLeghorn are members of this group

9. Create a Login on the SQL Server for the AcmeUsers group created in step 8.

10. Create a role named Registrars in the MIDTERM-XX database and make EMurphy a member of this role. Create a role named Deans and make AcmeUsers a member of this role.

11. Create an Application Role named MidtermApp, and give it your default password.

12. The following permissions should be granted on the MIDTERM-XX database:

a. DAckroyd is the database administrator for MIDTERM-XX.

b. The Registrars role needs the ability to read all the tables in the database. It should also have the ability to insert and update data to the SCHEDULE table.

c. The Deans role needs read and write permissions to all tables in the database.

d. The MidtermApp needsthe ability to update STUDENT and SCHEDULE tables, and must have read-only rights to all other tables. However, it must be denied the ability to view INSTRUCTOR salaries.

13. Create a backup device named NW-MIDTERM-XX, where XX are your initials. This device should point to c:\backups (you will have to create this folder).

14. Use a maintenance plan to achieve the following:

a. Create a full backup of the MIDTERM-XX database that runs every Saturday at 10:00PM. Use the NW-MIDTERM-XX device.

b. Create a differential backup of the MIDTERM-XX database the runs every night except Saturday at 10:00PM.Use the NW-MIDTERM-XX device.

c. Create a transaction log backup of the MIDTERM-XX database that runs every 2 hours (MIDTERM-XX needs to be in full recovery mode). Use the NW-Backup-XX device.