Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI Introduction to Relational Databases

2. Sharing Knowledge and Success • Oracle is a relational database management system (RDBMS). • A DBMS allows users to... • put data in • keep/manage the data • get data out and work with it

3. Database Management • Disadvantages of traditional file processing systems… • uncontrolled redundancy • inconsistent data • inflexibility • limited data sharing • poor enforcement of standards • low programmer productivity • excessive program maintenance • An DBMS deals with these problem my introduction… • Concurrency control and locking • Transaction processing • Read-consistency

4. Database Management: Data Models • Hierarchical Data Model (IMS) • nested sets of 1:1 or 1:M relationships • Network Data Model (IDMS, TOTAL) • multiple sets of 1:1, 1:M, M:1, or M:N relationships • Relational Data Model (Oracle, SQL Server, DB2, mySQL) • A Relational DBMS expresses data in terms of a “relation” or a table consistent of named columns with data organized into rows. relationships are NOT physically implemented • uses primary keys to represent associations • terminology: relations (tables), columns, tuples (rows), domain, degree, cardinality, primary keys, concatenated keys, alternate keys, foreign keys, Referential Integrity or R.I. • relational algebra, three main operators: select, project, join • normalization theory:1NF, 2NF, 3NF

5. Sharing Knowledge and Success • The Language of Oracle: SQL • Structured Query Language, English like query language • keywords: select, from, where, and order by • Example query:select city, temperature from weather where temperature > 80 order by temperature; • Examples of relational databases • stock tables in newspaper • sports scores • 100 year example in book

6. History of SQL History of SQL • The paper, “A Relational Model of Data for large Shared Data Banks,” by Dr. E. F. Codd, was published in June 1970 in … Communications of the ACM. Codd’s model is now accepted as the definitive model for relational database management systems (RDBMS). • The language, Structured English Query Language (SEQUEL) was developed by IBM to use Codd’s model. • SEQUEL later became SQL. • In 1979, Relational Software, Inc. (now Oracle Corporation) introduced the first commercially available implementation of SQL. Today, SQL is accepted as the standard RDBMS language.

7. Client-Server Architecture In the Oracle database system environment, the database application and the database are separated into two parts: a front-end or client portion, and a back-end or server portion—hence the term client/server architecture. Client and server are located on different computers, and these computers are connected through a network. The server and clients of an Oracle database system communicate through Oracle Net Services, Oracle's network interface. SQL

8. The Dangers in a Relational Database • It looks very easy to use a RDBMS • learning about normalization, SQL, etc. make for instant “experts.” • lack of experience with major production systems can create catastrophic project failures. • Testing cycles are getting shorter • newer development tools make software development quicker, usually systems testing gets shortened. • Recent college grads... • least experienced developers usually have more training with relational database technology. • veteran developers are busy with older projects.

9. The Dangers in a Relational Database Bad Examples of Table and Column Names • TablesDEPT EMP EMPS MYEMPSPE PROJ TITLES PERSONNEL • ColumnsAD1 AU_LNAME AU_ORD BLOC CDLEXP DEPTNO DNAME DISCOUNTTYPEEMPNO ENAME ENUMBER ESALHIGHQTY HIRANGE LORANGE LOWQTYNOTE ORD_NUM PNAME PROJNOPUBDATE QTYOH SLSTAXPCT WORKHRS • Reasons • abbreviation used without good reason • inconsistent abbreviations, underlines, and use of plurals • purpose not apparent from name • name rules have limitations

10. The Dangers in a Relational Database Use English Name for Data: • Poor Example:Better Example:

11. The Basic Parts of Speech in SQL • SQL is a language. Oracle SQL is a superset of the American National Standards Institute (ANSI) and the International Standards Organization (ISO) SQL92 standard at entry level conformance. • PL/SQL is Oracle’s procedural language extension to SQL. It allows you to link several SQL commands through procedural language. • Oracle SQL Developer is a graphical client side software tool used to connect to a server remotely.

12. 12 Include Users. Put them on the project team, teach them SQL. Name tables, columns, data with users. Develop application thesaurus to ensure consistency. Use English words. Don’t mix levels in naming. Avoid codes and abbreviations. Use meaningful keys. Decompose overloaded keys. Analyze and design from the tasks, not just the data. Normalization is not design. Move tasks from users to the machine. Don’t be seduced by development speed. Take time in analysis, design, testing, tuning. Ten Commandments (suggestions?)