DBS - REVIEWER.pdf
Document Details
Uploaded by HandsDownVoice
Bulacan State University
Full Transcript
LESSON 1 - Introduction to Database OBJECT - It can be a photo, audio, video, or a Management Systems document created in other programs and stored as a bitmap in the database. DATABA...
LESSON 1 - Introduction to Database OBJECT - It can be a photo, audio, video, or a Management Systems document created in other programs and stored as a bitmap in the database. DATABASE - It is a collection of data organized in a manner that allows access, retrieval, and use of that ATTACHMENT - It can be a document or image that is data. attached to the field, which can be opened in the program that created the document or image DATA - It is a collection of unprocessed items, which (functioning similarly to e-mail attachments). can include text, numbers, images, audio, and video. RECORDS - It is a group of related fields. For example, INFORMATION - It is processed data; that is, it is a student record includes a set of fields about one organized, meaningful, and useful. student. A primary key is a field that uniquely - It is a facts provided or learned about identifies each record in a file. In some tables, the something or someone. primary key consists of multiple fields called a composite key. HIERARCHY OF DATA - Data is organized in layers. FILES - It is a collection of related records stored on a - In the computer profession, data is classified storage medium such as a hard disk or optical disc. in a hierarchy. - Each higher level of data consists of one or - A database includes a group of related data more items from the lower level. files. - Depending on the application and the user, DATA INTEGRITY - It is also known as Garbage in, different terms describe the various levels of garbage out (GIGO). the hierarchy - Bit, Byte, Field, Record, Table, Database, - It is a computing phrase that points out the DBMS accuracy of a computer’s output depends on the accuracy of the input CHARACTER - In the American Standard Code for Information Interchange (ASCII) coding scheme, each byte represents a single character, which can be a number, letter, space, punctuation mark, or other QUALITIES OF VALUABLE INFORMATION symbols. 1. Accurate information is error-free. FIELDS - It is a combination of one or more related 2. Verifiable information can be proven as characters or bytes and is the smallest unit of data a correct or incorrect. user access. 3. Timely information has an age suited to its use. - A field name uniquely identifies each field. 4. Organized information is arranged to suit the - The field size defines the maximum number needs and requirements of the decision- of characters a field can contain. maker. 5. Accessible information is available when the DATA TYPE - Specifies the kind of data a field can decision-maker needs it. contain and how the field is used 6. Useful information has meaning to the person who receives it. 7. Cost-effective information should give more COMMON DATA TYPES value than it costs to produce. TEXT - It also called alphanumeric letters, numbers, or special characters. ACID properties of database NUMBER - It also numbers only. ATOMICITY - In database ensures that the AUTO NUMBER - It is a unique number automatically transactions are indivisible and irreducible where assigned by the DBMS to each added record transactions either commit or abort. CURRENCY - It is dollar and cent amounts or numbers CONSISTENCY - Any change in the values of a containing decimal values. database at a particular instance is consistent with DATE - It also called date/time. changes to other values. (date) MONTH DAY YEAR ISOLATION - Transaction in database ensures that the working transaction will not be changed or affected (time) HOURS MINUTE SECONDS by any other transaction. MEMO - It is lengthy text entries DURABILITY - It is the databases states that “once a transaction has been committed, should remain in the YES / NO - It only the values Yes or No (or True or same status” even in the case of failures such as False) Power loss, Database crash, and etc. HYPERLINK - It is an E-mail address or Web address THE OBJECTIVE OF DBMS that links to a Web page on the Internet or document on a local network. Provide for mass storage of 12. Increased concurrency: Many DBMSs relevant data manage concurrent database access, Making easy access to data for the ensuring that two or more users are allowed authorized user. Providing a to access the same file simultaneously, prompt response to user’s requests without interfering with each other, or for data. resulting in loss of information or its Eliminate redundantly (Duplicate) integrity. the data. 13. Improved backup and recovery services: Allow multiple users to be active at modern DBMSs provide facilities to minimize one time. the amount of processing that is lost Allow the growth of database following a failure. system Provide data integrity. Disadvantages of Electronic Databases over Manual Protect the data from physical databases harm and unauthorized access. Serving different types of the users. Complexity - The provision of the functionality we Provide security with a user access expect of a good database makes it complex to set up. privilege. - Failure to understand the system can lead to bad Combining interrelated data to design decisions, which can have serious generate a report consequences for an organization. Provide multiple views for the same data Cost of setting up a database - The cost of setting up an electronic database varies significantly, depending MANUAL DATABASE - It is one that is not on the hardware, software, and functionality computerized. required. The need for conversion & difficult transition ELECTRONIC DATABASE - It is one that is computerized and can be accessed/manipulated - This includes the cost of converting existing using computer applications. applications to run on the new DBMS and hardware plus the cost of training staff to use these new systems, and possibly the Advantages of Electronic Databases over Manual employment of specialist staff to help with databases the conversion and running of the system 1. Electronic databases store very large Performance - It is a file-based system is written for a amounts of data. specific application, such as invoicing. As a result, 2. Electronic databases allow easy input and performance is generally very good. editing of data. - The DBMS is written to be more general, to 3. Electronic databases enable automatic cater to many applications rather than just updating and recalculating of data. one. The effect is that some applications 4. Electronic databases make it easier to query, may not run as fast as they used to. search, filter, and retrieve required data. 5. Electronic databases format, arrange and Higher impact of a failure - The centralization of present information in customizable ways. resources increases the vulnerability of the system. 6. Electronic databases can easily share the Since all users and applications rely on the availability information with other software of the DBMS, the failure of certain components can applications/programs. bring all operations to a halt. 7. Electronic databases allow centralized use of information amongst many users over a DATABASE MANAGEMENT SYSTEM - It is a software network and therefore reduce duplication, program used to create and manage an electronic e.g in banks. database. 8. Data is validated before it is entered into - It provides users with tools used to add, electronic databases. Errors created during delete, access, modify, and analyze data data entry are minimized stored in one location. 9. Many built-in functions are available in Electronic databases to simplify calculations. FEATURES OF DBMS 10. Improved security: Database security is the protection of the database from 1. Logical Data structures / Objects – such as unauthorized users. This may take the form tables, forms, queries, and reports, used to of user names and passwords to identify store and manipulate structured data. people authorized to use the database. 2. A query language (such as SQL) is used to 11. Economy of scale: Combining all the manipulate or extract data. organization’s operational data into one 3. Filter Commands- that display data which database, and creating a set of applications satisfy certain conditions. that work on this one source of data, can result in cost savings. 4. Data validation commands: that ensure the Fits the needs of many small integrity of data entered and stored in the businesses and home users database. Popular FMS’s are packaged along 5. Relationships/associations between data with the operating systems of objects/ tables. personal computers (i.e. Microsoft 6. Sort commands/tools that arrange data Card-file and Microsoft Works) values in a certain order. Good for database solutions for 7. Import/Export commands enable sharing of handheld devices such as Palm data between the database program and Pilot, Microsoft Card-file, and other programs that use similarly structured Microsoft Works Palm Pilot data. Disadvantages of FMS’s 8. Built-in functions (similar to those available in spreadsheet applications) that simply Typically, does not support multi- calculate. user access Limited to smaller databases LESSON 2 – The use of DBMS and the Database Limited functionality Database Management System - DBMS - It is a Decentralization of data combination of computer software, hardware, and Redundancy and Integrity issues information designed to electronically manipulate DATA MANAGEMENT - It is the practice of collecting, data via computer processing. keeping, and using data securely, efficiently, and cost- Two Types of Database Management Systems effectively. 1. DBMS’s - It also known as Database Management Validity - An FMS should guarantee that at any given Systems. moment the stored data reflect the operations performed on them. - It allows access to multiple files or tables at a time. Protection - Illegal or potentially dangerous operations on the data should be controlled by the Advantages of DBMS’s FMS. Greater flexibility Concurrency - In multiprogramming systems, Good for larger databases concurrent access to the data should be allowed with Greater processing power minimal differences. Fits the needs of many medium to large-sized organizations Performance - Compromise data access speed and Storage for all relevant data data transfer rate with functionality. Provides user views relevant to DBMS Concepts - DBMS solutions come equipped tasks performed with a unique set of components, each responsible for Ensures data integrity by managing performing different tasks. transactions (ACID test) Supports simultaneous access Basic DBMS Components Enforces design criteria in relation to data format and structure 1. Software - A DBMS is a software-based system that Provides backup and recovery provides a management interface, which helps users controls control databases and data sources. Advanced security 2. Data - DBMS provides controls for managing Disadvantages of DBMS’s operational data, such as records and metadata, as well as index files, data dictionaries, and Difficult to learn administrative information. Packaged separately from the operating system (i.e., Oracle, 3. Procedures - It is a documents that standardize Microsoft Access, Lotus/IBM database management, used as guidelines by Approach, Borland Paradox, Claris employees and users, and as automation policies. FileMaker Pro) 4. Database Languages - DBMS use various languages Requires skilled administrators to perform tasks such as controlling user access and Expensive specifying database schema. 1. Data Manipulation Language (DML) 2. FMS’s - It also known as File Management Systems. 2. Data Definition Language (DDL) - It is a Database Management System that 3. Data Control Language (DCL) allows access to single files or tables at a time. 4. Database Access Language (DAL) Advantages of FMS’s 5. Query processor - It serves as a communication intermediary between users and the DBMS data Simpler to use engine. Less expensive 6. Runtime Database Manager - It enables DBMS to SQL - It is also known as Structured Query Language. centralize management of runtime data. It is a computer language that closely - A runtime database manager resembles English, but that database validates user authorizations, processes approved programs understand. queries, determines which strategy provides optimal SQL to describe sets of data that can help you query results, ensures data integrity, and handles any answer questions. task that requires handling query and runtime data. It use the correct syntax. Syntax is the set of rules by which the 7. Database manager - It can handles database jobs elements of a language are correctly and enables administrators to perform database combined. operations and maintenance tasks, including data SELECT Last_Name backup and restore, cloning, deleting, updating the FROM Costumers database, and executing patches. WHERE First_Name = ‘John Doe’ 8. Database Engine - It performs the main data Select Statement - It contains a complete description storage and retrieval tasks. of a set of data that you want to obtain from a - A database engine can be built into the database. DBMS software or as a remote resource accessed via SQL Select All - To select all columns from a database an API table, we use the asterisk (*) character. - Application Programming Interface. SQL Clause - Like a sentence, a SQL statement has 9. Report Generator - It enables users to extract clauses. DBMS files and display the information in structured - Each clause performs a function for the SQL formats, according to predefined specifications. statement. - Report generation processes help - Some clauses are required in a SELECT users perform analyses and derive actionable insights statement. Data Redundancy - It is the repetition of data. SQL Clauses - It is a common issue in computer data SELECT - It is a statement is used to select storage and database systems. data from a database table. - In a database means that some data fields - Lists the fields that contain data are repeated in the database. of interest Disadvantages of Data Redundancy FROM - Lists the tables that contain the Increases the size of the database fields listed in the SELECT clause. unnecessarily. Causes data inconsistency. WHERE - Specifies field criteria that must be met by each record to be included in the Decreases efficiency of the database. results. May cause data corruption. ORDER BY - Specifies how to sort the results. Types of Database Models GROUP BY - In a SQL statement that 1. Hierarchical Database Model - It can organize data contains aggregate functions, lists fields that into a tree-like structure, where each record has a are not summarized in the SELECT clause. single parent or root. - In SQL, we use the GROUP BY 2. Relational Model - It sorts data into tables, also clause to group rows based on the value of known as relations, each of which consists of columns columns. and rows. HAVING - The SQL HAVING clause is used if - Each column lists an attribute of the entity in we need to filter the result set based on question, such as price, zip code, or birth date. aggregate functions such as MIN() and MAX(), SUM() and AVG(), and COUNT(). - Together, the attributes in a relation are called a - In a SQL statement that contains domain. aggregate functions, specifies conditions - A particular attribute or combination of attributes is that apply to fields that are summarized in chosen as a primary key that can be referred to in the SELECT statement. other tables when it’s called a foreign key. SQL Select Where Clause - It can allow us to fetch 3. Network Model - It builds on the hierarchical model records from a database table that matches specified by allowing many-to-many relationships between condition(s) linked records, implying multiple parent records.