Schema

Schema in dbms

1. Data Independence Presented by- Pragya Srivastava B.Tech(3 rd year) 1336710013

2. schema The word schema comes from the Greek word skhēma, which means shape and overall structure, or more generally, plan . The plural is skhēmata. In English, both schemas and schemata are used as plural forms. Overall structure or design of database is called schema.

3. name roll no class marks course Course no Deptt. For example : student schema

4. Sub Schema A subschema is a subset or proper subset of the schema and inherits the same property that a schema has. The plan (or scheme) for a view is often called subschema. It gives the users a window through which he or she can view only that part of the database, which is of interest to him/her.

5. q. What is the difference b/w schema & subschema? The schema is the physical arrangement of the data as it appears in the DBMS. The subschema is the logical view of data.

6. 3-schema architecture The three-schema model consisting conceptual level, an external level, and an internal or physical level was first introduced by Charles Bachman in 1975. The motive of studying data base architecture is to provide an abstract view. Abstract view means that the system hides 2 nd detail on how the data is stored & maintained in data base.

7. The abstraction can be achieved by the help of 3-levels -> Physical level Logical level View level

8. Physical level It deals with how data is stored in storage medium. Physical level has physical/internal schema.

9. Logical level it describe what data is store in database and what relationship exist among those data? Logical level has logical/conceptual schema. Logical level hides the details of physical storage.

10. View level This level is closest to the user. It has external schema. External schema describe the part of the database that perticular user group is interacting in and hides rest of the database from user group.

11. Data Independence The capacity to change schema at one level without having change in next higher level. We have to changes because the size of database increases day by day.

12. Logical Data Independence: I t is the capacity to change the Conceptual schema without having change in external schema. Conceptual level has conceptual schema or Logical schema.

13. Example of logical data independence- The name field in conceptual view is stored as first name , middle name , last name whereas in external view ,it remains to be as a single name field. FIRST NAME MIDDLE NAME LAST NAME NAME AMIT SINGH CHAUHAN View level Logical level

14. Physical Data Independence: I t is the capacity to change the internal schema without having change in logical schema. The modification in physical level is necessary to improve the performance of system.

15. Example of Physical data independence- consider two users ‘A’ & ‘B’. Both are selecting the fields "Employee Number" and "Employee Name". If user ‘B’ adds a new column (e.g. salary) to his table, it will not effect the external view for user ‘A’, through the internal schema of the database has been changed for both users ‘A’ & ‘B’.

16. Emp_no. Emp_name Emp_no. Emp_name B A EMPLOYEE Emp_no. Emp_name Emp_no. Emp_name Emp_salary A B