SQL Server Installation and Data Manipulation Assignments

Task

In this module’s assignment, you will locate and install SQL Server, attach databases to the SQL Server installation, manipulate the data in the attached databases, and create a database and tables to populate with data.

Put your name, course, module number, and date at the beginning of your assignment in your Microsoft® Word® document.

Put the header "Part 1" in your assignment Word document.

Install SQL Server. You can download SQL Sequel Express from the Microsoft website.

Please review and follow the Module 1 Learning Activity. The Module 1 Learning Activity steps through the process of downloading and installing SQL Server Express, attaching one of the databases used in the class, and working with SQL Server’s Management Studio to execute a SQL query and produce a result set.

The databases you will attach to your SQL Server installation should include the following databases from the textbook resource site:

1.BowlingLeagueExample (attached)

2.EntertainmentAgencyExample (attached)

3.RecipesExample (attached)

4.SalesOrderExample (attached)

5.SchoolSchedulingExample (attached)

Directions for locating and downloading the sample databases are detailed on pages 9 and 10 of the class required textbook.

6.AdventureWorks (Optional from backup or script)

Submit a Windows Snipping Tool picture (or screenshot) of your SQL Server® Management Studio window with the installed databases displayed.

Your installed databases should include all five of the databases downloaded from the textbook resource site.

Required Comment Addition for All Assignments SQL Query Questions

You will be required to include a Windows® Snipping Tool picture (or screenshot) with every SQL query question you submit within all the assignments in all modules of the SQL Query Design class.

Failure to include the required comment at the top of the SQL query question will result in zero points earned for the question result submission regardless of whether the SQL query syntax and result set are correct or not.

Note: If you forget the /* */ symbols in your comment, you will get an error when the SQL query is executed in SQL Server.

An example of a correct question submission from a query in SQL Server to produce all attributes and tuples within the bowlers table is provided in the following picture

Put the header "Part 2" in your assignment document. Number your answers to correspond to the number of the question you are answering (e.g., 2-1, 2-2).

Table: Bowler

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

Assignment Instructions

BowlerID: <<< 200 >>>

BowlerLastName: <<< Your Last Name >>> Sudheesh

BowlerFirstName: <<< YourFirstName >>>  Parvy

BowlerMiddleInit: NULL

BowlerAddress: <<< 123 PurdueGlobal Ave. >>>

BowlerCity: <<< West Lafayette >>>

BowlerState: <<< IN >>>

BowlerZip: <<< 47907 >>>

BowlerPhoneNumber: <<< (765) 123-4567 >>

TeamID: <<< 8 >>>

Table: Teams

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

TeamID: <<< Quarter and Year in Numbers (e.g., 319 would be quarter 3 in the year 2019) >>>

TeamName: <<< Your Initials Here (Your initials will be the team name) >>>

CaptainID: <<< 200 >>>

Table: Tournaments

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

TourneyID: <<< 99 >>>

TourneyDate: <<< Use today’s date >>>

TourneyLocation: <<< Imperial Lanes >>>

Table: Members

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

MemberID: <<< Quarter and Year in Numbers (e.g., 319 would be quarter 3 in the year 2019) >>>

MbrFirstName: <<< Your first name >>> Parvy

MbrLastName: <<< Your last name >>> Sudheesh

MbrPhoneNumber: <<< 332-9817 >>>

Gender: <<< NULL >>>

Table: Major

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

MajorID: <<< Quarter and Year in Numbers (e.g., 319 would be quarter 3 in the year 2019) >>>

Major: <<< Information Tech >>>

Put the header "Part 3" in your assignment document. Number your answers to correspond to the number of the question you are answering (e.g., 3-1, 3-2).

Table: Bowler

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

BowlerID: <<< 500 >>>

BowlerLastName: <<< Your Last Name >>> Sudheesh

BowlerFirstName: <<< YourFirstName >>> Parvy

BowlerMiddleInit: NULL

BowlerAddress: <<< 123 PurdueGlobal Ave. >>>

BowlerCity: <<< West Lafayette >>>

BowlerState: <<< IN >>>

BowlerZip: <<< 47907 >>>

BowlerPhoneNumber: <<< (765) 123-4567 >>

TeamID: <<< 8 >>>

Activity: Update the added tuple to the database. Update BowlerPhoneNumber to: <<< (555) 555-5555 >>>

Table: Teams

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

TeamID: <<< 500 >>>

TeamName: <<< Your Initials Here (Your initials will be the team name) >>>

CaptainID: <<< 200 >>>

Activity: Update the added tuple to the database. Update TeamName to: <<< PGU >>>

Part 1. Install SQL Server Express and Class-Required Databases

Table: Tournaments

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

TourneyID: <<< 500 >>>

TourneyDate: <<< Use today’s date >>>

TourneyLocation: <<< Imperial Lanes >>>

Activity: Update the added tuple to the database. Update TourneyLocation to: <<< Tommy Lanes >>>

Table: Members

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

MemberID: <<< 500 >>>

MbrFirstName: <<< Your first name >>>

MbrLastName: <<< Your last name >>>

MbrPhoneNumber: <<< 332-9817 >>>

Gender: <<< NULL >>>

Activity: Update the added tuple to the database. Update MbrPhoneNumber to: <<< 555-5555 >>>

Table: Major

Activity: Add a tuple to the database with the following attribute values (do not include the <<< >>> symbols):

MajorID: <<< 500 >>>

Major: <<< Information Tech >>>

Activity: Update the added tuple to the database. Update Major to: <<< Bio Science >>>

Put the header "Part 4" in your assignment document. Number your answers to correspond to the number of the question you are answering (e.g., 4-1, 4-2).

Table: Bowler

Activity: Delete the previously updated tuple with the BowlerPhoneNumber equal to: (555) 555-5555

Table: Teams

Activity: Delete the previously updated tuple with the TeamName equal to: PGU

Table: Tournaments

Activity: Delete the previously updated tuple with the TourneyLocation equal to: Tommy Lanes

Table: Members

Activity: Delete the previously updated tuple with the MbrPhoneNumber equal to: 555-5555

Table: Major

Activity: Delete the previously updated tuple with the Major equal to: Bio Science

Put the header "Part 5" in your assignment document. Number your answers to correspond to the number of the question you are answering (e.g., 5-1, 5-2).

Using the M1 Assignment Part 5 Reference Document, perform the following activities:

5-1: Create a new database called PetAdoptionInfo in SQL Server.

5-2: Create six new tables in the PetAdoptionInfo database with the required attribute’s characteristics as noted in the provided IT 526 M1 Create Table Resource Data document within the M1 Assignment Part 5 Reference Document. Make sure you create the table structures to account for the relationships and the foreign keys needed in each table based on the entity-relationship (ER) model in the IT 526 M1 Create Table Resource Data document.

5-3: Insert the provided data as new tuples in each of the six tables respectively based on the IT 526 M1 Create Table Resource Data document within the M1 Assignment Part 5 Reference Document.

5-4: Create SQL Server queries for each of the six PetAdoptionInfo database tables to display all tuples and attributes in each of the tables.