Computer Programming 6 - 1st Periodical Examination

File Systems

• the method of organizing and retrieving files from a storage medium (hard drives, flash drives, CDs, etc.)

• used by personnel in any organization to track and monitor necessary data

A. Disadvantages in Using File Systems

• Lengthy development times

• Report generation and query processing may be slow

• System administration is complex

• Security and data sharing may present issues

• Programming the systems may be too extensive

Database Systems

• refer to an organization of components that define and regulate the collection, storage, management, and use of data within a database environment

• computerized systems whose overall purpose is to maintain information and make that information available on demand

What is a Database?

• a collection of both end-user data and metadata

• presents a more complete depiction of data

• basically works in a similar way as a typical file cabinet does

Metadata

Is self-describing data that contains characteristics and relationships that link the data found within the database itself.

End-user Data

Are raw facts of interest relevant to the end-user using the database.

5 Major Parts of a Database System

Hardware, Software, OS, DBMS Software, Application Programs

Hardware

Identifies all the system’s physical devices.

Software

refers to the collection of programs used by the computers within the database, of which are three 3 types:

• OS (Operating System) Software
• DBMS Software
• Application Programs

OS Software

Refers to the program that manages the hardware components which makes all other software run on a computer.

DBMS Software

Is the software that manages the database within the database system.

Application Program

Are mostly used to access and manipulate data in the DBMS, as well as manage the application environment (interfaces, menus, etc.) where data is accessed and manipulated.

Peopleware

Refers to the people who use the database under certain privileges:

• System Administrators
• Database Administrators (DBAs)
• Database Designers and Architects
• System Analysts and Programmers
• End Users

System Administrators

Oversee the general operations of the database system.

Database Administrators (DBAs)

Manage the use and functionality of the system.

Database Designers and Architects

System Analysts and Programmers

End Users

Use the application programs and, in turn, the database system, for the daily operations of an organization.

Procedures

Are the rules that govern the design and use of the database system by enforcing standards on how the business will be conducted within the organization.

Data

In its entirety, represents the collection of information, their relations and characteristics, within the database system.

Database Models

• Define the logical design of data that can be stored, organized, and manipulated in a database system.

• Provide a representation of the infrastructure offered by a particular database system.

2 Categories of Database Models

• Conceptual Model
• Implementation Model

Conceptual Model

Focuses on the logical nature of the data representation. It is concerned with what is represented in the database.

Implementation Model

Which places emphasis on how the data are represented in the database or how the data structures are implemented to represent what is modeled.

The Building Blocks of a Database Model

• Entities
• Attributes
• Relationships

• Constraints

Entities

Are anything about which data are to be collected and stored, such as a person, a place, a thing, or an event. It represents a particular kind of object in the real world that may either be a physical or conceptual object.

Attributes

Are the defining and/or important characteristics of an entity.

Relationships

Describe the association among entities (for example, a student - teacher relationship, where a student attends classes conducted by several teachers, and a teacher teaching several students). There are three (3) types of relationships:

• One-to-One Relationships (1:1)
• One-to-Many Relationships (1:*)

• Many-to-Many Relationships (*:*)

One-to-One Relationships (1:1)

Depict one entity having a relationship to another entity. For example, a student may be taught by only one teacher throughout a class.

One-to-Many Relationships (1:*)

Depict one entity having a relationship to multiple entities. For example, a teacher having multiple students in a class.

Many-to-Many Relationships (*:*)

Depict multiple entities having multiple relationships to multiple other entities. For example, students can learn many subjects, and many subjects may be applicable to many students.

Constraints

Are restrictions placed on the data in order to ensure data integrity. They are usually written in the form of rules.

Types of Database Models

• Hierarchical Database Model

• Network Model

• Relational Model

• Entity-Relationship Model (E-R Model)

• Object-Oriented Model

Hierarchical Database Model

Is used to manage large amounts of data for complex manufacturing projects, and has a basic logical structure of an up-side-down tree where parent entities can have several children and can be children of other parent entities.

Network Model

Was created to represent complex data relationships, improve database performance, and impose a database standard. It is very similar to the hierarchical model such that it also facilitates 1:* relationships. However, the network database also allows the child to have several parents.

Relational Model

Is a collection of relations visually represented by two-dimensional (2) tables having distinct names. It functions similar to a spreadsheet table with rows called records and columns called fields.

Entity-Relationship Model (E-R Model)

Yields a more graphical representation of entities and their attributes and relationships in a database structure via an entity-relationship diagram. This database model also introduced the three (3) types of relationships.

Object-Oriented Model

Stores data, relationships, and attributes within objects, which represent real-world entities. Objects are categorized into classes, share attributes and methods, and can inherit properties from parent classes.

Database Management Systems (DBMS)

• Part of the database system that manages the database structure and controls access to the data stored in the database.

• Can be a compiled collection of programs or a single program that contains all of the necessary functions.

Functions of a Database Management Systems (DBMS)

• allows data dictionary management
• allows data storage management
• allows data transformation and presentation
• implements security management
• provides access control for multiple users
• provides backup and recovery
• manages data integrity
• provides data access via query language
• provides special communication interfaces and routines

Data Dictionaries

• Also called the Metadata Repository.

• Centralized repositories of information about data such as meaning, relationships to other data, origin, usage, and format.

2 Main Types of Data Dictionaries

• Integrated Data Dictionaries
• Standalone Data Dictionaries

Integrated Data Dictionaries

Are those included as part of the DBMS.

Standalone Data Dictionaries

Are third-party dictionaries separate from the DBMS.

2 Classifications of Data Dictionaries

• Active Data Dictionaries
• Passive Data Dictionaries

Active Data Dictionaries

Are automatically updated by the DBMS by every database access, thus, keeping its access information updated.

Passive Data Dictionaries

Are not automatically updated and requires processing.

What is SQL?

• Stands for Structured Query Language

• A database sublanguage used in querying, updating, and managing relational databases

• Derived from an IBM research group that created Structured English Query Language (SEQUEL) in the 1970s.

• Can either be used in formulating interactive queries or be embedded in an application as instructions for handling data.

• Designed for both technical and non-technical users.

Major Components of SQL

• Data Manipulation Language (DML)
• Data Definition Language (DDL
• Data Control Language (DCL)

Data Manipulation Language (DML)

Is a module of the SQL language which allows you to retrieve, update, add, or delete data in a database.

Data Definition Language (DDL)

Enables you to create and modify the database itself.

Data Control Language (DCL)

Maintains the proper security for the database

CREATE DATABASE statement

Is used to create a database.

DROP DATABASE statement

Is used to delete a database.

ALTER TABLE statement

Allows for the modification of specific attributes of a table, such as addition and deletion of columns.

DROP TABLE statement

Is used to delete a specified table in a database.

SELECT clause

Is used to list the attributes desired in the result of a query, allowing retrieval of data in SQL.

FROM clause

Lists the relations to be scanned in evaluation of an expression.

WHERE clause

Consists of a predicate involving attributes of relations that appear in the FROM clause.

Aggregation

• A common programming term associated with the various methods in summarizing data

• Means to “combine into groups”

• Allows data to be manipulated into something that is not merely displayed

• Transforms data into “real” information: the pattern or context that is used as requirement in order to transform data into something relevant

Aggregate Function Keywords

• SUM keyword
• AVG keyword
• MIN keyword
• MAX keyword
• COUNT keyword

SUM keyword

Will add values of a specified column.

AVG keyword

Will find the average of all given values in a specified column.

MIN keyword

Finds and returns the lowest value in a column.

MAX keyword

Finds and returns the highest value in a column.

COUNT keyword

Will count and return the number of records in a column.

INSERT INTO Statement

• It inserts new data entries in a database table.
• It can be written in two (2) forms:

○ The first statement does not specify any column names and simply contains the values that will be put in their respective fields

○ The second statement specifies column names and the values that will be inserted in them.

UPDATE Statement

• It updates existing data in a database table.

• The SET clause defines which field is to be updated as a condition.

DELETE Statement

• It deletes rows/data entries in a table.

• It also uses the WHERE clause to find a given condition, and then delete the row containing that condition.

• Removing the WHERE clause will delete ALL the records/rows/entries in a table.

• Careful consideration must be given when deleting all records as this action cannot be undone once the statement is executed.

SQL Join Clauses

• These are used to combine records from two (2) or more tables in a database based on a common field/column.

• It has different types depending on the tables used, the data output required, and the way the tables are joined.

Inner Join

• It is a join statement that returns rows where there is at least one match in both tables.

• The primary key of one table must have a matching foreign key to the other table to combine with.

Outer Join

• It is considered as an extension of the inner join, but does not require each record in the two (2) joined tables to have a matching record.

• The joined table retains each record even if no other matching record exists.

• It can further be subdivided into other types of joins depending on which table(s) one retains the rows from (left, right, or both).

LEFT JOIN Statement

It returns all rows from the left table (the first table) even if there are no matches on the right table (the second table).

RIGHT JOIN Statement

It returns all rows from the right table (the second table) even if there are no matches on the left table (the first table).

FULL JOIN Statement

• Both tables are secondary (or optional).

• If rows are being matched in table A and table B, then all rows from table A are displayed even if there is no matching row in table B, and vice versa.

What are SQL Views?

• These are SELECT statements saved in the database.

• Once saved, these can be referred to the same as any table in the database.

• These do not contain data, but allow manipulation similar to tables as if the view was a real table with data.

Benefits of using Views

• Views can reduce complexity
• Views can increase reusability
• Views can properly format data
• Views can create calculated columns
• Views can be used to rename column names
• Views can create subsets of data
• Views can be used to enforce security restrictions

CREATE VIEW

• It creates a new view from existing database tables

• View is stored within the database.

• After a view has been created, it can be brought back by executing a SELECT statement together with its view name.

ALTER VIEW

It allows the editing or alteration of a created or existing view.

DROP VIEW

• It deletes a created or existing view

• The deleted view is basically deleted from the database; exercise caution when deleting unnecessary views.

Functional Dependency

• It describes the relationship between fields (columns) in a table

• It is a constraint between two (2) sets of fields from the database.

• Any field or attribute that provides functional dependency to another field is called the determinant.

Classifications of Functional Dependency

• Full Functional Dependency
• Partial Functional Dependency
• Transitive Functional Dependency

Full Functional Dependency

Indicates that if A and B are fields of a table, B is functionally dependent on A if B “is functionally dependent on A, but not on any proper subset of A”.

Partial Functional Dependency

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

Transitive Functional Dependency

Is a condition where 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, then C is transitively dependent on A via B.

Database Normalization

• This is the process of organizing data to minimize redundancy in the design of a relational database management system (RDBMS).

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

• This is a specific process that allows designers to turn unstructured data into a properly designed set of tables and data elements.

Goals of Normalization

• Eliminate data redundancy
• Eliminate insert, delete, and update anomalies.

3 Main Rules for Normalizing Data

To eliminate repeating data
To eliminate partial dependencies
To eliminate transitive dependencies