As we continue to explore databases and ideas, this project will help you practice model databases and relationships, as well as help you get started on your capstone project.
First, you will expand your CompanyDatabase to have Departments, Products and Orders. You will be crafting 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.
Objectives
Work with Foreign Keys
Craft Queries
Requirements
Add a few tables and foreign keys to an existing database
Create the Schema for your final project
Explorer Mode
[x] In your Company Database, add a table named Departments. Give this table a few columns:
Id as a primary key
DepartmentName as text
Building as text
[x] Add a Foreign key DepartmentId to your Employee Table
[x] Add tables named Products and Orders.
Orders should have the columns
OrderNumber as string
Id as a primary key
DatePlaced as datetime
Email as string
Product should have the columns
Id as a primary key
Price as double
Name as string
Description as string
NumberInStock as int
[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 3 tables (Orders, Products and ProductOrders) 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 order id, display on the OrderNumber, and all the product names
[x] Inserts a new product
[x] Inserts a new order
[x] Adds a product to an order
[x] Adds 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
Adventure Mode
[ ] Model out and implement a database for your Bank app from the past weekend
RobbleRabble
commented
4 years ago
Foreign Keys - The SQL
As we continue to explore databases and ideas, this project will help you practice model databases and relationships, as well as help you get started on your capstone project.
First, you will expand your CompanyDatabase to have
Departments,ProductsandOrders. You will be crafting 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.
Objectives
Requirements
Explorer Mode
[x] In your Company Database, add a table named
Departments. Give this table a few columns:Idas a primary keyDepartmentNameas textBuildingas text[x] Add a Foreign key
DepartmentIdto yourEmployeeTable[x] Add tables named
ProductsandOrders.Ordersshould have the columnsOrderNumberas stringIdas a primary keyDatePlacedas datetimeEmailas stringProductshould have the columnsIdas a primary keyPriceas doubleNameas stringDescriptionas stringNumberInStockas int[x] In our company, 1
Ordercan have manyProductsand 1Productcan have manyOrders. This will be a Many-to-Many relationship. Create the 3 tables (Orders,ProductsandProductOrders) and foreign keys needed for this to happen[x] Create queries that can do the following:
2OrderNumber, and all the product names[x] Turn in the above queries as a gist
Adventure Mode
Epic Mode
entity framework coreAdditional Resources
Recommended Practice: