Computer Science 2

Create a SQL Server database called: ITCO333Database. Using Data Definition Language (DDL) and Data Manipulation Language (DML) you will create tables based on your Unit 1 ERD. In addition, you will create the below DEPARTMENTS and EMPLOYEES tables. Before you begin creating the new tables, review your tables created in Unit 1 IP to ensure that your database is in third normal form (3NF).

Part 1:

Your ITCO333 database should contain data related to the organizational departments in your Unit 1 chosen topic. Therefore, create a DEPARTMENTS table with the following field specifications:

Field Name     Data Type     Other Comments and Requirements

Department_ID       int     Primary Key

Department_Name     nvarcha(50)    

Insert at least four records of sample data into the DEPARTMENTS table.

Your ITCO333 database should contain data related to employees in your Unit 1 chosen topic. Therefore, create an EMPLOYEES table with the following field specifications:

Field Name     Data Type     Other Comments and Requirements

Employee_ID     Int     Primary Key

Last_Name     nvarchar(50)     Cannot be null.

First_Name     nvarchar(50)     Cannot be null.

Birth_Date     datetime    

Employment_Start_Date     datetime    

Hourly_Pay     decia(p[,s])     Must be greater that 0

Department_ID     Int     Related to the DEPARTMENTS table.

Create a foreign key constraint.

Manager_ID     Int     Related to the Employee_ID in this table.

Create a foreign key constraint.

Insert at least eight records of sample data into the EMPLOYEES table.

Part 2:

Using your Unit 1 ERD, create tables, fields, primary keys and relationship constraints in your ITCO333Database.

Insert your Unit 1 sample data into the newly created tables.

Part 3:

Generate a SQL Server Database Diagram.

Additional Requirements:

    All tables must be in Third Normal Form (3NF). This may require you to normalize your Unit 1 data. Be sure to incorporate Instructor feedback from your Unit 1 IP.

    Use the following data types:

        Integers: int

        Decimals: decimal(p[,s])

        Strings: nvarchar(50)

        Date/Time: datetime

Deliverable: One Word document with:

    SQL DDL to create database

    SQL DDL to create tables (including EMPLOYEES and DEPARTMENTS tables)

    SQL DML to insert data (including EMPLOYEES and DEPARTMENTS sample data)

    SQL Server Database Diagram

The aforementioned SQL DML must be in text format (no screen shots).