Database Management System

Database Management Systems : 

Database Management Systems

What Is a DBMS?: 

What Is a DBMS? A database is a collection of data, typically describing the activities of one or more related organizations. For example, a university database might contain information about the following: Entities such as students, faculty, courses, and classrooms. Relationships between entities, such as students' enrollment in courses, faculty teaching courses, and the use of rooms for courses. A database management system , or DBMS , is software designed to assist in maintaining and utilizing large collections of data.

Why Use a DBMS?: 

Why Use a DBMS? Data independence and efficient access. Reduced application development time. Data integrity (reliability) and security. Uniform data administration. Concurrent access, recovery from crashes.

Advantages of DBMS: 

Data independence: Application programs should be as independent as possible from details of data representation and storage. The DBMS can provide an abstract view of the data to insulate application code from such details. Efficient data access: A DBMS utilizes a variety of sophisticated techniques to store and retrieve data efficiently. This feature is especially important if the data is stored on external storage devices. Advantages of DBMS

Slide 5: 

Data integrity (reliability) and security: If data is always accessed through the DBMS, the DBMS can enforce integrity constraints on the data. For example, before inserting salary information for an employee, the DBMS can check that the department budget is not exceeded. Also, the DBMS can enforce access controls that govern what data is visible to different classes of users. Concurrent access and crash recovery: A DBMS schedules concurrent accesses to the data in such a manner that users can think of the data as being accessed by only one user at a time. Further, the DBMS protects users from the effects of system failures.

Slide 6: 

Reduced application development time: Clearly, the DBMS supports many important functions that are common to many applications accessing data stored in the DBMS. This, in conjunction with the high-level interface to the data, facilitates quick development of applications. Such applications are also likely to be more robust than applications developed from scratch because many important tasks are handled by the DBMS instead of being implemented by the application.

Slide 7: 

Data administration: When several users share the data, centralizing the administration of data can offer significant improvements. Experienced professionals who understand the nature of the data being managed, and how different groups of users use it, can be responsible for organizing the data representation to minimize redundancy and for fine-tuning the storage of the data to make retrieval efficient.

The Relational Model: 

The Relational Model In the relational model, the schema for a relation species its name, the name of each fi eld (or attribute or column ), and the type of each field. As an example, student information in a university database may be stored in a relation with the following schema: Students( sid: string, name: string, login: string, age: integer, gpa: real)

ER Model Basics: 

ER Model Basics Relationship : Association among two or more entities. lot dname budget did since name Works_In Departments Employees ssn

: 

address name ssn Person buys makes employs Company Product name category stockprice name price

Key Constraints: 

Key Constraints Many-to-Many 1-to-1 1-to Many Many-to-1 lot dname budget did since name Works_In Departments Employees ssn

SQL: Structured Query Language (‘Sequel’): 

SQL: Structured Query Language (‘Sequel’)

Relational Query Languages (SQL): 

Relational Query Languages (SQL) Structured Query Language (SQL) is the most widely used commercial relational database language. Developed by IBM (system R) in the 1970s Need for a standard since it is used by many vendors Standards: SQL-86 SQL-89 (minor revision) SQL-92 (major revision) SQL-99 (major extensions, current standard)

Relational Query Languages (SQL): 

Relational Query Languages (SQL) A major strength of the relational model: supports simple, powerful querying of data. Queries can be written intuitively, and the DBMS is responsible for efficient evaluation. The key: precise semantics for relational queries. Allows the optimizer to extensively re-order operations, and still ensure that the answer does not change. SQL = DDL + DML + ……

Slide 15: 

Data Definition Language (DDL) CREATE, ALTER, DROP, TRUNCATE, RENAME Data Manipulation Language (DML) INSERT, UPDATE, DELETE

Slide 16: 

Data Control Language (DCL) GRANT, REVOCE Transaction Control Language (TCL) GRANT, REVOCE Queries SELECT

Slide 17: 

CREATE TABLE <Tablename> (column1, datatype, column2 datatype); Create table EMPLOYEE (Empno Number, Empname Char(10), Doj Date); Table created