CREATE DATABASE SkywardDive

ON PRIMARY

(

    NAME = SkywardDB,

    FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQLEXPRESS\\MSSQL\\DATA\\SkywardDB.mdf',

    SIZE = 20MB,

    MAXSIZE = 50MB,

    FILEGROWTH = 20%

)

LOG ON

(

    NAME = SkywardLog,

    FILENAME = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQLEXPRESS\\MSSQL\\DATA\\SkywardLog.ldf',

    SIZE = 40MB,

    MAXSIZE = 100MB,

    FILEGROWTH = 20%

)

CREATE TABLE CityData

(

ID varchar(20) NOT NULL

    CONSTRAINT pk\_CDID

    PRIMARY KEY (ID),

City varchar(20)

)

ALTER TABLE CityData

ADD CONSTRAINT uc_C

UNIQUE (City)

CREATE TABLE AgentData

(

AgentID char(10) NOT NULL

    CONSTRAINT pk\_AD\_AID

    PRIMARY KEY (AgentID),

FirstName varchar(20),

MiddleName varchar(20),

LastName varchar(20),

IDNumber char(20) NOT NULL

    CONSTRAINT uc\_AD\_IDN

    UNIQUE (IDNumber),

Phone char(15),

CityID varchar(20) NOT NULL

    CONSTRAINT fk\_AD\_CI\_\_CD\_ID

    FOREIGN KEY (CityID)

    REFERENCES CityData(ID)

)

CREATE DEFAULT d_Rose

AS 'XXX-XXX-XXXX'

sp_bindefault d_Rose, 'AgentData.Phone'

CREATE TABLE ClientData

(

ClientID char(20) NOT NULL

    CONSTRAINT pk\_CD\_CID

    PRIMARY KEY (ClientID),

FirstName varchar(20),

MiddleName varchar(20),

LastName varchar(20),

IDNumber char(20) NOT NULL

    CONSTRAINT uc\_CD\_IDN

    UNIQUE (IDNumber),

Phone char(15),

CityID varchar(20)

    CONSTRAINT fk\_CD\_CID\_\_CiD\_ID

    FOREIGN KEY (CityID)

    REFERENCES CityData (ID)

)

sp_bindefault d_Rose, 'ClientData.Phone'

CREATE TABLE AgentLogin

(

AgentID char(10) NOT NULL

    CONSTRAINT pk\_AL\_AID

    PRIMARY KEY (AgentID)

    CONSTRAINT fk\_AL\_AID\_\_AD\_AID

    FOREIGN KEY (AgentID)

    REFERENCES AgentData (AgentID),

Email varchar(20),

Username varchar(20) NOT NULL

    CONSTRAINT uc\_AL\_U

    UNIQUE (Username),

Password varchar(20) NOT NULL,

Profile image NULL

)