SQL Concepts and Database Design

SQL Concepts and Database Design

Task 1

Employee Table

The table employee will consists of all the essential information regarding the employees working inside the tour company. This table will consists of the fields EmpNumber, EmpFirstName, EmpLastName, CommissionRate, YrlySalary,DepartmentID, JobID. Among the fields EmpNumber will be the primary key for the table as it would be unique for every employee working inside the tour company. Whereas DepartmentID and JobID are the primary keys in this table as it links the table with the Department and Job table for the retrieval of information from those two tables regarding the employees. Following is the data type, format the data will be stored, and the range of values for all the fields.

CREATE TABLE Employee (

EmpNumber INT PRIMARY KEY Not Null,

EmpFirstName VARCHAR (10) Not Null,

EmpLastName VARCHAR (9) Not Null,

CommissionRate INT Null,

YrlySalary INT Not Null,

DepartmentID INT Not Null,

JobID INT Not Null);

Invoice Table

The table invoice will consists of all the information essential for the generation of Invoice. This table will consists of the fields InvNumber, InvDate, EmpNumber, InvAmount, CustomerID. Among the fields InvNumber will be the primary key for the table as it would be unique for every employee working inside the tour company, and EmpNumber will be the foreign key in this table as it will be linking the Invoice table with the Employee table to retrieve the EmpNumber from the Employee table. Following is the data type, format the data will be stored, and the range of values for all the fields.

CREATE TABLE Invoice (

InvNumber INT Not Null,

InvDate DATETIME,

EmpNumber INT PRIMARY KEY Not Null,

InvAmount INT Not Null,

CustomerID INT PRIMARY KEY Not Null);

InvoiceLine Table

The table InvoiceLine will consists of all the essential information on the line number of the invoice. This table will consist of the fields InvLineNumber, InvNumber, ProductNumber, and Quantity. Among the fields InvLineNumber will be the primary key for the table as it would be unique for every employee working inside the tour company, and ProductNumber will be the foreign key in this table as it will be linking the InvoiceLine table with the Product table to retrieve the Product Number from the Product table. Following is the data type, format the data will be stored, and the range of values for all the fields.

CREATE TABLE InvoiceLine (

InvLineNumber INT Not Null,

InvNumber INT Not Null,

ProductNumber INT Not Null,

Quantity INT Not Null);

Product Table

The table Product will consist of all the essential information regarding the product. This table will consist of the fields ProductNumber, ProductDescription, ProductCost. Among the fields ProductNumber will be the primary key for the table as it would be unique for every product. Following is the data type, format the data will be stored, and the range of values for all the fields.

CREATE TABLE Product (

ProductNumber INT Not Null,

ProductDescription VARCHAR (10) Not Null

ProductCost INT Not Null);

Department Table

The table Department will consist of all the essential information regarding the employees working inside the different departments. This table will consist of the ...