Databases: Databases Contents
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 + database management system Three-level architecture: Three-level architecture Application-level
Internal representation Types of databases: Types of databases hierarchical
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
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
Project tables Examples: SQL: Examples: SQL CREATE TABLE emp_details (
first_name char(15) not null,
last_name char(15) not null,
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
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
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