This assignment on SQL table relationships was completed by an expert who is knowledgeable in the IT sphere. You can read this sample, but you can’t present it as your own homework. On our blog, in addition to this sample, a variety of different examples of tasks and their solutions are presented. In any case, the use of our service will be helpful for you, as we also offer expert help. It is worth paying attention to AssignmentShark.com because we offer the best help with technical assignments on the market.
While your order is being completed, you have the opportunity to contact the expert directly via chat and ask any questions. Our service occupies a special place in students’ lives. First of all, it concerns those students who don’t have time to deal with their homework. Using our service is absolutely legal, and your personal information will be secure. Read our IT sample to see how our experts solve tasks.
Creating Tables and Establishing Relationships Using SQL
The Department of labor and wages, on the basis of specifications of products, schedules a production plan of demand for workers of different specialties and qualifications.
Input information:
The Production Department gives the specifications of the product and the need, in hours, for workers of different professions and places in the context of workshops and production units for each product.
The planning section generates the schedule of release, which contains the planned number of products of each type for each date.
Listed is the creation of tables and relationships between them and basic queries of SQL. Note: creating a database is done in SQL Server 2014.
CREATE DATABASE WorkforcePlanning
CREATE TABLE Department (ID int NOT NULL,
Name nvarchar(40) NOT NULL ,
PRIMARY KEY (ID))
CREATE TABLE Speciality (ID int NOT NULL,
DepartmentID int NOT NULL,
Name nvarchar(40) NOT NULL,
Qualification int NOT NULL,
Gap time NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (DepartmentID) REFERENCES Department(ID))
CREATE TABLE Demand (ID int NOT NULL,
SpecialityID int NOT NULL,
Amount int NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (SpecialityID) REFERENCES Speciality(ID))
CREATE TABLE Products (ID int NOT NULL,
Name nvarchar(40) NOT NULL,
Price decimal(12, 2) NOT NULL,
PRIMARY KEY (ID))
CREATE TABLE CalendarPlan (ID int NOT NULL,
ProductID int NOT NULL,
DepartmentID int NOT NULL,
Quantity int NOT NULL,
Shelf date NOT NULL,
PRIMARY KEY (ID),
FOREIGN KEY (ProductID) REFERENCES Products(ID),
FOREIGN KEY (DepartmentID) REFERENCES Department(ID))
INSERT INTO Department (ID , Name)
VALUES (1 ,’Bridges Dpt‘),
(2 ,’Wheels Dpt‘),
(3 ,’Hinges Dpt‘),
(4 ,’Generators Dpt‘),
(5 ,’Engines Dpt‘);
INSERT INTO Speciality (ID , DepartmentID , Name, Qualification , Gap)
VALUES (1 ,1, ‘Tinker‘, 2, CONVERT(time, ‘10:00:00‘)),
(2 , 2, ‘ Mechanic‘, 1, CONVERT(time, ‘20:00:00‘)),
(3 , 3, ‘ Locksmith‘, 5, CONVERT(time, ‘11:00:00‘)),
(4 , 4, ‘Electrician ‘, 3, CONVERT(time, ‘16:00:00‘)),
(5 , 5, ‘Tire installer‘, 4, CONVERT(time, ‘17:00:00‘));
INSERT INTO Demand (ID , SpecialityID , Amount)
VALUES (1 , 1, 4),
(2 , 2, 2),
(3 , 3, 1),
(4 , 4, 3),
(5 , 5, 6);
INSERT INTO Products (ID , Name , Price)
VALUES (1 , ‘Wheels’, 2500.00),
(2 , ‘Bridge‘, 2000.00),
(3 , ‘Hinge‘, 1500.00),
(4 , ‘Generator‘, 1250.00),
(5 , ‘Engine‘, 1000.00);
INSERT INTO CalendarPlan (ID , ProductID , DepartmentID, Quantity, Shelf)
VALUES (1 , 3, 2, 20, CONVERT(date, ‘30/09/15′, 3)),
(2 , 2, 1, 10, CONVERT(date, ‘25/09/15‘, 3)),
(3 , 4, 3, 40, CONVERT(date, ‘20/09/15‘, 3)),
(4 , 5, 5, 70, CONVERT(date, ‘11/09/15‘, 3)),
(5 , 1, 4, 50, CONVERT(date, ‘10/09/15‘, 3));
