VERİ TABANI SİSTEM GERÇEKLEME DERS NOTLARI -1 1.DERS : GİRİŞ, TANIMLAR.. Kaynaklar (türkçe, ing.): Prof. Dr. Ünal Yarımağan, Veri Tabanı Sistemleri - PDF

Description
VERİ TABANI SİSTEM GERÇEKLEME DERS NOTLARI -1 1.DERS : GİRİŞ, TANIMLAR.. Kaynaklar (türkçe, ing.): Prof. Dr. Ünal Yarımağan, Veri Tabanı Sistemleri Elmasri, Navathe, Fund. of Database Systems, 5th ed.,

Please download to get full document.

View again

of 67
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information
Category:

Scrapbooking

Publish on:

Views: 402 | Pages: 67

Extension: PDF | Download: 0

Share
Transcript
VERİ TABANI SİSTEM GERÇEKLEME DERS NOTLARI -1 1.DERS : GİRİŞ, TANIMLAR.. Kaynaklar (türkçe, ing.): Prof. Dr. Ünal Yarımağan, Veri Tabanı Sistemleri Elmasri, Navathe, Fund. of Database Systems, 5th ed., Addison Wesley Edward Sciore, Database Design and Implementation, 2009, John Wiley 1 1 Giris Konu Başlıkları VT nedir? VTYS nedir? Veri modeli nedir? Örnek bir veri tabanı: UNIVERSİTE VT kullanıcıları VT programla dilleri VTYS olanakları ve dosya-işleme sisteminden farkları VTYS ana işlevsel modülleri VTYS 3-şema mimarisi ve veri bağımsızlığı VT kullanım/erişim mimarileri (2-katlı, 3 katlı) VT türleri RM RA-SQL 2 2 Veri Tabanı Nedir? Birden çok uygulama tarafından kullanılan Gereksiz yinelemelerden arınmış Düzenli bir şekilde saklanan Birbiriyle ilşkili (uyumlu olarak) Sürekli, fakat statik olmayan Belirli bir amaç için bir araya getirilmiş VERİ TOPLULUĞU (küçük bir dünya) dur. Örnek: şirket, bakanlık, üniversite, market stok takip. 3 3 VT nedir? VT na yeni bilgi, en kisa zamanda yansitilmali VT büyüklüğü için bir kısıtlama yok Örnek : Amazon.com 2 terab (10 12 B) 20 milyon kitap 200 sunucu bilgisayar üzerinde kayıtlı Günlük 15 milyon kullanıcı Yaklaşık 100 admin 4 4 VTYS Nedir? Veri tabanı sistemi ile ilgili her türlü işletimsel gereksinimleri karşılamak için kullanılan sistem seviyesinde, karmaşık, merkezi yazılım sistemine VTYS denir. VTYS genel olarak şu olanakları sağlar: VT tanımlanması, gerçeklenmesi (oluşturulması), kullanımı, paylaşımı Kontrollü veri tekrarı Sorgu işemede verimli erişim metodlarını kullanır. Çoklu kullanıcılı hizmet, veri kurtarma ve yedeklme imkanı sağlar. Farklı kullanıcı arayüzlerine imkan sağlar. Üst seviyeli karmaşık iş kısıtlamlarının tanımlanması, gerçeklenmesi ve sağlanmasına olanak sağlar. Güvenlik tanımlamaları ve sağlanmasını kontrol eder. VT sistemine, gerek işletim sistemi gerek diğer kullanıcılar (uygulama programları gibi...) doğrudan erişemez; ancak VTYS üzerinden erişebilir. 5 5 Genel VT / VTYS yapısı kullanıcılar..... VTYS Veri sözlüğü log dosyaları İndex dosyaları Veri dosyaları Veri sözlüğü, veri tabanı tanımlarının (metada) saklandığı dosyalardır. İndex dosyları, fiziksel erişim dosyalarıdır. Log dosyaları güvenlik amaçlı dosyalardır. 6 6 Konular VT nedir? VTYS nedir? Veri modeli nedir? Örnek bir veri tabanı: UNIVERSİTE VT kullanıcıları VT programla dilleri VTYS olanakları ve dosya-işleme sisteminden farkları VTYS ana işlevsel modülleri VTYS 3-şema mimarisi ve veri bağımsızlığı VT kullanım/erişim mimarileri (2-katlı, 3 katlı) VT türleri RM RA-SQL 7 7 Veri Modeli Nedir? Gerçek dünya verilerini kavramsal ve mantıksal seviyede düzenlemek için kullanılan yapı ve kavramlar bütünü olarak tanımlanır. Örnek; E-R modeli, ilişkisel model (RM) Genel VT tasarımı: kavramsal tasarım mantıksal düzenleme, varlık ve bağıntıların belirlenmesi veri tipleri, değer aralığı, uzunluk belirlenmesi veri bütünlüğü kısıtlamalarının belirlenmesi fiziksel tasarım tercihleri kullanıcıların belirlenmesi ve güvenlik ayarları 8 8 Categories of Data Models Conceptual (high-level, semantic) data models: Provide concepts that are close to the way many users perceive data. (Also called entity-based or object-based data models.) Implementation (representational) data models: used by many commercial DBMS implementations (e.g. relational data models used in many commercial systems). Physical (low-level, internal) data models: Provide concepts that describe details of how data is stored in the computer. These are usually specified in an ad-hoc manner through DBMS design and administration manuals 9 9 Schemas versus Instances Database Schema: The description of a database. Includes descriptions of the database structure, data types, and the constraints on the database. Schema Diagram: An illustrative display of (most aspects of) a database schema. Schema Construct: A component of the schema or an object within the schema, e.g., STUDENT, COURSE. Database State: The actual data stored in a database at a particular moment in time. This includes the collection of all the data in the database. Also called database instance (or occurrence or snapshot). The term instance is also applied to individual database components, e.g. record instance, table instance, entity instance 10 10 Example of a Database Schema vs. State 11 11 Konular VT nedir? VTYS nedir? Veri modeli nedir? Örnek bir veri tabanı: UNIVERSİTE VT kullanıcıları VT programla dilleri VTYS olanakları ve dosya-işleme sisteminden farkları VTYS ana işlevsel modülleri VTYS 3-şema mimarisi ve veri bağımsızlığı VT kullanım/erişim mimarileri (2-katlı, 3 katlı) VT türleri RM RA-SQL 12 12 VT kullanıcıları Yönetici (Admin) VT erişimi ve kullanımı kontrol eder Sistem için gerekli s/w,h/w desteğini belirler Güvenlik açığını, verimsiz kaynak kullanımını belirler Tasarımcı (Designer) Verinin her aşamada modellenmesi ile ilgilenir. Gerçekleme öncesi aşamalardan sorumludur. VT kullanıcıları ile haberleşir gereksinim analizi yapar. Genel olarak bütün kullanıcılar ile yakın temas vardır. Son kullanıcılar (End user) VT sistem yazılımcısı 13 13 DBMS Languages Data Definition Language (DDL) Used by the DBA and database designers to specify the conceptual schema of a database. In many DBMSs, the DDL is also used to define internal and external schemas (views). Data Manipulation Language (DML) High-Level or Non-procedural Languages: These include the relational language SQL May be used in a standalone way or may be embedded in a programming language Low Level or Procedural Languages: These must be embedded in a programming language 14 14 DBMS Programming Language Interfaces Programmer interfaces for embedding DML in a programming languages: Embedded Approach: e.g embedded SQL (for C, C++, etc.), SQLJ (for Java) Procedure Call Approach: e.g. JDBC for Java, ODBC for other programming languages Database Programming Language Approach: e.g. ORACLE has PL/SQL, a programming language based on SQL; language incorporates SQL and its data types as integral components User-friendly interfaces Menu-based, popular for browsing on the web, Formsbased,Graphics-based (Point and Click, Drag and Drop, etc.) Natural language: requests in written English 15 15 Database System Utilities To perform certain functions such as: Loading data stored in files into a database. Includes data conversion tools. Backing up the database periodically on tape. Reorganizing database file structures. Report generation utilities. Performance monitoring utilities. Other functions, such as sorting, user monitoring, data compression, etc. Data dictionary / repository: Used to store schema descriptions and other information such as design decisions, application program descriptions, user information, usage standards, etc. Application Development Environments and CASE (computer-aided software engineering) tools : PowerBuilder (Sybase), JBuilder (Borland), JDeveloper 10G (Oracle) 16 16 Niye VTYS? dosya-işleme niye yetersiz? Veri sorgulama kolaylığı 17 17 Niye VTYS? dosya-işleme niye yetersiz? Üstveri(metadata):Veri tabanında saklanan verinin tanımları (meta data) ve diğer kısıtlamalar da saklanıyor. Farklı küçük dünyalar aynı veri tabanında saklanabiliyor. (Buna Program-veri bağımsızlığı denir.) Dosya işleme uygulaması sadece o uygulama için yazılmış. Kayıt saklama ve erişimde güçlü veri yapıları ihtiyacı Tampon kullanımı program-operasyon bağımsızlığı ihtiyacı Verinin farklı görünümü (multiple views) Paylaşım (sharing) ve çoklu hareket işleme (transaction processing) imkanı Eşzamanlılık Veri kurtarma ve geri sarma Güvenlik ve yetkilendirme SONUÇ OLARAK; yavaş saklama unitesinde saklanan, çok kişi tarafından erişilen, büyük veri yığınlarında, veri kurtarma desteği ile gerçek zamanda hizmet vermek için VTYS na ihtiyacımız var Konular VT nedir? VTYS nedir? Veri modeli nedir? Örnek bir veri tabanı: UNIVERSİTE VT kullanıcıları VT programla dilleri VTYS olanakları ve dosya-işleme sisteminden farkları VTYS ana işlevsel modülleri VTYS 3-şema mimarisi ve veri bağımsızlığı VT kullanım/erişim mimarileri (2-katlı, 3 katlı) VT türleri RM RA-SQL 19 19 Typical DBMS Component Modules 20 20 The three-schema architecture Data extracted from the internal DBMS level is reformatted to match the user s external view (e.g. formatting the results of an SQL query for display in a Web page) External schemas at the external level to describe the various user views. Usually uses the same data model as the conceptual schema. How data is used? Conceptual schema at the conceptual level to describe the structure and constraints for the whole database for a community of users. Uses a conceptual or an implementation data model. What data is inside? Programs refer to an external schema, and are mapped by the DBMS to the internal schema for execution. Internal schema at the internal level to describe physical storage structures and access paths (e.g indexes). Typically uses a physical data model. How data is stored? Proposed to support DBMS characteristics of: Program-data independence. Support of multiple views of the data. Not explicitly used in commercial DBMS products, but has been useful in explaining database system 21 organization 21 Data Independence Physical Data Independence: The capacity to change the internal schema without having to change the conceptual schema.(for example, the internal schema may be changed when certain file structures are reorganized or new indexes are created to improve database performance) Logical Data Independence: The capacity to change the conceptual schema without having to change the external schemas and their associated application programs. When a schema at a lower level is changed, only the mappings between this schema and higher-level schemas need to be changed in a DBMS that fully supports data independence. The higher-level schemas themselves are unchanged. Hence, the application programs need not be changed since they refer to the external schemas Physical Data Independence Select Sname from STUDENT where GradYear=1997 Database catalog (conceptual and physical schemas) Ease of use Query optimization İsolation from changes to physical schema 23 23 Logical Data Independence In a database, if users have their own external schemas, then this db support logical data independence. Adv. of logical data dependence: Customized external schema İsolation from changes to conceptual schema Suppose a change in conceptual schema: split STUDENT table into 2 tables: CURR_STUDENT and ALUMNI. What happens to external schema? Better security 24 24 Konular VT nedir? VTYS nedir? Veri modeli nedir? Örnek bir veri tabanı: UNIVERSİTE VT kullanıcıları VT programla dilleri VTYS olanakları ve dosya-işleme sisteminden farkları VTYS ana işlevsel modülleri VTYS 3-şema mimarisi ve veri bağımsızlığı VT kullanım/erişim mimarileri (2-katlı, 3 katlı) VT türleri RM RA-SQL 25 25 Centralized and Client-Server DBMS Architectures Centralized DBMS: Combines everything into single system including- DBMS software, hardware, application programs, and user interface processing software. User can still connect through a remote terminal however, all processing is done at centralized site Basic 2-tier Client-Server Architectures Specialized Servers with Specialized functions: Print server, File server, DBMS server, Web server, server... Clients can access the specialized servers as needed Applications running on clients utilize an Application Program Interface (API) to access server databases via standard interface such as: ODBC: Open Database Connectivity standard JDBC: for Java programming access Client and server must install appropriate client module and server module software for ODBC or JDBC Variations of clients are possible (tightly vs loosely coupled): e.g., in some object DBMSs, more functionality is transferred to clients including data dictionary functions, optimization and recovery across multiple servers, etc Three Tier Client-Server Architecture Common for Web applications Three-tier Architecture Can Enhance Security: Database server only accessible via middle tier (Application Server or Web Server) Clients cannot directly access database server 28 28 Classification of DBMSs Based on the data model used Traditional: Relational, Network, Hierarchical. Emerging: Object-oriented, Objectrelational. XML Other classifications Single-user (typically used with personal computers or embedded dbms) vs. multi-user (most DBMSs). Centralized (uses a single computer with one database) vs. distributed (uses multiple computers, multiple databases) Homogeneous DDBMS Heterogeneous DDBMS Federated or Multidatabase Systems Distributed Database Systems have now come to be known as clientserver based database systems because: They do not support a totally distributed environment, but rather a set of database servers supporting a set of clients Extension to DB capabilities, New Applications DB for Scientific applications Image/videos DB Data mining Spatial/temporal DB Information retrieval (deals with text in general, books, manuscripts, library-based articles) For more generic information about DBMS look at ent_system 30 30 Relational Model Relational Model Concepts Relational Model Constraints and Relational Database Schemas Update Operations and Dealing with Constraint Violations 31 General view: the relational model consisted of (1) data independence from hardware and storage implementation and (2) automatic navigation, or a high-level, nonprocedural language for accessing data. Instead of processing one record at a time, a programmer could use the language to specify single operations that would be performed across the entire data set. 2 historical famous relational databases: System R by IBM and Ingres by UC-Berkeley. Tables are the basic building blocks. 32 The relational Model of Data is based on the concept of a Relation The strength of the relational approach to data management comes from the formal foundation provided by the theory of relations We review the essentials of the formal relational model. In practice, there is a standard model based on SQL. Note: There are several important differences between the formal model and the practical model. A Relation is a mathematical concept based on the ideas of sets The model was first proposed by Dr. E.F. Codd of IBM Research in 1970 in the following paper: a Relational Model for Large Shared Data Banks, Communications of the ACM, June 1970 The above paper caused a major revolution in the field of database management and earned Dr. Codd the coveted ACM Turing Award 33 Informally, a relation looks like a table of values. A relation typically contains a set of rows. (not considered to be ordered) The data elements in each row represent certain facts that correspond to a real-world entity or relationship In the formal model, rows are called tuples Each column has a column header that gives an indication of the meaning of the data items in that column In the formal model, the column header is called an attribute name (or just attribute) Key of a Relation: The key uniquely identifies that row in the table. In the STUDENT table, SSN is the key. Sometimes row-ids or sequential numbers are assigned as keys to identify the rows in a table. Called artificial key or surrogate key 34 The Schema (or description) of a Relation: Denoted by R(A1, A2,...An) R is the name of the relation, The attributes of the relation are A1, A2,..., An Example: CUSTOMER (Cust-id, Cust-name, Address, Phone#) CUSTOMER is the relation name. Defined over the four attributes: Cust-id, Custname, Address, Phone#. Each attribute has a domain or a set of valid values. For example, the domain of Cust-id is 6 digit numbers. A relation is a set of such tuples (rows). A tuple is an ordered set of values (enclosed in angled brackets ).Each value is derived from an appropriate domain. For example: A row in the CUSTOMER relation is a 4-tuple and would consist of four values, for example: 632895, John Smith , 101 Main St. Atlanta, GA 30332 , (404) This is called a 4-tuple as it has 4 values A tuple (row) in the CUSTOMER relation. 35 A domain has a logical definition: Example: USA_phone_numbers are the set of 10 digit phone numbers valid in the U.S. A domain has a data-type or a format defined for it. The USA_phone_numbers may have a format: (ddd)ddd-dddd where each d is a decimal digit. Dates have various formats such as year, month, date formatted as yyyy-mmdd, or as dd mm,yyyy etc. The attribute name designates the role played by a domain in a relation: Used to interpret the meaning of the data elements corresponding to that attribute Example: The domain Date may be used to define two attributes named Invoice-date and Payment-date with different meanings Example: attribute Cust-name is defined over the domain of character strings of maximum length 25 dom(cust-name) is varchar(25) The role these strings play in the CUSTOMER relation is that of the name of a customer. The relation state, r(r) is a subset of the Cartesian product of the domains of its attributes each domain contains the set of all possible values the attribute can take. 36 A set S of relation schemas that belong to the same database. S is the name of the whole database schema S = {R1, R2,..., Rn} R1, R2,, Rn are the names of the individual relation schemas within the database S Here is the COMPANY database schema with 6 relation schemas: 37 Informal Terms Table Column Header All possible Column Values Row Formal Terms Relation Attribute Domain Tuple Table Definition Populated Table Schema of a Relation State of the Relation 38 Constraints are conditions that must hold on all valid relation states. There are three main types of constraints in the relational model: Key constraints (anahtar kısıtı) Entity integrity constraints (varlık bütünlük kısıtı) Referential integrity constraints (ima bütünlük kısıtı) Another implicit constraint is the domain constraint Every value in a tuple must be from the domain of its attribute (or it could be null, if allowed for that attribute) Yet another constraint is Semantic constraints.. 39 Superkey of R: Is a set of attributes SK of R with the following condition: No two tuples in any valid relation state r(r) will have the same value for SK That is, for any distinct tuples t1 and t2 in r(r), t1[sk] t2[sk] This condition must hold in any valid state r(r) Key of R: The only way to access only 1 record. A minimal superkey That is, a key is a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey (does not possess the superkey uniqueness property) Example: Consider the CAR relation schema: CAR(State, Reg#, SerialNo, Make, Model, Year) CAR has two keys: Key1 = {State, Reg#} Key2 = {SerialNo} Both are also superkeys of CAR {SerialNo, Make} is a superkey but not a key. 40 Entity Integrity: The primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(r). This is because primary key values are used to identify the individual tuples. t[pk] null for any tuple t in r(r) If PK has several attributes, null is not allowed in any of these attributes Note: Other attributes of R may be constrained to disallow null values, even though they are not members of the primary key. 41 A constraint involving two relations The previous constraints involve a single relation. Used to specify a relationship among tuples in two relations: The referencing relation and the referenced relation. Tuples in the referencing relation R1 have attr
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks