Week 08 Day 4 - Foreign Keys - The SQL -

[mdewey]

As we continue to explore databases and ideas, this project will help you practice modeling databases and relationships, as well as help you get started on your capstone project.

First you will expand you CompanyDatabase to have Departments, Products and Orders. You will be crafting a queries for these new tables.

After that, this weekend is the next big push your final project, work on the HTML and CSS; As well as the database schema.

Get a head of you final project as much as possible. The few weeks are going to come and go extremely fast.

Objectives

• Work with Foreign Keys
• Craft Queries
• Further you final project

Requirements

• Add a few tables and foreign keys to an existing database
• Create the Schema for your final project
• Work on the HTML & CSS for your final project

Optional

Install pgcli

• For Mac you can brew
• For Windows you need to install python and pip

Explorer Mode

• [x] In your Company Database, add a table called Departments. Give this a few columns

• [x] Add a Foreign key call DepartmentId to your Employee Table

• [x] Add Table Called Products and a table called Orders. Give each table a few columns

• [x] In our company, 1 Order can have many Products and 1 Product can have many Orders. This will be a Many to Many relationship. Create the tables and foreign keys needed for this to happen

• [x] Create queries that can do the following:

  • [x] Given a Department Id, Give me all employees in the department
  • [x] Given a Department Name, give me the phone extensions
  • [x] Find all Orders that contain the Product Id of 2
  • [x] Given an OrderId, Display on the OrderNumber, and all the product names
  • [x] Insert a new Product
  • [x] Creating a new Order
  • [x] Adding an Product to an Order
  • [x] Adding a new employee to a department
  • [x] Updating a employee's department
  • [x] Removing a Product from an Order

• [x] Turn in the above queries as a gist

• [x] Work on your final project HTML. DO not sleep on this. You have the time and resources to get a good bit of this done this weekend.

• [ ] Complete your first draft of your database schema for your final project.

Adventure Mode

• [x] Work on the Koans
• [ ] Give these a try : https://sqlkoans.com/

Epic Mode

• [ ] Investigate working with C#/Ruby and Postgres

Additional Resources

• https://www.postgresql.org/docs/10/static/index.html

  Recommended Practice:

• For more practice, Hackerrank has a SQL Track

[jsonrocha]

https://gist.github.com/rochaproforma/3a3b4f20f3e703cc1709e04c5fa37960

[mdewey]

Your homework is: Meets Expectations