SQL and Database Management

1. SQL and Database Management MIS 171 – Web Technologies and Innovation Paul F. Clay, Ph.D.

2. Definitions • Data • Facts, images, sound and video • Information • Data that has been processed so that it can be interpreted and used • Metadata • Data about data

3. Database System • The database system consists of four major components: • Database: • Collection of integrated and shared data • Hardware • Processor: CPU • Memory: primary, secondary and tertiary • Software: • Database Management System (DBMS) • Application programs • People: • Administrators, Designers, Developers, End Users

4. Database System: Database • An organized collection of logically-related persistent data • A shared self-describing collection of inter-related data • Implicit Properties • Represents some aspect of the real world called the “mini world” or the “universe of discourse” • A logically related collection of data • Developed for a specific purpose • Has “metadata,” i.e., repository of definitions • Database • Hardware • Software • People

5. Database System: Hardware • Processor to support the execution of the database system software. • Storage • Primary: main memory/ random access memory (RAM) and cache • Secondary: Magnetic Disks • Tertiary: Tapes • Database • Hardware • Software • People

6. Database System: Software • DBMS: Software used to define, create, maintain, and provide controlled access to the database • Define: Specify data types, structures and constraints for the data to be stored • Populate: Process of entering/ storing the data • Manipulate: includes such functions as querying to retrieve specific data, updating the database to reflect changes, and generating reports from the data • Control: grant / restrict permissions, security • Examples of DBMS: • Oracle 10g, IBM DB2, MS-SQL Server, MS-Access, My SQL • Database • Hardware • Software • People

7. Database System: People • Database Designer: • Design database structure, i.e., they are database architects • Database Administrators: • Manage the DBMS use and ensure that the database is functioning properly • Application programmers: • Responsible for writing application programs that use the database typically in a language like C, Java etc. • End users: • Interact with the system from online workstations or terminals • Database • Hardware • Software • People 7

8. Database Example: Table CORP metadata SYMBOL NAME -------------------- ---------------------------- INTC INTEL CORP CSCO CISCO SYSTEMS MSFT MICROSOFT CP ORCL ORACLE CORP AMAT APPLIED MATL JDSU JDS UNIPHASE SUNW SUN MICROSYS BRCD BROCADE COMMS SIRI SIRIUS SAT RADI MCDTA MCDATA CL A DELL DELL INC data 8

9. Benefits of Database Approach • The data can be shared • Sharing data is straightforward. • FS: Write program. Hard to share old data. • DB: Existing data can easily be shared by many applications. • Redundancy can be reduced • Facts are recorded in one place in database. • FS: Write program. Hard to control. • DB: Redundancy can be controlled. • Inconsistency can be avoided (to some extent) • Make sure Symbol of each company is valid. • Make sure each company name is not stored more than once. • FS: Write program. • DB: DBMS can enforce some types of “integrity.” 9

10. Benefits of Database Approach [2] • Transaction support can be provided • Allow multiple users to run the program at the same time. • FS: Write programs, maybe using locks. • DB: DBMS supports transaction management. • Security can be enforced • Authorize each user controlled access (e.g., insert, update). • FS: Write programs, maybe using passwords. • DB: DBMS enforces security. • Standards can be enforced • Standardize the data representation across systems? • FS: All programs must agree on the data representation. • DB: The DBA can ensure that all applicable standards are observed in the representation of data. 10

11. Evolution of Database Systems • Pre-1960s • 1945: Magnetic tape developed • 1959: IBM introduced the RAMAC (Random Access Method of Accounting And Control) system • 1960s • Information Management System (IMS) developed by IBM • SABRE developed by IBM and American Airlines • 1970s • Relational model developed by Ted Codd • ER diagram introduced by Chen • 1980s • DBMS developed for personal computers (DBASE, PARADOX) • Preliminary SQL standard published • 1990s • Parallel processors • OODB • Standards for data query and exchange (SQL2) • 50 disks, 2’ • 5 MB • $10k/MB 11

12. Overview • SQL • Structured Query Language • High level declarative language for relational database • De facto standard in the database world • Is now an official standard too, accepted by • American National Standards Institute (ANSI) • International Organization for Standardization (ISO) • The first standard (1986): SQL/86 • SQL2 (SQL/92) is the most popularly in use today

13. SQL • DDL • Define the database • CREATE, ALTER and DROP a TABLE, VIEW or INDEX • DML • Manipulate the data in an existing database • INSERT, UPDATE, DELETE • SELECT, i.e., query • DCL • Control user access to an existing database • GRANT and REVOKE

14. SQL- query • What is the first and last name of every student who has a GPA greater than 3.5? • SELECT F_Name, L_Name • FROM Student • WHERE GPA>3.5 SELECT <columns> FROM <table> [WHERE <condition>] [GROUP BY <columns>] [HAVING <condition>] [ORDER BY <column>]; 14

15. SELECT F_Name, L_Name FROM Student WHERE GPA>3.5 15

16. Decentralized Computing Questions?