Navigation

• Main Page

• Create a new Page
• All Pages
• Categories

• Administration
• File Management

• Login/Logout
• Your Profile

T-SQL For Database Creation

T-SQL

DROP DATABASE NvigorateDemo
go

CREATE DATABASE NvigorateDemo
go

USE NvigorateDemo

CREATE TABLE Customer
(
    CustomerId                bigint            not null    primary key        identity(1,1),
    FirstName                varchar(100)    not null,
    LastName                varchar(100)    not null,
    AddressId                bigint,
)

CREATE TABLE CustomerContactInformation
(
    CustomerId                bigint            not null    primary key,
    PrimaryCountryCode        varchar(5)        not null    default('1'),
    PrimaryAreaCode            varchar(3)        not null,
    PrimaryExchange            varchar(3)        not null,
    PrimaryNumber            varchar(4)        not null,
    PrimaryExtension        varchar(12)        not null    default (''),
    SecondaryCountryCode    varchar(5)        null,
    SecondaryAreaCode        varchar(3)        null,
    SecondaryExchange        varchar(3)        null,
    SecondaryNumber            varchar(4)        null,
    SecondaryExtension        varchar(12)        null,
    PrimaryEmail            varchar(255)    null,
    SecondaryEmail            varchar(255)    null
)

CREATE TABLE HomeAddress
(
    AddressId            bigint            not null    primary key        identity(1,1),
    AddressLine1        varchar(150)    not null,
    AddressLine2        varchar(150)    not null    default(''),
    City                varchar(100)    not null,
    StateAbbreviation    char(2)                not null,
    Zip                    varchar(10)        not null
)

CREATE TABLE Drivers
(
    CustomerId        bigint                not null,
    VehicleId        bigint                not null
)

CREATE TABLE Vehicle
(
    VehicleId        bigint                not null    primary key        identity(1,1),
    Make            varchar(25)            not null,
    Model            varchar(50)            not null,
    ModelYear        int                    not null,
    Color            varchar(50)            not null,
    VehicleTypeId    tinyint                not null
)

CREATE TABLE VehicleType
(
    VehicleTypeId    tinyint                not null    primary key        identity(1,1),
    VehicleType        varchar(10)            not null
)

CREATE TABLE Appointment
(
    AppointmentId    bigint                not null    primary key        identity(1,1),
    CustomerId        bigint                not null,
    VehicleId        bigint                not null,
    ScheduledFor    datetime            not null    default(getdate())
)

CREATE TABLE Bill
(
    BillId            bigint                not null    primary key        identity(1,1),
    AppointmentId    bigint                not null
)

CREATE TABLE LineItem
(
    LineItemId        bigint                not null    primary key        identity(1,1),
    BillId            bigint                not null,
    Units            decimal(10,4)        not null
)


CREATE TABLE [Service]
(
    ServiceId        bigint                not null    primary key        identity(1,1),
    Name            varchar(50)            not null,
    Cost            decimal(10,4)        not null
)

CREATE TABLE Product
(
    ProductId        bigint                not null    primary key        identity(1,1),
    Name            varchar(50)            not null,
    Cost            decimal(10,4)        not null
)

INSERT INTO VehicleType (VehicleType) VALUES ('Car')
INSERT INTO VehicleType (VehicleType) VALUES ('Truck')