Hsci 709
Download
1 / 29

HSCI 709 - PowerPoint PPT Presentation


  • 110 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