Assignment1:

Description: In this assignment, you will be utilizing the steps youve learned so far for translating real world systems into database systems. You will be identifying data elements, subdividing these elements into more usable pieces, transcribing tables/columns from our data elements and identifying primary and foreign keys. Additionally, you will be asked to infer some information as we did in the example video(s) (e.g. creating an employees table given a data element called technician in our customer invoice). Youll complete this assignment and upload to Canopy once complete. Feel free to fill out this Word document and submit the whole file.

Context: Youve been tasked with designing a database for a clothing manufacturer. You have been given an example customer invoice (ExampleInvoice.png on Canopy) and told that the new database will be interacted with by the following systems:

Mobile app for tracking customer order status

Internal billing system for identifying the payment status from the customer

Internal point of sale for the sales people to input new sales

Internal inventory system for tracking clothing quantities, sizes, colors, etc.

Now in the early stages of this design, you need to identify what the potential tables and keys may look like.

Task:

List the data elements

Subdivide, and list the newly subdivided data elements

List the tables and columns, be sure to mark the attributes of the column as well (nullable, data type, etc)

Re-list the tables and columns, marking primary and foreign keys in the tables, as well as any linking tables as need be.

Assignment2:

Description: In this assignment, you will be utilizing the steps youve learned so far for translating real world systems into database systems. Youll be completing your conversions by normalizing your data structures, adding indexes and then creating them in SQL Server Management Studio.

Context: Youve been tasked with designing a database for a clothing manufacturer. You have been given an example customer invoice (ExampleInvoice.png on Canopy) and told that the new database will be interacted with by the following systems:

Mobile app for tracking customer order status

Internal billing system for identifying the payment status from the customer

Internal point of sale for the sales people to input new sales

Internal inventory system for tracking clothing quantities, sizes, colors, etc.

Now in the early stages of this design, you need to identify what the potential tables and keys may look like.

Task:

Normalize and index mark (like in the video) your tables from last week, copy them into either this word document or a text file.

Create your tables, relationships and indexes in SQL Server Management Studio. Zoom to fit by clicking Database Diagram at the top of the window and doing Database Diagram->Zoom->To Fit and screenshot your database diagram. Next, expand your created tables and screenshot the indexes on each table (or if they all fit on the screen all at once).

What you are submitting:

Zip file containing screenshots and table descriptions from task #1.