Component 4 Introduction to Information and Computer Science

Topic V: Define the Basic Data Operations for Databases and How to Implement them

Implementation of a Relational Database Data Model • Use a small four-table database to

SQL Standards • Every DBMS is supposed to support standard SQL as defined by

SQL to Create the Database • This SQL example is for SQL Server, but

SQL to Create a Table CREATE TABLE Clinical. Trial. Testing. Institution ( Inst. Name

SQL to Create a Second Table CREATE TABLE Trial ( Trial. Code int NOT

Creating a Relationship To complete the relationship between two tables the DBMS must be

Slides: 8

Download presentation

Component 4: Introduction to Information and Computer Science Unit 6: Databases and SQL Lecture 5 This material was developed by Oregon Health & Science University, funded by the Department of Health and Human Services, Office of the National Coordinator for Health Information Technology under Award Number IU 24 OC 000015.

Topic V: Define the Basic Data Operations for Databases and How to Implement them in SQL • • SQL Standards Create the Database Create Tables Create Relationships Component 4/Unit 6 -5 Health IT Workforce Curriculum 2. 0/Spring 2011 Version 2

Implementation of a Relational Database Data Model • Use a small four-table database to show the creation of a database with its tables, attributes and relationships. Component 4/Unit 6 -5 Health IT Workforce Curriculum 2. 0/Spring 2011 Version 3

SQL Standards • Every DBMS is supposed to support standard SQL as defined by ANSI and ISO. • Basic SQL statements vary only slightly from one DBMS to another • Many of the more sophisticated DBMSs like SQL Server and Oracle differ once you get beyond basic SQL statements • We will look at some basic SQL statements Component 4/Unit 6 -5 Health IT Workforce Curriculum 2. 0/Spring 2011 Version 4

SQL to Create the Database • This SQL example is for SQL Server, but something similar would be used with other DBMSs CREATE DATABASE Drug. Trials ON (Name=Drug. Trials. dat, FILENAME=’C: componenet 4Drug. Trials. mdf’); • Other parameters could be specified, but this takes advantage of default values Component 4/Unit 6 -5 Health IT Workforce Curriculum 2. 0/Spring 2011 Version 5

SQL to Create a Table CREATE TABLE Clinical. Trial. Testing. Institution ( Inst. Name VARCHAR(15) NOT NULL, Inst. Contact INT NOT NULL, Inst. Street VARCHAR(15) NULL, Inst. City VARCHAR(12) NULL, Inst. State VARCHAR(2) NULL, Inst. Zip VARCHAR(9) NULL, CONSTRAINT Inst. Name. PK PRIMARY KEY(Inst. Name) ); Component 4/Unit 6 -5 Health IT Workforce Curriculum 2. 0/Spring 2011 Version 6

SQL to Create a Second Table CREATE TABLE Trial ( Trial. Code int NOT NULL, Trial. Start. Date datetime NOT NULL, Trial. End. Date datetime NULL, Trial. Results. Description varchar(100) NULL, Trial. Cost. Resource varchar(20) NULL, Drug. Name. FK varchar(20) NOT NULL, Inst. Name. FK varchar(15) NOT NULL, CONSTRAINT Trial. Code. PK PRIMARY KEY(Trial. Code) ); Component 4/Unit 6 -5 Health IT Workforce Curriculum 2. 0/Spring 2011 Version 7

Creating a Relationship To complete the relationship between two tables the DBMS must be informed about the primary to foreign key connection. ALTER TABLE Trial ADD CONSTRAINT CInst. Name. FK FOREIGN KEY(Inst. Name. FK) REFERENCES Clinical. Trial. Testing. Institution(Inst. Name) ON UPDATE CASCADE; Component 4/Unit 6 -5 Health IT Workforce Curriculum 2. 0/Spring 2011 Version 8