Hsci 709
This presentation is the property of its rightful owner.
Sponsored Links
1 / 29

HSCI 709 PowerPoint PPT Presentation


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

HSCI 709. MySQL Lecture 13. Review of SQL Commands. Starting mysql. Listing Existing Databases. Creating a New Database. Connecting to a Database. Create Table Statement. Deleting a Table. A Simple Information Model. PHYSICAL TABLES. PAT. PAT_CLNCIAN_ASSOC. PAT_ID INT. PAT_ID INT.

Download Presentation

HSCI 709

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


Hsci 709

HSCI 709

MySQL

Lecture 13


Review of sql commands

Review of SQL Commands


Starting mysql

Starting mysql


Listing existing databases

Listing Existing Databases


Creating a new database

Creating a New Database


Connecting to a database

Connecting to a Database


Create table statement

Create Table Statement


Deleting a table

Deleting a Table


A simple information model

A Simple Information Model

PHYSICAL TABLES

PAT

PAT_CLNCIAN_ASSOC

PAT_ID INT

PAT_ID INT

CLNCIAN_ID INT

PAT_FNM VARCHAR(20)

PAT_LNM VARCHAR(35)

PAT_CLNCIAN_IDX INT

ASSOC_BEGIN_DT DATE

ASSOC_END_DT DATE

ASSOC_CAT_CD TINYINT

CLNCIAN

CLNCIAN_ID INT

CLNCIAN_NM VARCHAR(250)


Sql commands

SQL Commands

CREATE TABLE PAT (

PAT_ID INT NOT NULL PRIMARY KEY,

PAT_FM VARCHAR(20),

PAT_LNM VARCHAR(35)

)

TYPE=INNODB;

CREATE TABLE CLNCIAN(

CLNCIAN_ID INT NOT NULL PRIMARY KEY,

CLNCIAN_NM VARCHAR(250)

)

TYPE=INNODB;

CREATE TABLE PAT_CLNCIAN_ASSOC(

CLNCIAN_ID INT NOT NULL,

PAT_ID INT NOT NULL,

PAT_CLNCIAN_IDX INT NOT NULL,

ASSOC_BEGIN_DT DATE,

ASSOC_END_DT DATE,

ASSOC_CAT_CD TINYINT NOT NULL,

INDEX(CLNCIAN_ID),

INDEX(PAT_ID),

PRIMARY KEY(CLNCIAN_ID, PAT_ID, PAT_CLNCIAN_IDX),

FOREIGN KEY (CLNCIAN_ID) REFERENCES CLNCIAN(CLNCIAN_ID),

FOREIGN KEY (PAT_ID) REFERENCES PAT(PAT_ID)

)

TYPE=INNODB;


Verifying the tables

Verifying the Tables


Loading data into pat table

Loading Data into PAT Table

INSERT INTO PAT(PAT_ID,PAT_FNM,PAT_LNM) VALUES

(45671,'JOHN','DOE'),

(45672,'JENNY','DOE'),

(45673,'CINDY','FINNEGAN');

INSERT INTO PAT(PAT_LNM,PAT_FNM,PAT_ID) VALUES

('MARTINEZ','MANUEL',45674),

('VALLARES','MARGARITA',45675);


Loading data into clncian table

Loading Data into CLNCIAN Table

INSERT INTO CLNCIAN(CLNCIAN_ID,CLNCIAN_NM) VALUES

(8998,'DR. FERNANDO DE LA ROSA'),

(8999,'DR. DIETLINDE WEINSTEIN'),

(9000,'DR. PIERO LUIGI DELLA MIRANDOLA');


Loading data into pat clncian assoc table

Loading Data intoPAT_CLNCIAN_ASSOC Table

INSERT INTO PAT_CLNCIAN_ASSOC(CLNCIAN_ID, PAT_ID,

PAT_CLNCIAN_IDX, ASSOC_BEGIN_DT,

ASSOC_END_DT, ASSOC_CAT_CD) VALUES

(9000,45671,1,'2001-02-19','2003-11-21',3),

(9000,45671,2,'2004-05-01','2005-01-31',1),

(8998,45671,1,'2003-11-21','2004-04-30',3),

(8999,45672,1,'1999-01-01','1999-12-31',2),

(8998,45674,1,'2001-09-13','2001-12-31',2),

(8999,45673,1,'1998-11-08','2000-07-24',1),

(8998,45675,1,'1997-03-01','',3);


Referential integrity

Referential Integrity

There is no record in the CLNCIAN table with CLNCIAN_ID = 9005


Select statement

SELECT Statement


Select fields statement

SELECT {fields} Statement


Select distinct statement

SELECT DISTINCT Statement


Select count statement

SELECT COUNT Statement


Joins

JOINS

SELECT PAT_FNM, PAT_LNM, CLNCIAN_NM,ASSOC_BEGIN_DT

FROM PAT, CLNCIAN, PAT_CLNCIAN_ASSOC

WHERE PAT.PAT_ID = PAT_CLNCIAN_ASSOC.PAT_ID AND

CLNCIAN.CLNCIAN_ID = PAT_CLNCIAN_ASSOC.CLNCIAN_ID AND

PAT.PAT_LNM = 'DOE';


Case tools

CASE TOOLS


Creating tables in dbdesigner 4

Creating Tables in DBDesigner 4


Creating links

Creating Links


Exporting the sql script

Exporting the SQL Script


The sql script file 1

The SQL script file(1)


The sql script file 2

The SQL script file(2)


Running the sql script

Running the SQL Script


Checking the results

Checking the Results


Summary

Summary

Business Domain

Abstraction

we are here

Modeling

SQL

DB

Case Tools

Physical Model

Implemented

Physical Schema

Logical

Information Model


  • Login