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 ;
