Introduction to Relational Databases and SQL

1. Introduction to Relational Databases and SQL Brian Panulla Web 2004 Pre-Conference

2. Administrivia • Objectives • Agenda • Prerequisites • Schedule • Logistics • Presenter Introduction • Attendee Introductions

3. Objectives • The primary objective of this session is to teach the basics of working with common Relational Databases, including data modeling and database design techniques. • Participants will also receive an introduction to query building using Standard or Structured Query Language (SQL).

4. Objectives • To that end, upon completion of the session you should be able to: • Understand database concepts and terminology • Design and create tables • Use SQL to retrieve and analyze information • Use SQL to enter and manipulate data

5. Agenda • Lesson 1: Database Concepts • Lesson 2: Entity-Relationship Modeling • Lesson 3: Creating Tables • Lesson 4: Using SELECT queries • Lesson 5: Using INSERT queries • Lesson 6: Using UPDATE queries • Lesson 7: Using Delete queries • Lesson 8: Database Project

6. Prerequisites • General experience in Web site development or management • General experience in a scripting language (ColdFusion, PHP, ASP) or full programming language (C/C++, Java)

7. Schedule(Half-Day Session) Start 1:00 PM Break (approximate) 3:00 PM Adjourn 5:00 pm

8. Restrooms Drinking fountains, refreshments, snacks Laptops Messages/phones Security Emergency measures Logistics

9. Presenter Brian Panulla B.S. Science, PSU (2000) Chief Information Officer Campus Data Group, LLC E-mail: bpanulla@psu.edu

10. Attendees • Your name • Organization name • Current position • Background in databases • Expectations

11. Questions?!?! • Ask away!

12. Lesson 1:Database Concepts

13. Lesson 1 Objectives • Discuss database concepts and terminology • Learn database design principles

14. Database Terminology • Database • Table (or relation, entity) • Row (or record, tuple) • Column (or field, attribute) • Data value

15. What is a database? • Common databases • Database tasks: • Retrieving • Sorting • Summarizing • Inserting • Updating • Deleting • Common DB software

16. What is a table? • Tables are the fundamental component of any relational database. • A table stores the data corresponding to a specific type of object • For example: • A Students table would store student information • An Orders table would store customer order information

17. What is a table? • Tables are made up of rows and columns • The columns of a table describe the characteristics of the information stored in that table • The data in each row belongs to a given instance of the type of data stored in a particular table

18. What is a table? • Each row contains one data value per column. • The range of values that can go into a particular column of a row is called the domain of that column, and is generally restricted to data of a specific type (integers, character data, dates, etc.)

19. Sample Database Design

20. What is a“Relational Database?” • The term “relational database” comes from the mathematical definition of a relation, or set. All objects in a relation must have the same properties or characteristics • The point? Tables group similar data or objects, with use one table per set of objects • Q: Is Excel a relational database system?

21. Database Design • An art unto itself, database design skills are crucial in the development of efficient, stable Web applications • Planning your database design should generally be one of the first tasks in a development project. Too often, however, the database is developed “as we go along,” creating problem after problem

22. Lesson 2:Entity-Relationship Modeling

23. Entity – Relationship Modeling • Entity-Relationship (or E-R) Modeling is the name given to one particular method of relational database design • The data to be stored in a database is categorized into entities. An entity is an example of one type of object to be modeled by the database. • Entity names are typically nouns.

24. Some Basic Entities Student Course Professor

25. Exercise • Discuss some of the entities that would exist in databases for: • the participants in a youth sporting league (baseball, softball, soccer, etc.) • the inventory of an independent gift shop or other store

26. E-R Modeling: Attributes • Each entity has one or more attributes that further describe that entity or relationship.

27. An Entity with Attributes ID Name Email Students GPA Phone

28. Exercise • Discuss some of the attributes that would describe the following entities: • the Teams entity in the youth sporting league database • the Products entity in the gift shop inventory database

29. E-R Modeling: Relationships • A given entity can be correlated with other entities by way of relationships • A relationship is typically named with a verb phrase: • A Personis aStudent • A Bookis published by aPublisher • There may also be attributes that truly belong to the relationship and not to an entity

30. ID Name Email Student GPA Phone Enrolls In Teaches Relationships Professor Year Date Grade Course

31. Exercise • Devise the relationships that connect the entities in the databases discussed in one of the previous exercises: • The youth sporting league database • The gift shop inventory database

32. Lesson 3:Creating Tables

33. Lesson 3 Objectives • Convert an E-R Diagram to a relational schema • Identify improvements to the design tables • Use the CREATE TABLE SQL statement to create tables in a database

34. Creating Tables • With an E-R Model in hand, the actual creation of a database is very straightforward • Each entity will generally become a table • Any relationships with one or more attributes will also become a table • Relationships without attributes may sometimes be modeled as a table

35. Ground Rules for Relational Database Design • No multi-part or multi-value fields • Eliminate redundant information • Avoid designs that call for the addition of columns to store new data • Avoid “anomalies”: • Update • Delete • Insert

36. Sample Database Design

37. Sample Database Design • No multi-part or multi-value fields

38. Sample Database Design • Eliminate redundant information

39. Sample Database Design • Avoid designs that call for the addition of columns to store new data

40. Sample Database Design • Avoid anomalies: Update Anomaly

41. Sample Database Design Delete Anomaly

42. Sample Database Design Insert Anomaly

43. Keys • A key is a field or set of fields that can be used to uniquely identify a particular record in a database table. • Examples: • Name, Department • SSN • A Primary Key is a field or set of fields chosen by the database designer to be used to define relationships between tables

44. Primary Keys • Primary Key fields: • Must contain unique, non-duplicated values (or sets of values in the case of multi-field keys • Cannot be NULL • In the event that no one column in a table is an appropriate Primary Key, an artificial primary key column is usually generated

45. Foreign Keys • When the values of a Primary Key column in a table are shared by a common column in a child table, that column is called the Foreign Key of the relationship. • Foreign Key fields must have the same data type and size as their related Primary Key field.

46. Foreign Keys • Foreign Keys are used in other tables in order to maintain relationships between data values. • By allowing us to split data into multiple related tables, foreign keys can help eliminate the anomalies described previously

47. A Better Design

48. The Relational Schema

49. Exercise • Develop a relational schema for one of the databases from the previous lesson

50. An Introduction to Relationships • Cardinality • One-to-Many Relationships • One-to-One Relationships • Many-to-Many Relationships