Relational Online 1

mandrak_ingles
Home>Homework Answsers>Computer Science homework help

SQL Server Express Edition and AdventureWorksLT2012 are used throughout this course. If you already have them installed from the prerequisite ITCO 221 course, proceed to Task #1 below.

If needed, download and install SQL Server 2012, Express Edition. Refer to Appendix A of the textbook.

If needed, create the AdventureWorksLT2012 database. It can be re-downloaded it here.

Task #1: Write a script to create an EMPLOYEES table in the AdventureWorksLT database using the following specifications:

Column Name

Datatype

Primary Key (Y/N)

Nullable (Y/N)

EmployeeID

Integer

Y

N

Hire Date

datetime

N

Y

LastName

Character (20)

N

N

FirstName

Character (10)

N

N

PostalCode

Character (10)

N

Y

Region

Character (15)

N

Y

Title

Character (30)

N

Y

County

Character (20)

N

Y

Task #2: Write a script to create a REGIONS table in the AdventureWorksLT database using the following specifications:

Column Name

Datatype

Primary Key (Y/N)

Nullable (Y/N)

RegionID

Integer

Y

N

RegionDescription

Character (50)

N

N

Task #3: Write a script to create a DEPARTMENTS table in the AdventureWorks database using the following specifications:

Column Name

Datatype

Primary Key (Y/N)

Nullable (Y/N)

DepartmentID

Integer

Y

N

DepartmentDescription

Character (50)

N

N

Task #4: Write a script to add the following records to the EMPLOYEES table:

16/7/1998SellersAndrew12345ILAdministratorCook
23/15/1987JonesJim40677ILCEODupage
34/5/2000JohnsonJennifer89036CAManagerKings
411/19/2000LarkRon23435NYManagerQueens
54/6/2008BuchananSteven74536VAAdministratorRichmond

Task #5: Write a script to add the following records to the REGIONS table:

1

Eastern

2

Western

3

Northern

4

Southern

Task #6: Write a script to add the following records to the DEPARTMENTS table:

10

CEO

20

Sales

30

Marketing

Task #7: Write a script to add the following column to the EMPLOYEES table:

Column Name

Datatype

Primary Key (Y/N)

Nullable (Y/N)

DepartmentID

Integer

N

N

Task #8: Write a script to establish a foreign key constraint between EMPLOYEES and DEPARTMENTS using the common field: DepartmentID;

Task #9: Create a database diagram of the final database clearly depicting all tables and relationships.

Submit the SQL, DDL, and DML scripts needed to complete tasks and the database diagram of the final database.

  • 11 years ago
  • 15
Report issue
Answer(1)
mmokros
4.7 (48)
5.0 (30)
Chat

Purchase the answer to view it

NOT RATED
  • solution_1.zip
Bids(0)