Create a second data file for database named midterm-xx-two

Assignment Help Database Management System

Reference no: EM131908119

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.

Reference no: EM131908119

Questions Cloud

Estimate the temperature difference : Use the exponential function to estimate the temperature difference y when 35 minutes have elapsed. Report your estimated temperature difference

Which the bank still makes profit lending money : Derive a formula that calculates the maximum value of d under which the bank still makes a profit lending money.

What is the estimated value of the subject property : The appraiser estimates that each bedroom has a worth of $10,000 and a swimming pool a worth of $15,000. Given the above information, what is the estimated.

Determining the geometric interpretation : What is the meaning of ?y/?x? What is its geometric interpretation? What is the meaning of dy/dx? What is its geometric interpretation?

Create a second data file for database named midterm-xx-two : Create a second data file for the database named MIDTERM-XX2 (where XX are your intitals) and place it in a filegroup named ARCHIVE.

Type of ornamental cherry tree : At age 4 years, how tall is this type of ornamental cherry tree, to the nearest tenth of a foot?

How long will it take to double your investment : How long will it take to double your investment to $2000? Show work.

What will your new plan look like in terms of mix : What will your new plan look like in terms of mix (base pay, performance pay, indirect pay)? Will all jobs have the same mix or will some jobs have a different

Compute the steady state availability : A critical communications relay has constant failure rate of 0.01 per day.Once it has failed ,the mean time to repair is 2.5 days (repair rate is constant).

User Account

All Pages