1 / 16

MySQL: Introduction

MySQL: Introduction. MIS 3501, Fall 2014 Jeremy Shafer Department of MIS Fox School of Business Temple University October 14, 2014. Introduction to Databases. A database is an ordered collection of information from which a computer program can quickly access information

alden-hinton
Download Presentation

MySQL: Introduction

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. MySQL: Introduction MIS 3501, Fall 2014 Jeremy Shafer Department of MIS Fox School of Business Temple University October 14, 2014

  2. Introduction to Databases • A database is an ordered collection of information from which a computer program can quickly access information • Each row in a database table is called a record • A record in a database is a single complete set of related information • Each column in a database table is called a field • Fields are the individual categories of information stored in a record

  3. Introduction to Databases Figure 7-1 Employee directory database PHP Programming with MySQL, 2nd Edition

  4. MySQL skills In this class we will use MySQL database software. Students are expected to be able to know enough SQL in order to explore existing databases, create new ones, and perform CRUD operations. “CRUD” • INSERT • SELECT • UPDATE • DELETE “Exploration” Show tables Describe tables “Creation” Create tables Drop tables

  5. A brief word about REGEXP • Consider this statement: select Name, Continent, Population from Country where Name REGEXP '^.*[xy].*$' order by Name; • The ^ indicates a start of a pattern • The .* indicates the presence of zero or more characters. • The [xy] means x or y • The $ means end of the string

  6. SQL Interactive Demonstrator You should get comfortable using the SQL Interactive Demonstrator (SID) provided to you in the Lynda video. It has already been set up for you on the class server. You can use it here: http://mis3501.temple.edu/sid

  7. To Do Connect to SID Use your “album” database Command: show tables; Command: describe album; Now use your “scratch” database

  8. To Do Create a table there with an automatically incrementing row id. (See: http://www.w3schools.com/sql/sql_autoincrement.asp ) Insert data into it (at least 2 or three records) Select data from it.

  9. To Do Update data in it. Select data again (see your changes?) Delete your record Drop the table Challenge – can you create a table with a status flag? And then write a select statement that uses it?

  10. Understanding Relational Databases • Relational databases consist of one or more related tables • A primary table is the main table in a relationship that is referenced by another table • A related table (or “child table”) references a primary table in a relational database • A primary key is a field that contains a unique identifier for each record in a primary table PHP Programming with MySQL, 2nd Edition

  11. Understanding Relational Databases (continued) • A primary key is a type of index, which identifies records in a database to make retrievals and sorting faster • A foreign key is a field in a related table that refers to the primary key in a primary table • Primary and foreign keys link records across multiple tables in a relational database PHP Programming with MySQL, 2nd Edition

  12. One-to-One Relationships • A one-to-one relationship exists between two tables when a related table contains exactly one record for each record in the primary table • Create one-to-one relationships to break information into multiple, logical sets • Information in the tables in a one-to-one relationship can be placed within a single table • Make the information in one of the tables confidential and accessible only by certain individuals PHP Programming with MySQL, 2nd Edition

  13. One-to-Many Relationship • A one-to-many relationship exists in a relational database when one record in a primary table has many related records in a related table • Breaking tables into multiple related tables to reduce redundant and duplicate information is called normalization • Provides a more efficient and less redundant method of storing this information in a database PHP Programming with MySQL, 2nd Edition

  14. One-to-Many Relationship (continued) Figure 7-3 Table with redundant information(This is usually bad) PHP Programming with MySQL, 2nd Edition

  15. One-to-Many Relationship (continued) Figure 7-4 One-to-many relationship PHP Programming with MySQL, 2nd Edition

  16. Determining Requirements Some useful tools Use Case Diagram ERD Schema diagram

More Related