The collection of tables: Employee, Project, Department etc. The advantage is that the query now can retrieve from single resultant table rather than having to retrieve from three joined tables. The view forms the virtual table created from the result of the query. Therefore, instead of specifying the join of three tables: Employee, EmpWorksOnProj, and Project every time we need a collaborative information and issue a query, we define a view that is specified as the outcome of the join among these tables. Note that there is many to many relationship between Employee and Project as one person can work on many project and also one project has many employees. For example, we may frequently need to refer to the my_company database and find Employee and Project information. The views can be thought of as a reference table, and we can use it as frequently as we want although it may not exist physically. ProjId INT(11) UNSIGNED CHECK (empId > 0),ĪLTER TABLE Employee ADD CONSTRAINT fk_emp_mgr FOREIGN KEY(managerId) REFERENCES Employee(empId) ĪLTER TABLE Employee ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptId) REFERENCES Department(deptId) ĪLTER TABLE Department ADD CONSTRAINT fk_dept_mgr FOREIGN KEY(deptMgrId) REFERENCES Employee(empId) ĪLTER TABLE Project ADD CONSTRAINT fk_proj_dept FOREIGN KEY(deptId) REFERENCES Department(deptId) My_company database: CREATE DATABASE my_company ĮmpId INT(11) UNSIGNED CHECK (empId > 0),ĭeptId INT(11) UNSIGNED CHECK (empId > 0), The examples below are tested with the MySQL database. For example, the update operation cannot be applied to all types views, but it has no limitation on applying SQL query on it. As a result, there is a limitation on the type of operation that can be applied to a view table. The tuples are like temporary data created as an outcome of the SQL query which typically draws filtered data from one or more base tables. Virtual tables mean the tuples in views do not have physical existence and are not stored into the database. SQL views are nothing but virtual tables reside in memory derived from one or more base tables. This article introduces the concept of views in SQL, how it works and shows how it is implemented with some examples. The definition of a view or a virtual table exists as a schema object. This is the reason the SQL:2006 standard introduced the use of view tables, or views. Leveraging the idea, we can create virtual tables from persistent base tables using SQL that would contain the exact data we need. The SQL DML operations are extensible and used to filter through one or more tables using complex query expressions. Due to security reason, we might want to make public only a certain amount of data while rest might be accessible to the privileged users. The volume of data must be filtered based upon some specified criteria for efficient use. Tables in a SQL database may contain enormous amount of data, but they are not always in a useful format to be readily used.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |