Purdue University Global Database Management Task

Part 1 Section 1: Professional Competency

The position of a System Administrator can be very complex depending on the work environment. An administrator must constantly maintain the system, but also must be cognizant of the users and the group environment in which they work. Over time, system professionals become aware and implement best practices to demonstrate professionalism in a variety of situations.

Do some research on best practices for a System Administrator in the library or on the Internet. You can also use your own best practices if you have been working in IT. In a minimum 2-page paper, provide and explain three best practices in each of the following areas of a System Administrator’s role:

1. The ability to focus on multiple tasks
2. The ability to meet deadlines
3. The ability to utilize system users’ feedback to improve system performance
4. The ability to balance the quality of work against meeting deadlines
5. The ability to acknowledge mistakes and correct errors

Section 2: Summary

Write a 3-paragraph summary on installing and configuring Windows file storage using server manager or Server Core CLI.

Section 3:

1. Write a 2-paragraph summary on using PowerShell as a Windows CLI tool in LabSim section 3.1. Include the pros/cons and features relevant to its use on a current version of Windows Server operating system.
2. Write a 2-paragraph summary on the installation and configuration of Server Core on a Windows network from LabSim section 3.4. Include a discussion on why to use Server Core over a standard gui Windows installation.
3. Write a 2-paragraph summary on image servicing from LabSim section 3.5. Provide details on the value of using image servicing in small, medium, or large server networks.

Part 2

Task 1 – Generating a Database Using Microsoft SSMS Designer Tools

Based on the established database design diagram:

• Using SQL Server Management Studio Designer tools, create the database with the name Movies_T.
• Using SQL Server Management Studio Designer tools, create the tables (convert entity names to table names).
• Using SQL Server Management Studio Designer tools, associate column names (convert attribute names to column or field names), datatype, and number of characters for each attribute (field).
• Using SQL Server Management Studio Designer tools, establish the primary keys for each of the tables.
• Using SQL Server Management Studio Designer tools, establish any foreign keys for each of the tables (here you are converting your relationships to foreign keys).
• Create a screenshot of your SQL Management Studio showing the database and tables you have created; embed this screenshot into your Microsoft Word report.

Please use the following guidance document to help you complete this assessment task: Instructions for Using the Microsoft SSMS Designer Tools to Generate the Movies Database

You will need to use the established database design diagram for this task. Download and use this diagram for your work.

Create screenshots of your Microsoft SSMS session showing the database objects being established. Embed all screenshots into your Microsoft Word report. Explain what is being shown in each of your screenshots.

Task 2 – Generating a Database Using DDL Statements

Create a new version of the Movies database called Movies_DB using the following SQL script:

• Movies_DB Database Creation Script

The following document provides instructions on executing the script in a Microsoft SQL Server Management Studio (SSMS) query window:

• Instructions for Establishing the Movies_DB Database

Examine the provided database creation script and briefly explain what each DDL statement is accomplishing. Also, contrast your experiences between using the Microsoft SSMS Designer tools and DDL statements to generate the movies database.

Task 3 – Modifying Database Schemas

Use the Query Window of Microsoft SSMS to run DDL statements for modifying the Movies_DB database.

Using SQL DDL statements:

• Add a new table called StarPhoneNumber that contains the following columns:
  • PhoneID – Integer, Identity(1,1), Primary Key
  • StarID – Integer
  • PhoneNumber – varchar(10)
  • PhoneType – varchar(15)
  • CountryCode – Integer
• Add two new columns to the StarPhoneNumber table using ALTER TABLE commands (your choice as to column name and data type).
• Remove one of the columns you added via the ALTER TABLE command.
• Establish a foreign key constraint using an ALTER TABLE command in which StarID in the StarPhoneNumber table references StarID in the Star table.
• Make up two more changes for the StarPhoneNumber table and apply them (e.g., change maximum character length) using ALTER TABLE commands.

You can leverage the database design diagram as a resource for this task. Download and use this diagram for your analysis.

Create screenshots of your Microsoft SSMS session showing the changes you implemented. Embed all screenshots into your Microsoft Word report. Explain what is being shown in each of your screenshots.

Task 4 – Manually Migrating Flat File Data into Relational Tables

Incorporate the first record from the Movies flat file dataset into the normalized tables of the Movies_DB database. Use the Designer tools from the Microsoft SQL Server Management Studio (SSMS) to accomplish this task. Instructions for the manual data entry are provided in the following document:

• Instructions for Manual Entry of Data Into the Movies_DB Database

Only enter the first record from the flat file dataset manually.

Task 5 – Migrating Flat File Data into Relational Tables Using a Migration Script

Follow the directions for establishing a temporary table called Movies_Import_Temp in the Movies_DB database. The instruction document and flat file dataset, which is a comma-separated values (CSV) file, are provided below.

• Instructions for Importing the Movies Flat File Dataset
• Flat File Dataset

Migrate the data contained in the Movies_Import_Temp table using the provided data migration script. The data migration script along with Instructions for executing it in a Microsoft SQL Server Management Studio (SSMS) query window are provided below.

• Instructions for Executing the Data Migration Script
• Data Migration Script

Generate confirmatory screenshots of your Microsoft SSMS session showing completion of the flat file dataset import process and execution of the data migration script. Embed all screenshots into your Microsoft Word report.

Briefly describe the purpose and function of the individual INSERT statements in the provided data migration script.

Task 6 – Use Basic SELECT Statements to Retrieve Data from Tables in the Movies_DB Database

Create SQL statements to retrieve data from the Movies_DB Database for the following problems. Generate screenshots to show each SQL query and the results of each query execution. Embed the screenshots into your Microsoft Word report. Leverage the database design diagram as a resource for this task.

Problem 1: List all of the directors with the last name of “Coppola.”

Expected Output

Problem 2: Show the last names of movie producers whose last names start with the letter M.

Expected Output

Problem 3: List all movie titles and ratings for movies with a rating greater than or equal to 8. Show the results in alphabetical order by movie title.

Expected Output

Problem 4: List all movie titles and ratings for movies with a rating between 5 and 6. Show the results in alphabetical order by movie title.

Expected Output

Problem 5: Show the producer records that do not have first name values. In other words, the first name value is NULL for these producer records. Present the results in alphabetical order.

Expected Output