LIC Special: DBMS Intro!

DATA BASE MANAGEMENT SYSTEM

Database: A collection of programs that enables you to store, modify, and extract information from a database. A general-purpose database management system (DBMS) is a software system designed to allow the definition, creation, querying, update, and administration of databases. Well-known DBMSs include MySQL, MariaDB, PostgreSQL, SQLite, Microsoft SQL Server, Oracle, SAP, dBASE, FoxPro, IBM DB2, LibreOffice Base and FileMaker Pro. Data in a database can be accessed and manipulated using SQL Syntax .

Types of keys in SQL:

Primary key–Uniquely identifies a record in a table-

Foreign key–Field in a table that is primary key in another table-

Super key-Primary key, Alternate Key and Unique Key are subset of Super Keys-

Candidate key-Set of one or more fields/columns that can identify a record uniquely in table. It can serve as primary key-

Alternate key-a candidate key that currently is not primary key-

Unique key-It is same as that of primary key but it can accept only null value and it cannot have duplicate values-

Composite or compound key-Combination of one or more fields or columns of one or more fields or columns of a table. It can be a candidate key or primary key-

Types of SQL statements:

DDL (Data definition language)–example.: create, drop, truncate, alter, rename-

DML (Data manipulation language)–example.: select, delete, insert, update-

TCL (Transaction control language– example.: commit, roll back-

DCL (Data control language)-example.: grant, revoke-

Normalization: Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency.

Normal forms:

1NF: Attribute must include only simple indivisible values.

2NF: It should be 1NF and every nonprime attribute is fully functionally dependent on primary key.

3NF: It should be 2NF and if every nonprime attribute is non-transitively dependent on primary key.

4NF: If for every Multivalued dependency X Y that holds over R, if X=R or XY=R or if X is a super key.

5NF: If for every join dependency that holds R, if Ri = R or join dependency is implied by the set of functional dependency over R in which the left side is key of R. BCNF: If it is 3NF and for every functional dependency X A, X must be a candidate key.