Docsity
Docsity
Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity

Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan

Guidelines and tips
Guidelines and tips
Sell on Docsity
Sell on Docsity
Docsity AI
Docsity AI
Log inSign up

Prepare for your exams

Study with the several resources on Docsity

Find documents

Prepare for your exams with the study notes shared by other students like you on Docsity

Search for your university

Find the specific documents for your university's exams

Docsity AINEW

Summarize your documents, ask them questions, convert them into quizzes and concept maps

Explore questions

Clear up your doubts by reading the answers to questions asked by your fellow students

Earn points to download

Earn points by helping other students or get them with a premium plan

Share documents
download point icon20 Points

For each uploaded document

Answer questions
download point icon5 Points

For each given answer (max 1 per day)

All the ways to get free points
Get points immediately

Choose a premium plan with all the points you need

Study Opportunities

Choose your next study program

Get in touch with the best universities in the world. Search through thousands of universities and official partners

Community

Ask the community

Ask the community for help and clear up your study doubts

Free resources

Our save-the-student-ebooks!

Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors

dbms, Slides of Database Management Systems (DBMS)

COMSATS Institute of Information Technology, Wah (CIITW)Database Management Systems (DBMS)

guria rani 09

Typology: Slides

2011/2012

Uploaded on 08/12/2012

guriarani09
guriarani09 🇵🇰

4.3

(3)

25 documents

1 / 21

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
Database management concepts
• Database Management Systems (DBMS)
• An example of a database (relational)
• Database schema (e.g. relational)
• Data independence
• Architecture of a DBMS
• Types of DBMS
• Basic DBMS types
• Retrieving and manipulating data: query processing
• Database views
• Data integrity
• Client-Server architectures
• Knowledge Bases and KBS (and area of AI)
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff
pf12
pf13
pf14
pf15

Partial preview of the text

Download dbms and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Database management concepts

  • (^) Database Management Systems (DBMS)
    • (^) An example of a database (relational)
    • (^) Database schema (e.g. relational)
    • (^) Data independence
    • (^) Architecture of a DBMS
    • (^) Types of DBMS
    • (^) Basic DBMS types
    • (^) Retrieving and manipulating data: query processing
    • (^) Database views
  • (^) Data integrity
  • (^) Client-Server architectures
  • (^) Knowledge Bases and KBS (and area of AI)
  • (^) DBMS tasks:
    • (^) Managing large quantity of structured data
    • (^) Efficient retrieval and modification: query processing and optimization
    • (^) Sharing data: multiple users use and manipulate data
    • (^) Controlling the access to data: maintaining the data integrity
  • (^) An example of a database (relational):
    • (^) Relations (tables)
    • (^) Attributes (columns)
    • (^) Tuples (rows)
    • (^) Example query: Salesperson='Mary' AND Price>100.
  • (^) Database schema (e.g. relational):
    • (^) Names and types of attributes
    • (^) Addresses
    • (^) Indexing
    • (^) Statistics
    • (^) Authorization rules to access data etc.
  • (^) Data independence: separation of the physical and logical data
    • (^) Particularly important for distributed systems
    • (^) The mapping between them is provided by the schema
  • (^) Architecture of a DBMS - three levels: external, conceptual and internal schema
  • (^) Types of DBMS
    • (^) The data structures supported: tables (relational), trees, networks, objects
    • (^) Type of service provided: high level query language, programming primitives

Relational structure

  • (^) Relations, attributes, tuples
  • (^) Primary key (unique combination of attributes for each tuple)
  • (^) Foreign keys: relationships between tuples (many-to-many). Example: SUPPLIES defines relations between ITEM and SUPPLIER tuples.
  • (^) Advantages: many-to-many relationships, high level declarative query language (e.g. SQL)
  • (^) SQL example (retrieve all items supplied by a supplier located in Troy): SELECT ItemName FROM ITEM, SUPPLIES, SUPPLIER WHERE SUPPLIER.City = "Troy" AND SUPPLIER.Supplier# = SUPPLIES.Supplier# AND SUPPLIES.Item# = ITEM.Item#
  • (^) Programming language interfaces: including SQL queries in the code

Retrieving and manipulating data: query processing

  • (^) Parsing and validating a query: data dictionary - a relation listing all relations and relations listing the attributes
  • (^) Plans for computing the query: list of possible way to execute the query, estimated cost for each. Example: SELECT ItemNames, Price FROM ITEM, SALES WHERE SALES.Item# = ITEM.Item# AND Salesperson="Mary"
  • (^) Index: B-tree index, drawbacks - additional space, updating; indexing not all relations (e.g. the keys only)
  • (^) Estimating the cost for computing a query: size of the relation, existence/size of the indices. Example: estimating Attribute=value with a given number of tuples and the size of the index.
  • (^) Query optimization: finding the best plan (minimizing the computational cost and the size of the intermediate results), subsets of tuples, projection and join.
  • (^) Static and dynamic optimization

Data integrity

Integrity constraints: semantic conditions on the data

  • (^) Individual constraints on data items
  • (^) Uniqueness of the primary keys
  • (^) Dependencies between relations Concurrency control
  • (^) Steps in executing a query
  • (^) Concurrent users of the database, interfering the execution of one query by another
  • (^) Transaction: a set of operations that takes the database from one consistent state to another
  • (^) Solving the concurrency control problem: making transactions atomic operations (one at a time)
  • (^) Concurrent transactions: serializability theory (two-phase locking), read lock (many), write lock (one).
  • (^) Serializible transactions: first phase - accumulating locks, second phase - releasing locks.
  • (^) Deadlocks: deadlock detection algorithms.
  • (^) Distributed execution problems:
  • (^) release a lock at one node (all locks accumulated at the other node?)
  • (^) strict two-phase locking

The Transaction Model

• Examples of primitives for transactions.

Primitive Description BEGIN_TRANSACTION Make the start of a transaction END_TRANSACTION Terminate the transaction and try to commit ABORT_TRANSACTION Kill the transaction and restore the old values READ Read data from a file, a table, or otherwise WRITE Write data to a file, a table, or otherwise

Distributed Transactions

a) A nested transaction

b) A distributed transaction

Writeahead Log

  • (^) a) A transaction
  • (^) b) – d) The log before each statement is executed x = 0; y = 0; BEGIN_TRANSACTION; x = x + 1; y = y + 2 x = y * y; END_TRANSACTION; (a) Log [x = 0 / 1] (b) Log [x = 0 / 1] [y = 0/2] (c) Log [x = 0 / 1] [y = 0/2] [x = 1/4] (d)

Serializability

• a) – c) Three transactions T

1

, T

2

, and T

3

• d) Possible schedules

BEGIN_TRANSACTION x = 0; x = x + 1; END_TRANSACTION (a) BEGIN_TRANSACTION x = 0; x = x + 2; END_TRANSACTION (b) BEGIN_TRANSACTION x = 0; x = x + 3; END_TRANSACTION (c) Schedule 1 x = 0; x = x + 1; x = 0; x = x + 2; x = 0; x = x + 3 Legal Schedule 2 x = 0; x = 0; x = x + 1; x = x + 2; x = 0; x = x + 3; Legal Schedule 3 x = 0; x = 0; x = x + 1; x = 0; x = x + 2; x = x + 3; Illegal (d)

Two-Phase Locking (1)

• Two-phase locking.

Data integrity

Backup and recovery

  • (^) The problem of keeping a transaction atomic: successful or failed What if some of the intermediate steps failed?
  • Log of database activity: use the log to undo a failed transaction.
  • (^) More problems: when to write the log, failure of the recovery system executing the log. Security and access control
  • (^) Access rules for relations or attributes. Stored in a special relation (part of the data dictionary).
  • (^) Content-independent and content-dependent access control
  • (^) Content-dependent control: access to a view only or query modification (e.g. and-ing a predicate to the WHERE clause)
  • (^) Discretionary and mandatory access control

Knowledge Bases and KBS (and area of AI)

  • (^) Information, Data, Knowledge (data in a form that allows reasoning)
  • (^) Basic components of a KBS
    • (^) Knowledge base
    • (^) Inference (reasoning) mechanism (e.g. forward/backward chaining)
    • (^) Explanation mechanism/Interface
  • (^) Rule-based systems (medical diagnostics, credit evaluation etc.)