hsci 709
Download
Skip this Video
Download Presentation
HSCI 709

Loading in 2 Seconds...

play fullscreen
1 / 29

HSCI 709 - PowerPoint PPT Presentation


  • 114 Views
  • Uploaded on

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.

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 ' HSCI 709' - brett-case


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

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;

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

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\';

summary
Summary

Business Domain

Abstraction

we are here

Modeling

SQL

DB

Case Tools

Physical Model

Implemented

Physical Schema

Logical

Information Model

ad