IT 240

CREATE KEYS AND INSERTING DATA Login in to your database on the database server. You will need a New Query window to run your commands. Select New Query from the Menu.

Download the Wedgewood SQL Code

You do not have the answer the Why? Questions below. They are rhetorical and you should think about them. Your submission to School will be a file with runnable code (read the last part of the assignment now).

1. Open and review the new Wedgewood SQL Code (the attributes have changed)

2. Run the new Wedgewood SQL Code

3. Create the Primary Keys on each table, except the ASSIGNMENT table (review the end of the assignment for code submission requirements). Remember the syntax for creating a Primary Key is {insert relevant data here}: ALTER TABLE {table} ADD CONSTRAINT {key name} PRIMARY KEY ({columns separated by comma}).

4. Create the Foreign Key for the EMPLOYEE table. Remember the syntax is: a. Did you have an issue? Why? b. Correct the issue in the EMPLOYEE table and remember the syntax to change a column is: ALTER TABLE {table} ALTER COLUMN {column} {datatype}. c. Save your code and then create the Foreign Key a. If there is an issue, correct it and create the Foreign Key ALTER TABLE {table} ADD CONSTRAINT {keyname} FOREIGN KEY ({column}) REFERENCES {table}({column}).

5. Create the Foreign Key on Project did you have any issue? If so, why?

6. Create the two Foreign Keys on ASSIGNMENT did you have an issue? Why? a. Correct the issue and create the Foreign Key

7. Create the Primary Key on the ASSIGNMENT table did you have an issue? Why? a. Correct the issue and create the Primary Key

8. Then using the slide data INSERT the remaining data for DEPARTMENT and EMPLOYEE tables.

9. Run a SELECT * on each table to confirm your DEPARTMENT table has select 9 rows and your employee table has 20 rows.

10. Only submit a .sql (text file) to D2L which should include the code you used for:

Creating the Primary Key on each table {5 pts each 15 pts}

Correcting the issue on the Foreign Key for EMPLOYEE {5 pts}

The code to create the Foreign Key on EMPLOYEE {5 pts}

Correcting the issue on the Foreign Key for PROJECT {0 pts} No Issue

The code to create the Foreign Key on PROJECT {5 pts}

Correcting the issue on the Foreign Key for ASSIGNMENT {5pts each - 10 pts}

The code to create the Foreign Keys on ASSIGNMENT {5pts each 10 pts}

Correcting the issue on the Primary Key for ASSIGNMENT {0 pts} No Longer Issue

The code to create the Primary Key for Assignment {10 pts}

Code to INSERT the data Insert data works fine {10 pts}

SELECT returns all rows {10 pts}

Code submitted as .sql or .txt and runnable {10 pts}

Used proper Wedgewood SQL Code (i.e. Not PRACTICE Schema {10 pts}

Create ASSIGNMENT table Run this code next and submit the results per the assignment. *********************************** ROP REATE Create EMPLOYEE table Run this code next and submit the results per the assignment. ROP REAT Create PROJECT table Run this code next and submit the results per the assignment. / ROP REA Create DEPARTMENT table Run this code first and submit the results per the assignment. Create ASSIGNMENT table Run this code next and submit the results per the assignment. *********************************** ROP REATE Create EMPLOYEE table Run this code next and submit the results per the assignment. ROP REAT Create PROJECT table Run this code next and submit the results per the assignment. / ROP REA Create DEPARTMENT table Run this code first and submit the results per the assignment