SQL Training Database Concepts

1. SQL Training Database Concepts

2. Lesson Objectives At the end of this lesson you will be able to: • Explain how data is stored in a relational database • Describe the purpose primary and foreign keys • Explain the role of SQL • Explain the role of referential integrity • Explain the role of indexes in improving database performance • Explain the purpose of the Oracle Catalog

3. Lesson Plan What is a Relational Database Introduction to Oracle What is SQL Creating Database Tables Preserving Data Integrity Improving Performance Using the Database Catalog

4. What is a Relational Database?

5. What is a Table? A table is a set of related data that contains: Columns, Fields, Attributes Rows, Records Product Rows, Records Columns, Fields, Attributes A Relational Database is a set of related Tables.

6. Relational Database Each table must follow these rules: • Each column has a unique name. • Entries in columns are single-valued (not a list of values). • Entries in columns are of the same kind. • Each row is unique. No two rows can have the same primary key value. SubscribedProduct Note: All rows are not shown. Primary Key

7. Relational Database Tables are related by common fields.

8. ProductType Relational DatabasePrimary & Foreign Keys Create Relationships pk Product DeliveryType pk fk pk SubscribedProduct pk fk fk

9. Normalization

10. Table Design - Normalization • Normalization is a set of guidelines (steps) used to optimally design a database to reduce redundant data and modification anomalies. • Advantages: • Data redundancy can be reduced. • Efficient disk space usage • Increase efficiency of database updates • Increase data integrity. • Minimize unwanted side effects of database updates, inadvertent deletions or insertion errors. • Disadvantages: • Requires more complicated SQL coding. • The more tables you have to join to retrieve the data the slower your query runs. • Can increase I/O and CPU overhead. Reduced database performance.

11. Vendor Vendor Contact Name Phone Component Price First Normal Form • A table is said to be in First Normal Form if: • The Table does not contain any repeating groups. • Each column has a unique name. • The order of the columns doesn’t matter. • No two rows may be identical. • Each cell must contain a single value. Vendor may supply many parts. • Vendor Table Data

12. Vendor Name Contact Name Phone Component Price Vendor VendorID VendorName VendorFirstName VendorLastName Phone VendorComponent VendorID Component Price First Normal Form • To put the table in 1NF we will break the data into two separate tables. VendorComponent Vendor

13. Vendor VendorID VendorName VendorFirstName VendorLastName Phone VendorComponent VendorID Component Price Foreign Key Primary Key Dependent/Child Table Parent Table First Normal Form • We added primary and foreign key fields to the tables. • Primary Key  Uniquely identifies a row, record in parent table. • Foreign Key  Matching row in another table, record in child table. • Primary – Foreign keys form the One to Many relationships between tables.

14. Second Normal Form • A Table is in Second Normal Form if it is in 1NF and every • Non-Keyed column is fully dependent on the entire primary key. Warehouse WarehouseID Name Manager Phone Fax Address City Province Postal Code Inventory ComponentID WarehouseID Description Weight StockedQty • Are these tables in Second Normal Form?

15. Second Normal Form • A Table is in Second Normal Form if it is in First Normal Form • And every Non-Keyed column is fully dependent on the entire primary key. Warehouse WarehouseID Name Manager Phone Fax Address City Province Postal Code Inventory ComponentID WarehouseID Description Weight StockedQty NO • The Description and Weight fields are dependent only on the ComponentID key. Neither have anything to do with WarehouseID. • How do we fix it?

16. Second Normal Form • A Table is in Second Normal Form if it is in First Normal Form • And every Non-Keyed column is fully dependent on the entire primary key. Warehouse WarehouseID Name Manager Phone Fax Address City Province Postal Code Inventory ComponentID WarehouseID Description Weight StockedQty Inventory ComponentID WarehouseID StockedQty Component ComponentID Description Weight • To put the tables in 2NF we put the Description and Weight fields into a new table named Component. • Description and Weight are fully dependent on the primary key of the Component table (ComponentID) • StockedQtyis fully dependant on the primary key of Inventory (ComponentID, WarehouseID).

17. Third Normal Form • A Table is in Third Normal Form if it is in Second Normal Form and every Non-Keyed Column is NOT dependent on any other Non-Keyed column. • Look for calculated fields. • Look for non-key fields that serve as a primary key in another table. • Product • ProductID • FuelSourceID • ProductCode • ProductDescription • ProductPrice • PowerRating • Voltage • Frequency • ShippingCost • Weight • The key, the whole key, and nothing but the key. • Shipping Cost = (Weight * Carrier Mileage rate * Mileage) + • Carrier Fixed Rate + Delivery Method Charge • Weight = Sum (Weight of all Components)

18. Can a database be Too Normalized? • When designing a database you need to keep two important objectives in mind: • 1.Keep your database free of modification anomalies • 2.Speed • Unfortunately, these are two competing objectives. • To make sure modification anomalies don’t corrupt your data, you should normalize your Tables. • However, separating data into different Tables will slow operations. • You need to use good judgment and common sense to arrive at a compromise to provide acceptable data integrity and performance.

19. Structure of an ORACLE Database

20. Database Object Hierarchy Database Tablespace Table Index View

21. users02.dbf system01.dbf users01.dbf Oracle Database Users Tablespace System Tablespace • Oracle uses the word database to mean the physical and logical structure that you use to store information. • In an oracle database you can create many different database objects (tables, views, indexes, etc.) All objects are stored in this one database. • Internally, the Oracle database is divided into a System Tablespace, and one or more User Tablespaces. • The System Tablespace holds the Oracle Catalog rows. The User Tablespace holds the rows of all User Tables.

22. Oracle Tablespace • Tables reside in tablespaces. Tablespaces reside in databases. • Oracle stores data logically in tablespaces and physically in datafiles. • A tablespace may contain multiple tables. • All databases have at least one tablespace, theSystem Tablespace, which Oracle creates automatically when you create the database.

23. What is Structured Query Language?

24. Structured Query Language • SQL is the language most commonly used to create and process data in relational databases. • SQL can be used with Access, DB2, MySQL, Oracle, MS SQL Server, Sybase, or any other relational database.

25. A Little Practice • To count the rows in a table, use the following command: Select count(*) from table_name; • To view the rows in a table, use the following command: Select * from table_name; • To view the definition of the table, use the following command: Describe table_name;

26. Creating Database Tables

27. Creating Tables • A table contains many columns. Each column must include a Data Type, Value Set (optional), and a Mandatory indicator. • Valid Oracle Data Types include Number, VarChar2, Char, and Date. • Value Sets can include itemized values and ranges. • Mandatory fields cannot be Null. Product Table AttributeData TypeMandatory ProductID INTEGER yes ProductTypeID INTEGER yes ProductName VARCHAR2(100) yes Note: Use the “describe” SQL command to display the table definition in the Oracle catalog: For example: describe Product;

28. Common Oracle Datatypes

29. Creating Tables Oracle uses Structured Query Language (SQL) to create tables. create table ProductType ( productTypeID INTEGER not null, productTypeName VARCHAR2(50) not null, constraint pkproductType primary key (productTypeID) ); create table Product ( productID INTEGER not null, productTypeID INTEGER not null, productName VARCHAR2(100) not null, constraint pkproduct primary key (productID), constraint fkproduct_producttype foreign key (productTypeID) references ProductType (productTypeID) );

30. Create Table As you create Oracle Tables and Indexes, you must provide a valid name for the object. These names must adhere to the following rules:  1. Names must be from 1 to 30 characters long.  2. Names can be case sensitive depending on deployment. Our Oracle server is not case sensitive.  3. A name must begin with an alphabetic character.  4. Names can only contain alphanumeric characters _, $, and #.  5. A name cannot be an Oracle reserved word. For a listing of Oracle reserved words: http://download.oracle.com/docs/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm

31. Preserving Data Integrity

32. Column Domain Constraints You can constrain column values in a table to a particular range. The column constraint will be validated when a column value is updated or inserted. create table NielsenUser ( nielsenUserID INTEGER not null, userFirstName VARCHAR2(50) not null, userLastName VARCHAR2(50) not null, userDepartment VARCHAR2(50) not null, userLocation VARCHAR2(50), hourlyRate NUMBER(7,2) not null, overtimeRate NUMBER(7,2) not null, workPhone VARCHAR2(25), mobilePhone VARCHAR2(25), email VARCHAR2(50), constraint pkNielsenUser primary key (nielsenUserID), constraint CHECK_hourlyRate Check (hourlyRate > 45));

33. Column Domain Constraints If you try to enter an hourly rate less than 45, you will receive an error.

34. Referential Integrity Child Table Parent & Child Table Parent Table Primary Key:ProductID Foreign Key:ModalityID Primary Key:CustomerTypeID Primary Key:CustomerID Foreign Key: CustomerTypeID Primary Key:UserID Foreign Key: CustomerID Referential Integrity “Thou shall not create orphans.” Every child (foreign key) must have a matching parent (primary key). You can not delete a parent if there is a matching child. You can not add a child record if you do not have a matching parent record. Referential Integrity helps to maintain data integrity.

35. Referential Integrity Parent Table: Customer Child Table: Users In the Customer table change customerName to “Avon Inc” for CustomerID 1. Delete UserID 1 from the Users Table. Add CustomerID 8 to the Customer Table. Allowed

36. Referential Integrity Parent Table: Customer Child Table: Users Change CustomerID 1 to CustomerID 2 in the Customer Table. Delete CustomerID 3 from the Customer Table. Add UserID 999 with a CustomerID 8 to the Users Table. Not Allowed

37. Adding Constraints create table Users ( userID INTEGER not null, customerID INTEGER not null, provinceID INTEGER not null, roleID INTEGER not null, street VARCHAR2(100), city VARCHAR2(50), postalCode VARCHAR2(25), firstName VARCHAR2(50), lastName VARCHAR2(50), phone VARCHAR2(25), email VARCHAR2(50), fax VARCHAR2(25), constraint pkusers primary key (userID), constraint fkuser_customer foreign key (customerID) references Customer (customerID));

38. Implementing Referential Integrity DELETE CASCADE When rows of a Parent Table are deleted, all associated Child Table rows are also deleted. create table Users ( userID INTEGER not null, customerID INTEGER not null, provinceID INTEGER not null, roleID INTEGER not null, street VARCHAR2(100), … fax VARCHAR2(25), constraint pkusers primary key (userID), constraint fkuser_customer foreign key (customerID) references Customer (customerID) on delete cascade); Is this a good thing to do?

39. Implementing Referential Integrity You can also alter the Tables and add the referential integrity rules. To Create the Primary Key and Foreign Key for the Customer and User Tables: ALTER TABLE Customer ADD CONSTRAINT pkCustomer PRIMARY KEY (CustomerID); ALTER TABLE Users ADD CONSTRAINT fkCustomer_Users FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID);

40. Using Primary and Foreign Keys When using primary and foreign keys remember : • Use the same field name for both the primary and foreign key • Names should be descriptive • Keep the same data types between primary and foreign keys • Keys should be numeric • Improve load performance by adding constraints after the data is loaded

41. Alter Table - Examples To add a column to the Product Table: ALTER TABLE Product ADD (IssueDate Date); To increase the column size to 60 characters: ALTER TABLE Product MODIFY (ProductNameVARCHAR(100)); To set the default value of a column. ALTER TABLE Incident MODIFY (IncidentStatusID INTEGER DEFAULT 1); To drop a referential integrity constraint on a table: ALTER TABLE Product DROP CONSTRAINT pkProduct CASCADE; Note: The CASCADE option drops any foreign keys that reference the primary key. ALTER TABLE Product DROP CONSTRAINT fkProduct_ProductType;

42. Improving Performance

43. Indexes for Performance Disk Space Requirements Locks during Update Primary Key Constraints Query Performance • Oracle provides two types of indexes: Unique and Non-unique. • Unique indexes enforce unique key constraints and improve query performance. • Non-unique indexes improve query performance. • Indexes are implemented internally as B-tree structures. Index rules include: • An Order By statement in a Select statement can reference any column in a table – whether or not the column is indexed. • The maximum number of columns in an index is 16. • An index does not store Null values. • An index can be created only for a Table, not a View.

44. CREATE INDEX ULastName on Users (LastName); <Jensen Jensen Branch Blocks Jensen Monroe Withrow <Blackman Blackman Harris Blackman Dobler Einstein Monroe Thomas Stevens Harris Janus Jensen Miller Withrow Wronski Adams Allen Leaf Blocks Jensen - RowID Miller - RowID Create Index Oracle uses B-Tree indexes that are balanced to equalize access time to any row. Branch blocks point to lower level index blocks. The lowest level index blocks (leaf blocks) contain the corresponding RowID that is used to locate the actual row.

45. Create Index CREATE UNIQUE INDEX idxCustomerName ON Customer (customerName); CREATE INDEX idxCity ON Users (city); • When you create an Index, Oracle automatically allocates the index data in the database. • Unique indexes enforce unique key constraints and improve query performance. • Non-unique indexes improve query performance.

46. Database CatalogDatabases are Self Describing

47. Oracle Catalog A Database is self-describing. All the information about the database is stored in the database. • The Catalog holds all database objects. • The Catalog is a set of Relational Tables. • The Catalog can be queried to gather information. • The Catalog can only be updated with DDL commands. Update, Insert and Delete SQL commands cannot be issued “directly” against the catalog.

48. Oracle Catalog - Describe DESCRIBE Customer;

49. Oracle Catalog DBA Catalog versus USER Catalog • For MANY of the examples we will look at, there is a DBA and a USER view. • For example: DBA_Catalog and USER_Catalog view. • As you would guess, the DBA views are only available if you have DBA privileges. • The type of data shown by each view is the same. The difference is that the DBA view will show the data for ALL users and the USER view will show the data only for the current user.

50. Oracle Catalog USER_CATALOG: Contains one row for every Table or View owned by the current user. SELECT * FROM User_Catalog; Note: Use this User_Catalog query to determine if all the Tables in the next workshop are created successfully.