MAM RANDIL.pdf

NATIONAL COLLEGE OF SCIENCE & School of TECHNOLOGY Nation Builders COMPUTER STUDIES DEPARTMENT INFORMATION IT 201 MANAGEMENT 1 Prelim: Week 1 – INTRODUCTION TO D...

NATIONAL COLLEGE OF SCIENCE & School of TECHNOLOGY Nation Builders COMPUTER STUDIES DEPARTMENT INFORMATION IT 201 MANAGEMENT 1 Prelim: Week 1 – INTRODUCTION TO DATABASE Prepared by:Danielle Dino  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite DATA is a raw and unorganized fact that required to be processed to make it meaningful. Generally, data comprises facts, observations, perceptions numbers, characters, symbols, image, etc.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT INFORMATION is defined as refined or processed data that has been transformed into meaningful and useful form for specific users.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Database ❑Any collection of related ❑Databases can be stored in information different ways oPhone Book oOn paper oShopping list oIn your mind oTodo list oOn a computer oYour 5 best friends oThis powerpoint oFacebook’s User Base oComments Section A database is a structured collection of data organized for efficient storage, retrieval, and management. It can be as simple as a single file or a complex system with multiple interconnected tables.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT File System A file system is a process that manages how and where data on a storage disk, typically a hard disk drive (HDD), is stored, accessed and managed. Drawback (disadvantage) of using file systems to store data: ▸ Data redundancy and inconsistency ▸ Difficulty in accessing data ▸ Data isolation ▸ Integrity problems ▸ Atomicity of updates ▸ Concurrent access by multiple users ▸ Security problems  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Database System consists of database (DB) + database Management System (DBMS) + application program Or is just a computerized record keeping system. Database is a repository for a collection of computerized data files. Components of Database System ▸ Data ▸ Hardware ▸ Software ▸ User  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Table The data in the database is stored in database objects which are called as tables. This table is basically a collection of related data entries and it consists of columns and rows.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Field Every table is broken up into smaller entities called fields. The fields in the CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY. A field is a column in a table that is designed to maintain specific information about every record in the table Record or Row A record, also called a row of data, is each individual entry that exists in a table. Following is a single row of data or record in the CUSTOMERS table  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Column A column is a vertical entity in a table that contains all information associated with a specific field in a table. For example, a column in the CUSTOMERS table is ADDRESS, which represents location description and would consist of the following: THANK YOU! QUIZ 1  1. It is data that has been transformed into meaningful and useful form for specific users.  2. Generally, data comprises facts, observations, perceptions numbers, characters, symbols, image, etc.  3. It comprises facts, observations, perceptions numbers, characters, symbols, image, etc. 4-6. GIVE 3 DISADVANTAGE / DRAWBACKS OF USING FILE SYSTEM TO STORE DATA 7. Every table is broken up into smaller entities called -----.  8. is a process that manages how and where data on a storage disk, 9. It is also called a row of data 10. Any collection of related information  NATIONAL COLLEGE OF SCIENCE & School of TECHNOLOGY Nation Builders COMPUTER STUDIES DEPARTMENT INFORMATION IT 201 MANAGEMENT 1 Prelim: Week 2 – INTRODUCTION TO DATABASE Prepared by: DANIELLE DINO Types of Databases  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Four main types of Databases ▪ Hierarchical Database ▪ Network Database ▪ Relational Database ▪ Object Relational Database  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Hierarchical Database ▸ As its name implies, the Hierarchical Database Model defines hierarchically-arranged data. ▸ The most intuitive way to visualize this type of relationship is by visualizing an upside down tree of data. In this tree, a single table acts as the "root" of the database from which other tables "branch" out. ▸ Relationships in such a system are thought of in terms of children and parents such that a child may only have one parent but a parent can have multiple children.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Hierarchical Database ▸ Parents and children are tied together by links called "pointers" (perhaps physical addresses inside the file system). ▸ A parent will have a list of pointers to each of their children.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Hierarchical Database Problems: ▸ When a user needs to store a record in a child table that is currently unrelated to any record in a parent table, it gets difficulty in recording and user must record an additional entry in the parent table. ▸ This type of database cannot support complex relationships, and there is also a problem of redundancy, which can result in producing inaccurate information due to the inconsistent recording of data at various sites.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Network Database ▸ The network database model was created to solve the shortcomings of the hierarchical database model. ▸ In this type of model, a child can be linked to multiple parents, a feature that was not supported by the hierarchical data model. ▸ The parent nodes are known as owners and the child nodes are called members.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Network Database Problems: There are some disadvantages in the network model even though it is an improvement over the hierarchical model. These are − ▸ The network model is much more complicated than the Hierarchical model. As such, it is difficult to handle and maintain. ▸ Although the Network model is more flexible than the Hierarchical model, it still has flexibility problems. Not all relations can handled by assigning them in the form of owners and members. ▸ The structure of the Network Model is quite complicated and so the programmer has to understand it well in order to implement or modify it.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Relational Database ▸ This database is based on the relational data model, which stores data in the form of rows(tuple) and columns(attributes), and together forms a table(relation). ▸ A relational database uses SQL for storing, manipulating, as well as maintaining the data. E.F. Codd invented the database in 1970. ▸ Each table in the database carries a key that makes the data unique from others. ▸ Examples of Relational databases are MySQL, Microsoft SQL Server, Oracle, etc.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Object-Relational Database ▸ An Object relational model is a combination of a Object oriented database model and a Relational database model. ▸ So, it supports objects, classes, inheritance etc. just like Object Oriented models and has support for data types, tabular structures etc. like Relational data model. ▸ One of the major goals of Object relational data model is to close the gap between relational databases and the object oriented practices frequently used in many programming languages such as C++, C#, Java etc.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT The Range of Database  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT ▸ Personal Database PCs/PDAs, Cellphones – OK in special situations where need to share data amongst users is unlikely to arise ▸ Workgroup Database - Designed to support collaboration in a small team (less than 25 people) ▸ Department Database - typically larger than a workgroup (25-100 people) and more diverse range of functions – e.g. personnel database  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT ▸ Enterprise Database – scope of the whole organization. May be more than one, as a single database for a large organization may be impractical due to performance difficulties for large databases, diverse needs of user groups, and difficulty of achieving common definition of data (metadata) for all users. Views of Database  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT View of Data ▪ A database contains a no. of files and certain programs to access and modify these files. ▪ But the actual data is not shown to the user, the system hides the actual details of how data is stored and maintained. Data Abstraction ▪ It is a mechanism to hide complexity of database in database system. It allows database system to provides abstract view of database user. It hides how data are actually stored and maintain in database.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Data abstraction simplifies users interactions with the system, and it has three levels:  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Levels of Data Abstraction Internal/Physical Level ▸ the lowest level of abstraction describes how a record (e.g., customer) is stored. Conceptual/Logical Level ▸ Defines all database entities, their attributes, and their relationships ▸ Security and integrity information ▸ In the conceptual level, the data available to a user must be contained in or derivable from the physical level  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Levels of Data Abstraction External/View Level ▸ the highest level of abstraction only part of entire database. ▸ it simplifies interaction with the system ▸ it allows database system to provide many views for the same database. ▸ Application program hide details of data types ▸ Views can also hide information (such as employee’s salary) security purposes.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Database Terminology Instance ▸ refers to a single occurrence of a database running on a database server. ▸ database change as information is inserted, deleted and updated. ▸ The collection of information stored in the database at particular moment is called an instance of the database. 22 Database Terminology Schema ▸ refers to the organization or structure of a database. ▸ It defines how data is organized and how relationships among data elements are handled. ▸ A database schema includes tables, fields, relationships, and constraints. ▸ Schema is of three types: Physical schema, logical schema and view schema. 23 THANK YOU!  NATIONAL COLLEGE OF SCIENCE & School of TECHNOLOGY Nation Builders COMPUTER STUDIES DEPARTMENT INFORMATION IT 201 MANAGEMENT 1 Prelim: Week 3 – Database Languages Prepared by: DANIELLE DINO Database Languages  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite DATABASE LANGUAGES ▸ Data Query Language (DQL) ▹ Used to query the database for information. ▹ Get information that is already stored. ▸ Data Definition Language (DDL) ▹ Used for defining database schemas ▸ Data Control Language (DCL) ▹ Used for controlling access to the data in database. ▹ User & permission management ▸ Data Manipulation Language (DML) ▹ Used for inserting, updating and deleting data from the database ▸ Data Query Language (DQL) Command Description SELECT Retrieves certain records from one or more tables ▸ Data Definition Language (DDL) Command Description CREATE Creates a new table, a view of a table, or other object in database ALTER Modifies an existing database object, such as a table. DROP Deletes an entire table, a view of a table or other object in the database. ▸ Data Control Language (DCL) Command Description GRANT Gives a privilege to user REVOKE Takes back privileges granted from user ▸ Data Manipulation Language (DML) Command Description INSERT Creates a record UPDATE Modifies records DELETE Deletes records 4  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Data Definition Language (DDL) Use to create (completely define) a database, modify its structure, and destroy it when you no longer need it. ▸ Create, alter, and drop schema objects and other database structures, including the database itself and database users. Most DDL statements start with the keywords CREATE, ALTER, or DROP. ▸ Use to create, change, or destroy the basic elements of a relational database. Basic elements include tables, views, schemas, catalogs, clusters, and possibly other things as well. ▸ Delete all the data in schema objects without removing the structure of these objects (TRUNCATE).  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Data Manipulation Language (DML) You can specify what you want to do with the data in your database – enter it, change it, remove it, or retrieve it. ▸ The DML statements you can use are INSERT, UPDATE, DELETE, MERGE, and SELECT. These statements can consist of a variety of parts, including multiple clauses. For example, ALTER TABLE changes the structure of a table, whereas INSERT adds one or more rows to the table. ▸ Each clause may incorporate value expressions, logical connectives, predicates, aggregate functions, and subqueries. DML statements enable you to query or change the contents.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT DML statements are the most frequently used SQL statements and enable you to: ▸ Add new rows of data into a table or view (INSERT) by specifying a list of column values or using a sub query to select and manipulate existing data. ▸ Change column values in existing rows of a table or view (UPDATE). ▸ Update or insert rows conditionally into a table or view (MERGE). ▸ Remove rows from tables or views (DELETE). ▸ Retrieve or fetch data from one or more tables or views (SELECT).  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite COMPUTER STUDIES DEPARTMENT Data Control Language (DCL) The DCL provides security for your database; the amount of protection depends on the implementation. ▸ The Data Control Language (DCL) has four commands: COMMIT, ROLLBACK, GRANT, and REVOKE. These commands protect the database from harm, both accidental and intentional. ▸ Transaction control statements manage the changes made by DML statements and group DML statements into transactions. THANK YOU!  NATIONAL COLLEGE OF SCIENCE & School of TECHNOLOGY Nation Builders COMPUTER STUDIES DEPARTMENT INFORMATION IT 201 MANAGEMENT 1 Prelim: Week 4 – Database Management System Prepared by: DANIELLE DINO Database Management Systems (DBMS)  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Database Management Systems (DBMS) A special software program that helps users create and maintain a database (on a computer) ▹ Make it easy to manage large amounts of information ▹ Handles Security ▹ Backups ▹ Importing/exporting data ▹ Concurrency ▹ Interacts with software applications ▪ Programming Language  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Database Management Systems (DBMS) ▪ Functionalities: Define: Specifying the data type, structure and constraints for the data to be stored. Construct: Process of storing data on some storage medium Manipulate: Querying the database to retrieve specific data, updating database and generating reports. Share: Allow multiple users and programs to access the database simultaneously.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Example of DBMS are: ▸ MySQL ▸ PostgreSQL ▸ Microsoft Access ▸ SQL Server ▸ FileMaker ▸ Oracle ▸ dBASE ▸ Sybase  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Objectives of DBMS ▸ To provide large space or storage for relevant data ▸ To provide easy access to the data for the users. ▸ To provide quick response to user request for any data. ▸ To remove duplicate (redundant) data. ▸ To update the database latest modification immediately. ▸ To allow the multiple users to be active at one time. ▸ As the organization grows, DBMS allows the growth of the database system. ▸ To provide maximum protection to data from any physical damage and unauthorized access.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Advantages of DBMS ▪ Redundancy problem can be solved. ▪ Has a very high security level. ▪ Support multiple users. ▪ Shared data ▪ Enforcement of standards ▪ Provide backup of data  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite Disadvantages of DBMS ▸ Complexity ▸ Size ▸ Maintaining currency ▸ Higher impact of a failure ▸ Cost of DBMS System Development Life Cycle  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite System Development Life Cycle ▸ From concept to production, you can develop a database by using the system development life cycle, which contains multiple stages of development. This top-down, systematic approach to database development transforms business information requirements into an operational database.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite System Development Life Cycle 1. Strategy and Analysis Study and analyze the business requirements. Interview users and managers to identify the information requirements. Incorporate the enterprise and application mission statements as well as any future system specifications. Build models of the system. Transfer the business narrative into a graphical representation of business information needs and rules. Confirm and refine the model with the analysts and experts.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite System Development Life Cycle 2. Design Design the database based on the model developed in the strategy and analysis phase. 3. Build and Document Build the prototype system. Write and execute the commands to create the tables and supporting objects for the database. Develop user documentation, Help text, and operations manuals to support the use and operation of the system.  School of NATIONAL COLLEGE OF SCIENCE AND TECHNOLOGY Nation Builders Amafel Building, Aguinaldo Highway, Dasmariñas City, Cavite System Development Life Cycle 4. Transition Refine the prototype. Move an application into production with user acceptance testing, conversion of existing data, and parallel operations. Make any modifications required. 5. Production Roll out the system to the users. Operate the production system. Monitor its performance, and enhance and refine the system. THANK YOU!