SQL question

CMS Project: Phase II Instructions

In this phase, you will create tables based upon the ERD and SQL code below.You will thenpopulate each table with the data presented below. Finally, you will create queries that will be used to support reports for Accounting and Management. You will not actually create the reports in a GUI environment– only the queries that will serve as the basis for the reports. Screenshots are required for a grade to be given. One screenshot is not the idea; however, multiple screenshots along the way is the goal.

Background:

The following ERD will be used as the basis for this Phase.

 

 

 

 

 

Part A: Table Creation and Data Loading

Instructions: Create a new database in SQL Server and run the following CREATE TABLE commands. Note that you must run the CREATE TABLE statements in the order presented (and load the data in the order presented) to avoid conflicts resulting from foreign key constraints.

Additional instructions for materials to turn in for this phase of your project are included at the end of this specification document.

CREATE TABLE Regions

(RegionID int not null,

 RegionAbbreviation varchar(4),

 RegionName varchar(100),

 CONSTRAINT PK_Regions PRIMARY KEY (RegionID))

 

CREATE TABLE Countries

(CountryID int not null,

 CountryName varchar(50),

WeeklyHours int,

Holidays int,

VacationDays int,

 RegionID int,

 CONSTRAINT PK_Countries PRIMARY KEY (CountryID),

 CONSTRAINT FK_CountriesRegions FOREIGN KEY (RegionID) References Regions)

 

CREATE TABLE EmployeeTitles

(TitleID int not null,

 Title varchar(15),

 CONSTRAINT PK_EmpTitles PRIMARY KEY (TitleID))

 

CREATE TABLE BillingRates

(TitleID int not null,

Level int not null,

Rate float,

CurrencyName varchar(5),

CONSTRAINT PK_BillingRates PRIMARY KEY (TitleID, Level),

CONSTRAINT FK_BillingRatesTitles FOREIGN KEY (TitleID) References EmployeeTitles)

 

 

 

CREATE TABLE Employees

(EmpID int not null,

 FirstName varchar(30),

 LastName varchar(30),

Email varchar(50),

 Salary decimal(10,2),

 TitleIDint,

 Level int,

 SupervisorID int,

 CountryID int,

 CONSTRAINT PK_Employees PRIMARY KEY (EmpID),

 CONSTRAINT FK_EmployeesCountries FOREIGN KEY (CountryID) References Countries,

CONSTRAINT FK_EmployeesEmpTitles FOREIGN KEY (TitleID) References EmployeeTitles,

 CONSTRAINT FK_EmployeeSupervisors FOREIGN KEY (SupervisorID) References Employees)

 

CREATE TABLE ContactTypes

(ContactTypeID int not null,

 ContactType varchar(30)

 CONSTRAINT PK_ContactTypes PRIMARY KEY (ContactTypeID))

CREATE TABLE ContractTypes

(ContractTypeID int not null,

 ContractType varchar(30)

 CONSTRAINT PK_ContractTypes PRIMARY KEY (ContractTypeID))

 

CREATE TABLE BenefitTypes

(BenefitTypeID int not null,

 BenefitType varchar(30)

 CONSTRAINT PK_BenefitTypes PRIMARY KEY (BenefitTypeID))

 

 

CREATE TABLE Clients

(ClientID int not null,

 LegalName varchar(50),

 CommonName varchar(50),

 AddrLine1 varchar(50),

 AddrLine2 varchar(50),

 City varchar(25),

 State_Province varchar(25),

 Zip varchar(9),

 CountryID int,

 CONSTRAINT PK_Clients PRIMARY KEY (ClientID),

 CONSTRAINT FK_ClientsCountries FOREIGN KEY (CountryID) REFERENCES Countries)

 

 

 

CREATE TABLE Contacts

(ContactID int not null,

 FirstName varchar(50),

 LastName varchar(50),

 AddrLine1 varchar(50),

 AddrLine2 varchar(50),

 City varchar(25),

 State_Province varchar(25),

 Zip varchar(9),

 CountryID int,

 ContactTypeID int,

 CONSTRAINT PK_Contacts PRIMARY KEY (ContactID),

 CONSTRAINT FK_ContactsCountries FOREIGN KEY (CountryID) REFERENCES Countries)

 

CREATE TABLE ContractTypes

(ContractTypeID int not null,

ContractTypeDesc varchar(50),

CONSTRAINT PK_ContractTypes PRIMARY KEY (ContractTypeID))

 

CREATE TABLE Contracts

(ContractID int not null,

 ContractDesc varchar(100),

 ClientID int,

 ContractTypeID int,

 CONSTRAINT PK_Contracts PRIMARY KEY (ContractID),

 CONSTRAINT FK_ContractsClients FOREIGN KEY (ClientID) REFERENCES Clients,

 CONSTRAINT FK_ContractsContractTypes FOREIGN KEY (ContractTypeID) REFERENCES ContractTypes)

 

CREATE TABLE ContractsContacts

(ContractID int not null,

 ContactID int not null,

 CONSTRAINT PK_ContractsContacts PRIMARY KEY (ContractID, ContactID),

 CONSTRAINT FK_CC_Contracts FOREIGN KEY (ContractID) REFERENCES Contracts,

 CONSTRAINT FK_CC_Contacts FOREIGN KEY (ContactID) REFERENCES Contacts)

 

 

CREATE TABLE Projects

(ProjectID int not null,

 ProjectName varchar(50),

 HourCapAmount decimal(10,2),

 ProjectManagerID int,

 ContractID int,

 CONSTRAINT PK_Projects PRIMARY KEY (ProjectID),

 CONSTRAINT FK_ProjectsEmployees FOREIGN KEY (ProjectManagerID) REFERENCES Employees,

 CONSTRAINT FK_ProjectsContracts FOREIGN KEY (ContractID) REFERENCES Contracts)

 

 

CREATE TABLE EmployeesProjects

(EmpID int not null,

 ProjectID int not null,

 StartDate smalldatetime,

 EndDate smalldatetime,

 CONSTRAINT PK_EmployeesProjects PRIMARY KEY (EmpID, ProjectID),

 CONSTRAINT FK_EP_Employees FOREIGN KEY (EmpID) REFERENCES Employees,

 CONSTRAINT FK_EP_Projects FOREIGN KEY (ProjectID) REFERENCES Projects)

 

 

CREATE TABLE Timesheets

(TimesheetID int not null,

 SupervisorApproveDate smalldatetime,

 CONSTRAINT PK_Timesheets PRIMARY KEY (TimesheetID))

 

CREATE TABLE WorkHours

(EmpID int not null,

 ProjectID int not null,

 WH_Day int not null,

 WH_Month int not null,

 WH_Year int not null,

 HoursWorked float,

 TimesheetID int,

 CONSTRAINT PK_WorkHours PRIMARY KEY (EmpID, ProjectID, WH_Day, WH_Month, WH_Year),

 CONSTRAINT FK_WorkHoursEmployees FOREIGN KEY (EmpID) REFERENCES Employees,

 CONSTRAINT FK_WorkHoursProjects FOREIGN KEY (ProjectID) REFERENCES Projects,

 CONSTRAINT FK_WorkHoursTimesheets FOREIGN KEY (TimesheetID) REFERENCES Timesheets)

 

 

CREATE TABLE BenefitsTaken

(EmpID int not null,

 BenefitTypeID int not null,

 BT_Day int not null,

 BT_Month int not null,

 BT_Year int not null,

 HoursTaken float,

 TimesheetID int,

 CONSTRAINT PK_BenefitsTaken PRIMARY KEY (EmpID, BenefitTypeID, BT_Day, BT_Month, BT_Year),

 CONSTRAINT FK_BenefitsTakenEmployees FOREIGN KEY (EmpID) REFERENCES Employees,

 CONSTRAINT FK_BenefitsTakenBenefitTypes FOREIGN KEY (BenefitTypeID) REFERENCES BenefitTypes,

 CONSTRAINT FK_BenefitsTakenTimesheets FOREIGN KEY (TimesheetID) REFERENCES Timesheets)

 

Data Section

The following information is currently maintained in various spreadsheets throughout CMS. Data from these spreadsheets must be uploaded into your newly created tables before the database can be considered operational.

 

REGIONS

ID        Abbr.               Region Name

1          NAR               North America

2          CALA             Central and Latin America

3          APAC             Asia and Pacific

4          EMEA             Europe, Middle East, and Africa

 

COUNTRIES

ID        Country Name                        Weekly Hours             Holidays          Vacation Days            Region

1          United States              40                                11                    10                    NAR

2          Canada                                    40                                12                    15                    NAR

3          United Kingdom         38                                10                    10                    EMEA

4          France                         38                                14                    10                    EMEA

5          Ireland                         38                                10                    15                    EMEA

6          Italy                             35                                9                      20                    EMEA

7          Thailand                      40                                17                    20                    APAC

8          Singapore                    40                                17                    21                    APAC

9          Panama                        40                                12                    15                    CALA

 

BENEFIT TYPES

ID        Benefit Type Name

1          Vacation

2          Holiday

3          Jury Duty

4          Maternity Leave

5          Paternity Leave

6          Military Duty

CONTACT TYPES

ID        Contact Type Name

1          Systems Engineer

2          Sales

3          Billing

 

CONTRACT TYPES

ID        Contract Type Name

1          Maintenance

2          Fixed Price

3          License

4          Time and Materials

 

CLIENTS

IDLegal NameCommon         Address1Address2CityStateZip        Country

1BMA British Mobile 130 Wake Dr.              WakeNC24539           US

2FT                  France Mobile 123 East St.     Suite #2           Paris    45678       France

3IBC   IBC                 456 Main                                 Johor   78945    Singapore

4MTM MTM               6789 First St.              MeadGA45678           US

5     BT            Britain Tele     98769 Park St.            Level 3London           48695  UK

 

CONTRACTS

ID        ContractDesc  Contract Type Client

1          Work Order 1  Maint               FT

2          Work Order 1  T&M               BT

3          Work Order 1  Fixed Price      IBC

4          Work Order 2  Maint               IBC

5          Work Order 1  Fixed Price      MTM

6          Work Order 2  T&M               FT

CONTACTS

IDFirstLast     Addr1             Addr2CityState          Zip       CountryType

1BuggBunny   123 Looney                 NoWhere         AK45678        US       SysEng

2ElmerFudd    789 Park Pl.     Apt 3Skyville  NM45678        US       Billing

3DaffyDuck    45678 One St.             Norwood         45678  UK      Sales

4DarthVader   456 Two St.                Towns 47896  UK      Sales

5LukeSky        #4 Tatooine                 Paris                45678  FranceBilling

6PrincessLea   723 Coruscant             Rome               45678  ItalySysEng

7JohnDoe        987 Main St.               Paris                78945  FranceSysEng

8JaneDoe        7658 Oak Ln.              Crue    VA      45678  USSysEng

 

CONTRACTS’ CONTACTS

Contract                      Client              Contact Name

Work Order 1              BT                   Daffy Duck

Work Order 1              FT                    John Doe, Jane Doe, Princess Lea

Work Order 2              FT                    Elmer Fudd

Work Order 1              IBC                 Buggs Bunny

Work Order2               IBC                 Luke Sky

Work Order 2              IBC                 Darth Vader

Work Order 1              MTM               Daffy Duck

 

EMPLOYEETITLES

ID        Title

1          Consultant

2          Analyst

3          Director

BILLINGRATES

TitleID                        Level               Rate                 Currency

1                      1                      150.00             USD

1                      2                      200.00             USD

1                      3                      300.00             USD

2                      1                      50.00               USD

2                      2                      100.00             USD

2                      3                      150.00             USD

3                      1                      250.00             USD

3                      2                      350.00             USD

3                      3                      450.00             USD

EMPLOYEES

ID        First                 Last     CountryID      Email                           Salary              Title     LevelID          

1          Matthew          Smith        1                 [email protected]       45000              Consultant 1

2          Mark                Jones   1                      [email protected]       94000              Director 1       

3          Luke                Rice          4                 [email protected]            65000              Consultant 2

4          John                 Rich          5                 [email protected]           74000              Consultant3

5          James               Doe          6                 [email protected]           40000              Analyst1

6          Peter                Pride    3                     [email protected]        60000              Analyst2

7          Eric                  Potter       3                 [email protected]       81000              Consultant 3

8          Pau

Solution:

15% off for this assignment.

Our Prices Start at $11.99. As Our First Client, Use Coupon Code GET15 to claim 15% Discount This Month!!

Why US?

100% Confidentiality

Information about customers is confidential and never disclosed to third parties.

Timely Delivery

No missed deadlines – 97% of assignments are completed in time.

Original Writing

We complete all papers from scratch. You can get a plagiarism report.

Money Back

If you are convinced that our writer has not followed your requirements, feel free to ask for a refund.

WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?