Database Operations and Design

The assignment consists of four parts: Part 1, where students are required to create a database called ITCO630_P1.MDF and add tables with specific data; Part 2, which involves designing a single table to store invoice information and applying normalization to reduce the table to third normal form; Part 3, where students must download, unzip, and attach the sample database ITCO630_A, then create views based on that database; and Part 4, which requires students to write queries using the ITCO630_A database.

Assignment 1
Use SQL Management Studio to create a database called ITCO630_P1.MDF. Be sure to store the
database in a location that you will remember. Add the following tables and data to the database.
Use the appropriate field types and lengths for the tables.
Create the following queries and save them all in a file called ITCO630_P1.SQL. Please note
that you can execute individual queries in a query file by highlighting the lines that you want to
execute before running the script. Define what database to use with a USE statement.
 Get the full details of the employee table.
 Get the full details of the employees who work in store S2.
 Get the full details of the promotions with a budget greater than $100,000.
 Get the employee number and promotion number for employees with unknown (NULL)
jobs who work on promotion P2.

 Using a subquery, get the first and last names of the employees who work in the Orange
Appeal store.
 Using a subquery, get full details of all the employees whose stores are located in
Orlando.
Detach the database and create a zip file called ITCO630_P1.ZIP including the associated log
file and the query file. After creating the zip file, reattach the database.
Assignment 2
Consider a typical sales invoice that would include the following information. Design a single
table to hold all of the information required to store an invoice including this information. Next,
apply normalization to reduce this table to third normal form. Prepare a Word document showing
the original and final tables. Include a short summary (2–3 paragraphs) of the problems with the
original single table design and how normalization improved the design.
Orders:
 Order_id
 Order_date
 Customer_id
 Customer_name
 Customer_address
 Customer_city
 Customer_state
 Item_id
 Item_description
 Item_qty
 Item_price
 Item_total_price
 Order_total_price
Assignment 3
Download, unzip, and attach the sample database ITCO630_A from the link below. The sample
database represents an educational institution with students and different schools. Each student
goes to just one school. The students have various roles in different assignments and may work
on more than one assignment at a time.
Download the database for this assignment.
The following are the tables and data in the ITCO630_A database:

Using the sample database, write the scripts in a file called ITCO630_P3.SQL to create the
following views. Remember to include a USES clause at the top of the script file to use the
ITCO630_A database. Also include code that checks if the view already exists. If it does, it
should be dropped and recreated.
1. Create a view named v_worker showing the student number, assignment number, and
start date where the role is "worker."
2. Create a view called v_no_points with all the columns of the assignment table except the
points column.
3. Create a view called v_count that shows the number of students working on each
assignment. The view should have columns for the assignment number and the count.
Assignment 4

Create the following queries on the ITCO630_A database used in Unit 3 and save them all in a
file called ITCO630_P5.SQL. Please note that you can execute individual queries in a query file
by highlighting the lines that you want to execute before running the script. Remember to define
what database to use with a USE statement. Complete the following:
1. Using a join, get the full details of all the students who work on the mid-term exam
assignment.
2. Get the assignment names (duplicates eliminated) being worked on by students at Central
University.
3. Get the last names of all the students who are working on assignment A1.
4. Get the student numbers and start dates of all the students with start dates equal to the
earliest date.
5. Insert yourself into the student table using the last five digits of your phone number as the
student number and show yourself as attending Central University. Then show all the
records in the student table.
6. Delete yourself from the student table by matching your student number, and then show
all the records in the student table.
You should create a zip file called ITCO630_P5.ZIP with your query file (ITCO630_P5.SQL)
included.