1 / 45

Week 3 September 12

Week 3 September 12. Three-Level Architecture Database Management System (DBMS) Relational Data Model Views. Privacy and Confidentiality. SSN: 123 45 6789 Customer: John K Smith Address: 1234 Main Street Dallas, TX 68213 Account: 5432 1234 4567 8901 Credit limit: 20,000

garrison-powers
Download Presentation

Week 3 September 12

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. Week 3September 12 Three-Level Architecture Database Management System (DBMS) Relational Data Model Views

  2. Privacy and Confidentiality SSN: 123 45 6789 Customer: John K Smith Address: 1234 Main Street Dallas, TX 68213 Account: 5432 1234 4567 8901 Credit limit: 20,000 Current balance: 9,123.00 Employer: Enron Corp. Monthly income: 100,000.00

  3. Schema and Subschemas User User User User User User Individual Views Subschema Subschema Subschema Complete catalog of all data retained in the database Schema DBMS Software Manages the database Physical Database

  4. Database EnvironmentThree Level Architecture User’s view of the database External Level Community view Conceptual Schema Conceptual Level Physical representation Internal Schema Internal Level Physical data organization Physical storage

  5. Independence • Each user should be able to access the same data, but have a different customized view of the data • Users should not have to deal directly with physical database storage details • The DBA should be able to change the database storage structures without affecting the users’ views • The internal structure of the database should be unaffected by changes to the physical aspects of storage • The DBA should be able to change the conceptual or global structure of the database without affecting all users

  6. Three-Level Architecture • External LevelDescribes that part of the database that is relevant to a particular user • Conceptual LevelDescribes what data is stored in the database and relationships among the data • Internal LevelDescribes how the data is stored in the database

  7. Database EnvironmentThree Level Architecture Subschema Subschema Subschema External External/conceptual mapping Logical data independence Conceptual Schema Conceptual Conceptual/internal mapping Physical data independence Internal Schema Internal

  8. Database EnvironmentThree Level Architecture Subschema Subschema Subschema External External/conceptual mapping Logical data independence Conceptual Schema Conceptual Conceptual/internal mapping Physical data independence Internal Schema Internal

  9. Database EnvironmentThree Level Architecture Subschema Subschema Subschema External External/conceptual mapping Logical data independence Conceptual Schema Conceptual Conceptual/internal mapping Physical data independence Internal Schema Internal

  10. Data Independence • Logical data independenceImmunity of external schemas to changes in the conceptual schema • Physical data independenceImmunity of the conceptual schema to changes in the internal schema “Plug and Play!”

  11. Database Environment User User User User User User Individual Views Subschema Subschema Subschema Complete catalog of all data retained in the database Schema DBMS Software Manages the database Shared and Managed Physical Database

  12. File-Based Systems User User User User User User Subschema Subschema Subschema File File Integrity Problems File Schema Each user has his/her file DBMS Software File Everyone has access to all data in the file

  13. Database Languages: DDL vs. DML • Data definition language (DDL)Used to describe name the entities required for the application and the relationships that may exist between the different entities • Specify or modify the database schema and subschemas • Data manipulation language (DML)Provides a set of operations that support the basic data manipulation operations the data • Read and update (i.e., insert, update, delete) the database

  14. Models • Represents the real thing • Identifies the components and their interactions • Specifies the behavior For example...

  15. Data Models • An integrated collection of concepts for describing and manipulating data, relationships between data and constraints on the data in an organization • Three components: • Structural part - set of rules applied to the construction of the database • Manipulative part - defines the types of operations allowed on the data • Integrity rules - ensures the accuracy of the data

  16. Data Models • Object-based • Entity-relationship • Semantic • Functional • Object-oriented • Record-based (transactions) • Relational • Network • Hierarchical • Physical • Unifying • Frame memory Knowledge-based Object-relational Transaction-based How data are stored

  17. Record-Based Data Models • Relational (Oracle, DB2, Sybase, Informix, SQL 7, Ingres, etc.) • Based on concepts of mathematical relations • Tables, rows, columns • Network (CODASYL - COnference on DAta SYstem Languages) (Image) • Many-to-many relationships • Record types, data items • Hierarchical (IMS) • Segment types, fields In COBOL: files, records, fields

  18. Database Management Queries Physical Database Application Programs DBMS Other Software Manages the database

  19. Functions of a DBMS • Data storage, retrieval and update • User-accessible catalog • Transaction support • Concurrency control services • Recovery services • Authorization services • Support for data communications • Integrity services • Services to promote data independence • Utility services

  20. DBMS Programmers Users DBA Application Programs Queries Database Schema DML Processor Query Processor DDL Processor Program Object Code Database Manager Dictionary Manager DBMS Access Methods File Manager Operating System System Buffers

  21. Database Manager Authorization Control Checks user authorization Processes query Determines optimal strategy Command Processor Query Optimizer Integrity Checker Checks integrity constraints Transaction Manager Scheduler Recovery Manager Buffer Manager Data Manager

  22. Database Manager Authorization Control Command Processor Query Optimizer Integrity Checker Manages concurrent operations Performs command operation Transaction Manager Scheduler Ensures recovery in case of failures Transfers data between primary and secondary storage Recovery Manager Buffer Manager

  23. Database Manager Authorization Control Command Processor Query Optimizer Integrity Checker Manages concurrent operations Query  Transaction  Journal  Buffered  OS Performs command operation Transaction Manager Scheduler Ensures recovery in case of failures Transfers data between primary and secondary storage Recovery Manager Buffer Manager

  24. System Catalog • A repository of information describing the data in the database (metadata) • Stores • Names of users authorized to access the DBMS • Names of all data items in the database • Types and sizes • Constraints • Data items and authorization level granted to each user • Active vs. Passive • Integrated vs. Standalone

  25. Relational Data Model

  26. Relational Model • Objectives • A degree of data independence • Address data semantic, consistency and redundancy problems • Set-oriented data manipulation language • Structured Query Language (SQL) Presen- tation method Criteria Database Data Set Information

  27. Presen- tation method Data Set Information Criteria Presen- tation method Criteria Data Set Information Criteria Database Presen- tation method Data Set Information

  28. Domain = all values an attribute can assume Attribute-1 Attribute-2 Attribute-n Entity • Tuples (rows) • Cardinalitiy = number of tuples Relation • Attributes (columns) • Degree of a relation = number of attributes

  29. Domain of an Attribute • Set of allowable values for one or more attributes Attribute 1 Attribute 2 Union or Intersection Domain Domain Information

  30. Properties of Relations • Distinct (i.e., unique) relation name • Each cell contains exactly one atomic (single) value • No repeating groups • Distinct attribute name • The values of an attribute come from the same domain • Order of attributes has no significance • Each tuple is distinct (i.e., unique) • No duplicate tuples • Order of tuples has no significance

  31. Unique Identification of a Relation Relation key ? Superkey Candidate key Primary key Foreign key

  32. Identifying a Tuple • SuperkeyAn attribute or a set of attributes that uniquely identifies a tuple within a relation • Candidate keyA super key such that no proper subset is a superkey within the relation • Uniquely identifies the tuple (uniqueness) • Contains no unique subset (irreducibility) • Primary keyThe candidate key that is selected to identify tuples uniquely within a relation • Should remain constant over the life of the tuple • Most efficient way of identifying a tuple

  33. Finding the Primary Key Super Key Candidate Key Primary key

  34. Keys CDs Relation 129341 Help! Beatles Columbia 1-29150-8384-0 129342 Hard Day’s Night Beatles Columbia 1-29150-7115-0 Beatles 129343 Sergeant Pepper’s Columbia 1-29150-2484-0 129344 Magical Mystery Tour Beatles Columbia 1-29150-7515-0 129345 Abbey Road Beatles Apple 1-15700-9510-0 • Attributes • Catalog number • Record title • Artist name • Record label • UPC Superkey? Candidate key? Primary key?

  35. Selecting a Key • Criteria • An efficient way of identifying an entity • The attribute (value) remains constant over the life of the entity • Never changes

  36. Identifying a Tuple • Foreign keyAn attribute or set of attributes within one relation that matches the candidate key of some (possibly the same) relation Relation key foreign key Relation key

  37. Foreign Key CDs Relation 129341 Help! Beatles COL 1-29150-8384-0 129342 Hard Day’s Night Beatles COL 1-29150-7115-0 Beatles 129343 Sergeant Pepper’s COL 1-29150-2484-0 Must match! 129344 Magical Mystery Tour Beatles COL 1-29150-7515-0 129345 Abbey Road Beatles APP 1-15700-9510-0 COL Columbia Records Recording Label Relation (home relation) APP Apple Records

  38. Relational Integrity Constraints placed on the set of values allowed for the attributes of a relation. • Entity integrity • No attribute of a primary key can be null (every tuple must be uniquely identified) • Referential integrity • If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation, or the foreign key value must be wholly null (i.e., no key exists in the home relation) • Enterprise constraints (organizational)

  39. Null Value • Absence of any value (i.e., unknown or nonapplicable to a tuple)

  40. Views • A view is a virtual relation or one that does not actually exist, but dynamically derived • Can be constructed by performing operations (i.e., select, project, join, etc.) on values of existing base relations • Base relation - a named relation, corresponding to an entity in the conceptual schema, whose tuples are physically stored in the database • View - a dynamic result of one or more relational operations operating on the base relations to produce another

  41. Schema and Subschemas Internal Level Physical Database DBMS DBMS Software Some end-user applications can be supported by views Schema Conceptual Level External Level Subschema Subschema Subschema User User User User User User

  42. Views Base Relation R Base Relation S Foreign Key Key Key Criterion View

  43. Purpose of Views • Provides a powerful and flexible security mechanism by hiding parts of the database from certain users • Permits user access in a way that is customized to their needs • Simplify complex operations on the base relations • Designed to support the external model • Provides logical independence

  44. Updating Views • Allowed on views • Derived from a single base relation, and • Containing the primary key or a candidate key • NOT allowed on views • Derived from multiple base relations • Involving aggregations (i.e., summations) or groups operations • Vendors may have other constraints on updating views

More Related