1 / 47

Introduction to Relational Databases

Introduction to Relational Databases. CS 265 EA Summer I 2007 Karl R. Wurst. 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

irish
Download Presentation

Introduction to Relational Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related