The instructions for my sql assignment are listed below. I created the create co

Question

The instructions for my sql assignment are listed below. I created the create command (hopefully its right) but do not know where to go from there. I listed what I made for the create command after the instructions. Pleaseee help.

Submit one Notepad file containing create, insert, and select commands

Submit one Notepad file containing the drop commands, in the correct order.

Create all the tables needed for the Attorney/Client ERD that we completed in class (Attorney, Bar, Specialty, Registration, AttSpec, Client, Case, Judge, Court, Zip, & Assignment). Be sure all PKs and FKs are correct.

Insert 2 records into Zip, Attorney, Bar, Specialty, Client, Court, and Judge.

Insert 2 records into AttSpec, & Registration.

Insert 4 records into Assignment.

List all the information from each table in this order – Zip, Bar, Specialty, Attorney, AttSpec, Registration, Court, Judge, Case, and Assignment.

CREATE TABLE Attorney (

AttorneyID INT NOT NULL,

AttorneyFName VARCHAR(50) NOT NULL,

AttorneyLName VARCHAR(50) NOT NULL,

AttorneyAddress VARCHAR(50) NOT NULL,

ZipCode INT,

PRIMARY KEY (AttorneyID),

FOREIGN KEY (ZipCode) REFERENCES Zip ON DELETE SET NULL

)

CREATE TABLE Specialty (

SpecialtyID INT NOT NULL,

SpecialtyName VARCHAR(50) NOT NULL,

SpecialtyDesc VARCHAR (150) NOT NULL,

AttorneyID INT,

PRIMARY KEY (SpecialtyID),

FOREIGN KEY (AttorneyID) REFERENCES Attorney ON DELETE SET NULL

)

CREATE TABLE Work (

WorkID INT NOT NULL,

AttorneyID INT,

SpecialtyID INT,

PRIMARY KEY (WorkID),

FOREIGN KEY (AttorneyID) REFERENCES Attorney ON DELETE SET NULL,

FOREIGN KEY (SpecialtyID) REFERENCES Specialty ON DELETE SET NULL

)

CREATE TABLE Register (

RegistrationID INT NOT NULL,

AttorneyID INT,

BarID INT,

PRIMARY KEY (RegistrationID),

FOREIGN KEY (AttorneyID) REFERENCES Attorney ON DELETE SET NULL,

FOREIGN KEY (BarID) REFERENCES Bar ON DELETE SET NULL

)

CREATE TABLE Bar (

BarID INT NOT NULL,

State CHAR(2) NOT NULL,

Certificate VARCHAR(50) NOT NULL,

PRIMARY KEY (BarID)

)

CREATE TABLE Zip (

ZipCode INT NOT NULL,

City VARCHAR (25) NOT NULL,

State CHAR(2) NOT NULL,

PRIMARY KEY (ZipCode)

)

CREATE TABLE Client (

ClientID INT NOT NULL,

ClientFName VARCHAR(50) NOT NULL,

ClientLName VARCHAR(50) NOT NULL,

DOB DATE NOT NULL,

ClientPhone CHAR(10) NOT NULL,

ZipCode INT,

PRIMARY KEY (ClientID),

FOREIGN KEY (ZipCode) REFERENCES Zip ON DELETE SET NULL

)

CREATE TABLE Assignment (

AssignmentID INT NOT NULL,

AttorneyID INT,

ClientID INT,

CaseID INT,

PRIMARY KEY (AssignmentID),

FOREIGN KEY (AttorneyID) REFERENCES Attorney ON DELETE SET NULL,

FOREIGN KEY (ClientID) REFERENCES Client ON DELETE SET NULL,

FOREIGN KEY (CaseID) REFERENCES Case ON DELETE SET NULL

)

CREATE TABLE Court (

CourtID INT NOT NULL,

CourtName VARCHAR(25) NOT NULL,

ZipCode INT,

PRIMARY KEY (CourtID),

FOREIGN KEY (ZipCode) REFERENCES Zip ON DELETE SET NULL

)

CREATE TABLE Case (

CaseID INT NOT NULL,

CaseDesc VARCHAR(200) NOT NULL,

CaseType VARCHAR(50) NOT NULL,

CourtID INT,

PRIMARY KEY (CaseID),

FOREIGN KEY (CourtID) REFERENCES Court ON DELETE SET NULL

)

CREATE TABLE Judge (

JudgeID INT NOT NULL,

JudgeFName VARCHAR(50) NOT NULL,

JudgeLName VARCHAR(50) NOT NULL,

CourtID INT,

PRIMARY KEY (JudgeID),

FOREIGN KEY (CourtID) REFERENCES Court ON DELETE SET NULL

)

Explanation / Answer

Answer is as follows :

Insert Statements :

Table Attorney :

INSERT INTO Attorney values(1,"John", "Fd.", "Shicago", 12345);
INSERT INTO Attorney values(2,"Tom", "Kick", "Washington DC", 48569);

Table Specialty :

INSERT INTO Specialty values(122,"Jack","ABC", 1);
INSERT INTO Specialty values(123,"Tim","A85C", 2);

Table Bar :

INSERT INTO Bar values(145,"a","ABCDERF");
INSERT INTO Bar values(146,"b","ASEEDFR");

Table Zip :

Insert INTO Zip values(12345,"Shicago","ABC");
Insert INTO Zip values(48569,"Washington DC","DEF");

Table Client :

Insert Into Client values(147,"Tobiaz","Zoq", "23-11-1989", 12589,12345);
Insert Into Client values(175,"Kim", "Tepper", "11-04-1999", 47856,48569);

Table Court :

Insert Into Court values(1785,"ASW",48569);
Insert Into Court values(78514,"QWE",12345);

Table Case :

Insert Into Case values(47856,"AQWED","Divorced",1785);
Insert Into Case values(478569, "ERTGG","Criminal", 78514);

Table Judge:

Insert Into Judge values(8569,"Robert", "FD.", 78514);
Insert Into Judge values(4785,"John", "Toe",1785);

Table work :

Insert Into Work values(478566,1,122);
Insert Into Work values(785681,2,123);

Table Register :

Insert Into Register values(7851123,2,146);
Insert Into Register values(7854123,1,145);

Table Assignment :

Insert Into Assignment values(7845,1,147,47856);
Insert Into Assignment values(7846,2,146,478569);
Insert Into Assignment values(7842,1,147,478569);
Insert Into Assignment values(7412,2,146,47856);

All Select Statements:

Select * From Attorney;

Select * From Zip;

Select * From Specialty;

Select * From Client;

Select * From Court;

Select * From Judge;

Select * From Case;

Select * From work;

Select * From Register;

All the inserted in the queries is assumed, you can edit asa per your requirements....

if there is any query or missing part please ask in comments...

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.