database

Views:
 
Category: Education
     
 

Presentation Description

No description available.

Comments

By: yamaha123 (49 month(s) ago)

hello, i need to download this doc.. thnx

By: saran.rajendran (50 month(s) ago)

Hi, can i download this ppt

Presentation Transcript

Databases: 

Databases Contents Terminology Relational databases Database design Other types of databases Summary

A student’s lab report: 

A student’s lab report I applied a load of 100 Kg at the midspan of the beam. The dial gauge showed 4 mm deflection. Then I applied another 100 Kg load. Now the deflection increased to 8.5 mm.

Terminology: 

Terminology Database: A collection of persistent and structured data Database management system: A software component that manages databases Database system: database + database management system

Three-level architecture: 

Three-level architecture Application-level Conceptual-level Internal representation

Types of databases: 

Types of databases hierarchical relational object-oriented logic-based distributed multi-media flat files

Relational database systems: 

Relational database systems Based on the relational model by Codd E. F. Principal idea – organise data in the form of two-dimensional tables Standard set of operations for accessing and modifying data

The relational model: 

The relational model Definition of an n-ary relation: (From set theory) An n-ary relation among sets:  A1, A2, .. An  is a subset of  (  (  (A1 x A2)  x .. )  x An) A set of ordered n-tuples RDBMS terminology n-ary relation - Table A1, A2, .. An - Domains: Columns n - degree of the table:  Number of columns Each n-tuple is a row (record)

Example of a relation: 

Example of a relation Table: Steel Plate Sections

Definitions: 

Definitions Primary key: A domain of a table is called a primary key if its value in a record uniquely identifies the record in the table. Composite primary key: A cartesian product of two or more domains such that its value in a record uniquely identifies the record in the table Foreign key: A domain of a table is called a foreign key if its value refers to the primary key of another table. Candidate key is either a primary key or alternate key

Definitions: 

Definitions Projection (View) A projection of a relation is an m-ary relation, m <= n obtained from R by deleting n-m of the components in each ordered n-tuple in R. (It is an abstraction of the original table).

Join: 

Join

Limitations of relational databases : 

Limitations of relational databases Records are of constant size! Variable size elements like lists are not supported!

Exercise: 

Exercise Design a database to store details of leaf springs

Accessing data: 

Accessing data GUI – graphical user interface API - application program interface SQL - Structure query language

SQL- Structured Query Language : 

SQL- Structured Query Language Defines syntax for performing standard operations on databases Create tables Insert records Update records Delete records Join tables Project tables

Examples: SQL: 

Examples: SQL CREATE TABLE emp_details ( first_name char(15) not null, last_name char(15) not null, comment text(50), dept char(20), emp_id int , PRIMARY KEY (emp_id) ) DROP TABLE table_name

Examples: SQL: 

Examples: SQL SELECT first_name, last_name FROM emp_details WHERE dept = `civil' ORDER BY last_name, first_name DESC INSERT INTO steel_plates (Section-ID, Thickness, Width, Area) VALUES ('I-100', 12, 100, 1200) UPDATE emp_details SET salary=50000 WHERE emp_id = 1234 DELETE FROM emp_details WHERE emp_id = 12345

Relational database design : 

Relational database design A bad design results in maintenance problems Normal forms have been proposed to provide guidelines for good designs. Tables in a well-designed database exist in higher normal forms There are primarily five normal forms, denoted as 1NF, 2NF, 3NF, 4NF and 5NF + a special case of 3NF called the Boyce-Codd normal form

First normal form : 

First normal form A relation is said to be in first normal form (1NF) if it contains only atomic (scalar) values

Functional dependency : 

Functional dependency

2NF: 

2NF Every non-key attribute is irreducibly dependent on the primary key.

A relation in 2NF: 

A relation in 2NF

A relation in 2NF: 

A relation in 2NF

A relation not in 2NF: 

A relation not in 2NF

Bridge details: 

Bridge details What are the functional dependencies? Is it in 2NF?

Update anomalies: 

Update anomalies Redundancy Insert Update Delete

3NF: 

3NF Every non-key attribute is mutually independent

A relation in 3NF: 

A relation in 3NF

A relation not in 3NF: 

A relation not in 3NF

Update anomalies: 

Update anomalies Redundancy Insert Update Delete

Transaction processing: 

Transaction processing A transaction is a logical unit of work (LUW). Either all requests in a transaction go through successfully, otherwise none of them go through

Mechanisms for implementing transactions: 

Mechanisms for implementing transactions Locks Commit/Rollback

Object databases: 

Object databases Need to store non-relational data Better integration with object-oriented systems

Geographic databases: 

Geographic databases Relational model inefficient for storing geographic information

Multimedia databases: 

Multimedia databases Mixture of structured and unstructured data Usually multi-media data is stored in files, meta-data in relational databases Applications in air-traffic control, entertainment

Distributed databases: 

Distributed databases Storing all data on a single computer creates scalability problems. Distributing data on multiple machines creates issues related to ensuring integrity and consistency across machines.

Summary: 

Summary Data should be properly organised – for ease of retrieval and maintainability DBMSs allow sharing data across applications Relational databases are simple and efficient Good database design requires knowledge of FDs