Week 6 (Wednesday and Friday)

Week 6 (Wednesday and Friday) - Introduction to SQL Using...

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

View Full Document Right Arrow Icon
8/17/2009 1 Introduction to SQL Using and manipulating the data you stored Session 6 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 What’s next? 6-2 What s next?
Image of page 1

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

View Full Document Right Arrow Icon
8/17/2009 2 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 ¾ 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 Many dialects exist
Image of page 2
8/17/2009 3 Examples of DDL 6-5 Examples of DML 6-6
Image of page 3

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

View Full Document Right Arrow Icon
8/17/2009 4 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 ¾ Before populating the tables, you need to h d b 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
Image of page 4
8/17/2009 5 Creating tables ¾ Steps to create a table 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 ¾ 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, V A d V Ph V St t V O d ) 6-10 V_Areacode, V_Phone, V_State, V_Order)
Image of page 5

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

View Full Document Right Arrow Icon
8/17/2009 6 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.) ¾ 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
Image of page 6