Forensics Database

Essential Question: How can I design a database and build it from scratch?

Mastery Objectives:

• SWBAT create a database using an online SQL Server.

• SWBAT design an ERD for a database.

• SWBAT create tables for their database.

Resources: 

• http://sqlfiddle.com/

• https://www.w3schools.com/sql/sql_ref_keywords.asp 

• https://www.w3schools.com/sql/sql_ref_create.asp 

• Draw.io

• https://docs.google.com/spreadsheets/d/11sd5LSdNLumT0X4kk2_UmjgCaSvrhvhuGs6pkV7TERc/edit?usp=sharing 

Directions: Your project has two parts: an ERD for your project and create a SQL Database.  You will be creating a database using most of the suggested fields for a forensics database (see the link to the spreadsheet above). 

ERD

Your ERD should be created in draw.io. Select ERD and write a line for each field. Create each table for your database. Link tables using the lines. Make keys (indexes) for each table.  Remember, a key or index is a unique field.

Database

You will actually be creating your database in sqlfiddle.com. You will need to create the database and then create all the tables and fields for your database. See examples of code below:

CREATE DATABASE testDB; 

CREATE TABLE Persons (

PersonID int,

LastName varchar(255),

FirstName varchar(255),

Address varchar(255),

City varchar(255)

); 

CREATE INDEX idx_lastname ON Persons (LastName); 

ALTER TABLE Customers ADD Email varchar(255); 

ALTER TABLE Customers DROP COLUMN Email;