Project Description
Database is the compilation of data that is organized in order for its contents to be accessed by different people, managed easily and updated. In comprehensible terms, it can be described as the listing of organized information with elements that are searchable. The most familiar type of database in the library comprises of records that are usually describing journals, articles and newspapers (Robert, 2004). The retrieval process of information from a database can be achieved through the process of matching it. Matching people’s queries against all the information contained in the database is the fundamental nature of retrieving any information that may be computerized.
The Database used herein is a database of a company that provides goods to a general population. The company has its stock shared out and therefore has members of the company who are the stockholders. The company is divided into various departments that have various departmental heads. It is also providing a number of different stock items. Each member is allowed shares for a specific stock item, but any one member can have shares in more than one item, with sometimes one stock type having more than one client having shares. The departments are divided into the number of stock that is being provided to the clients. This creates a scenario whereby the relationship between the company and the stock is a one to one relationship. There is also the employee entity in which he has his information displayed in the form of a name and an ID. This has only a one to one relation to both the departmental head as well as the department itself. This means that the employee can only work for one department and one departmental head, the vice versa is also true.
The database in this project shows clearly the methods with which the knowledge on databases is applied. It clearly shows how the database application process provides both a learning experience for the one applying it, and the information is hence provided in a good formation; Showing the benefits of using this kind of a database system. The following tools will be used:
DDL (Data Definition language) – these are statements in SQL language that describes database structures and schemas. The commands involved are: create, alter, drop, truncate, comment and rename. CREATE command creates objects in the database; ALTER changes the structure of the table. DROP removes objects or data from a table; TRUNCATE removes records from a table together with spaces allocated for a table. COMMENT adds comments to the dictionary containing data and RENAME renames objects.
DML (Data Manipulation Language) – these are commands in SQL that manipulate or manage data in the database schemas. These commands include select, insert, update, delete, merge, call, explain plan, lock table. SELECT gets data from the database tables; INSERT stores the data in the database tables. UPDATE updates data which already exists in a table. DELETE removes all records from a table, but the spaces for the records remain. MERGE inserts or updates data. CALL calls a java or SQL subprogram. EXPLAIN PLAN explains access path to data and LOCK TABLE controls concurrency when accessing database from multiple locations or simultaneously.
DCL (Data Control Language) deals with controlling rights and privileged to the database. Commands include GRANT which gives a user access privileges to a database. REVOKE withdraws access privileges from a user that was granted access using the GRANT command.
Entity Relationship Diagram
Data Definition Language for Tables, Indexes, and Constraints
CREATE TABLE `employee` ( `EMPLOYEE_ID` varchar(5) NOT NULL, `FIRST_NAME` varchar(10) NOT NULL, `LAST_NAME` varchar(10) NOT NULL, `EMAIL` varchar(20) NOT NULL, `SALARY` int(11) NOT NULL, `PHONE_NUMBER` varchar(15) NOT NULL,
`HIRE_DATE` varchar(10) NOT NULL, `MANAGER_ID` varchar(5) NOT NULL,
`DEPARTMENT_ID` varchar(5) NOT NULL, PRIMARY KEY (`EMPLOYEE_ID`);
CREATE TABLE `location` ( `LOCATION_ID` varchar(5) NOT NULL,
`CITY` varchar(12) NOT NULL, `STATE_PROVINCE` varchar(12) NOT NULL,
`COUNTRY` varchar(12) NOT NULL, PRIMARY KEY (`LOCATION_ID`)
) ;
('L002', 'HOUSTON', 'TEXAS', 'U. S.');
CREATE TABLE `department` ( `DEPARTMENT_ID` varchar(5) NOT NULL, `MANAGER_ID` varchar(25) NOT NULL, `DEPARTMENT_NAME` varchar(25) NOT NULL, `LOCATION_ID` varchar(5) NOT NULL, PRIMARY KEY (`DEPARTMENT_ID`),
KEY `LOCATION_ID` (`LOCATION_ID`), KEY `MANAGER_ID` (`MANAGER_ID`)
) ;
('D002', 'E002', 'INFORMATION TEC', 'L002'),('D003', 'E003', 'HUMAN RESOURCE', 'L001');
The code below sets constraints for the 3 tables above i.e. sets relationships between the tables in terms of primary key and foreign key.
-- Constraints for table `department`
ALTER TABLE `department`
ADD CONSTRAINT `department_ibfk_4` FOREIGN KEY (`DEPARTMENT_ID`) REFERENCES `department` (`DEPARTMENT_ID`),
ADD CONSTRAINT `department_ibfk_1` FOREIGN KEY (`MANAGER_ID`) REFERENCES `employee` (`EMPLOYEE_ID`),
ADD CONSTRAINT `department_ibfk_2` FOREIGN KEY (`LOCATION_ID`) REFERENCES `location` (`LOCATION_ID`),
ADD CONSTRAINT `department_ibfk_3` FOREIGN KEY (`MANAGER_ID`) REFERENCES `employee` (`EMPLOYEE_ID`);
Data Manipulation Language for Querying the Database
Code to create a view for first name last name and department name (create a view called employees and department)
CREATE VIEW Employees_and_Departments AS SELECT employee.FIRST_NAME, employee.LAST_NAME, department.DEPARTMENT_NAME FROM employee INNER JOIN department ON employee.DEPARTMENT_ID = department.DEPARTMENT_ID ORDER BY department.DEPARTMENT_ID
)
Code to create a view of employees earning above average salary (assuming average salary is 2000). Create a view of employees earning above average salary.
CREATE VIEW Above_avg_Salary_Employees AS SELECT employee.FIRST_NAME, employee.LAST_NAME, employee.SALARY, department.DEPARTMENT_NAME FROM employee INNER JOIN department ON employee.DEPARTMENT_ID = department.DEPARTMENT_ID WHERE employee.SALARY >2000
code to select employees using first name , last name and their department
you can select from the view created
SELECT * FROM `employees_and_departments`
OR
SELECT employee.FIRST_NAME, employee.LAST_NAME department.DEPARTMENT_NAME FROM employee INNER JOIN department
ON employee.DEPARTMENT_ID = department.DEPARTMENT_ID ORDER BY department.DEPARTMENT_ID;
) SQL code to select first name, last name, salary and department name for employees earn above average salary (assuming 2000 is the average salary)
You can select from the view Above_average_salary_Employees using the code
SELECT * FROM `above_avg_salary_employees` GROUP BY DEPARTMENT_NAME
OR
SELECT employee.FIRST_NAME, employee.LAST_NAME, employee.SALARY, department.DEPARTMENT_NAME FROM employee INNER JOIN department ON employee.DEPARTMENT_ID = department.DEPARTMENT_ID WHERE employee.SALARY >2000 GROUP BY department.DEPARTMENT_ID;
Conclusion
Databases promote the levels of literacy for most people; administrators, students, Entrepreneurs, Teachers, scientists, doctors among others (Robert, 2004). It provides an opportunity for all to address applications and competencies of technology.
Assists in promoting Health in Firms and Institutions: With today’s increasing concerns with issues related to health, eating disorder, substance abuse, pregnancies and disease outbreaks, there is need for information that is updated to support everyone in understanding more on of how to prevent these health problems. Through databases, the programs provide more on issues related to health.
Databases assist in increasing achievement in students and promotion of skill:
The key sources of information today are the electronic databases. Today, most of the information gets published in electronic formats. Information can be covered in practically all areas of knowledge such as sociology, science, medicine, Engineering, philosophy, law, economics among so many. The voluminous databases are usually stored in most of the information agencies or companies referred to as retrieval services (Robert, 2004). One people are connected to these retrieval services it becomes easier to access any of the worlds literally.
Today, most students are exposed to increased information than the way people were decades ago. Access of information from online sources has lead to most traditional books becoming obsolete (Newark, 2003). To prepare ourselves for the future, there is a need for deeper thinking strategies and skills, perhaps the 21st century skills. Achievement of students can hence be impacted by their learning experiences that are rich in technology.
It is a reliable source to refer to at any time:
As a source, of information, a database can be shared by different users and available at any time so long as the retrieval service operates. There is no given limit to the number of times that the database can be displayed or searched (Robert, 2004). Unlike the books and any other document in the libraries, the databases do not get misplaced or deteriorate.
Allows the Educators and Entrepreneurs to grow professionally and be updated:
The components contained in the database are inclusive of numerous articles from thousands of professional journals (Newark, 2003). These components can be regularly updated, and most provide access for just in time published information that assists most educators, entrepreneurs to flow with up-to-date information about their businesses and careers. The advantage of depending on databases is that they are flexible and ever up-to-date with the events in the world. As a form of technology, it can be said to be an opportunity, since it creates employment for thousands of people in the world (Newark, 2003). Conclusively, the purpose of technology in people’s lives is to answer unexplainable questions asked on a daily basis.
References:
McGeachin, Robert B (2004), "The Impact of Electronic Bibliographic Databases and Electronic Journal Articles on the Scholar's Information-Seeking Behavior and Personal Collection of “Reprints”." Science & Technology Libraries 25.1-2
Newark, DE (2003), Database: Art Conservation Dept., University of Delaware
Paul D, (2009) MySQL. Upper Saddle River, NJ: Addison-Wesley.
Source:| Oracle FAQ." Welcome to The Oracle FAQ | Oracle FAQ. Web. 26 May 2011.
Witold L. (2002) MSQL: a Multidatabase Language. Le Chesnay: Inst. National De Recherche En Informatique Et En Automatique,
