Understanding Database Management Systems and Data Storage

Slide Note

Explore the world of Database Management Systems (DBMS) and learn about the evolution of data storage from flat-file to relational databases. Discover the key features of a DBMS, different database types, administration tools, SQL and NoSQL databases, CAP theory, and considerations for choosing between SQL and NoSQL databases. Dive into the realm of data management and stay updated on industry trends.

arauz_a Follow

Uploaded on Sep 23, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript

Database L. Grewe

Database Desired Features of a Database Management System (DBMS) Easy to store, retrieve, modify data Security Handle Concurrent Access Data Recovery Consistent Data Metadata should be available.

History Flat-FileNot really a database, simply store data in files. NetworkStores data in a network structure. HierarchicalStores in a hierarchical structure. Old, not used anymore RelationalStores data in tables. Makes use of the Math concepts of relations. Since 1970s. Example Oracle. Object-orientedExpands upon Relational model to handle more complicated kinds of data in non-transactional manners.

Accessing Database Administration Many tools Some computer applications, some web interfaces Example of desktop application TOAD by Dell allows access to multiple databases Example of web interface --

Many databases

SQL or NoSQL SQL structured query language -- used to access Relational databases

CAP theory Says it is impossible for a distributed data store to simultaneously provide more than two out of the following three guarantees:[1][2][3] In other words, the CAP theorem states that in the presence of a network partition, one has to choose between consistency and availability. Consistency Availability Partition tolerance Every request receives a (non- error) response without guarantee that it contains the most recent write The system continues to operate despite an arbitrary number of messages being dropped (or delayed) by the network between nodes Every read receives the most recent write or an error

Which database SQL or NoSQL? Cost? Knowledge? Scaling? Features? Availability?

Following trends Search, read, meetups

We are not talking about data in cloud or distributed data Take CS 6320 to learn about these kinds of topics Your text here

Accessing Databases for Admin Use DBA tool desktop apps and web interfaces

Relational Databases Data is organized in tables. Each table consists of a set of columns, each column representing some item of information. A relation represents an operation on a database described by Mathematical Set Operations such as unions and joins. A Union B = all the elements of set A and B. A Joint B = only the comment elements of set A and B. For example, below is a table representing information about software distributors. They keys here are "Name, Street Address, City, State, Distribute" Search a database by searching against certain key values which represent For example, we may want to find all the entries in the Database that have the key State = CA. To search a database you must use the language the Database understands. Most relational databases understand a version of a scripting language called SQL. Street Address Name City State Distribute Microsoft, Ado be Enterprise Suite Butch Grewe 100 Campus Dr. Seaside CA Doug MacIntire 100 6th Street. NY NY

Relational Databases Relational Database Is a set of segments and consist of one or more data files. May contain many kinds of multiple data objects such as tables, indexes, etc. Tablespace EXAMPLES in Oracle System tablespace = Contains the data dictionary, stored procedures, triggers, and system rollback segment. Used for system maintenance and operation. Data tablespace= Stores user data in tables and indexes. User data tablespaces don't need to be online all of th time unlike system tablespace. Need to be online in order to acces data in them. Note: There is often a defined default tablespace for each user. A user does not have to specify the tablespace when creating new objects, like tables, that they want to be in the default tablespace. The DBA sets up what the default tablespace is when creating a user. Temporary tablespace= Used by operations, such as sorting, unions, joins, that take up temporary space. Table Sits inside of a particular tablespace. Contains data in a table (row, column) format. When creating a table you can specify the tablespace it is to be inside of. If not the default tablespace for the user is assumed.

How to think about databases TIP: Think of a database as a filing cabinent: the drawers withing the cabinet are tablespaces and the folders in those drawers are datafiles, and the pieces of paper in each folder are tables or other database objects, information written on a piece of paper is the data stored in the table.

Relational DB and SQL Structured Query Language = script language to manipulate a database

SQL: create table Using SQL*Plus to create a Table below are two examples of creating tables. The second specifies the tablespace the table should appear in, used if you have more than one tablespace. It also specifies the initial size, how it grows in size and minimum number of extents allocated for it. SQL> create table customer( last_name state_cd sales I prefer to use a GUI admin tool like TOAD or web interface to create table varchar2(30) not null, varchar(2), number); SQL> create table products( name description varchar2(300), price number(4,2) ) tablespace productspace storage(initial 25k next 25k minextents 1); varchar2(30) not null,

SQL: Alter a table Using SQL*Plus to alter a Table Use the SQL alter command to add or drop columns in a table after it has been created. Suppose we have a table customer that has the following fields: last_name, state_CD, sales To add a column tax_exempt_id SQL>alter table customer add tax_exempt_id To drop the column sales SQL> alter table customer drop column sales; varchar2(20);

CRUD Create, Read, Update, Delete select all the keys/fields from the table SELECT select * from table select Distribute from table select only the field Distribute from the table select * from table where Name is 'Butch Grewe' Select all keys/fields from table where the key/field Name = Butch Grewe

CRUD Create, Read, Update, Delete INSERT insert into table values ('James Bond', '1 Eiffel Tower', 'Paris', 'France','Spy Software') Insert into the table, the data entry having the following key/field entries in order: Name = James Bond Street Address = 1 Eiffel Tower City = Paris State = France (huh???) Distribute = Spy Software

CRUD Create, Read, Update, Delete updates any records with the name 'Butch Grewe' to have the value of 6 for the field age. UPDATE update table set age=6 where Name='Butch Grewe'

CRUD Create, Read, Update, Delete DELETE delete from table where age=10 From table, deletes all records/rows that have the field of age with the value 10.

Conditional clauses The where clause the where clause.... where a1=x and a2=y ALL of the conditions must be true where a1=x or a1=y Only one of the conditions need to be true where a1=x or a2 >z Only one of the conditions need to be true where a1 between x and y Range of values where a1 not between x and y NOT in range of values where a1 in (x, y, z) Must be in the list of items where a1 not in (x,y,z) Must NOT be in the list of items. where a1 != x where a1 like 'M%' Must be NOT equal to value. Pattern Search....means a1 must start with a capital M. where a1 like '%son%' Pattern Search....means a1 must have somewhere in its value the string 'son'. Examples that would be good: Furgeson, Forsonning, etc. where a1 not like 'M%' Pattern Search....means a1 does NOT start with a capital M. where a1 like '%son%' Pattern Search....means a1 must have somewhere in its value the string 'son'. Examples that would be good: Furgeson, Forsonning, etc.

Order clause the order by clause select * from table order by name Order alphabetically in descending order the records in the table, by their value stored in the column name.

Oracle a relational database Identification You identify an Oracle Database by the following information: Host IP = IP address where Oracle Database under consideration is installed. Port = Port number of Oracle Database installation. SID = ID of the particular tablespace that describes the database you wish to access. The following shows an example of some items that could be listed in this file: The first entry means the (134.154.11.126) server under port number 1521, Oracle was installed with an SID = labdb to identify the table space under question and that TCP communications protocol should be used in accessing it. The second entry means that on the (207.62.129.150) server under port number 1521, Oracle was installed with an SID = intprog to identify the tablespace labdb.world = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 134.154.11.126) (PORT = 1521)) (CONNECT_DATA = (SID = labdb)) ) INTERNETPROG.WORLD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 207.62.129.150) (PORT = 1521)) (CONNECT_DATA = (SID = intprog)) )

Oracle data types Datatype CHAR Description fixed-length cahracter field, paded with trailing blanks char(size) char(30) //30 characters Currently, same as CHAR varchar(size) varchar(30) Variable length character field varchar2(size) Variable length character data Maximum Size 2000 bytes VARCHAR VARCHAR2 4000 bytes LONG NUMBER 2GB Variable length numeric data number OR number(l,d) where "l" stands for length and "d" for the number of decimal digits number(5,2) can store numbers like 100.02 1x10^-130 to 9.99x10^125 BOOLEAN holds values of true or false only. This is actually a PL/SQL data type. Data time values represents using format DD-MON-YY (e.g. 09- SEP-0 which means September 9, 2004). DATE Dec 31, 9999 RAW Variable length raw binary data raw(size) Variable-length raw binary data 20000 bytes LONG RAW 2 GB

Datatype VARRAY Description New to Oracle 8i, an array that is an ordered list. varray(num_elements) of type e.g. varray(100) of number of of 100 s. Row ID variable type User can define their own data type. You do so using the following SQL command.As a user, the DBA had to give you the privilege to be able to create user-defined data types. create type your_data_type_name as XXXX where XXX specifies the kind of info you want represented as your_data_type. Here are two examples: create type price_list as varray(100) of number; Maximum Size // this is an array maximum number ROWID User Defined Type 6 bytes create type room_type as object( capacity number, podium boolean, computers number); ROWID Row ID variable type

Oracle built in functions go to oracle.com for more Function ABS CEIL FLOOR MOD POWER Meaning absolute value ceiling the floor modular x to power of y ROUND round off the number SIGN SQRT TRUNC AVG MAX MIN retrieve the sign square root truncate take the average the maximum of set the minimum of set calculate standard deviation take the sum get the current sytem date STDDEV SUM SYSDATE

PHP and connecting to Oracle (or any similar relational database) See http://us3.php.net/manual/en/intro.oci8.phpAND http://us3.php.net/manual/en/ref.oci8 .php for api and some additional examples <?php $user="login_CHANGE_THIS"; $password= "password_CHANGE THIS"; $host = "mcsdb2.sci.csueastbay.edu:1521/MCSDB2"; //CHANGE THIS //setup sql query $query = "SELECT * FROM DOGS WHERE AGE > " . @$_POST['age']; echo "Query is = " .$query; echo " "; echo "Host is = " .$host; echo " "; echo " "; Setting up host and account information To connect to an Oracle instance SQL query to table DOGS to retrieve all //connect to database, where tsnames.ora is setup $connect_obj = oci_connect($user, $password, $host); if($connect_obj) { echo "connected okay"; } else { $err = oci_error(); echo "Oracle connection error " . $err['message']; return; } Connect to Oracle and if any problems report error

This is about executing the SQL statement and catching any exceptions Continued //create sql statement $sql_statement = oci_parse($connect_obj, $query) ; SQL statement object created using then oci php module //execute statement try{ $r = oci_execute($sql_statement, OCI_COMMIT_ON_SUCCESS); if(!$r) { $p = oci_error($sql_statement); echo " error in execution " . oci_error($p); } } catch(Exception $e) { echo " Failed to get database info" . $e->getMessage(); } Execute the SQL statement & results returned to $r If error report Catch any exceptions like database not available, table doesn t exist, etc

Free up the SQL statement and close connection these are open resources and very important to free Continued //close the connection oci_free_statement($sql_statement); //need to free so close can work //without this it will not close //until this script ends if(!oci_close($connect_obj)) {echo " oci connection not closed!!!"; } ?>