\ufeffName<\/p>\n
Lecturer<\/p>\n
Course<\/p>\n
Date<\/p>\n
Exercise 8:<\/p>\n
For the data given we need to create two tables as follows:<\/p>\n
Employees Table<\/p>\n
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 ;<\/p>\n
Explanation<\/p>\n
Here we used the SQL DDL Command \u201cCREATE\u201d, which is referred to us create table construct in proper SQL language, in order to create the table \u2018employee_t\u2019 to represent employees with the five columns as shown above. We use the first column \u2018Employee_ID\u2019 as our primary key so that it can be joined with the second table below.<\/p>\n
Dependents Table<\/p>\n
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`)<\/p>\n
) ENGINE=MyISAM DEFAULT CHARSET=utf8;<\/p>\n
Explanation<\/p>\n
First I executed the above MYSQL DDL command of \u2018CREATE\u2019 to create the dependents table with three headings namely; \u201d Employee_ID, Dependents and Dependent Type. In order to link the \u2018dependents_t\u201d table with the employees table I used the MYSQL DDL COMMAND \u2018ALTER\u2019 as follows:<\/p>\n
ALTER TABLE `dependents_t`<\/p>\n
ADD CONSTRAINT FOREIGN KEY (`Employee_ID`)<\/p>\n
REFERENCES employee_t(`Employee_ID`)<\/p>\n
This creates a constraint under the Employee_ID column and makes it the foreign key in our table.<\/p>\n
Exercise 9:<\/p>\n
1st Part:<\/p>\n
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\u2019s domain contains atomic values only, and each attribute\u2019s 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.<\/p>\n
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.<\/p>\n
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 \u201cEmployee_Level\u201d, \u201cEmployee_Department\u201d and \u201cReports_to\u201d. To keep the data \u201cwell structured\u201d 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. <\/p>\n
Lastly, for a \u201cwell structured\u201d 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 \u201cwell structured\u201d 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.<\/p>\n
2nd Part:<\/p>\n
The SQL code for the Tables is as follows:<\/p>\n
Employee Table.<\/p>\n
— Database: `employee_db`<\/p>\n
— Table structure for table `employee_t`<\/p>\n
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 ;<\/p>\n
— Dumping data for table `employee_t`<\/p>\n
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’);<\/p>\n
Dependents Table<\/p>\n
— Table structure for table `dependents_t`<\/p>\n
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;<\/p>\n
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’);<\/p>\n
Exercise 9:<\/p>\n
The SQL Query to list managers\u2019 names and then who reports to each manager is as follows: <\/p>\n
SELECT Employee_name AS Employee, Reports_to AS Manager FROM employee_t<\/p>\n
WHERE Reports_to=’Jennifer’OR Reports_to=’Basil’ OR Reports_to=’Hana’ order by Reports_to<\/p>\n
The following table is an extract of how the report looks like:<\/p>\n
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<\/p>\n
George Basil<\/p>\n
Sally Basil<\/p>\n
Lucas Jennifer<\/p>\n
Claire Jennifer<\/p>\n
The SQL script to show the supervisors\u2019 names followed by who reports to each supervisor is shown below: <\/p>\n
SELECT Employee_name AS Employee, Reports_to AS Supervisor FROM employee_t<\/p>\n
WHERE Reports_to=’George’OR Reports_to=’Sally’ OR Reports_to=’Lucas’ OR Reports_to=’Claire’ order by Reports_to;<\/p>\n
The following data will be extracted.<\/p>\n
Employee_Name Reports_to<\/p>\n
Alice Claire<\/p>\n
Rhonda Claire<\/p>\n
Ryan Claire<\/p>\n
Harry Claire<\/p>\n
Henry Claire<\/p>\n
Aziz Claire<\/p>\n
Jason George<\/p>\n
Robert George<\/p>\n
Charles George<\/p>\n
Cyrus George<\/p>\n
Marco George<\/p>\n
Darryl George<\/p>\n
Sabrina George<\/p>\n
Abdul Lucas<\/p>\n
Harvey Lucas<\/p>\n
Andrew Lucas<\/p>\n
Isabella Lucas<\/p>\n
Anthony Lucas<\/p>\n
Alexandra Lucas<\/p>\n
John Sally<\/p>\n
Chris Sally<\/p>\n
David Sally<\/p>\n
Lana Sally<\/p>\n
Rebecca Sally<\/p>\n
Abi Sally<\/p>\n
Ian Sally<\/p>\n
Daniel Sally<\/p>\n
Exercise 11:<\/p>\n
SELECT O.Order_ID, Customer_name, O.Order_Date,<\/p>\n
O.Quantity, Sum([unit_Price]*[Quantity]) AS [Order Cost] , O.Mode_Payment<\/p>\n
FROM\u00a0Customer_t\u00a0 C, Order_line_t\u00a0OL, Order_t\u00a0 O, Product_t P<\/p>\n
WHERE\u00a0O.Order_ID = 26<\/p>\n
AND O.Order_ID =\u00a0OL.Order_I<\/p>\n
AND\u00a0P.Product_ID = OL.Product_ID <\/p>\n
GROUP BY O.Order_Date, C.Customer_name, O.Order_ID ;<\/p>\n
This will give us the following view for Order Number 26 with two lines of items.<\/p>\n
The result is as follows:<\/p>\n
Order_ID Customer_name Order_Date Quantity Order Cost Mode_Payment<\/p>\n
26 Battle Creek Furniture 10\/30\/1998 7 $1,875.00 check<\/p>\n
26 California Classics 10\/21\/1998 3 $2,000.00 cash<\/p>\n
Exercise 12:<\/p>\n
It will be better to use a view and the following are some of the benefits of using a database View:<\/p>\n
1. Views Hide Complexity<\/p>\n
For queries that require joining several tables, or that have complex calculations\/logic, it\u2019s possible to code that logic to a view, and select your options from the view like you would do for a table.<\/p>\n
2. Security Mechanism<\/p>\n
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.<\/p>\n
3. Views Do Simplify the Supporting Legacy Code<\/p>\n
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.<\/p>\n
The SQL View for Our Query is as Follows<\/p>\n
SELECT O.Order_ID, Customer_name, O.Order_Date,\u00a0 Sum([Order_Quantity]*[Product_Price]) AS [Order Cost], Sum([Ordered Product].Order_Quantity) AS Total_Order_Quantity, Product.Product_Line_Name<\/p>\n
Customer_t\u00a0 C, Order_line_t\u00a0OL, Order_t\u00a0 O, Product_t P<\/p>\n
WHERE\u00a0O.Order_ID = 26<\/p>\n
AND O.Order_ID =\u00a0OL.Order_I<\/p>\n
AND\u00a0P.Product_ID = OL.Product_ID GROUP BY O.Order_Date, C.Customer_name, O.Order_ID ;<\/p>\n","protected":false},"excerpt":{"rendered":"
\ufeffName Lecturer Course Date Exercise 8: For the data given we need to create two tables as follows: Employees Table<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-50328","post","type-post","status-publish","format-standard","hentry"],"yoast_head":"\n