1 / 29

HSCI 709

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.

brett-case
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. HSCI 709 MySQL Lecture 13

  2. Review of SQL Commands

  3. Starting mysql

  4. Listing Existing Databases

  5. Creating a New Database

  6. Connecting to a Database

  7. Create Table Statement

  8. Deleting a Table

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

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

  11. Verifying the Tables

  12. 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);

  13. 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');

  14. 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);

  15. Referential Integrity There is no record in the CLNCIAN table with CLNCIAN_ID = 9005

  16. SELECT Statement

  17. SELECT {fields} Statement

  18. SELECT DISTINCT Statement

  19. SELECT COUNT Statement

  20. 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';

  21. CASE TOOLS

  22. Creating Tables in DBDesigner 4

  23. Creating Links

  24. Exporting the SQL Script

  25. The SQL script file(1)

  26. The SQL script file(2)

  27. Running the SQL Script

  28. Checking the Results

  29. Summary Business Domain Abstraction we are here Modeling SQL DB Case Tools Physical Model Implemented Physical Schema Logical Information Model

More Related