Database Project

Hello! This is your individual assignment. Expectations are that you do this on your own. By yourself. Han Solo. Feel me? So, This assignment is designed to see if you can draw an ERD from an existing database, modify that design, then create the SQL code that would implement those changes.   

The database that you’ll be diagramming is the tutorial database from w3schools. https://www.w3schools.com/sql/​ should get you to the tutorial site, and any command, if you click the ‘Try it yourself’ button, should take you to the online editor that uses that database.   

Graphically   

 

  

Is what the site looks like…   

See the green ‘Try it Yourself’ button??? Click that…

  

 

Once you do, you should be in the editor environment...it looks like:   

 

  

The database you’ll be modeling is on the right....   

 

  

You need to use your understanding of primary and foreign keys to make the appropriate relationships in an ERD between the entities. Additionally you should be able to figure out which entities are normal, associative, or weak...use appropriate symbols!   

Specifically the directions for the assignment are:   

1 - Create the ERD from the w3schools sql site. (25 points) Your ERD CANNOT BE HAND DRAWN. You must use a program to draw it. I am indifferent as to the program. Personally I use paint to print screen and modify ERD’s. You could do this, or you could use PowerPoint, or any other program. Please note that I am looking for specific symbols. ​Microsoft Project makes Class diagrams, not ERDs, and if you choose to use a Class diagram you will be deducted points as associative and weak entities are not shown in Class diagrams.

https://erdplus.com/#/​ is a tool that some students have used in the past - PLEASE

NOTE THAT NOT ALL SYMBOLS ARE PRESENT!!!! STUDENTS HAVE HAD TO MODIFY THESE DIAGRAMS BEFORE TURNING THEM IN FOR FULL CREDIT. If you don’t get it, I’m kind of hung up on you guys using the appropriate symbols...

  

2 - Create a second ERD with the following modifications: (25 points)   

A - The company now has multiple stores; stores have an address, square footage, a phone number, and one employee is the store manager

  

B - Stores have products that are stored in an inventory. The same product can and should be stored (and sold) at multiple stores. Inventories should capture the quantity of a product on hand at a given store. Inventories should also track how much space is left in a given store given the number of products on hand. This space is a calculated field using the square footage - don’t worry about the specific calculation, just be able to store the value in a field.

  

C - Employees can have one of four different titles: Sales Associate, Assistant Manager, Store Manager, and Inventory Specialist

  

D - Additionally the employment history of the employee should now be tracked. This includes a start date and an end date for each employment of an employee. Employees should be able to be promoted, fired, hired, and moved back and forth between stores as the company needs.

  

PLEASE NOTE THAT TO FILL THESE NEW REQUIREMENTS YOU WILL NEED TO  

CREATE NEW TABLES, MODIFY EXISTING TABLES, ADD NEW FIELDS, AND  

SPECIFY SOME FIELD VALUES. 

 

3 - Create SQL statements needed to modify the existing database to reflect the changes in question 2. For each command, print screen the ‘after’ image. For example, if I made a table called ‘Black Panther’ I would expect to see the evidence of the table in the database.

  

 

  

Changes should include all of the following: a. Create all new tables (20 points) b. Modify existing tables to reflect new fields (include all primary and foreign keys)

(10 points) c. Create the constraints that reflect the primary and foreign keys (10 points) d. No key should have a null value (Neither primary or foreign) (10 points)

  

Please note that EVERY command should have a picture! I want to see what you’ve done. If there isn’t a corresponding picture for all of the 4 requirements (a-d) you will be deducted all points for that item. I.E. there are several new primary and foreign keys. Make sure you modify the new AND existing tables to reflect these changes.

 

Your finished document that you upload into TurnItIn should look as follows:   

ERD for the existing database.   

<Insert your ERD picture here>   

ERD for the modified database   

<Insert your ERD picture here>   

EACH SQL statement executed to make the necessary changes.   

<Insert the picture of each SQL statement plus the execution of said statements> <There should be many different pictures here>  

This assignment is worth 100 points. 

 

This assignment MUST be turned in through the Canvas prompt.