1 / 21

Data Dictionaries

Data Dictionaries. CSCI 4227/5227 Advanced Database. Overview. What Is a Data Dictionary? How Does the DBMS Use a Data Dictionary? How Do You Use a Data Dictionary? What Is In a Data Dictionary? Oracle’s Data Dictionary?. What Is a Data Dictionary?.

Patman
Download Presentation

Data Dictionaries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Dictionaries CSCI 4227/5227 Advanced Database

  2. Overview • What Is a Data Dictionary? • How Does the DBMS Use a Data Dictionary? • How Do You Use a Data Dictionary? • What Is In a Data Dictionary? • Oracle’s Data Dictionary?

  3. What Is a Data Dictionary? • Contains information about the structures in the database • Also called • System Catalog • Strictly speaking this is what Oracle has • Meta Data • Generic term, for data about data • Data Repository • Synonym for Data Dictionary used to imply stand-alone systems • According to Codd an RDB MUST use tables for its Data Dictionary

  4. How Does the DBMS Use a Data Dictionary? • Security • Integrity • View Definition • Parsing SQL • Optimizing SQL

  5. How Do You Use a Data Dictionary? • By the DBA • Who built what when • Who gave you access to this item and when • By the programmer • Checking information when building programs • Building self adapting code • Documenting the database layout

  6. What Is In a Data Dictionary? • Each “object” in the database • Tables, Views,Types, Procedures, Functions, Columns … • Who created it • Its Definition • What it uses • What uses it

  7. Oracle’s Data Dictionary Tables • For details see • Palinski Chapter 3 • Elmasri Navathe Chapter 17 • Three types of views of the meta data • USER (created by this user) • ALL (accessible to this user) • USER is those tuples in ALL where owner = current user • DBA (accessible only to persons with DBA privileges)

  8. Average User All User DBA (Cannot be viewed)

  9. DBA User All and DBA are the same and visible User

  10. An example: User_Tables (7 items): BRANCH STAFF PROPERTYFORRENT CLIENT PRIVATEOWNER VIEWING REGISTRATION

  11. All_tables (109 items) (non-DBA user)* * for brevity sake, this list does not include user created tables

  12. DBA_Tables • Too many to list (2,429 additional items) • Cannot be seen by a non-DBA user. For a DBA user DBA_xxx and All_xxx are equivalent.

  13. Meta-Meta Data • The tables DICT and DICT_COLUMNS describe the data dictionary tables • In technical terms they are meta-meta data • In practical terms they are an on-line guide to the data dictionary

  14. The Big Picture • CATALOG (CAT) • Describes Tables, Views Select * From user_catalog; TABLE_NAME TABLE_TYPE ----------------------- ----------- P TABLE S TABLE SP TABLE SP2 TABLE SP_V VIEW

  15. The Other Big Picture • User_objects • Describes all DB Objects select object_name, object_type,object_id, created, last_ddl_time from user_objects where object_name like 'S%' OBJECT_NAME OBJECT_TYPE OBJECT_ID CREATED LAST_DDL_ --------------- ------------------- ---------- --------- --------- SYS_C0013194 INDEX 75702 02-SEP-08 02-SEP-08 SYS_C0013193 INDEX 75700 02-SEP-08 02-SEP-08 SYS_C0013192 INDEX 75698 02-SEP-08 02-SEP-08 SYS_C0013191 INDEX 75696 02-SEP-08 02-SEP-08 SYS_C0013190 INDEX 75694 02-SEP-08 02-SEP-08 SYS_C0013189 INDEX 75692 02-SEP-08 02-SEP-08 SYS_C0013188 INDEX 75690 02-SEP-08 02-SEP-08 SYS_C0011684 INDEX 72962 14-AUG-08 14-AUG-08 STAFFGLASGOW VIEW 76394 03-SEP-08 03-SEP-08 STAFF TABLE 75691 02-SEP-08 02-SEP-08

  16. Sequences Comments Table Column Constraints Exceptions to Constraints LOBS Indexes Clusters Types Triggers Procedures and Functions Packages Tablespaces and Quotas AND MANY MANY MORE

  17. Adding a comment to the data dictionary • Syntax: • COMMENT ON TABLE [schema.]obj1 IS 'text‘ • COMMENT ON COLUMN [schema.]obj1.column IS 'text' • To drop a comment from the database, set it to the empty string ' ‘ • Related Views: • DICTIONARY • DICT_COLUMNS • USER_COL_COMMENTS • USER_TAB_COMMENTS 1. obj may be a table or a view

  18. Views versus Synonyms • A view is a virtual table made up of one or many tables and columns derived from columns within those tables. • A synonym is an alternate name for an existing object. • Oracle will resolve object names in the following order: current user private synonym public synonym

  19. Synonym create synonym big_sp for sanderso.sp_v create public synonym pub_sp for scott.sp_v (note public synonym must be created by DBA) select synonym_name, table_owner, table_name from user_synonyms; SYNONYM_NAME TABLE_OWNE TABLE_NAME --------------- ---------- ---------- BIG_SP SCOTT SP_V select synonym_name, table_name, owner from all_synonyms where table_owner = 'SCOTT' SYNONYM_NAME TABLE_NAME OWNER --------------- ---------- ----------------------- BIG_SP SP_V SCOTT PUB_SP SP_V PUBLIC

  20. Synonym Caution Oracle will resolve object names in the following order: • current user: if you override an existing private synonym • private synonym : If you omit the public clause, then the synonym is private and is accessible only within its schema. • public synonym : Public synonyms are accessible to all users. However each user must have appropriate privileges on the underlying object in order to use the synonym. Thus you can accidentally overlay another object. SQL Format: CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object [@dblink NOTE:You must have CREATE SYNONYM system privilege to create a PRIVATE synonym. You must have CREATE PUBLIC SYNONYM privilege or be a DBA or to create a PUBLIC synonym.

  21. Example of Synonym Precedence SQL> select * from dual; D - X SQL> create synonym dual for staffglasgow; Synonym created. SQL> select * from dual; STAFF FNAME LNAME POSITION S DOB SALARY BRANC ----- ---------- ---------- ---------- - --------- ---------- ----- SG37 Ann Beech Assistant F 10-NOV-60 12000 B003 SG14 David Ford Supervisor M 24-MAR-58 18000 B003 SG5 Susan Brand Manager F 03-JUN-40 24000 B003 SQL> drop synonym dual; Synonym dropped. SQL> select * from dual; D - X

More Related