Class Notes For Oracle 12c: SQL, 3rd Edition

Preview (16 of 99 Pages)

100%

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 1 preview image

Loading page ...

Oracle12c: SQL1-1Chapter 1Overview of Database ConceptsAt a GlanceInstructor’s NotesChapter OverviewChapter ObjectivesInstructor NotesTroubleshooting TipsQuick QuizzesDiscussion QuestionsKey Terms

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 2 preview image

Loading page ...

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 3 preview image

Loading page ...

Oracle12c: SQL1-2Chapter OverviewIn this chapter,you will learn basic database concepts. You will learn how a databasemanagement system (DBMS) can be used to create, modify, and store data. Of particularimportance is the design process. The design process includes creatinganentity-relationshipmodel (E-Rmodel) and normalizing data through various stages. This chapter concludes with anoverview of the JustLee Booksdatabase that is referenced throughout the book.Chapter ObjectivesAfter completing this chapter, you should be able to do the following:Define database termsIdentify the purpose of a database management system (DBMS)Explain database design using entity-relationship models and normalizationExplain the purpose of a Structured Query Language (SQL)Understand how this textbook’s topics are sequenced and how the two sample databasesare usedInstructor NotesThe hands-on exercises at the end of this chapter focus on two topics: getting familiar with theJustLee Books database contents and developing Entity Relationship Diagrams.Thecase studyat the end of this chapter will be quite challenging for students brand new to databases.If this isthe case, you may wish to have students only address a portion of this design assignment.Pointout that this chapter serves only as an introduction to database design,and entire books andcourses exist for data modeling topics.The next chapter will present instructions for students to create the database using a providedscript.Students will need thecapability to create tables and perform DML activity to build theJustLee Books database.Please review the Instructor’s Setup Guideprovided in the Instructor Downloads areaforsuggestions in providing students access to an Oracle server.Database TerminologyIn a relational database model, data are stored in tables. The table represents a particular entity.The columns in a table represent attributes or characteristics about the entity, while the rowssignify an occurrence of the entity. The smallest unit of data is a character.A column is alsoreferred to as a field,and a row is also referred to as a record.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 4 preview image

Loading page ...

Oracle12c: SQL1-3Troubleshooting TipSet up a scenario of students/courses/faculty orpatients/doctors/procedures as a demonstration of the differentsteps in the design of a database. Forexample, all patients fill outa form during the first visit. Identifya character, a field, a record,etc.Quick Quiz1. What makes up a record?ANSWER:Agroup of related fields2. What is an attribute?ANSWER:Asingle element of data that is to be collected about an entity3. What is a field called in the physical database?ANSWER:Acolumn4. What is a record called in the physical database?ANSWER:Arow5. What is a file called in the physical database?ANSWER:AtableDatabase Management SystemA database is typically enveloped within a database management system,which provides avariety of functionality to create aproduction system environment.Functionality ranges frommultiuser access to backup capabilities.Database DesignThe Systems Development Life Cycle (SDLC) is a generic process that can serve as a guidelinefor creating various types of systems. It is a somewhat linear process;however, it is notuncommon in a large project for the steps to overlap. In addition, if a problem is discoveredduring the process, this may require the designer(s) to return to a previous step to determine theunderlying cause.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 5 preview image

Loading page ...

Oracle12c: SQL1-4Quick Quiz1. What type of problem can result from data redundancy?ANSWER: Inconsistency in the data (i.e., not all copies of the data are updated correctly)making the data unreliable2. What is the purpose of normalization?ANSWER:To reduce data redundancy3. What is the purpose of a primary key?ANSWER:To uniquely identify each record4. What is the starting point for determining what should be included in a database?ANSWER:Identifying the information that must come out of the database (i.e., itspurpose/objective)5. What is the significance of a many-to-many relationship?ANSWER:It cannot be directly included in the physical relational database.Entity-Relationship (E-R) ModelThe ability to retrieve data from a database depends on how the data is structured. The E-Rmodel is used to determine the different entities that will be depicted in the database. Once theentities are identified, the relationship among the entities must be determined. Any many-to-many relationships will need to be eliminated before the physical database is created. Usually,this is through the introduction of a bridging table.Each entity is typically represented in an E-Rmodel as a box.Relationships between tables arerepresented with lines indicating the type of relationship:one-to-one, one-to-many, and many-to-many.A large percentageof relationships will be one-to-many.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 6 preview image

Loading page ...

Oracle12c: SQL1-5Troubleshooting TipContinuing with the example from the previous section, identifyeach of the entities to be included in the database. Then identifyattributes to be collected about each entity to prepare for the nextsection. It will help the student if the output objectives of thedatabase are identified also.Database NormalizationThe normalization process is used to determine how the data should be structured withindatabase tables to avoid data anomalies and redundancy. Most databases are normalized to third-normal form (3NF). This requires that each table have a primary key and contain no partial ortransitive dependencies. Once the tables are normalized, the designer must ensure that data canbe reconstructed through the appropriate foreign keys.Troubleshooting TipUse one or two entities identified by the students and walk throughthe normalization process. Then divide the students into groups tocomplete the process with any remaining tables. Ensure that theoutput objectives can be achieved by including necessary foreignkeys.Structured Query Language (SQL)SQL is not a programming language that supports application logic such as conditionalprocessing or looping structures.It is a data sublanguage that is focused on data tasks,includingqueries, data modifications, and creating database objects.Every relational databasesupportsSQL use at some level.SQL standards are established by the ANSI and ISO industrycommittees.SQL*Plus is the database interface tool used in this text.Quick Quiz1. What is the purpose of SQL?ANSWER:It allows users to create database objects and enter, manipulate, and retrieve datafrom a database.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 7 preview image

Loading page ...

Oracle12c: SQL1-62. Who establishes the SQL standards?ANSWER: ANSI and ISO3. What is SQL*Plus?ANSWER: An interactive tool provided by Oracle 12cto issue SQL statements4. What is SQL?ANSWER: A data sublanguage5. How is SQL different from a programming language?ANSWER:It works with sets of data and can navigate tables.Databases Used in this TextbookThe basic assumption made by designers will ultimately determine the structure of the data. Thedatabase for JustLee Books is based on eight tables. The tables will allow a user to determinewhich books have been purchased by which customers, who published each book, the amountdue for each order, etc.Please review the Instructor’s AdditionalExample Databases document provided in theInstructor Downloads area for other database examples that may be used for practical tests oradditional exercises.Quick Quiz1. What is the primarykey for the BOOKS table?ANSWER: ISBN2. Identify a foreign key in the ORDERITEMS table.ANSWER: Both Order# and ISBN columns3. What is the purpose of the BOOKAUTHOR table?ANSWER: To eliminate the many-to-many relationship between the BOOKS and AUTHORtables4. How would the database structure be different if only one author was allowed to write eachbook?ANSWER: The BOOKAUTHOR bridging table would not have been necessary.5. What is the primary key for the ORDERITEMS table?ANSWER: The composite primary key of Order# + Item#

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 8 preview image

Loading page ...

Oracle12c: SQL1-7Troubleshooting TipHelp the students become familiar with thestructure of the JustLeeBooksdatabase by asking questions such as which author wrote aparticular book, which book(s) was ordered by a customer, etc.Topic SequenceMany SQL books initially focus on database queries and address database creation in laterchapters.This text initially focuses on the creation of a database to ensurethatstudentsunderstand the database structure,including constraints prior to accomplishing data manipulationor retrieval tasks.Software Used in this TextbookOracle12cprovides two client tools that can be used to connect to an Oracle database andcomplete the exercises in this text: SQL*Plus and SQL Developer. The figures in the text displaythe SQL Developer interface;however, either tool may be used. For those using previousversions of Oracle, you also may elect to use theInternet interface tool named iSQL*Plus. Beaware thatiSQL*Plus deprecated with Oracle11g.Discussion Questions1. Why go through the database design process? Wouldn’t it be simpler just to throw allofthedata in one table?2. Use the example scenario created in class and discuss some of the anomalies that would resultif the data were not normalized.Key TermsAmerican National Standards Institute (ANSI)—One of two industry-accepted committeesthat sets standards for SQL.bridging table—A table created to eliminate a many-to-many relationship between two tables.character—The basic unit of data. It can be a letter, number, or special symbol.column—In a relational database, fields are commonly represented as columns and may bereferred to as "columns."common column—A column that exists in two or more tables and contains equivalent data.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 9 preview image

Loading page ...

Oracle12c: SQL1-8common field—A column that exists in two tables and is used to “join” two tables.data redundancy—Refers to having the same data in different places within a database, whichwastes spaces and complicates database updates and changes.database—A collection of interrelated files.database management system (DBMS)—A generic term that applies to a software productthat allows users to interact with a database to create and maintain the structure of the database,and then to enter, manipulate, and retrieve the data it stores.entity—Any person, place, or thing with characteristics or attributes that will be included in adatabase. In the E-Rmodel, an entity is usually represented as a square or rectangle.Entity-Relationship (E-R) Model—A diagram that identifies the entities and datarelationships in a database. The model is a logical representation of the physical system to bebuilt.field—One attribute or characteristic of a database entity.file—A group of records about the same type of entity.first-normal form (1NF)The first step in the normalization process in which repeating groupsof data are removed from database records.foreign key—When a common column exists in two tables, it will usually be a primary key inone table and will be called a foreign key in the second table.lookup table—A table referenced by a foreign key constraintnormalization—A multistep process that allows designers to take the raw data about an entityand evolve the data into a form that will reduce a database’s data redundancy.primary key—A fieldthat serves to uniquely identify a record in a database table.record—A collection of fields describing the attributes of one database element. In PL/SQL, arecordis a composite datatype that can assume the same structure as the row being retrieved.row—A group of column values for a specific occurrence of an entity. In a database, recordsare commonly represented as rows.second-normal form (2NF)—The second step in the normalization process in which partialdependencies are removed from database records.SQL*Plus—A tool enabling users to interact with the database. Through SQL*Plus, users canenter SQL commands, set or alter environmental variables, display the structure of tables, andexecute interactive scripts.structured query language (SQL)—The industry standard for interacting with a relationaldatabase. It is a datasublanguage,and unlike a programming language, it processes sets of dataas groups and can navigate data stored within various tables.third-normal form (3NF)—The third step in the normalization process in which transitivedependenciesareremoved from database records.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 10 preview image

Loading page ...

Oracle12c: SQL1-9unnormalized—Refers to database records thatcontainrepeating groups of data (multipleentries for a single column).

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 11 preview image

Loading page ...

Oracle 12c: SQL2-1Chapter 2Basic SQL SELECT StatementsAt a GlanceInstructor’s NotesChapter OverviewChapter ObjectivesInstructor NotesTroubleshooting TipsQuick QuizzesDiscussion QuestionsKey Terms

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 12 preview image

Loading page ...

Oracle 12c: SQL2-2Chapter OverviewThe purpose of this chapter is to learn the basic SELECT statement used to retrieve data from adatabase table. The students will learn to use the SELECT clause to retrieve all columns, onecolumn, and multiple columns from a table specified in the FROM clause.In addition, studentslearn how to perform simple arithmetic operations and concatenation in the SELECT clause.Students will need to execute theJLDB_Build.sqlscript file. Each student should be assigned adifferent user name so the generated tables will be located in a different schema for each student.Each studentmust be granted sufficient privileges to execute statements to create tables andexecute queries.Chapter ObjectivesAfter completing this chapter, you should be able to do the following:Identify keywords, mandatory clauses, and optional clauses in a SELECTstatementSelect and view all columns of a tableSelect and view one column of a tableDisplay multiple columns of a tableUse a column alias to clarify the contents of a particular columnPerform basic arithmetic operations in the SELECT clauseRemove duplicate lists using either the DISTINCT or UNIQUE keywordUse concatenation to combine fields, literals, and other dataInstructor NotesSELECT Statement SyntaxEvery SELECT statement is required tohave a SELECT and FROM clause. Each statementclausebegins with a keyword. The SELECT clause is used to identify the column or columns tobe retrieved from a table. The name of the table is identified in the FROM clause.The structureof the SELECT statement is depicted in Figure 2-2.This chapter only addresses the SELECT andFROM clauses.Later chapters will address all of the remaining clauses.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 13 preview image

Loading page ...

Oracle 12c: SQL2-3Quick Quiz1. How do you retrieve all columns from a table without listing the column names?ANSWER: Use anasterisk in the SELECT clause2. How do you separate multiple columns listed in a SELECT statement?ANSWER: Use commas3. Are keywords case sensitive?ANSWER:No4. Are column names case sensitive?ANSWER:No5. Where do you identify the table containing the specified columns?ANSWER: In the FROM clauseTroubleshooting TipDemonstrate that SQLis not case sensitive in terms of thecommand keywords or column/table names when a command isexecuted. Also demonstrate the various methods of executing anSQL statement.Troubleshooting TipIdentify the two differentOracle SQL tool interfaces:the clientSQL*Plus and SQL Developer.Appendix B introduces theseinterfaces.Selecting All Data in a TableThe asterisk is used to represent all columns in a table. The asterisk is not a wildcard in thetraditional sense. It has a specific use in SQL commands. If the asterisk is used in the SELECTclause, the clause can contain no other column references.Selecting One Column from a TableTo select a specific column from a table, list the name of the column after the SELECT keyword.Note that the column name can be entered in uppercase, lowercase, or mixed case. However, thecolumn name is displayed in uppercase characters by default.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 14 preview image

Loading page ...

Oracle 12c: SQL2-4Selecting Multiple Columns from a TableIf more than one column name is specified in the SELECT clause of the SELECT statement,they must be separated by a comma. Spaces between the column names and commas will notaffect the results of the statement.Troubleshooting TipFor practice, have the students retrieve data from various tables.Start with selecting allofthe columns from a table, then just one,andthen several columns.Operationswithin the SELECT StatementUsing Column AliasesA column alias can be used to give a more descriptive heading to a column of data. It should belisted directly after the column name, without a comma. If the alias consists of spaces or specialsymbols, or to retain the case, enclose the alias in double-quotation marks. Otherwise, simply listthe column alias. The optional keyword AS can be used to denote the column alias.Using Arithmetic OperationsAny basic arithmetic operation can be performed,with the exception ofexponential operations.Oracle12cfollows the standard order of operations, which can be overridden using parentheses.The operation can be specified in the column list just like a column name. However, a columnalias should be assigned or the column heading will display the express in the output.Troubleshooting TipFor practice, have the students calculate the profit, profit margin%, etc.and include a column alias.Using DISTINCT and UNIQUETo suppress duplicate rows in the output, enter the DISTINCT or UNIQUE keyword after theSELECT command. The keyword will apply to all columns listed in the SELECT clause, eventhough it is entered only once in the clause.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 15 preview image

Loading page ...

Oracle 12c: SQL2-5Troubleshooting TipSelect a couple of columns from the BOOKS table and include theDISTINCT or UNIQUE keyword. Demonstrate that unless theentire row being displayed is identical, the same category namewill be listed several times.Creating ConcatenationColumns can be combined through the use of the concatenation operator. However, to includespaces or string literals, they must be enclosed in single quotation marks. Commas cannot beused in front of the concatenation operator or an error message will be returned.Quick Quiz1. What is required if a column alias contains a blank space?ANSWER: The alias must be enclosed in double quotation marks.2. What symbol is used for concatenation?ANSWER:Two vertical bars, ||3. What arithmetic operations can be used in a SELECT statement?ANSWER: *, /, +,-4. How can Oracle11gidentify a column alias without the AS keyword?ANSWER: There is no comma separating the column name from its alias.5. Why use a column alias?ANSWER: To provide a more descriptive column headingDiscussion Questions1. Discuss the link(s) between the SELECT and FROM clauses.2. Discuss case sensitivity and how it relates to the SELECT statement.Key Termscharacter field—A field composed of nonnumeric data. This field will not display a headinglonger than the width of the data stored in the field.

Class Notes For Oracle 12c: SQL, 3rd Edition - Page 16 preview image

Loading page ...

Oracle 12c: SQL2-6clause—Each section of a statement that begins with a keyword (SELECT clause, FROMclause, WHERE clause, etc.).column alias—Another name substituted for a column name. A column alias is created in aquery and displayed in the results.concatenation—The combining the contents of two or more columns or character strings. Twovertical bars, or pipes (||), instruct Oracle12cto concatenate the output of a query.keywords—Words used in a SQL query that have a predefined meaning to Oracle9i. Commonkeywords include SELECT, FROM, and WHERE.numeric column—A column composed of only numeric data. In output, the column willdisplay the entire column heading, regardless of the width of the field. (Also known as anumericfield.)projection—Choosing specific column(s) in a SELECT statement.query—A question posed to the database.relational database management system (RDBMS)—A software program used to create arelational database. It has functions that allow users to enter, manipulate, and retrieve data.string literal—Alphanumeric data, enclosed within single quotation marks, that instructs thesoftware to interpret “literally” exactly what has been entered and to show it in the resultingdisplay.syntax—The basic structure, pattern, or rules, for anSQL statement. For an SQL statement toexecute properly, the correct syntax must be used.

Preview Mode

This document has 99 pages. Sign in to access the full document!

Report

Study Now!

Document Details

Related Documents

Solution Manual For Oracle 12c: SQL, 3rd Edition

DevOps and Its Importance Study Notes

Veeam Certified Engineer VMCE-V12

Modules 1 - 3 Basic Network Connectivity and Commu

Notes CCNA

CCNA 200-301 Portable Command Guide 5th Edition 20

CCNA 200-301 Portable Command Guide 5th Edition 22

CCNA 200-301 Portable Command Guide 5th Edition 36

Foundations of Novell Networking Section 26

Modulo 7 DHCPv4

Company

Explore

Study Tools