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
SQL question
.awasam-promo3 {
background-color: #F5F9FF;
color: #000000;
text-align: center;
padding: 20px;
border-radius: 10px;
}
.button {
background-color: #4CAF50;
border: none;
color: white;
padding: 10px 20px;
text-align: center;
text-decoration: none;
display: inline-block;
font-size: 16px;
margin: 4px 2px;
cursor: pointer;
border-radius: 5px;
}
.button-whatsapp {
background-color: #41D07D;
border: none;
color: white;
padding: 10px 20px;
text-align: center;
text-decoration: none;
display: inline-block;
font-size: 16px;
margin: 4px 2px;
cursor: pointer;
border-radius: 5px;
}
.awasam-alert {
color: red;
}
Needs help with similar assignment?
We are available 24×7 to deliver the best services and assignment ready within 6-8 hours? Order a custom-written, plagiarism-free paper
Get Answer Over WhatsApp
Order Paper Now
