Mysql introduction
Download
1 / 16

MySQL: Introduction - PowerPoint PPT Presentation


  • 93 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' MySQL: Introduction' - alden-hinton


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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
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


Introduction to databases1
Introduction to Databases

Figure 7-1 Employee directory database

PHP Programming with MySQL, 2nd Edition


Mysql skills
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


A brief word about regexp
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


Sql interactive demonstrator
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


To do
To Do

Connect to SID

Use your “album” database

Command: show tables;

Command: describe album;

Now use your “scratch” database


To do1
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.


To do2
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?


Understanding relational databases
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


Understanding relational databases continued
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


One to one relationships
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


One to many relationship
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


One to many relationship continued
One-to-Many Relationship (continued)

Figure 7-3 Table with redundant information(This is usually bad)

PHP Programming with MySQL, 2nd Edition


One to many relationship continued1
One-to-Many Relationship (continued)

Figure 7-4 One-to-many relationship

PHP Programming with MySQL, 2nd Edition


Determining requirements
Determining Requirements

Some useful tools

Use Case Diagram

ERD

Schema diagram


ad