# database

Views:

Category: Education

## Presentation Description

No description available.

By: yamaha123 (100 month(s) ago)

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

## 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

### 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