01_Handout_1(5).pdf

IT2003 Advanced Database Concepts This type of data uses a pr...

IT2003 Advanced Database Concepts This type of data uses a predef ined and expected f ormat or schema. A. Components of Database System ID Name Age Gender Address Contact Email 1. Database – an organized collection of structured inf ormation, or data, typically stored electronically in a computer system. 1 Justin 27 Male Makati, +639358491 justineyork@gmail A database is usually controlled by a database York PH 124.com management system (DBMS). Together, the data and the Table 1. Structured Data DBMS, along with the applications that are associated with b. Unstructured Data – data that exist in its original (raw) state them, are ref erred to as a database system or database. that can come in all shapes and sizes. 2. Database Management System (DBMS) – serves as an interf ace between the database and its end-users or programs, allowing The mall show has 200 people attendees. The first attendee to arrive is Justin users to retrieve, update, and manage how the inf ormation is York. He has an ID number 1, he is male, 27 years old and lives in Makati. His contact # is +639358491124 and email is [email protected]. organized and optimized. A DBMS also f acilitates oversight and control of databases, Table 2. Unstructured Data enabling a variety of administrative operations such as c. Semi-structured Data – individual data items of the same type perf ormance monitoring, tuning, and backup and recovery. may have dif f erent sets of attributes. It contains semantic tags 3. Database Application – a set of one (1) or more computer and there is no pre-def ined schema. programs that serves as an intermediary between the user and the { DBMS. id: 1, Application programs read or modif y database data by Name: "Justin York", sending database query language to the DBMS. Age: 27, 4. Users Gender: "Male",  Naive users are unsophisticated users who interact with the Address: system by using predef ined user interf aces, such as web or { mobile applications. City: "Makati" Country: "Philippines  Application programmers are computer prof essionals who }, write application programs. Contact:  Sophisticated users interact with the system without writing { programs. Instead, they f orm their requests either using a Mobile: "+639358491124" database query language. Email: “[email protected]” } Database System Architecture – involves database design and } construction on how the databases will operate and f unction within exisiting Table 3. Semi-structured Data structures and location. a. Centralized database system – a database that supports data B. Types of Database Management System located at a single site. SQL (Structured Query Language) – a computer language for b. Distributed database system – a collection of multiple storing, manipulating, and retrieving data stored in a relational interconnected databases, spread physically across various database. locations. NoSQL Structures of Data in DBMS  Stands f or “Not only SQL” is generally used to describe a new a. Structured Data – data that are stored in relational databases. 01 Handout 1 *Property of STI  [email protected] Page 1 of 4  IT2003 generation of DBMS that is not based on the traditional o MySQL – an open-source SQL relational database relational database model and has been developed to address management system that was developed and supported by the challenges represented by Big Data. Oracle.  f eatures a dynamic schema f or unstructured data and the data o SQLite – a relational database management system contained can be stored in dif ferent ways. in a C programming library.  This allows you to create documents without f irst having to caref ully plan and def ine their structure. D. T-SQL Statement Blocks NewSQL  Incorporates and builds on the concepts and principles of BEGIN/END – used to def ine a set of SQL statements that execute Structured Query Language (SQL) and NoSQL systems. together.  By combining the reliability of SQL with the speed and DECLARE – used to declare a variable in SQL server. Variable perf ormance of NoSQL, NewSQL provides improved names have to start at “@”. If we want to use a variable in SQL f unctionality and services. server, we need to declare it. Object-Oriented database SET or SELECT – used to assign a value(s) to a variable.  A database that stores object rather than data as individual CAST – converts an expression of one data type to another. relations. It makes use of object-oriented languages such as PRINT – allows users to return a pre-def ined message or value. C++, Java, and C#. Ex.  Objects can have inheritance relationships with other classes, BEGIN allowing one object to contain the data of another object as well DECLARE @FirstVariable AS VARCHAR(100); as the data of the new object inheriting. SELECT @FirstVariable = ‘My First Variable in SQL’;  Choose object-oriented database when you have a business PRINT @FirstVariable; need f or high perf ormance on complex data. END XML (Extensible Markup Language)  Designed to f acilitate the exchange of structured documents. TRY-CATCH – consists of a try block f ollowed by one or more catch  Simplif ies data sharing between various systems because of its clauses, which specif y handlers f or dif f erent exceptions. This platf orm-independent nature. f eature helps to deal with any unexpected or exceptional situations  Choose XML as long as your datasets stay relatively small. that occur when a program/query is running. Ex. C. SQL Extensions BEGIN PL-SQL – a combination of SQL along with the procedural f eatures DECLARE @FirstVariable INT = 0; of programming languages. It was developed by Oracle Corporation DECLARE @SecondVariable AS VARCHAR(10); and stands f or Procedural Language. T-SQL – stands f or Transact- SQL. A Microsof t’s and Sybase’s BEGIN TRY extension of SQL that adds and declare variables, support SET @SecondVariable = @FirstVariable / ‘Error’; transaction control, error and exception handling, and row END TRY processing to SQLs existing f unctions. BEGIN CATCH Other SQL PRINT ‘Error Message: ’ + ERROR MESSAGE(); o PostgreSQL – an open-source object-relational database END CATCH system that uses and extends the SQL language. END 01 Handout 1 *Property of STI  [email protected] Page 2 of 4  IT2003 E. Conditional Statements Ex. (IF-ELSE AND ELSE-IF CONDITION) T-SQL Conditional Statements specif y a block of code to be executed, if the returning BEGIN IF-ELSE condition is true DECLARE @v_Grade INT; specif y a block of code to be executed, if the returning DECLARE @v_Result varchar(30); ELSE condition is f alse DECLARE @v_Verdict varchar(10); specif y a new condition to test, if the previous returning SET @v_Grade = 90 ELSE-IF condition is f alse loops through a block of code as long as a specif ied condition --Evaluate if the grade is 75 to 100 then execute the next statement. WHILE IF @v_Grade >= 75 AND @v_Grade = 60 AND @v_Grade