mysql q1

• client software
• server software
• SQL interface

• user<->Database Application<->Database Management System(DBMS)<->Database
• 

Database Management System

• 1) Have only a few tables
• 2) Are simple in design
• 3) Involve only a few computers
• 4) Support only a few users
• 5) MS-Access bundles the RDBMS and Application components together

• IBM DB2
• Oracle

• 1) Support several users simultaneously
• 2) Include more than one application
• 3) Involve multiple computers
• 4) Are complex in design
• 5) Have many tables
• 6) Have many databases

• Structured Query Language
• It is the standard language used for retrieval and manipulation of data from a relational database.

An SQL query or simply query is a programming statement. It is the method by which you retrieve and update information. Instead of pointing and clicking to add record, you issue an SQL Insert statement.

editing environment.

MySQL is a RDBMS that uses the SQL language. It is very popular for web applications. It is open source, scalable. It can handle 2 – 16 terabytes of data

• DML= Data Manipulation Language
• SELECT
• INSERT
• UPDATE
• DELETE

• DDL =Data Definition Language
• CREATE DATABASE, TABLE
• DROP DATABASE, TABLE

• 1.Capitalize all keywords – SELECT, not select
• 2.Use camel case for names -- hourlyPay, custID
• 3.Start each clause on a new line
• 4.Break long clauses into multiple lines
• 5.Indent continued lines
• 6.Place your name and a description of the script at the top of the file
• 7.Further comments are only required for code that is difficult to understand or where requested
• 8.Where possible, use part of table name in column name e.g. customer table column names: custID, custFName

• Block comment (more than one line)
• /*  
• stuff 
• more stuff
• */
• Single line comment
• -- This is a comment.  A space is required after the 2 hyphens.

MySQL is NOT case sensitive. It ignores white space and every statement ends with a semicolon   ;

records and fields

• one-to-many
• primary key and foreign key
• one vendor may have many invoices
• PK in vendor table
• FK in invoices table(same value for same vendor)

Enhanced Entity Relationship (EER) Diagram.

• A primary key is marked with a key icon.
• A dark diamond shows a foreign key.

• 1:Identify the data elements
• 2:Subdivide each element into its smallest useful components
• 3:Identify the tables and assign columns
• 4:Identify the primary and foreign keys
• 5:Review whether the data structure is normalized
• 6:Identify the indexes

The first name and last name are different components.

First (1NF) The value stored at the intersection of each row and column must be a scalar value, and a table must not contain any repeating columns.

• More tables, and each table has an index on its primary key. That makes data retrieval more efficient.
• Each table contains information about a single entity. That makes data retrieval and insert, update, and delete operations more efficient.
• Each table has fewer indexes, which makes insert, update, and delete operations more efficient.
• Data redundancy is minimized, which simplifies maintenance and reduces storage

Second (2NF)  Every non-key column must depend on the entire primary key.

Third (3NF) Every non-key column must depend only on the primary key.

• A table is in first normal form (1NF) when:
• 1.each column has a unique name
• 2.the order of rows and columns doesn’t matter
• 3.each column has a single data type
• 4.no two rows contain identical values
• 5.each column contains a single value
• 6.columns do not contain repeating groups

• -- create the database
• DROP DATABASE IF EXISTS ap;
• CREATE DATABASE ap;

• -- select the database
• USE ap;

• create table animal
• (
•    anID    INT    primary key,
•    anName    varchar(15),
•    anWeightkg    decimal(6,1),
•    anDOB date,
•    anPurchPrice    decimal(7,2),
•    anType    char(10),
•    constraint animalType_fk_animal
•       foreign key (anType)
•       references animalType (atType)
• );

In the SCHEMAS in the Navigator(must be refreshed) on the left.

In mysql, another name for a database.

right click the table name and pick Select Row...

• 1) from file pick Open SQL Script.
• 2) click lightning bolt execution icon.

• Right click on table name and choose Alter Table.
• Field names, datatypes, and other properties are listed below.

Right click on table name and choose Alter Table.

• 1) from File, New Query Tab
• 2) type SELECT * FROM tableName;(make sure the proper table is bold in the Navigator)
• Or
• SELECT *
• FROM database.tableName
• 3) Run

• insert into animalType values
• ('Capybara',2,'fruit'),
• ('Snake',25,'mice'),
• ('Ferret',10,'pellets'),
• ('Elephant',50,'hay');

• SELECT *
• FROM om.orders
• ORDER BY customer_id;