Download
1 / 14
140 likes | 157 Views
A database system is a vital computer-based record-keeping system for enterprises, offering an organized collection of interrelated data for multiple applications. Learn about relational database models, DBMS technology, relational model terms, MySQL features, and the importance of data integrity.
E N D
INTRODUCTION A Database system is basically a computer based record keeping system. The collection of data, usually referred to as the database, contains information about one particular enterprises. It maintains any information that may be necessary to the decision making processes involved in the management of that organization. A database may also be defined as a collection of interrelated data stored together to serve multiple applications: the data is such stored so that it is a independent of programs which use the data ; a common and controlled approach is used in adding new data and in modifying and retrieving existing data within the database.
DATABASE A database may be defined as a collection of interrelated data stored together to serve multiple applications. • PURPOSE OF DBMS (DATABASE MANAGEMENT SYSTEM) A database system should be a repository of the data needed for an organisation’s data processing. That data should be accurate, private , and protected from damage. It should be organised so that diverse applications with different data requirements can employ the data. DBMS (DATABASE MANAGEMENT SYSTEM) A DBMS refers to a software that is responsible for storing , maintaining and utilizing databases . A database along with a DBMS is referred to as a database system. • DATA REDUNDANCY Duplication of data is known as data redundancy.
RELATIONAL DATABASE MODEL In a relational data model, the data is organised into tables. These tables are called relations. A row in a table represents a relationship among a set of values. Examples of common relational database management syste:- Oracle database, microsoft SQL Server, MYSQL, IBM DB2, SAP Sybase adaptive server Enterprise, SQLite etc. • THE RELATIONAL MODEL TERMINOLOGY The relational model was propounded by E.FCodd of the IBM and has since been acknowledged as a very important concept in DBMS technology . RELATION: a relation is a table , data arranged in rows and columns. A relation has the following properties
In any given column of a table, all items are of the same kind whereas items in different columns may not be of the same kind. • For a row, each column must have an atomic (indivisible) value and also for a row, a column cannot have more than one value. • All rows of a relation are distinct. That is, a relation does not contain two rows which are identical in every column. That is, a relation does not contain two rows which are identical in every column. That is, each row of the relation can be uniquely identified by its contents. A crucial feature of relational data model is that associations between rows are represented solely by data values in columns drawn from a common domain. TUPLE:- The rows of the table are generally referred to as Tuples. ATTRIBUTES:- The columns of tables are generally refered to as Attributes DEGREE:- The number of attributes in a relation is called Degree. CARDINALITY:- The number of rows in a relation is called Cardianlity. VIEWS :- A View is a (virtual) table that does not really exist in its own right but is instead derived from one or more underlying base tables.
KEYS • PRIMARY KEY A Primary Key is a set of one or more attributes that can uniquely identify tuples within the relation. • CANDIDATE KEY All attribute combinations inside a relation that can serve as primary key are Candidate Keys as they are candidates for the primary key position. • ALTERNATE KEY A candidate key that is not the primary key is called the Alternate Key. • FOREIGN KEY A non-key attribute, whose values are derived from the primary key of some other table, is known as Foreign Key in its current table. • REFERENTIAL INTEGRITY Referential integrity is a system of rules that a DBMS uses to ensure that relationships between records in related tables are valid, and that users don’t accidentally delete or change related data.
MYSQL • MySQL is a free, open-source Relational Database Management System. • A MySQl database system consists of a MySQL server instance and a MySQL database. • A single MySQL database can contain many tables at once and store thousands of individual records. • MySQL clients are programs that connect to the MySQL server and issue queries in a pre-specified format. MySQL is compatible with the standards based SQL (Structured Query Language) . The client program may contact the server programmatically or manually.
Some of the key features of MySQL are: • Speed • Ease of use • Free of cost • Query Language Support • Portability • Data Types • Security • Scalability and Limits • Connectivity • Localization • Clients and Tools • DATA DICTIONARY :- A Data Dictionary is a file that contains “metadata” i.e., ”data about data”.
Processing capabilities of SQL • DATA DEFINITION LANGUAGE (DDL):- The SQL DDL provides commands for defining relation schemas, deleting relations, creating indexes, and modifying relation schemas. The DDL provides a set of definitions to specify the storage structure and access methods used by the database system. • INTERACTIVE DATA MANIPULATION LANGUAGE (DML) :- The SQL DML includes a query language based on both the relational algebra and the tuple relational calculus. It includes also commands to insert, delete, and modify tuples in the database. • EMBEDDED DATA MANIPULATION LANGUAGE:- • VIEW DEFINITION. • AUTHORIZATION. • INTEGRITY. • TRANSACTION CONTROL.
DDL COMMANDS The Data Definition Language (DDL) commands , as the name suggests, allow you to perform tasks related to data definition. A Data Manipulation Language (DML) is a language that enables users to access or manipulate data as organized by the appropiate data model. That is, through these commands, you can perform tasks like:- • Create , alter and drop schema objects. • Grant (to give permission) and Revoke(to take permission) privileges and roles. • Maintenance commands.
By data manipulation, we mean • The retrieval of information stored in the database. • The insertion of new information into the database. • The deletion of information from the database. • The modification of data stored in the database. TCL COMMANDS A transaction is successfully completed (known as COMMIT) if and only if all its constituent steps are successfully completed. To manage and control the transactions, the transaction control commands are used. COMMIT ---- it makes all the changes made by statements issued, permanent. ROLLBACK ---- it undoes all changes since the beginning of a transaction or since a savepoint SAVEPOINT ---- it marks a point upto which all earlier statements have been successfully completed and if required – in case of failure –one may undo the changes i.e., rollback up to this very point. SET TRANSACTION ---- it establishes properties for the current transactions.
Some important questions • What is database system ? What is its need ? A Database is a collection of interrelated data and a database system is basically a computer based recordkeeping system. • What is SQL ? What are different categories of commands available in SQL? In order to access data within the Oracle database, all programs and users must use, Structured Query Language (SQL). SQL is the set of commands that is recognised by nearly all RDBMSs. 1. DATA DEFINITION LANGUAGE 2. DATA MANIPULATION LANGUAGE 3. TRANSACTION CONTROL LANGUAGE 4. SESSION CONTROL COMMANDS 5. SYSTEM CONTOL COMMANDS
Differentiate between DDL and DML commands. The Data Definiton Language (DDL) commands, as the name suggests, allow you to perform tasks related to data definition . That is, through these commands, youy can perform tasks like, create, alter and drop schema objects, grant and revoke privileges. The Data Manipulation Language (DML) commands , as the name suggests, are used to manipulate data. That is, DML commands query and manipulate data in existing schema objects.
