Week 6 (Wednesday and Friday)

Week 6 (Wednesday and Friday) - 8/17/2009 1 Introduction to...

Info iconThis preview shows pages 1–8. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 8/17/2009 1 Introduction to SQL Introduction to SQL Using and manipulating the data you stored Session 6 Checkpoint Checkpoint What we have learnt so far Why database design The tool for capturing business rules (ER diagrams) Defining relations (normalization) Data can now be stored Whats next? 6-2 8/17/2009 2 Using and manipulating data Using and manipulating data Relational model Model defines the table concept Relational algebra is the manipulator Implementation of relational model Tables in DBMS SQL for the relational algebra 6-3 About SQL About SQL Structured Query Language (SQL) Commands fall into two categories Data definition language (DDL) Data manipulation language (DML) Characteristics of SQL Non-procedural SQL is now an ANSI standard 6-4 SQL is now an ANSI standard Many dialects exist 8/17/2009 3 Examples of DDL Examples of DDL 6-5 Examples of DML Examples of DML 6-6 8/17/2009 4 From relations to tables From relations to tables Two step process: create and populate Create communicates the table structures to the DBMS using SQL SQL is used to insert/delete/update records into DBMS The internal model DBMS creates the physical structure on 6-7 physical storage device Varies depending on the DBMS being used (Oracle, MS Access, MS SQL Server etc.) Creating database Creating database Before populating the tables, you need to create the database A database is referenced by a name Contains a group of tables, indexes, meta-data, etc. CREATE DATABASE database-name 6-8 DROP DATABASE database-name 8/17/2009 5 Creating tables Creating tables Steps to create a table Convert ER diagram to relations by Convert ER diagram to relations by normalization Decide the attributes domain Issue SQL statements CREATE TABLE table-name ( column-name data type 6-9 data-type, ) Example Example Two tables to be created: Product (P_Code , P_Descript, P_Indate, P_Onhand, P_Min, P_Price, P_Discount, V_Code ) Vendor (V_Code , V_Name, V_Contact, 6-10 V_Areacode, V_Phone, V_State, V_Order) 8/17/2009 6 Example (cont.) Example (cont.) Creating table command CREATE TABLE Vendor ( V_Code INTEGER NOT NULL UNIQUE, V_Name VARCHAR(35) NOT NULL, V_Contact VARCHAR(15) NOT NULL, V_Areacode CHAR(4) NOT NULL, V_Phone CHAR(8) NOT NULL, V_State CHAR(3) NOT NULL, V_Order CHAR(1) NOT NULL, column PRIMARY KEY ( V_Code )); 28 July 2009 11 T2-2009 column name Example (cont.) Example (cont.) Creating table command data type CREATE TABLE Vendor ( V_Code INTEGER NOT NULL UNIQUE, V_Name VARCHAR(35) NOT NULL, V_Contact VARCHAR(15) NOT NULL, V_Areacode CHAR(4) NOT NULL, V_Phone CHAR(8) NOT NULL, V_State CHAR(3) NOT NULL, V_Order CHAR(1) NOT NULL, PRIMARY KEY ( V_Code )); 28 July 2009 12 T2-2009 8/17/2009 7 Example (cont.) Example (cont.) Creating table command CREATE TABLE Vendor ( V_Code INTEGER NOT NULL UNIQUE, V_Name VARCHAR(35) NOT NULL, V_Contact VARCHAR(15)...
View Full Document

Page1 / 47

Week 6 (Wednesday and Friday) - 8/17/2009 1 Introduction to...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online