Using SubqueriesSubquery:

Is a query that is contained within other queries. Can be inserted into any clauses in the SELECT statement Is a SELECT statement that is inserted in another SELECT statement

Three main ways of specifying subqueries: A subquery that is a part of a table list specifies a data source A subquery that is a part of a condition specifies a single calculated column specifies a

criteria A subquery that is a part of a column list specifies a single calculated column

StudentNo StudentName

001 Angel Locsin002 Lee min Ho003 Yoona Ah

ORnum StudentNo AmPd PaymentMode1 001 16000.00 Cash2 002 3000.00 Installment3 002 5000.00 Installment4 002 1500.00 Installment5 003 10500.00 Cash

Example 1:SELECT StudentName,

Page 1ARBRA.14

Return to the table:Table 1- primary keyTable 2- foreign key

Inserted with other select statement

General SELECT statement:

SELECT columnlistFROM tablelist

WHERE condition

SELECTFROM WHERE

(“Ito ang ginagamit para ma auto ang subqueries “)

You can select column (“ kung kukuha ng table”)

Basic SyntaxAlmost the

same

(SELECTSUM(AmPd)From FeesWHERE StudentNo = Students.StudentNoAND PaymentMode = Installmet’)AS ‘Total Amount Paid by Installment’FROM Student

Output

StudentName Total Amount paid by InstallmentAngel Locsin NullLee min Ho 9500.00Yoona Ah Null

Making an example “From table galling sa Student table. Nag combine sila.”

Combined the 3 installment

3000.005000.0013.000

Example.2 Statement A

SELECT: StudentNameFROM: StudentsWHERE:StudentNo IN(SELECT StudentNo FROM Fees)WHERE PaymentMode = “Installment”

Output

Statement BSELECT: StudentNameFROM: Student FeesWHERE:Students.StudentNo = Fees.StudentNoAND Fees PaymentMode = InstallmentGroupBY Students.StudentNameStudents.StudentNo

Statement C

Page 2ARBRA.14

From the studentno from installment to fee “display who’s play the installment date group “niya kung sinu –sino ang naka

installment”

StudentNameYoona Ah

SELECT: StudentNameFROM: StudentsINNER JOIN: FeesON: Students.StudentNo = Fees.StudentNoWHERE: PaymentMode = InstallmentGroupBY: Students.StudentName, Students.StudentNo

Correlated Subquery

Uncorrelated subquery Refers for a subquery that are unrelated and completey independent of the

outer query.Correlated subquery

Refers to a subquery that are specifically related to the outer query.Example 1

Statement ASELECT: StudentName From StudentWHERE: (SELECT SUM(Am/Pd)FROM: FeesWHERE: Students.StudentNo =Fees.StudentNo =10000

Output

StudentName;Yoona Ah

Statement B

SELECT: StudentName. FROM StudentsLEFT JOIN: FeesON: Students.StudentNo,= Fees.StudentNoGROUPBY: StudentNo.StudentName“HAVING SUM(Am/Pd) < 10000”

Example 2

Statement A

Page 3ARBRA.14

Compute the Query.Unreated sa Table.

Select Statement and condition.“Same Lang by all handout”

Select___student number “yung ang query”

-Query Kaya lumabas na out

na 3 “Yoona Ah”

Group By : You can use the group by to avoid the repetition of row.

“Code maganda gamitin ang Statement A “

SELECT: StudentNameSELECT COUNT (StudentNo)FROM: Fees

WHERE: StudentNo = Fees.StudentNoAs: Number of payment TransactionFROM: StudentsORDER BY: Students.StudentNo

Outout

Statement B

SELECT: StudentNameCOUNT (StudentNo)As: Number of payment TransactionFROM: StudentsFROM: FeesLEFT JOIN: FeesON: StudentNo = Fees.StudentNoORDER BY: Students.StudentNo

Functional DependencyFunctional Dependency

Describe the relationship between attributes(Column) in a table

Classifications:

Full Functional dependency

Page 4ARBRA.14

Payment Installment and Transaction

Share as the old handout mas madaling intindihing ang Statement A as Always

A B

BIs functionally dependent on

A

Determinant

Student Name Number of Payment Transaction

Angel Lucsin 1

Lee min Ho 2

Yoona Ah 3

Indicate that it A and B are attributes (column) of a table. B is fully functionally dependent on A and B is functionally dependent on A. but not on any proper subset A.

Partial Functional dependency

Indicate that it A and B are attributes of a table. B is partially dependent on A if there is some attributes that can be removed from A and yet the dependency still holds

Transitive Functional dependency

Is a condition when A , B and C are attributes of a table such that. If A is functionally dependent on B and B is functionally dependent on C and C is transitively dependent on A via B

DataBase Normalization

Normalization

Is the process of organizing data to minimize redundancy in the design of a “relational database management system(RDBMS)”

Refers to a series of recommended steps taken to remove redundancy and update anomalies from database design.

Goal of normalization

Eliminate redundant date Eliminate Insert,Delete and Update anomalies

Three main rules for normalizing data Eliminate repeating data

o The rule is all about disallowing multivalued attributes Eliminate partial dependencies

o This refers to situations where the primary key for a table a composite primary key which means a key composed of multiple columns

Eliminate transitive dependencyo This refers to situational where in a column in the table refers to a non-key

column in referring to the primary of the table.

Normal FormsFirst Normal Form(1NF)

Page 5ARBRA.14

States that the domain of an attributes of an attributes must include only atomic values and that the value of any attribute in a row must be a single value from the domain of that attributes.

Second Normal Form (2NF)

A relation is in the second normal form 2NF if only if it is in 1NF and every non-key attributes is fully dependent on that primary key.

It is accomplished is fully dependences on the primary key dependencies.

To put a table 2NF requires. That you identify the primary key That you determine the relationship between the parts of the key and

the data.

Third Normal Form (3NF)

A relation is in the third normal form if and only if it is ?NF and every non-key attributes is non- transitively dependent on the primary key.

Unnormalized table:

StudentNo StudentName Program codes, ProgramDesc0618 Oy, Lee BSIT, BS in Information

Technology0679 Santos, Eric BSCS, BS in Computer Science

First Normal Form:

StudentNo StudentName ProgramCode ProgramDesc0618 Oy, Lee BSIT BS in Information

Technology0679 Santos, Eric BSCS BS in Computer

Science

Unnormalized Forms:

StudentNo StudentName Program codes, ProgramDesc0618 Oy, Lee BSIT, BS in Information

Technology0679 Santos, Eric BSCS, BS in Computer Science

Page 6ARBRA.14

Second Normal Form

StudentNo StudentName0618 Oy, Lee0679 Santos, Eric

Third Normal Form:

StudentNo StudentName0618 Oy, Lee0679 Santos,Eric0693 Perez, Vina

Boyce Codd Normal Form(BCNF)

Handle 3 problem with an entity that might have two primary keys Was named after Rey Boyce and Edgar Codd

Fourth Normal Form(4NF)

A relation is in the fourth normal form (4NF) if and only it a Multi-Valued Dependencies(MVD) exist in a table than all attributes of the tables are also functionally dependent on A.

Fifth Normal Form(5NF)

Is also called the projection-join normal form (P/NF) A relation is in the fifth normal form if and only if every join dependency in the

relation is a consequences of the candidate keys of the relation.

Alternative to Normal Form

Page 7ARBRA.14

ProgramCode ProgramDescBSIT BS in Information Technology

BSCS BS in Computer ScienceBSIT BS in Information TechnologyStudentNo ProgramCode

0618 BSIT0679 BSCS0693 BSIT

Star Scheme

Defines data entities in always that supports the view of the decision maker in a business as well as the data entities that reflect the important operational aspects of a business.

XML SCHEMA Benefits

It about the text that appears in elements to be constrained to be specific types. It shows user- defined types to created It allow uniqueness and foreign key constraints It is integrated with names spaces to allow different parts of a document to

confirm to different schemes.

XML and APIs Two standard models for programmatic manipulation of XML

DOMo Stands for Document Object Model

SAXo Stands for Simple APO for XML

DOM(Document Object Model)

Page 8ARBRA.14

Enrollment

EnrollmentNo

StudentID

ProgramID

SectionID

TotalFees

AmountPaid

SchoolYear

Programs

ProgramID

ProgramDecs

Student Info

Student ID

Student Name

Section

Section IDSection Name

Is a standard API (Application programming interface) which treats XML content as a tree. With each element represented by a node called DOMNode.

Some interface and methods in the java API for DOM. Node interface Element Attributes getElementsBy.TagName(name) getAttributes(name) Text node

SAX(Simple API for XML)

Is the record commonly used programming interface of XML Is an event model designed to provide a common interface between parsers and

applications Is built on the notion of event hardlers which consist of user- defined functions

associated with parsing events.

XML Document SchemaDTD Limitation

Individual text elements and attributes cannot be typed further DTD mechanism is difficult to use There is a lack of typing in IDs and IDREFSs

XML Schema

Is a newer from of DTD that more closely matches database features and terminology Can be separated files with an and extension Can also be embedded at the beginning of an XML document

Document type definition (DTD)

Specifies the elements (tags) the attributes (characteristic associated with each tags) and the elements relationship for an XML document

Can be a separate file with a DTD document Can also introduced at the beginning of XML document.

What is XML?XML

Page 9ARBRA.14

Stands for Extensible Markup Language Was designed to carry data and not to display data. Was created to structured, store, and transport information.

Advantage of using XML Tags makes XML message self documenting The format XML documents is not rigid XML allows nested structures A wide variety of tools are available to assist the processing of the XML format

XML Data’s StructureXML document

Is a tree which has a root node that contains various child nodes Five Kinds Of Nodes:

Root Is the abstract node that contains the entire XML document

Element Is simply a pair of matching start and end tags and all the test that

appears between them Text

Refers to the parsed character or any other kind of non-text node enclosed between two tags

CommentFormat

<-this is a comment-> Processing Instruction

Has a target and a value

Hierarchical PathHierarchical Path

Is an ordered sequencing of segments tracing the hierarchical structures

Preorder Traversal

Page 10ARBRA.14

Is also called the hierarchical sequence or the left list path,traces all the segments from the root starting at the left most segment

Hierarchical StructuresHierarchical Structures

Is an upside down “tree” that represents the basic logical structure of hierarchy Is composed of:

Segment Is the equivalent of a file system’s record type.

Parent Children

Root Segment

Level 1Segment

Level 2Segment

level 3Segment

Schools

Page 11ARBRA.14

Final Assembly

ComponentA

ComponentB

ComponentC

AssemblyA

AssemblyB

AssemblyC

Part EPart DPart CPart BPart A

Faculty

Staff

Hierarchy Chart Are created by the programmer to help document a program Convey the big picture of the modules for functions used in a program Used as representation of the information contained in a database

Data Dictionary

School NameSchool Address

School Type

DeptName DeptHead

Page 12ARBRA.14

FacultyName FacultyMajor Degree

Staff Name Staff Position

School

Department

FacultyStaff

Data Dictionary

Is centralized repository of information about data such as meaning relationship to other origin usage

Has two main type Integrated Standalone

Can be classified as: Active data dictionary Passive data dictionary

Basic Data Modeling ConceptsData model

Is the relatively simple representation of a more complex real world object

ANS/SPARC

Stands for American National Standard institute/ Standard Planning and Requirement Committee .

Defined three different data models based on their degree of abstraction:Conceptual ModelInternal ModelExternal Model

End User view End User view

Database Management SystemDatabase Management Systems

Page 13ARBRA.14

External Model External Model

Conceptual Model Designer a view.

Internal Model

Logical Independence.

DBMS view

Is a collection of programs that manages the database structure and controls across to the data stored in the database

Examples:MS SQLORACLEDB2

Functions of a DBMS Data dictionary management Data storage management Data transformation and presentation Security management Multiuser across control Backup and recovery management Database access languages and application programming interface

Query languageData definition language(DDL)Data manipulation language(DML)

Database communication interfaces

Database ModelEntity Relationship model

Was introduced by Peter Chen in 1976 Yield a graphical entities and their relationships in database structure Is usually represented by an entity relationship diagram Introduced the three types of relationship

One-to- many(1:*) relationshipMany-to-many(*:*)relationshipOne-to-one(1:1)relationship

Is based on the following components:EntityAttributesRelationship

Entity relationship

One-to-one

Page 14ARBRA.14

One –to-many

Many-to-many

Many-to-one

Page 15ARBRA.14

A1

A2

A4

A3

B1

B2

B3

B4

A1

A3

A2

B1

B2

B3

B4

A1

A2

A4

A3

B1

B2

B3

B4

A1

A2

A3

B1

B2

B3

Object-oriented model

Stores both data and their relationships in single structure known as an object Based on the component such as

ObjectIs abstraction of a real world entity.

Attributes Describe the properties of an object

ClassIs a collection of similar objects with shared attributes and method

Class hierarchyRefers to the arrangement of classes

InheritanceRefers to the ability of an object within the class hierarchy to inherit the attributes end methods of the classes above it.

Basic building blocks of a Database Model

EnityIs anything about which data are to be collected and stored

AttributeIs the characteristic of an entity

RelationshipDescribes the association among entities. TYPES

One-to- many(1:*) relationshipMany-to-many(*:*)relationshipOne-to-one(1:1)relationship

Constraints

Page 16ARBRA.14

A3 B3

Is a restriction placed on the data

Hierarchical Model

Was develop in the 1960s Is used to manage large amounts of data for complex manufacturing projects Has a basic logical structure represented by an upside down tree

Network Model

Was created to represent complex data relationship improve database performance and impose a database standard

Was defined by the Conference on the Data System Language (CODSYL). Conference on the Data System Language (CODSYL). Established database standards Created the Database Task Group (DBTG) in the 1980s

the Database Task Group (DBTG) was charged to create standard specifications for an environment that would facilitate database

creation and data manipulation

Relational Model

Was introduced in 1970 by E.F Codd of IBM Is collection of relations or two dimensional tables having district names.

Database System Five major parts of a database System. Hardware Software Operating system Software Application Analysis and Programming

People

System Administrations Database Administrators Database designers System analysis and Programmers End user

Procedure

Page 17ARBRA.14

data

DatabaseDatabase

Is a shared integrated computer structure that stores a collection of

End user data

Row facts of interest to the end user

Mata data

Data about data through which the end user data are integrated and managed

AdvantagesReduced data redundancy Data integrityData independenceData securityData consistencyEasier use of dataLess storage

DisadvantagesComplexityExpenseVulnerabilitySizeTraining codesCompatibilityLooking in of technologyLack of lower level control

Database ModelDatabase model

Page 18ARBRA.14

Is a collection of logical constructs used to represent the data structure and the data relationships found within the database.

Categories: Conceptual model

Focuses on the logical nature of the data representationExample: entity relationship (E.R) model

Implementation modelPlaces emphasis on how the database or how the data are represented in the database or how the the data structures are implemented to represent what is modeled

Example Hierarchical database model Network database model Relational database model Object-Oriented database model

File SystemsFile System

Are used by a manager of any small organization to track necessary data

Disadvantages of a files systemLengthy development timeDifficulty of getting quick answerComplex system administrationLack of security and limited data sharingExtensive programming

Database SystemDatabase system

Is a computerized system whose overall purpose is to maintain and make that information available on demand

Example Computerized recoding keeping system Electronic filing cabinet Repository for a collection of computerized data files

Page 19ARBRA.14

Make use of the database Select rows from the relation

Creates a new(smaller) relation by extracting records that consist of an attributes meeting a particular condition

Derive new attributes

Involves the manipulation of the existing data plus some additional parameter (it necessary) to derive new data

Index or sort row

Indexing is the logical ordering of rows in a relation according to some key Sorting is the physical ordering of a relation

Calculate total and performance measures

Done when the proper subset of data is defined and the rows of the relation are ordered in the required way

Present data

Final step in the retrieval of data

Page 20ARBRA.14