Database Management System without and with using Oracle

Database Management System without and with using Oracle 1

Lecture Outline • • • SCRUD! Hw 1: File system vs. db system Overview of database systems Preliminary on SQL 2

What was it like before DBMS was available ? By storing the data in files: customers. txt transaction. txt manufacture. txt Now write C++ programs to implement specific tasks 3

Doing it without a DBMS. . . • Record the fact that “Bill” bought “an Ford Explore” (let us further assume Bill is a new customer, and it is the first time for he to buy a car from the agency: Write a C program to do the following: Read ‘Customer. txt’ Read ‘transaction. txt’ Find&update or add bill to the record “Customer. txt” Update ‘transaction. txt’ Write “customer. txt” Write “transaction. txt” 4

Problems without an DBMS. . . • Assume you use C++ language. • If data structure changes, you need to change C code. • If you want to record other information, you need define new data structure literally and write another set of new C++ programs to manipulate the modified data structure. You need recompile the source code. 5

Problems without an DBMS. . . • You have to take everything into consideration in order to make your program robust and bullet proof. CRASH ! Read ‘customers. txt’ Read ‘transactions. txt’ Find&update or add the record “bill” Find&update or add the record “bill bought a ford Explore” Write “customers. txt” Write “transactions. txt” 6

Problems without an DBMS • Think about how many kinds of data manipulation needs? – – – Sort Summation Count Search Modification List could be very long Etc. • Each of them could be needed for each table or even for questions regarding multiple tables. • Each of them need to be hard coded in C++. 7

Many other Problems without an DBMS. . . • • • Crash management Concurrency control Transaction management Internal low level coding Exception handling User control Disk space management backup List goes long … 8

History of DBMS and RDBMS • Read introduction chapter of the Textbook and any other RDBMS books. 9

What is Data? • Data is a set of values of qualitative or quantitative variables; restated, pieces of data are individual pieces of information. • Data is measured, collected and reported, and analyzed, whereupon it can be visualized using graphs or images. • Data as an abstract concept can be viewed as the lowest level of abstraction, from which information and then knowledge are derived. • Raw data, i. e. , unprocessed data, refers to a collection of numbers, characters and is a relative term; data processing commonly occurs by stages, and the "processed data" from one stage may be considered the "raw data" of the next. • The word "data" used to be considered as the plural of "datum", but now is generally used in the singular, as a mass noun. 10

Database and DBMS • What is a database? – A set of organized files or a collection of files that store the data. – A database is a collection of data that is saved and organized in files so that the data can easily be accessed, managed, and updated. – Data organized in some predefined ways usually saved in files. • What is a database management system (stripped down)? – A piece of software that – Database Management System = DBMS – A big piece of software (written by system software programmers in CC++) that accesses and updates those files for you, designed to make data storing and manipulation tasks easier. • By storing data in a DBMS, rather than as a collection operating system files, we can use the DBMS’s features to manage the data in a robust and efficient manner. 11

Advantages of A DBMS • • Data independent Efficient data access Powerful Query language Data integrity and security Data administration Concurrent access and crash recovery Reduced application development time 12

Data independence • Application programs should be as independent as possible from details of data representation and storage. The DBMS can provide an abstract view of the data to insulate application code from such details. 13

Where are DBMS used ? • DBMS is pervasive, used almost everywhere in your daily life. • You are actually living in a human being world that, is kept monitored, tracked by all kinds of DBMS in a digital world. • In other words, you and your behavior are a bunch of data in this digital era. 14

Classification of DBMSs data model: relational / object-orieted / hierarchical / network / objectrelational users: single-user / multi-user location: distributed / centralized cooperation: homogeneous / heterogeneous OLTP: on-line transaction processing • Used to run the day-to-day operations of a business • event-oriented: take an order, make a reservation, payment for goods, withdraw cash, . . . 15

RDBMS • We are interested in Relational Database system. Relational DBMS = RDBMS • In relational model database, data files are structured as relations (tables) 16

Database Systems • The giant commercial database vendors: – – Oracle IBM (with DB 2) Microsoft (SQL Server) Sybase • Some free database systems (Unix) : – Postgres – My. SQL – Predator • In CSCI 242 we use Oracle QL Server. – You can also choose My. SQL, but less support! – If you are thinking about Access, try it later on your own. 17

An Example of a Traditional Enterprise level Database Application Suppose we are building a system to store the information about: • customers • products • manufactures • who buys what, who produces what 18

more examples of RDBMS – Backend for traditional “database” applications • Registrar system at SUCO – Backend for large Websites • www. microsoft. com, msdn, Search engine – Backend for online shopping • ebay 19

Who interact with (R)DBMS – RDBMS providers implement them. – Advanced administrators install, maintain and tune-up them. – A good developer should develop value-added software using them – End users use software developed by developers 20

Enters a DMBS “Two-tier system” or “client-server” connection (ODBC, JDBC, dbprovider) • End users • administrators • developer Data files Database server (centralized by a third party ) Applications You will work 21 here

The language of RDBMS SQL (Structured query Language) – – Data Definition Language – DDL Data Manipulation Language – DML Data Control Language – DCL Data Transaction Language- DTL 22

How the Programmer Sees the DBMS • Start with DDL to create tables: CREATE TABLE Students ( Name CHAR(30) NOT NULL, SSN CHAR(9) PRIMARY KEY, Category CHAR(20) ); • Continue with DML to populate tables: INSERT INTO Students VALUES(‘Charles’, ‘ 123456789’, ‘undergraduate’). . 23

SQL • SQL is a declarative language, one in which you specify goal what you want and let the language processor engine figure out what steps to take to accomplish the goal. • In general, SQL statements instruct the relational database engine about the desired end-state condition, but do not have to give step by step instructions to the engine. 24

Transparent to SQL programmers • For example, in handling a SELECT statement, the database engine program may flush buffers, dirty write caches, read sectors from disk, follow linked lists, etc. , none of which the programmer has to know. But with most SQL engines, the programmer can add proprietary hints to adjust the way the query is processed. By the way, deletes, updates and inserts are also generically referred to as queries. 25