Please complete the following

Together in one window of SQL management studio.

Part $2$:

Creating the COMPANY database schema using DDL and DML

statements.

Make sure to understand all the relationships depicted in the Page $5$ from the given ER

Diagram for Company Database as Explained in Class.

$2-1)$ Transform them into a Logical Company Database schema as explained in class.

Write the corresponding DDL to create Company Database Schema with Create

Table $($or Alter Table if it is already created$)$ for all $6$ Relations by defining PK

Constraint, FK Constraints and Not Null Constraints as explained in class with the

finalized ER Diagram.

The Part$2$ of Lab$2\_2$ should be done based on the finalized E$-$R diagram completed from

the Lab$2-1$ data which is almost the same with the ER diagram in the textbook given

below with a few differences that were explained in class. $10$ avoid the misinterpretation, the finalized correct transiormed database scheme from the E$-$R diagram is given in the Figure $5\mathrm{.}7$ in Page $5$ and Page $6$ in this Lab$2-2$ specification below.

The DDLs for Lab$2\_2$ should be done based on the finalized correct transformed scheme that is given in the page $5$ and $6$ in the Lab$2\_2.$ Your FK Constraints for Lab$2\_2$ should be based on all the relationships identified from the E$-$R Diagram that are illustrated in the page $5$ and the scheme and records are given in the page $6$ in this Lab$2\_2$ specification.

The Finalized Correct Database Scheme and the records $($database state$)$ to be Inserted for the Company database is given in the last page $($Page $6)$ of this Lab$2\_2$ and in the Lab$2\_2$ section of the class webpage.

You have to create a database scheme precisely with the given Meta data information $($Table names, Column Names$)$ as in the given picture in Page $6.$

Make sure to insert all four tuples into Department tables as given.

Delete all the invalid data you inserted in the Part $1$ first before starting this main Part $2$ or you can create fresh new Tables in a new Company database with a slightly different Company database name combined with your name.

The typical SQL commands $($DDL$)$ to look up for this lab are as follow:

Create Database...;

Drop Database $...$;

Use database$\_$name;

Delete table...;

Drop table Employee;

Create table Employee $(....)$;

ALTER TABLE EMPLOYEE ADD COLUMN...

ALTER TABLE EMPLOYEE ADD CONSTRAINT...

ALTER TABLE EMPLOYEE DROP COLUMN...;

ALTER TABLE EMPLOYEE DROP CONSTRAINT...;

ALTER TABLE EMPLOYEE ALTER COLUMN...;

Select $*$ From Employee;

$2\_2)$ Populating the COMPANY database using SQL $($DML$)$ statements with the given data below. Make sure to insert the exact data set as given.

The typical SQL commands $($DML$)$ to populate a table and show the rows inserted into the table for this task are like the following example.

INSERT INTO EMPLOYEE VALUES$(\text{'}$John$\text{'},\text{'}$B$\text{'},$ 'Smith', $\text{'}123456789\text{'},\text{'}9-$Jan$-55\text{'},\text{'}731$

Fondren, Houston, TX$\text{'},\text{'}$M$\text{'},30000,\text{'}987654321\text{'},5)$; $```$

INSERT INTO EMPLOYEE VALUES $(\text{'}$James$\text{'},\text{'}$E$\text{'},$ 'Borg', $888665555,\text{'}10-$Nov$-27\text{'},\text{'}450$

Stone, Houston, TX$\text{'},\text{'}$M$\text{'},55000,$ NULL, $1)$;

$**$

Alter Table Employee

Add Foreign Key $($Super$\_$ssn$)$ References Employee $($Ssn$)$;

Select $*$ From Employee;

insert into DEPARTMENT values $(\text{'}$Headquarters$\text{'},1,888665555,\text{'}19-$Jun$-71\text{'})$;

INSERT INTO DEPENDENT VALUES $(123456789,$ 'Alice', $\text{'}$F$\text{'},\text{'}31-$Dec$-78\text{'},$ 'Daughter'$)$;

INSERT INTO PROJECT VALUES $(\text{'}$ProductX$\text{'},1,$ 'Bellaire', $5)$;

INSERT INTO WORKS$\_$ON VALUES $(123456789,1,32\mathrm{.}5)$;

$..$

ALTER TABLE employee ADD

foreign key $($superssn$)$ references employee$($ssn$),$

foreign key $($dno$)$ references department$($dnumber$)$;

ALTER TABLE department ADD Constraint FKMgrSsnPKSsn

foreign key $($mgrssn$)$ references employee$($ssn$)$

$*$

$```$

$2\_3)$ After creating PK constraints, FK Constraints, Not Null constraints in your tables, Test the followings with the same invalid data you inserted in Part $1.$

To test a Primary Key Constraint and Entity Integrity Constraint,

$-$ Insert a duplicate PK value $($for example, same SSN into your table Employee$),$

$-$ Insert a duplicate tuple.

$-$ Insert Null into a PK column.

To test a Not Null Constraint Insert a Null into a Non$-$Null Column in a table

To Test a Referential Integrity Constraint $($FK Constraint$)$ Insert a non$-$existing PK value to a corresponding FK column in a table in a relationship

Copy and paste your SQL statements in a Word document and Add screenshots showing your statements and the results TOGETHER in each Window Screenshot.

$2\_4)$ Visualize the Database Relationships.

Use "Database Diagram" in the left pane $($or the tab "Database Tools" on the tool bar if your management studio is older than $2012)$ to create a visual representation of the database tables and relationships. Add the figure to your report. Figure $3.$ Symbols of ER diagrams

Figure $5\mathrm{.}7$

Referential integrity constraints displayed on the COMPANY relational database schema.

EMPLOYEE

EMPLOYEE

DEPARTMENT

DEPENDENT

DEPT LOCATIONS

PROJECT

WORKS$\_$ON