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
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
)
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started