chapter 1introduction to oracle9i: sql1 chapter 1 overview of database concepts
TRANSCRIPT
![Page 1: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/1.jpg)
Chapter 1 Introduction to Oracle9i: SQL 1
Chapter 1Overview of Database Concepts
![Page 2: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/2.jpg)
Chapter 1 Introduction to Oracle9i: SQL 2
Database Terminology
• Database – logical structure to store data
• Database Management System (DBMS) – software used to create and interact with the database
![Page 3: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/3.jpg)
Chapter 1 Introduction to Oracle9i: SQL 3
Database Components
• Character
• Field
• Record
• File
![Page 4: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/4.jpg)
Chapter 1 Introduction to Oracle9i: SQL 4
Database Components - Character
• Basic unit of data
• Can be a letter, number, or special symbol
![Page 5: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/5.jpg)
Chapter 1 Introduction to Oracle9i: SQL 5
Database Components - Field
• A group of related characters
• Represents an attribute or characteristic of an entity
• Corresponds to a column in the physical database
![Page 6: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/6.jpg)
Chapter 1 Introduction to Oracle9i: SQL 6
Database Components - Record
• A collection of fields for one specific entity
• Corresponds to a row in the physical database
![Page 7: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/7.jpg)
Chapter 1 Introduction to Oracle9i: SQL 7
Database Components - File
• A group of records about the same type of entity
![Page 8: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/8.jpg)
Chapter 1 Introduction to Oracle9i: SQL 8
Components Example
![Page 9: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/9.jpg)
Chapter 1 Introduction to Oracle9i: SQL 9
Review of Database Design
• Systems Development Life Cycle (SDLC)
• Entity-Relationship Model (E-R Model)
• Normalization
![Page 10: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/10.jpg)
Chapter 1 Introduction to Oracle9i: SQL 10
Systems Development Life Cycle (SDLC)
• Systems investigation – understanding the problem
• Systems analysis – understanding the solution
• Systems design – creating the logical and physical components
![Page 11: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/11.jpg)
Chapter 1 Introduction to Oracle9i: SQL 11
Systems Development Life Cycle (SDLC)
• Systems implementation – placing completed system into operation
• Systems maintenance and review – evaluating the implemented system
![Page 12: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/12.jpg)
Chapter 1 Introduction to Oracle9i: SQL 12
Entity-Relationship Model (E-R Model)
• Used to depict the relationship that exists among entities
![Page 13: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/13.jpg)
Chapter 1 Introduction to Oracle9i: SQL 13
E-R Model Symbols
![Page 14: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/14.jpg)
Chapter 1 Introduction to Oracle9i: SQL 14
Relationships
• The following relationships can be included in an E-R Model:– One-to-one
– One-to-many
– Many-to-many
![Page 15: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/15.jpg)
Chapter 1 Introduction to Oracle9i: SQL 15
One-to-one Relationship
• Each occurrence of data in one entity is represented by only one occurrence of data in the other entity
• Example: Each individual has just one Social Security Number (SSN) and each SSN is assigned to just one person
![Page 16: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/16.jpg)
Chapter 1 Introduction to Oracle9i: SQL 16
One-to-many Relationship
• Each occurrence of data in one entity can be represented by many occurrences of the data in the other entity
• Example: A class has only one instructor, but each instructor can teach many classes
![Page 17: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/17.jpg)
Chapter 1 Introduction to Oracle9i: SQL 17
Many-to-many Relationship
• Data can have multiple occurrences in both entities
• Example: A student can take many classes and each class is composed of many students
• Can not be included in the physical database
![Page 18: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/18.jpg)
Chapter 1 Introduction to Oracle9i: SQL 18
Example E-R Model
![Page 19: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/19.jpg)
Chapter 1 Introduction to Oracle9i: SQL 19
Normalization
• Determines required tables and columns for each table
• Multi-step process
• Used to reduce or control data redundancy
![Page 20: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/20.jpg)
Chapter 1 Introduction to Oracle9i: SQL 20
Unnormalized Data
Contains repeating groups in the Author column in the BOOKS table
![Page 21: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/21.jpg)
Chapter 1 Introduction to Oracle9i: SQL 21
First-Normal Form (1NF)
• Primary key is identified
• Repeating groups are eliminated
![Page 22: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/22.jpg)
Chapter 1 Introduction to Oracle9i: SQL 22
First-Normal Form (1NF)
ISBN and Author columns together create a composite primary key
![Page 23: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/23.jpg)
Chapter 1 Introduction to Oracle9i: SQL 23
Composite Primary Key
• More than one column is required to uniquely identify a row
• Can lead to partial dependency - a column is only dependent on a portion of the primary key
![Page 24: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/24.jpg)
Chapter 1 Introduction to Oracle9i: SQL 24
Second-Normal Form (2NF)
• Partial dependency must be eliminated– Break the composite primary key into
two parts, each part representing a separate table
![Page 25: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/25.jpg)
Chapter 1 Introduction to Oracle9i: SQL 25
Second-Normal Form (2NF)
BOOKS table in 2NF
![Page 26: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/26.jpg)
Chapter 1 Introduction to Oracle9i: SQL 26
Third-Normal Form (3NF)
Publisher contact name has been removed
![Page 27: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/27.jpg)
Chapter 1 Introduction to Oracle9i: SQL 27
Summary of Normalization Steps
• 1NF: eliminate repeating groups, identify primary key
• 2NF: table is in 1NF and partial dependencies eliminated
• 3NF: table is in 2NF and transitive dependencies eliminated
![Page 28: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/28.jpg)
Chapter 1 Introduction to Oracle9i: SQL 28
Linking Tables
• Once tables are normalized, make certain tables are linked
• Tables are linked through a common field
• A common field is usually a primary key in one table and a foreign key in the other table
![Page 29: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/29.jpg)
Chapter 1 Introduction to Oracle9i: SQL 29
![Page 30: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/30.jpg)
Chapter 1 Introduction to Oracle9i: SQL 30
JustLee Books’ Database
Assumptions– No back orders or partial shipments– Only US addresses– Shipped orders are purged (deleted) at the end
of the month
![Page 31: Chapter 1Introduction to Oracle9i: SQL1 Chapter 1 Overview of Database Concepts](https://reader030.vdocuments.us/reader030/viewer/2022032806/56649efd5503460f94c11c6d/html5/thumbnails/31.jpg)
Chapter 1 Introduction to Oracle9i: SQL 31
Structured Query Language (SQL)
• Data sublanguage
• Used to:– Create or modify tables– Add data to tables– Edit data in tables– Retrieve data from tables