Introduction to Database Database is a collection of information in a structured way. We can say that it is a collection of a group of facts and processed information’s. Databases store information in fields, records, and files. A field is a single piece of information—a customer’s first name, for example. A record is one complete set of fields—the customer’s first name, last name, address, phone number, and account information. And a file is a collection of records—a complete list of all customers. File System The file system that comes with your computer is a very primitive kind of database management system. Data are kept in big unstructured named clumps called files. A flat file database is a database designed around a single table. The flat file design puts all database information in one table, or list, with fields to represent all parameters. A flat file may contain many fields, often, with duplicate data that are prone to data corruption. If you decide to merge data between two flat files, you need to copy and paste relevant information from one file to the other. There is no automation between flat files. Database Management System (DBMS) To use information contained in a database, you need a Database Management System (DBMS), a collection of software programs that let you to enter, organize, and select information stored in the database. Each column in a database table contains a different type of attribute and each row corresponds to a single record. For example, in a table of customers, the columns might include name, address, phone number, and account information; each row is a separate customer. The most typical DBMS is a Relational Database Management System (RDBMS). An RDBMS stores information in tables of rows and columns. The relationships between table data can be collated, merged and displayed in database forms. Most relational databases offer functionality to share data across networks over the Internet DBMS vs. File Systems Databases management systems (DBMS's -- Oracle, SQL,) have certain features that you can't get with a flat file. 1. Speed. Modern DBMS's have very advanced routines for getting you data as quickly as possible. 2. Management of relational entities. DBMS's are good at storing data about multiple, related entities. 3. Transaction and update guarantees. DBMS's allow you to protect your data -back it up, be able to roll back unwanted transactions, ensure that a set of operations on data executes atomically, etc. 4. Data Security. Database provide different level of securities by having password to access different level of database which is not provided with file systems 5. Data Administration. You can administrate the values being entered in the database by having various constraints on the data field. For example, you may allow only numeric fields in salary field. 6. Concurrent Access. Multiple users can access the database at a same time and by using lock system u can manage the updating of the database on the same field. 7. Crash recovery. In case your database crashes, it has a special mechanism by using you can recover your database on the last saved state. Advantages & Disadvantages Advantages 1. Data independence - application programs are insulated from changes in the way data is structured and stored which allows dynamic changes and provides growth potential. 2. Efficient data access 3. Data administration - centralized 4. Data integrity & security 5. Concurrent access and crash recovery 6. Reduced application development time Disadvantages 7. Problems associated with centralization 8. Cost of software/hardware and migration 9. Complexity of backup and recovery In reality, centralized databases are applicable only to small operations. Companies are bought, sold, and merge often necessitating interaction between distributed databases. An enterprise database is constructed from the distributed databases. Components of DBMS The components of a database system include: Data: Database: data that is stored more-or-less permanently in a computer. Database management system (DBMS): software which allows the user to use or modify the database. DBMS Facilities o Data definition language (DDL): used to define the conceptual scheme. The scheme is compiled and stored in the data dictionary. o Data manipulation language (DML): query sublanguage (retrieval), maintenance sublanguage (insertion, deletion, modification). Structure of DBMS o DDL compiler: Compiles conceptual schemes to tables stored in the data dictionary. o Database manager: translates query into file operations o Query Processor: o File manager: often a general purpose file system provided by the operating system. o Disk manager o o o o Telecommunication system Data files Data dictionary: structure and usage of data contained in the database. Access aids: indexes Database application programmers: develop programs or interfaces for naive and online users which are precompiled queries. Database implementers Database administrator (DBA): oversee and manage resources Design of the conceptual and physical schemas Security and authorization: Data availability and recovery from failures - backups and repairing damage due to hardware or software failures or misuse. Database tuning: performance and database evolution Database Designers End users Casual end users Naive or parametric end users Sophisticated end users Stand-alone users Users: o o o o o Data Model A data models is a collection of concepts that can be used to describe the structure of a database. By structure of a database we mean the data types relationships and constrains that should hold on the data. The following are the various types of data models used: Semantic Data model Firstly we should be clear on what both terms means: Semantics – way of linking the entity and the information we have about the data to help us get a good understanding of the system Data Model – Data models are made up of: 1) Structures used to create the model (building blocks) 2) Operators we can use on the structures 3) Integrity rules, both explicit and implicit A Widely used semantic data model is called as the Entity Relationship (ER) model. Relational Model The Relation is the basic element in a relational data model. A relation is subject to the following rules: 1. 2. 3. 4. 5. 6. 7. 8. Relation (file, table) is a two-dimensional table. Attribute (i.e. field or data item) is a column in the table. Each column in the table has a unique name within that table. Each column is homogeneous. Thus the entries in any column are all of the same type (e.g. age, name, employee-number, etc). A Tuples (i.e. record) is a row in the table. The order of the rows and columns is not important. Values of a row all relate to some thing or portion of a thing. Duplicate rows are not allowed (candidate keys are designed to prevent this). DBMS Architecture The three levels of the architecture are three different views of the data: 1. Internal - physical or storage view The internal view is the view about the actual physical storage of data. It tells us what data is stored in the database and how. At least the following aspects are considered at this level: Storage allocation. e.g. B-trees, hashing and etc. Access paths e.g. specification of primary and secondary keys, indexes and pointers and sequencing. Miscellaneous e.g. Data compression and encryption techniques, optimization of the internal structures. 2. Conceptual - community user view The conceptual view is the information model of the enterprise and contains the view of the whole enterprise without any concern for the physical implementation. The conceptual view is the overall community view of the database and it includes all the information that is going to be represented in the database. The conceptual view is defined by the conceptual schema which includes definitions of each of the various types of data. 3. External - individual user view The external level is the view that the individual user of the database has. The same database may provide a number of different views for different classes of users. In general, the end users and even the applications programmers are only interested in a subset of the database. For example, a department head may only be interested in the departmental finances and student enrolments but not the library information. The librarian would not be expected to have any interest in the information about academic staff. The payroll office would have no interest in student enrolments. Queries Queries are the primary mechanism for retrieving information from a database and consist of questions presented to the database in a predefined format. Many database management systems use the Structured Query Language (SQL) standard query format. SQL is standard computer language for accessing and manipulating database systems. SQL statements are used to retrieve and update data in a database. SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc. SQL Data Definition Language (DDL) The Data Definition Language (DDL) part of SQL permits database tables to be created or deleted. We can also define indexes (keys), specify links between tables, and impose constraints between database tables. The most important DDL statements in SQL are: CREATE TABLE - creates a new database table ALTER TABLE - alters (changes) a database table DROP TABLE - deletes a database table SQL Data Manipulation Language (DML) SQL (Structured Query Language) is syntax for executing queries. But the SQL language also includes syntax to update, insert, and delete records. These query and update commands together form the Data Manipulation Language (DML) part of SQL: SELECT - extracts data from a database table UPDATE - updates data in a database table DELETE - deletes data from a database table INSERT INTO - inserts new data into a database table
"Introduction to Database"