Recent Changes

{cs165.png} The Portable Dental Clinic
Description: This portable clinic would be online and would collect all dental records from a certain clinic then store them in a database. It would include a homepage wherein the dentist can sign up or log in. Upon logging in, the dentist can view, update, and add records of patients.
records of histheir patients.
Team members:
Jobelle Anne Azur (2006-13245)

Week 11
(Mar. 11, 2011) - (Query Processing)
Query Processing
involes activities that retrieves data from the DB
transforms a query in a high level language into a correct and efficient execution strategy expressing in low-level language
Four Main Phases of Query Processing
Query Decomposition - high level query is converted into a relational algebra query
checks query if syntactically and semantically correct
Query Optimization
chooses an efficient execution strategy for processing the query
uses database statistics
Code Generation
phases that transforms the execution strategy into low-level operations
Runtime Query Execution
phase that executes the low level operations to retrieve from the database
Query Decomposition
first phase of any query processing
aims to transform high level language into relational alfebra query
checks for syntactic and semantic errors
Stages of Query Decomposition
Analysis
query is analyzed for syntax errors
verifies that the relation and attributes in the query are defined in the system catalog
Normalization - query is converted into a normalized form that can be easily manipulated
Semantic Analysis - rejects normalized queries that are incorrectly formulated or are contradictory
Simplification
Query Restructuring
All notes in this journal are either from JEDI Database Systems by Ma. Rowena C. Solamo or from CS165 Lectures.

Binary - a relation with two attributes
N-ary - relation with n attributes
Relational KeysSteps in Normalization
First Normal Form (FNF) - a relation is in FNF if it contains no repeating groups
Second Normal Form (SNF) - it is in SNF if it is in FNF and every nonkey attribute is fully functionally dependent on the primary key
Third Normal Form (TNF) - a relation is in TNF it is SNF and there are no transitive dependencies existing
Boyce-Codd Normal Form (BCNF) - a relation is in BCND if every determinant is a candidate key
Fourth Normal Form - a relation is in the fourth normal form if it is in BCND and contains no nontrivial multi-valued dependencies
Fifth Normal Form - if a relation has no join dependency
Transforming E-R Diagrams to Relations
Represent Entities
Primary key of the entity becomes the PK of the relation
value of PK must uniquely identify every row of a relation
PK should not be redundant. No attribute of the key can be deleted without destroying its unique identification
Represent Relationships
1:N relationship: place the primary key on the one side entity as a foreign key on the many side entity
M:N relationship: the relationship of the two entities becomes a relation
Unary relationship: the entity type is modeled as a relation
Is-A relationship: create a separate relation for the class and for each of the subclass
Many-to-many: create a new relation having the primary key
Normalize the Relations
Merging Relations
Merge relations that refer to the same entity
Week 6-7
(Jan. 25, 2011 - Feb. 3, 2011) - (Physical Database Design)

Week 4-5
(Jan. 11, 2011 - Jan. 20, 2011) - (The Relational Database Design)
The objectives of conducting Technical Reviews are to make sure the quality of the products to be delivered are of the best and expected quality of the clients. It is necessary to make sure that all errors are uncovered so that the engineers make necessary corrections to the said errors. Technical reviews can be formal or informal but there are certain guidelines to follow in conducting such a review.
Guidelines during a FORMAL TECHNICAL REVIEW
Before the Review
Producer of the product notifies the leader that the product is finished and needs review
Project leader contacts a review leader who generates copies of work product, and distributes it to other reviewers, evaluates product for readiness and establishes agenda for review meeting
During the Review
Producer introduces agenda and work product.
Producer (or designated reader) gives a walkthrough of the work product, reading the material while reviewers raise issues.
Recorder notes valid problems or errors discovered.
At end of review, all attendees vote-on what to do:
Accept product without further modification (RARE !)
Reject and Rework the product. Another review will be set up.
Correct minor errors. Accept upon review leader approval.
All review attendees sign-off, indicating participation in review and concurrence with review team’s findings.
After the Review
Recorder prepares review summary report which includes:
List of reviewers & role each played at the review
Date, name of work product reviewed, producer name
Reviewer prep time
Time elapsed in review
List of issues to be resolved with corresponding line #
Outcome/Resolution
Signatures of everyone involved (Sign-off)
Review reports placed under configuration management
Review Leader follows-up to ensure that open items are properly corrected according to resolution
What makes a good ERD (Data Model)
Completeness
Data Reusability
Stability
Flexibility
Elegance
Communication
Integration
Conflicting Objectives
Logical Database Design - process of trasforming conceptual data model into a logical data model.logical datbase model - design that conforms to the data model for a class of database management system.
4 types of logical databases
Hierarchical Model -records arranged in top-down structure, resembles an upside-down tree, oftenly uses the terms parent and child, child can only have on parent
Network Model - almost the same with hierarchical model except parent and child records cannot be distiguished, many to many relationship
Relational Model - data are represented in table form with rows and columns, associations are represented logically by the values that are stored within the columns.
Object-oriented Model - attributes are called the objects
RELATIONAL DATA MODEL-based on the mathematical concept of a relation which can be physically represented using a table
3 components of a relational model
Data Structure - collection of objects or relations that store data.
Data Manipulation - requires very powerful operations to manipulate the data stored.
Data Integrity - maintains the accuracy and consistency of data.
Relationships
Unary -a relation with only one attribute
Binary - a relation with two attributes
N-ary - relation with n attributes
Relational Keys
Week 6-7
(Jan. 25, 2011 - Feb. 3, 2011) - (Physical Database Design)

All notes in this journal are either from JEDI Database Systems by Ma. Rowena C. Solamo or from CS165 Lectures.
Week 1
(Nov. 23, 2010) - (Database Development Process and Components of Database Engines)

(Jan. 25, 2011 - Feb. 3, 2011) - (Physical Database Design)
Physical Database Design - process of mapping logical database structures into a set of physical database structure. (Example here.)
Decisions made during this stage have a major impact on data accessibility, response time, security, user-friendliness and similar factors.
Three Major Inputs to Physical Database Design
Logical database structure (Relational Schema)
User processing requirements
Characteristics of DBMS and other components of the operating environment
Business Rules (in the context of analysis phase) or Integrity Constraints (in the context of design phase) - specifications that preserves the integrity of the logical data model.
Some advantages of placing integrity constraints:
It provides faster application development with fewer errors.
It reduces maintenance effort and expenditures.
It promotes ease of use of the database.
Data Volume and Usage Analysis
Data Volume analysis - this involves using estimates of the database size to select physical storage devices and estimate the cost of storage.
Usage analysis - involves using estimates of usage paths or patterns to select file organizations and access methods to plan for the use of indexes and to plan a strategy for data distribution.
File Organization and Access Method
File Organization - technique for physically arranging the records of a file on a secondary storage device. It is a physical arrangement of data in a file into records and pages on secondary storage.
Two file organizations:
Sequential File Organization
Hashed File Organization
File Access Method - defines the steps involved in storing and retrieving records from a file
Three file access methods:
Sequential File Access Method
Indexed File Access Method
Random-access or Direct-access Method
Indexes - is a table that is used to determine the rows in a table that satisfy some condition.
We use indexes for applications that are used primarily to support data retrievals such as decision support applications.
Indexed Sequential Files
Indexed Sequential File Organization - records are stored sequentially by primary key value. This normally has a primary key, a separate index or indexes and an overflow area. (Example: phone director listing)
Indexed Non-sequential File Organization - records are stored non-sequentially, a full index is requires. (Example: books in the library)
Clustering Indexes - used when records in a file are often retrieved based on non-key attributes.
Multi-level indexing - attempts to reduce the range of searches. It treats the index like any other file, splits the index into a number of smaller indexes, and maintains an index to the indexes.
Denormalization - process of transforming normalized tables into unnormalized physical record specifications.
In general, this may:
combine columns from several tables together to form a physical record
partition a table into several physical records
do a combination of both
Common situations for which denormalization is considered:
Two entities with a one-to-one relationship.
A many-to-many relationship with nonkey attributes.
Reference data.
Week 8-9
(Feb. 15, 2011 - Feb. 24, 2011) - (Database Implementation and Database Connectivity Concepts)

aggregate functions
apply to a column or row
(Sample SQL statements here.)
Week 2
(Nov. 30, 2010) - (Data Analysis Phase)
Modeling Time-Dependent Data
In case of a time-dependent data, another entity can be created that would allow to keep track of time.
(Sample ERD here.)
Week 4-5
(Jan. 11, 2011 - Jan. 20, 2011) - (The Relational Database Design)
Week 6-7
(Jan. 25, 2011 - Feb. 3, 2011) - (Physical Database Design)
Physical Database Design - process of mapping logical database structures into a set of physical database structure. (Example here.)
Week 8-9
(Feb. 15, 2011 - Feb. 24, 2011) - (Database Implementation and Database Connectivity Concepts)
JDBC (Java Database Connectivity)
Java API that enables Java programs to execute SQL commands and interact with any SQL-compliant database
Design Pattern (Example here.)
two pattern views
Static view

Modeling Time-Dependent Data
In case of a time-dependent data, another entity can be created that would allow to keep track of time.
Source: JEDI Database Systems by Ma. Rowena C. Solamo
Week 4-5
(Jan. 11, 2011 - Jan. 20, 2011) - (The Relational Database Design)
Week 11
(Mar. 11, 2011) - (Query Processing)
All notes in this journal are either from JEDI Database Systems by Ma. Rowena C. Solamo or from CS165 Lectures.

Source: JEDI Database Systems by Ma. Rowena C. Solamo
Week 4-5
(Jan. 18,11, 2011 and- Jan. 20,
Week 6-7
(Feb. 1,(Jan. 25, 2011 and- Feb. 3,
Week 8-9
(Feb. 8,15, 2011 and- Feb. 10,24, 2011) -
JDBC (Java Database Connectivity)
Java API that enables Java programs to execute SQL commands and interact with any SQL-compliant database
Stored Function - computes scalar results and enforcing domain constraints.
Week 10
(Feb. 22, 2011 and Feb. 24,(Mar 1, 2011) -
Transaction Management
Three important services to ensure that the database is reliable and remains in a consistent state