This competency assessment assesses the following Outcome(s):
IT234M2-2:\u00a0Explore Data Definition Language (DDL) statements to define the database structure or schema.
Purpose
The module assessment consists of six tasks. The first two tasks involve producing a database from a provided physical model. The first task entails establishing the database using Microsoft SQL Server Management Studio Designer tools.The second task involves generating the same database using SQL Data Definition Language (DDL) statements. You will leverage SQL CREATE statements in the second task to generate the database and tables. In business, you will have choices of different methods for creating a database, so you want to be familiar with the available options.
Databases can evolve over time to meet evolving business needs. As a result, database structures may be modified periodically due to new or changing requirements. For the third assessment task, you will use Data Definition Language (DDL) statements to modify a database by adding a table and associated attributes.
There are varieties of ways to get data inserted into a database. One way entails manual data entry through use of the Designer tools contained in Microsoft SQL Server Management Studio (SSMS). You will manually enter records into the established database for your fourth assessment task.
Another means of data entry involves migrating data from an Excel file. For your fifth assessment task, you will import the Movies flat file dataset into a temporary table in the specified Movies database. The data from the temporary table will then be migrated to the normalized tables using a provided data migration script. Use the provided import instructions document to accomplish this task.
Assessment Instructions
It is very important that you watch the module videos associated with conceptual database modeling prior to completing the assessment. Navigate to the Academic Tools area of this Module and select Library then Required Readings to access your texts and videos.
Microsoft SQL Server and SQL Server Management Studio (SSMS) must be installed and used to complete this assessment.
The assessment for this module is divided into six tasks, and you will generate a Microsoft Word report from the completed work.
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\u00a0Movies_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:\u00a0Instructions for Using the Microsoft SSMS Designer Tools to Generate the Movies Database
You will need to use the established\u00a0database design diagram\u00a0for this task. Download and use this\u00a0diagram\u00a0for 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\u00a0Movies_DB\u00a0using 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\u00a0database creation script\u00a0and 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\u00a0Movies_DB database.
Using SQL DDL statements:
Add a new table called StarPhoneNumber that contains the following columns:PhoneID \u2013 Integer, Identity(1,1), Primary Key
StarID – Integer
PhoneNumber – varchar(10)
PhoneType \u2013 varchar(15)
CountryCode \u2013 Integer <\/p>\n
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\u00a0database design diagram\u00a0as 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\u00a0Movies_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 <\/p>\n
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\u00a0Movies_Import_Temp\u00a0in the\u00a0Movies_DB\u00a0database. 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\u00a0Movies_Import_Temp\u00a0table 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\u00a0database design diagram\u00a0as a resource for this task.
Problem 1:\u00a0List all of the directors with the last name of “Coppola.”
Expected Output <\/p>\n
Problem 2:\u00a0Show the last names of movie producers whose last names start with the letter M.
Expected Output <\/p>\n
Problem 3:\u00a0List 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 <\/p>\n
Problem 4:\u00a0List 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 <\/p>\n
Problem 5:\u00a0Show 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 <\/p>\n
Proper notification of any resubmission, repurposing, or reworking of prior work per the\u00a0Purdue Global Student Coursework Resubmission, Repurposing, and Reworking Policy Resource.
Compose assessment work in a Microsoft Word report and be sure to identify yourself, your class, and module assessment at the top of your paper.
Embed the screenshots of your SQL statements and confirmatory output into the Microsoft Word report.
Enter responses to assessment questions into the Microsoft Word report.
If the work submitted for this competency assessment does not meet the minimum submission requirements, it will be returned for revision. If the work submitted does not meet the minimum submission requirements by the end of the term, you will receive a failing score.
Plagiarism
Plagiarism is an act of academic dishonesty. It violates the University\u2019s Code of Student Conduct, and the offense is subject to disciplinary action. You are expected to be the sole author of your work. Use of another person’s work or ideas must be accompanied by specific citations and references. Whether the action is intentional or not, it still constitutes plagiarism.
For more information on plagiarism and the University\u2019s Code of Student Conduct, refer to the current\u00a0University Catalog. <\/p>\n","protected":false},"excerpt":{"rendered":"
This competency assessment assesses the following Outcome(s): IT234M2-2:\u00a0Explore Data Definition Language (DDL) statements to define the database structure or schema. Purpose The module assessment consists of six tasks. The first two tasks involve producing a database from a provided physical model. The first task entails establishing the database using Microsoft SQL Server Management Studio Designer […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[10],"class_list":["post-16701","post","type-post","status-publish","format-standard","hentry","category-research-paper-writing","tag-writing"],"_links":{"self":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts\/16701","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/comments?post=16701"}],"version-history":[{"count":0,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/posts\/16701\/revisions"}],"wp:attachment":[{"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/media?parent=16701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/categories?post=16701"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/papersspot.com\/blog\/wp-json\/wp\/v2\/tags?post=16701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}