Chapter 6 DATABASE DESIGN AND DEVELOPMENT A VISUAL

Chapter 6 DATABASE DESIGN AND DEVELOPMENT: A VISUAL APPROACH Raymond Frost – John Day – Craig Van Slyke Chapter 6 Creating Databases with Oracle Using SQL 1 Database Design and Development: A Visual Approach © 2006 Prentice Hall

Chapter 6 Oracle Data Types Exhibit 6 -1: Data Types Available in Oracle Database Design and Development: A Visual Approach © 2006 Prentice Hall 2

Chapter 6 Skydiving Member Database Table Design Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -2: Design and Data for the Skydiving Club Member Database 3

Chapter 6 Skydiving Member Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -2: Design and Data for the Skydiving Club Member Database 4

Chapter 6 Sky_Member Database Script Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -3: Script File for the Sky_Member Database 5

Chapter 6 Logging Into Oracle Source: Used by permission of Oracle Exhibit 6 -4: i. SQL*Plus Login Screen Database Design and Development: A Visual Approach © 2006 Prentice Hall 6

Chapter 6 i. SQL*Plus Work Screen Source: Used by permission of Oracle Exhibit 6 -5: i. SQL*Plus Work Screen Database Design and Development: A Visual Approach © 2006 Prentice Hall 7

Chapter 6 Executing a Script in i. SQL*Plus Source: Used by permission of Oracle Exhibit 6 -6: Script File Executed in Work Screen Database Design and Development: A Visual Approach © 2006 Prentice Hall 8

Chapter 6 Arcade Database Table Design Exhibit 6 -7: Create the Arcade Database Design and Development: A Visual Approach © 2006 Prentice Hall 9

Chapter 6 Arcade Database with Data Exhibit 6 -7: Create the Arcade Database Design and Development: A Visual Approach © 2006 Prentice Hall 10

Chapter 6 Arcade Database Script Exhibit 6 -8: Script File for the ARCADE Database 11 Database Design and Development: A Visual Approach © 2006 Prentice Hall

Chapter 6 Enrollment Database Script Exhibit 6 -9: Design of ENROLLMENT Database Design and Development: A Visual Approach © 2006 Prentice Hall 12

Chapter 6 Enrollment Data Exhibit 6 -9: Design of ENROLLMENT Database Design and Development: A Visual Approach © 2006 Prentice Hall 13

Chapter 6 Enrollment Data Exhibit 6 -9: Design of ENROLLMENT Database Design and Development: A Visual Approach © 2006 Prentice Hall 14

Chapter 6 Enrollment Data Exhibit 6 -9: Design of ENROLLMENT Database Design and Development: A Visual Approach © 2006 Prentice Hall 15

Chapter 6 Enrollment Data Exhibit 6 -9: Design of ENROLLMENT Database Design and Development: A Visual Approach © 2006 Prentice Hall 16

Chapter 6 Enrollment Data Exhibit 6 -9: Design of ENROLLMENT Database Design and Development: A Visual Approach © 2006 Prentice Hall 17

Chapter 6 Dropping and Creating Tables must be dropped in the opposite order from which they were created. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -10: Order for Dropping and Creating Tables 18

Chapter 6 Enroll Database Script: Part 1 Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -11: Script File to Create the Enroll Database 19

Chapter 6 Enroll Database Script: Part 2 Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -11: Script File to Create the Enroll Database 20

Chapter 6 Troubleshooting Reserved words cannot be used as field names. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -12: Reserved Words May Not Be Used as Field Names 21

Chapter 6 Troubleshooting Do not include a comma after the last field in the list. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -13: Including a Comma after the Last Field Name 22

Chapter 6 Troubleshooting Commas cannot be omitted in the field list. Database Design and Development: A Visual Approach © 2006 Prentice Hall Exhibit 6 -14: Omitting Commas in the Create Table Statement 23

Chapter 6 Troubleshooting There should not be a comma at the end of the field list. Exhibit 6 -15: Extra Comma in Column List Database Design and Development: A Visual Approach © 2006 Prentice Hall 24

Chapter 6 Troubleshooting There should not be a comma at the end of the value list. Exhibit 6 -16: Extra Comma in Value List Database Design and Development: A Visual Approach © 2006 Prentice Hall 25

Chapter 6 Troubleshooting Values in foreign key fields must match the corresponding primary key value. Exhibit 6 -17: Referential Integrity Violation Database Design and Development: A Visual Approach © 2006 Prentice Hall 26

Chapter 6 Practice Exercise 1 Exhibit 6 -18: Hospital Database Design and Development: A Visual Approach © 2006 Prentice Hall 27

Chapter 6 Practice Exercise 1 Exhibit 6 -19: Hospital Database Design and Development: A Visual Approach © 2006 Prentice Hall 28

Chapter 6 Practice Exercise 2 Exhibit 6 -20: Acme Database Design and Development: A Visual Approach © 2006 Prentice Hall 29

Chapter 6 Practice Exercise 2 Exhibit 6 -21: Acme Database Design and Development: A Visual Approach © 2006 Prentice Hall 30

Chapter 6 Practice Exercise 3 Exhibit 6 -22: Amazon Database Design and Development: A Visual Approach © 2006 Prentice Hall 31

Chapter 6 Practice Exercise 3 Exhibit 6 -23: Amazon Database Design and Development: A Visual Approach © 2006 Prentice Hall 32