Introduction to Relational Databases and SQL Brian Panulla Web 2004 Pre-Conference
Administrivia • Objectives • Agenda • Prerequisites • Schedule • Logistics • Presenter Introduction • Attendee Introductions
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).
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
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
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)
Schedule(Half-Day Session) Start 1:00 PM Break (approximate) 3:00 PM Adjourn 5:00 pm
Restrooms Drinking fountains, refreshments, snacks Laptops Messages/phones Security Emergency measures Logistics
Presenter Brian Panulla B.S. Science, PSU (2000) Chief Information Officer Campus Data Group, LLC E-mail: firstname.lastname@example.org
Attendees • Your name • Organization name • Current position • Background in databases • Expectations
Questions?!?! • Ask away!
Lesson 1 Objectives • Discuss database concepts and terminology • Learn database design principles
Database Terminology • Database • Table (or relation, entity) • Row (or record, tuple) • Column (or field, attribute) • Data value
What is a database? • Common databases • Database tasks: • Retrieving • Sorting • Summarizing • Inserting • Updating • Deleting • Common DB software
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
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
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.)
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?
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
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.
Some Basic Entities Student Course Professor
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
E-R Modeling: Attributes • Each entity has one or more attributes that further describe that entity or relationship.
An Entity with Attributes ID Name Email Students GPA Phone
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
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
ID Name Email Student GPA Phone Enrolls In Teaches Relationships Professor Year Date Grade Course
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
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
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
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
Sample Database Design • No multi-part or multi-value fields
Sample Database Design • Eliminate redundant information
Sample Database Design • Avoid designs that call for the addition of columns to store new data
Sample Database Design • Avoid anomalies: Update Anomaly
Sample Database Design Delete Anomaly
Sample Database Design Insert Anomaly
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
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
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.
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
Exercise • Develop a relational schema for one of the databases from the previous lesson
An Introduction to Relationships • Cardinality • One-to-Many Relationships • One-to-One Relationships • Many-to-Many Relationships