Mysql introduction
This presentation is the property of its rightful owner.
Sponsored Links
1 / 16

MySQL: Introduction PowerPoint PPT Presentation


  • 70 Views
  • Uploaded on
  • Presentation posted in: General

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

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


  • Login