Introduction to Relational Databases

1. Introduction to Relational Databases CS 265 EA Summer I 2007 Karl R. Wurst

2. Objectives • To understand the nature and characteristics of databases • To understand tables and relationships • To understand the technique of normalization and why it is necessary • Models • To understand the Student Schema used in the textbook

3. The Characteristics of Databases • The purpose of a database is to help people track things of interest to them • Unlike a list or spreadsheet, a database may store information that is more complicated than a simple list • Data is stored in tables, which have rows and columns like a spreadsheet. A database may have multiple tables, where each table stores data about a different thing • Each row in a table stores data about an occurrence or instance of the thing of interest • A database stores data and relationships

4. Applications, the DBMS and SQL • Applications are the computer programs that users work with • The Database Management System (DBMS) creates, processes and administers databases • Structured Query Language (SQL) is an internationally recognized standard database language that is used by all commercial DBMSs

5. Components of aDatabase System SQL

6. Users • A user of a database system will • Use a database application to track things • Use forms to enter, read, delete and query data • Produce reports

7. The Database • A database is a self-describing collection of related records • Self-describing • The database itself contains the definition of its structure • Metadata is data describing the structure of the database data • Tables within a relational database are related to each other

8. Database Management System (DBMS) • A database management system (DBMS) serves as an intermediary between database applications and the database • The DBMS manages and controls database activities • The DBMS creates, processes and administers the databases it controls

9. Functions of a DBMS • Create databases • Create tables • Create supporting structures • Read database data • Modify database data (insert, update, delete) • Maintain database structures • Enforce rules • Control concurrency • Provide security • Perform backup and recovery

10. Data in Tables

11. The Key Characteristic of Databases: Related Tables

12. Databases Create Information • Data = Recorded facts and figures • Information = Knowledge derived from data • Databases record data, but they do so in such a way that we can produce information from the data • The data on STUDENTs, CLASSes and GRADEs could produce information about each student’s GPA

13. The Relational Database Model • The dominant database model is the relational database model – all current major DBMS products are based on it • Created by IBM engineer E. F. Codd in 1970 • It was based on mathematics called relational algebra • We will be working with the relational database model

14. Entity • An entity is some identifiable thing that users want to track: • Customers • Computers • Sales

15. Relation • Relational DBMS products store data about entities in relations, which are a special type of table • A relation is a two-dimensional table that has the following characteristics: • Rows contain data about an entity • Columns contain data about attributes of the entity • All entries in a column are of the same kind • Each column has a unique name • Cells of the table hold a single value • The order of the columns is unimportant • The order of the rows is unimportant • No two rows may be identical

16. A Relation

17. A Nonrelation Example Cells of the table hold multiple values

18. A Nonrelation Example Two rows are identical

19. Keys • A key is a combination of one or more columns that is used to identify rows in a relation • A composite key is a key that consists of two or more columns • For a key to be unique, often it must be a composite key

20. Candidate and Primary Keys • A candidate key is a key that determines all of the other columns in a relation • A primary key is a candidate key selected as the primary means of identifying rows in a relation: • There is one and only one primary key per relation • The primary key may be a composite key • The ideal primary key is short, numeric and never changes

21. Surrogate Keys • A surrogate key as an artificial column added to a relation to serve as a primary key: • DBMS supplied • Short, numeric and never changes – an ideal primary key! • Has artificial values that are meaningless to users • Normally hidden in forms and reports

22. Surrogate Keys NOTE: The primary key of the relation is underlined below: • RENTAL_PROPERTY without surrogate key: RENTAL_PROPERTY (Street, City,State/Province, Zip/PostalCode, Country, Rental_Rate) • RENTAL_PROPERTY with surrogate key: RENTAL_PROPERTY (PropertyID, Street, City, State/Province, Zip/PostalCode, Country, Rental_Rate)

23. Relationships Between Tables • A table may be related to other tables • For example • An Employee works in a Department • A Manager controls a Project

24. A Foreign Key • To preserve relationships, you may need to create a foreign key • A foreign key is the primary key of one relation that is placed in another relation to form a link between the relations • A foreign key can be a single column or a composite key • The key is called a foreign key in the table that received the key

25. Foreign Key Example Primary Key Foreign Key

26. Foreign Key Example Primary Key Foreign Key

27. Foreign Keys NOTE: The primary keys of the relations are underlined and any foreign keys are in italics in the relations below: DEPARTMENT (DepartmentName, BudgetCode, ManagerName) EMPLOYEE (EmployeeNumber, EmployeeName,DepartmentName)

28. Why use multiple tables? • Lists of data can have problems • What happens when Acme changes its name? • What happens when Spot learns to “Stay”? • What if someone else names their dog “Spot”?

29. Normalization • Normalization is the process of removing redundant data and assuring that the columns in a table belong there. • Normalized tables avoid the problems involved in adding, deleting and modifying data.

30. First Normal Form • Eliminate Repeating Groups • Each puppy can know zero or more tricks

31. In First Normal Form • Eliminate Repeating Groups • What is the key for Trick Table now?

32. Second Normal Form • Eliminate Redundant Data • We added a Trick ID to make a numeric key • But now Trick Name is redundant

33. In Second Normal Form • Eliminate Redundant Data • Trick Name depends only on Trick ID

34. Third Normal Form • Eliminate Columns Not Dependent on Key • Kennel Location has nothing to do with Puppy #

35. In Third Normal Form • Eliminate Columns Not Dependent on Key • Kennel Location has nothing to do with Puppy #

36. List Problems Eliminated • What happens when Acme changes its name? • What happens when Spot learns to “Stay”? • What if someone else names their dog “Spot”?

37. Relationships Between Tables • One-to-many (1:M) • Each puppy must have one (and only one) kennel • Each kennel may have zero or more puppies Puppy Table Kennel Code Kennel Name Kennel Location Kennel Table Puppy Table Puppy # Puppy Name Kennel Code Puppy Table Mandatory Optional

38. Relationships Between Tables • Many-to-many (M:M) • A puppy more know zero or more tricks • A trick may be learned by zero or more puppies Puppy-Trick Table Puppy Table Puppy # Puppy Name Kennel Code Puppy Table Puppy Table Puppy # Trick ID Where Learned Skill Level

39. Relationships Between Tables • One-to-one (1:1) • Exists, but not common

40. Structured Query Language (SQL) • Structured Query Language (SQL) is an international standard for creating, processing and querying database and their tables • Many database applications use SQL to retrieve, format, report, insert, delete, and/or modify data for users

41. SQL has Sub-Languages • Data Definition Language (DDL) • create, modify, delete relations • specify constraints • administer users, security, etc. • Data Manipulation Language (DML) • Specify queries to find relations that satisfy criteria • add, modify, remove relations

42. Creating Relations in SQL • Create the Puppy relation. CREATE TABLE puppy_table (puppy_number INTEGER, puppy_name CHAR(20), kennel_code INTEGER);

43. Adding Data in SQL • Add a puppy. INSERT INTO puppy_table (puppy_number, puppy_name, kennel_code) VALUES (1, “Fifi”, 2);

44. Retrieving Data in SQL • Print out everything about the puppies. SELECT * FROM puppy_table; 1|Fifi|2 2|Spot|3 3|Duke|1

45. Retrieving Data in SQL • Find Fifi’s kennel. SELECT puppy_name, kennel_name FROM puppy_table, kennel_table WHERE puppy_name = “Fifi” AND puppy_table.kennel_code = kennel_table.kennel_code; Fifi|Harvest Moon

46. Now you can get this joke: • Fromthinkgeek.com

47. References • Date, C. J., An Introduction to Database Systems, Addison Wesley, 2004 • Frost, Raymond, John Day and Craig Van Slyke, Database Design and Development: A Visual Approach, Prentice Hall, 2006 • Kroenke, David, Database Processing: Fundamentals, Design and Implementation, Prentice Hall, 2006 • Kroenke, David and David Auer, Database Concepts, 3/E, Prentice Hall, 2008 • Ramakrishnan, Raghu and Johannes Gehrke, Database Management Systems, 3/E, McGraw-Hill, 2003 • Rettig, Marc, Rules of Data Normalization (Poster), Miller Freeman, Inc., (no date) • Rischert, Alice, Oracle SQL by Example, 3/E, Prentice Hall PTR, 2004 • Silberschatz, Avi, Henry Korth and S. Sudarshan, Database Systems and Concepts, 5/E, McGraw-Hill, 2005