Name

Lecturer

Course

Date

Exercise 8:

For the data given we need to create two tables as follows:

Employees Table

CREATE TABLE IF NOT EXISTS `employee_t` ( `Employee_ID` int(2) NOT NULL AUTO_INCREMENT, `Employee_Name` varchar(9) DEFAULT NULL, `Employee_Level` varchar(10) DEFAULT NULL, `Employee_Department` varchar(25) DEFAULT NULL, `Reports_to` varchar(8) DEFAULT NULL, PRIMARY KEY (`Employee_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=35 ;

Explanation

Here we used the SQL DDL Command “CREATE”, which is referred to us create table construct in proper SQL language, in order to create the table ‘employee_t’ to represent employees with the five columns as shown above. We use the first column ‘Employee_ID’ as our primary key so that it can be joined with the second table below.

Dependents Table

CREATE TABLE IF NOT EXISTS `dependents_t` ( `Employee_ID` int(2) NOT NULL, `Dependents` varchar(10) DEFAULT NULL, `Dependent_Type` varchar(8) DEFAULT NULL, KEY `Employee_ID` (`Employee_ID`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Explanation

First I executed the above MYSQL DDL command of ‘CREATE’ to create the dependents table with three headings namely; ” Employee_ID, Dependents and Dependent Type. In order to link the ‘dependents_t” table with the employees table I used the MYSQL DDL COMMAND ‘ALTER’ as follows:

ALTER TABLE `dependents_t`

ADD CONSTRAINT FOREIGN KEY (`Employee_ID`)

REFERENCES employee_t(`Employee_ID`)

This creates a constraint under the Employee_ID column and makes it the foreign key in our table.

Exercise 9:

1st Part:

The Course Project Data Table 1 is NOT 1st normal form. 1st normal form (1NF) is used as a property for a relation in a relational database like Access or MYSQL. The relation is considered to be in 1NF if each attribute’s domain contains atomic values only, and each attribute’s value contains only one value from the said domain. From our data table we find out that some employees have more than one dependent thus the need to add more rows for the said employees in our table.

This can be changed into 1st normal from by creating two tables as shown in exercise 8 above. The first table will capture the first 5 columns of our information and use Employee_ID as the primary key as this is used to uniquely identify each employee. The second table will have the last two column information and to join it with the first table we use the Employee_ID column as the FOREIGN KEY.

The date above in the 1st Normal Form is NOT WELL STRUCTURED. This is because in our table we have several repeated values under the following table headings “Employee_Level”, “Employee_Department” and “Reports_to”. To keep the data “well structured” we need further normalize the tables by creating a further three tables to represent each of the columns above and represent their unique attributes with numerical digits e.g. for Employee_Level we can have the following representation: Employee to be represented by 1 and Supervisor by2, thus replacing all table data with just numerical digits.

Lastly, for a “well structured” table a single table must show one subject at a particular time and have distinct fields that can accommodate the data at its absolute minim, to do this we use fields having unique values. Advantages of “well structured” tables:1. It has the capability to support both planned and unplanned retrieval of information. 2. It should be scalable and cater for future expansion of the database. 3. Saves the time in future redesign and re organization of the data.

2nd Part:

The SQL code for the Tables is as follows:

Employee Table.

— Database: `employee_db`

— Table structure for table `employee_t`

CREATE TABLE IF NOT EXISTS `employee_t` ( `Employee_ID` int(2) NOT NULL AUTO_INCREMENT, `Employee_Name` varchar(9) DEFAULT NULL, `Employee_Level` varchar(10) DEFAULT NULL, `Employee_Department` varchar(25) DEFAULT NULL, `Reports_to` varchar(8) DEFAULT NULL, PRIMARY KEY (`Employee_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=35 ;

— Dumping data for table `employee_t`

INSERT INTO `employee_t` (`Employee_ID`, `Employee_Name`, `Employee_Level`, `Employee_Department`, `Reports_to`) VALUES (1, ‘John’, ‘Employee’, ‘Sales’, ‘Sally’), (2, ‘Jason’, ‘Employee’, ‘Manufacturing’, ‘George’), (3, ‘George’, ‘Supervisor’, ‘Manufacturing’, ‘Basil’), (4, ‘Sally’, ‘Supervisor’, ‘Sales’, ‘Basil’), (5, ‘Jennifer’, ‘Manager’, ‘Management’, NULL), (6, ‘Basil’, ‘Manager’, ‘Management’, NULL), (7, ‘Chris’, ‘Employee’, ‘Sales’, ‘Sally’), (8, ‘David’, ‘Employee’, ‘Sales’, ‘Sally’), (9, ‘Hana’, ‘Manager’, ‘Management’, NULL), (10, ‘Lana’, ‘Employee’, ‘Sales’, ‘Sally’), (11, ‘Robert’, ‘Employee’, ‘Manufacturing’, ‘George’), (12, ‘Charles’, ‘Employee’, ‘Manufacturing’, ‘George’), (13, ‘Rebecca’, ‘Employee’, ‘Sales’, ‘Sally’), (14, ‘Abi’, ‘Employee’, ‘Sales’, ‘Sally’), (15, ‘Abdul’, ‘Employee’, ‘Finance and Accounting’, ‘Lucas’), (16, ‘Cyrus’, ‘Employee’, ‘Manufacturing’, ‘George’), (17, ‘Harvey’, ‘Employee’, ‘Finance and Accounting’, ‘Lucas’), (18, ‘Lucas’, ‘Supervisor’, ‘Finance and Accounting’, ‘Jennifer’), (19, ‘Marco’, ‘Employee’, ‘Manufacturing’, ‘George’), (20, ‘Andrew’, ‘Employee’, ‘Finance and Accounting’, ‘Lucas’), (21, ‘Isabella’, ‘Employee’, ‘Finance and Accounting’, ‘Lucas’), (22, ‘Ian’, ‘Employee’, ‘Sales’, ‘Sally’), (23, ‘Claire’, ‘Supervisor’, ‘Logistics and Warehousing’, ‘Jennifer’), (24, ‘Anthony’, ‘Employee’, ‘Finance and Accounting’, ‘Lucas’), (25, ‘Alice’, ‘Employee’, ‘Logistics and Warehousing’, ‘Claire’), (26, ‘Rhonda’, ‘Employee’, ‘Logistics and Warehousing’, ‘Claire’), (27, ‘Darryl’, ‘Employee’, ‘Manufacturing’, ‘George’), (28, ‘Daniel’, ‘Employee’, ‘Sales’, ‘Sally’), (29, ‘Ryan’, ‘Employee’, ‘Logistics and Warehousing’, ‘Claire’), (30, ‘Sabrina’, ‘Employee’, ‘Manufacturing’, ‘George’), (31, ‘Harry’, ‘Employee’, ‘Logistics and Warehousing’, ‘Claire’), (32, ‘Henry’, ‘Employee’, ‘Logistics and Warehousing’, ‘Claire’), (33, ‘Alexandra’, ‘Employee’, ‘Finance and Accounting’, ‘Lucas’), (34, ‘Aziz’, ‘Employee’, ‘Logistics and Warehousing’, ‘Claire’);

Dependents Table

— Table structure for table `dependents_t`

CREATE TABLE IF NOT EXISTS `dependents_t` ( `Employee_ID` int(2) NOT NULL,`Dependents` varchar(10) DEFAULT NULL,`Dependent_Type` varchar(8) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `dependents_t` (`Employee_ID`, `Dependents`, `Dependent_Type`) VALUES (1, ‘Mary’, ‘Spouse’), (1, ‘Jane’, ‘Daughter’), (2, NULL, NULL), (3, ‘Dorothy’, ‘Spouse’), (3, ‘Michael’, ‘Son’), (3, ‘Sarah’, ‘Daughter’), (4, ‘Hector’, ‘Spouse’), (4, ‘Isabel’, ‘Daughter’), (4, ‘Manuel’, ‘Son’), (5, ‘John’, ‘Spouse’), (6, NULL, NULL), (7, NULL, NULL), (8, ‘Christine’, ‘Spouse’), (8, ‘Joanne’, ‘Daughter’), (8, ‘Jill’, ‘Daughter’), (9, ‘Salah’, ‘Spouse’), (10, ‘Demetrius’, ‘Spouse’), (10, ‘Derrick’, ‘Son’), (10, ‘Danielle’, ‘Daughter’), (11, ‘Lynda’, ‘Spouse’), (11, ‘Jacqueline’, ‘Daughter’), (11, ‘Claudia’, ‘Daughter’),(11, ‘Alice’, ‘Daughter’), (11, ‘James’, ‘Son’), (12, ‘Alison’, ‘Spouse’), (12, ‘George’, ‘Son’), (13, ‘Mark’, ‘Spouse’), (14, ‘Malcolm’, ‘Spouse’), (15, ‘Falak’, ‘Spouse’), (15, ‘Hana’, ‘Daughter’), (16, ‘Rosemary’, ‘Spouse’), (17, NULL, NULL), (18, ‘Mabel’, ‘Spouse’), (18, ‘George’, ‘Son’), (18, ‘Michael’, ‘Son’), (19, ‘Alicia’, ‘Spouse’), (19, ‘David’, ‘Son’), (19, ‘Andrew’, ‘Son’), (19, ‘Russell’, ‘Son’), (20, ‘Anne’, ‘Spouse’), (21, ‘Charles’, ‘Spouse’), (21, ‘Lydia’, ‘Daughter’), (22, ‘Blaine’, ‘Spoues’), (22, ‘Sean’, ‘Son’), (22, ‘Conor’, ‘Son’), (23, ‘Russell’, ‘Spouse’), (24, ‘Jane’, ‘Spouse’), (24, ‘Maria’, ‘Daughter’), (24, ‘Teresa’, ‘Daughter’), (24, ‘Mario’, ‘Son’), (24, ‘Michele’, ‘Daughter’), (25, ‘Carlo’, ‘Spouse’), (25, ‘Angelo’, ‘Son’), (25, ‘Sergio’, ‘Son’), (26, ‘Hiroto’, ‘Spouse’), (26, ‘Miu’, ‘Daughter’), (27, ‘Sofia’, ‘Spouse’), (27, ‘Paula’, ‘Daughter’), (27, ‘Nicole’, ‘Daughter’), (27, ‘Maria’, ‘Daughter’), (27, ‘Emilio’, ‘Son’), (27, ‘Miranda’, ‘Daughter’), (28, NULL, NULL), (29, NULL, NULL), (30, ‘Alan’, ‘Spouse’), (30, ‘Matthew’, ‘Son’), (31, ‘Laura’, ‘Spouse’), (31, ‘Alex’, ‘Son’), (32, ‘Olivia’, ‘Spouse’), (33, ‘Howard’, ‘Spouse’), (34, ‘Karam’, ‘Spouse’), (34, ‘Maram’, ‘Daughter’), (34, ‘Basil’, ‘Son’);

Exercise 9:

The SQL Query to list managers’ names and then who reports to each manager is as follows:

SELECT Employee_name AS Employee, Reports_to AS Manager FROM employee_t

WHERE Reports_to=’Jennifer’OR Reports_to=’Basil’ OR Reports_to=’Hana’ order by Reports_to

The following table is an extract of how the report looks like:

HYPERLINK “http://127.0.0.1/phpmyadmin/sql.php?db=employee_db&table=employee_t&sql_query=SELECT+Employee_name+AS+Employee%2C+Reports_to+AS+Manager+FROM+employee_t%0AWHERE+Reports_to%3D%27Jennifer%27OR+Reports_to%3D%27Basil%27+OR+Reports_to%3D%27Hana%27+ORDER+BY+%60employee_t%60.%60Employee%60+ASC&token=40d847311dfb6227a36350aa27ca2a3d” o “Sort”Employee HYPERLINK “http://127.0.0.1/phpmyadmin/sql.php?db=employee_db&table=employee_t&sql_query=SELECT+Employee_name+AS+Employee%2C+Reports_to+AS+Manager+FROM+employee_t%0AWHERE+Reports_to%3D%27Jennifer%27OR+Reports_to%3D%27Basil%27+OR+Reports_to%3D%27Hana%27+ORDER+BY+%60employee_t%60.%60Manager%60+ASC&token=40d847311dfb6227a36350aa27ca2a3d” o “Sort”Manager

George Basil

Sally Basil

Lucas Jennifer

Claire Jennifer

The SQL script to show the supervisors’ names followed by who reports to each supervisor is shown below:

SELECT Employee_name AS Employee, Reports_to AS Supervisor FROM employee_t

WHERE Reports_to=’George’OR Reports_to=’Sally’ OR Reports_to=’Lucas’ OR Reports_to=’Claire’ order by Reports_to;

The following data will be extracted.

Employee_Name Reports_to

Alice Claire

Rhonda Claire

Ryan Claire

Harry Claire

Henry Claire

Aziz Claire

Jason George

Robert George

Charles George

Cyrus George

Marco George

Darryl George

Sabrina George

Abdul Lucas

Harvey Lucas

Andrew Lucas

Isabella Lucas

Anthony Lucas

Alexandra Lucas

John Sally

Chris Sally

David Sally

Lana Sally

Rebecca Sally

Abi Sally

Ian Sally

Daniel Sally

Exercise 11:

SELECT O.Order_ID, Customer_name, O.Order_Date,

O.Quantity, Sum([unit_Price]*[Quantity]) AS [Order Cost] , O.Mode_Payment

FROM Customer_t  C, Order_line_t OL, Order_t  O, Product_t P

WHERE O.Order_ID = 26

AND O.Order_ID = OL.Order_I

AND P.Product_ID = OL.Product_ID

GROUP BY O.Order_Date, C.Customer_name, O.Order_ID ;

This will give us the following view for Order Number 26 with two lines of items.

The result is as follows:

Order_ID Customer_name Order_Date Quantity Order Cost Mode_Payment

26 Battle Creek Furniture 10/30/1998 7 $1,875.00 check

26 California Classics 10/21/1998 3 $2,000.00 cash

Exercise 12:

It will be better to use a view and the following are some of the benefits of using a database View:

1. Views Hide Complexity

For queries that require joining several tables, or that have complex calculations/logic, it’s possible to code that logic to a view, and select your options from the view like you would do for a table.

2. Security Mechanism

A view can be used to select certain rows and/or columns from a table, and permissions can then be set on the developed view instead of the tables themselves. This allows a user to surface only the data that he needs to see.

3. Views Do Simplify the Supporting Legacy Code

You can replace a table with a view with the same name, when you need to refactor a table needs a lot of code. The view will provide exactly the same schema like the original table, but instead the actual schema would already have changed. This prevents the legacy code referencing the table from breaking, allowing for changing the legacy code the users will.

The SQL View for Our Query is as Follows

SELECT O.Order_ID, Customer_name, O.Order_Date,  Sum([Order_Quantity]*[Product_Price]) AS [Order Cost], Sum([Ordered Product].Order_Quantity) AS Total_Order_Quantity, Product.Product_Line_Name

Customer_t  C, Order_line_t OL, Order_t  O, Product_t P

WHERE O.Order_ID = 26

AND O.Order_ID = OL.Order_I

AND P.Product_ID = OL.Product_ID GROUP BY O.Order_Date, C.Customer_name, O.Order_ID ;